1 function ora_complexity_check
2 (password varchar2,
3 chars integer := null,
4 letter integer := null,
5 upper integer := null,
6 lower integer := null,
7 digit integer := null,
8 special integer := null)
9 return boolean is
10 digit_array varchar2(10) := '0123456789';
11 alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
12 cnt_letter integer := 0;
13 cnt_upper integer := 0;
14 cnt_lower integer := 0;
15 cnt_digit integer := 0;
16 cnt_special integer := 0;
17 delimiter boolean := false;
18 len integer := nvl (length(password), 0);
19 i integer ;
20 ch char(1);
21 begin
22 -- Check that the password length does not exceed 2 * (max DB pwd len)
23 -- The maximum length of any DB User password is 128 bytes.
24 -- This limit improves the performance of the Edit Distance calculation
25 -- between old and new passwords.
26 if len > 256 then
27 raise_application_error(-20020, 'Password length more than 256 characters');
28 end if;
29
30 -- Classify each character in the password.
31 for i in 1..len loop
32 ch := substr(password, i, 1);
33 if ch = '"' then
34 delimiter := true;
35 elsif instr(digit_array, ch) > 0 then
36 cnt_digit := cnt_digit + 1;
37 elsif instr(alpha_array, nls_lower(ch)) > 0 then
38 cnt_letter := cnt_letter + 1;
39 if ch = nls_lower(ch) then
40 cnt_lower := cnt_lower + 1;
41 else
42 cnt_upper := cnt_upper + 1;
43 end if;
44 else
45 cnt_special := cnt_special + 1;
46 end if;
47 end loop;
48
49 if delimiter = true then
50 raise_application_error(-20012, 'password must NOT contain a '
51 || 'double-quotation mark which is '
52 || 'reserved as a password delimiter');
53 end if;
54 if chars is not null and len < chars then
55 raise_application_error(-20001, 'Password length less than ' ||
56 chars);
57 end if;
58
59 if letter is not null and cnt_letter < letter then
60 raise_application_error(-20022, 'Password must contain at least ' ||
61 letter || ' letter(s)');
62 end if;
63 if upper is not null and cnt_upper < upper then
64 raise_application_error(-20023, 'Password must contain at least ' ||
65 upper || ' uppercase character(s)');
66 end if;
67 if lower is not null and cnt_lower < lower then
68 raise_application_error(-20024, 'Password must contain at least ' ||
69 lower || ' lowercase character(s)');
70 end if;
71 if digit is not null and cnt_digit < digit then
72 raise_application_error(-20025, 'Password must contain at least ' ||
73 digit || ' digit(s)');
74 end if;
75 if special is not null and cnt_special < special then
76 raise_application_error(-20026, 'Password must contain at least ' ||
77 special || ' special character(s)');
78 end if;
79
80 return(true);
81 end;