In the Security Guide there is a section to assist you with the decisions about what rules you might want to have in place when users choose passwords, namely attributes like the minimum length of a password, the types of characters it must (and must not) contain, re-use of old passwords etc etc. The documentation refers to a number of pre-supplied routines that are now available in 12c to assist administrators. This is just a quick blog post to let you know that there is no “smoke and mirrors” going on here in terms of these functions. We’re implementing them in the same way that you might choose to build them yourself. In fact, you can readily take a look at exactly what the routines do because they are just simple PL/SQL code:
SQL> select text
2 from dba_source
3 where name in (
4 'ORA12C_STRONG_VERIFY_FUNCTION'
5 'ORA12C_VERIFY_FUNCTION',
6 'ORA_COMPLEXITY_CHECK',
7 'ORA_STRING_DISTANCE')
8 order by name, line;
TEXT
----------------------------------------------------------------------------------------------------------------------------------
function ora12c_strong_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
return boolean IS
differ integer;
begin
if not ora_complexity_check(password, chars => 9, upper => 2, lower => 2,
digit => 2, special => 2) then
return(false);
end if;
-- Check if the password differs from the previous password by at least
-- 4 characters
if old_password is not null then
differ := ora_string_distance(old_password, password);
if differ < 4 then
raise_application_error(-20032, 'Password should differ from previous '
|| 'password by at least 4 characters');
end if;
end if;
return(true);
end;
FUNCTION ora12c_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
db_name varchar2(40);
i integer;
reverse_user dbms_id;
canon_username dbms_id := username;
BEGIN
-- Bug 22369990: Dbms_Utility may not be available at this point, so switch
-- to dynamic SQL to execute canonicalize procedure.
IF (substr(username,1,1) = '"') THEN
execute immediate 'begin dbms_utility.canonicalize(:p1, :p2, 128); end;'
using IN username, OUT canon_username;
END IF;
IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1,
special => 1) THEN
RETURN(FALSE);
END IF;
-- Check if the password contains the username
IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
raise_application_error(-20002, 'Password contains the username');
END IF;
-- Check if the password contains the username reversed
FOR i in REVERSE 1..length(canon_username) LOOP
reverse_user := reverse_user || substr(canon_username, i, 1);
END LOOP;
IF regexp_instr(password, reverse_user, 1, 1, 0, 'i') > 0 THEN
raise_application_error(-20003, 'Password contains the username ' ||
'reversed');
END IF;
-- Check if the password contains the server name
select name into db_name from sys.v$database;
IF regexp_instr(password, db_name, 1, 1, 0, 'i') > 0 THEN
raise_application_error(-20004, 'Password contains the server name');
END IF;
-- Check if the password contains 'oracle'
IF regexp_instr(password, 'oracle', 1, 1, 0, 'i') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password differs from the previous password by at least
-- 3 characters
IF old_password IS NOT NULL THEN
differ := ora_string_distance(old_password, password);
IF differ < 3 THEN
raise_application_error(-20010, 'Password should differ from the '
|| 'old password by at least 3 characters');
END IF;
END IF ;
RETURN(TRUE);
END;
function ora_complexity_check
(password varchar2,
chars integer := null,
letter integer := null,
upper integer := null,
lower integer := null,
digit integer := null,
special integer := null)
return boolean is
digit_array varchar2(10) := '0123456789';
alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
cnt_letter integer := 0;
cnt_upper integer := 0;
cnt_lower integer := 0;
cnt_digit integer := 0;
cnt_special integer := 0;
delimiter boolean := false;
len integer := nvl (length(password), 0);
i integer ;
ch char(1);
begin
-- Check that the password length does not exceed 2 * (max DB pwd len)
-- The maximum length of any DB User password is 128 bytes.
-- This limit improves the performance of the Edit Distance calculation
-- between old and new passwords.
if len > 256 then
raise_application_error(-20020, 'Password length more than 256 characters');
end if;
-- Classify each character in the password.
for i in 1..len loop
ch := substr(password, i, 1);
if ch = '"' then
delimiter := true;
elsif instr(digit_array, ch) > 0 then
cnt_digit := cnt_digit + 1;
elsif instr(alpha_array, nls_lower(ch)) > 0 then
cnt_letter := cnt_letter + 1;
if ch = nls_lower(ch) then
cnt_lower := cnt_lower + 1;
else
cnt_upper := cnt_upper + 1;
end if;
else
cnt_special := cnt_special + 1;
end if;
end loop;
if delimiter = true then
raise_application_error(-20012, 'password must NOT contain a '
|| 'double-quotation mark which is '
|| 'reserved as a password delimiter');
end if;
if chars is not null and len < chars then
raise_application_error(-20001, 'Password length less than ' ||
chars);
end if;
if letter is not null and cnt_letter < letter then
raise_application_error(-20022, 'Password must contain at least ' ||
letter || ' letter(s)');
end if;
if upper is not null and cnt_upper < upper then
raise_application_error(-20023, 'Password must contain at least ' ||
upper || ' uppercase character(s)');
end if;
if lower is not null and cnt_lower < lower then
raise_application_error(-20024, 'Password must contain at least ' ||
lower || ' lowercase character(s)');
end if;
if digit is not null and cnt_digit < digit then
raise_application_error(-20025, 'Password must contain at least ' ||
digit || ' digit(s)');
end if;
if special is not null and cnt_special < special then
raise_application_error(-20026, 'Password must contain at least ' ||
special || ' special character(s)');
end if;
return(true);
end;
function ora_string_distance
(s varchar2,
t varchar2)
return integer is
s_len integer := nvl (length(s), 0);
t_len integer := nvl (length(t), 0);
type arr_type is table of number index by binary_integer;
d_col arr_type ;
dist integer := 0;
begin
if s_len = 0 then
dist := t_len;
elsif t_len = 0 then
dist := s_len;
-- Bug 18237713 : If source or target length exceeds max DB password length
-- that is 128 bytes, then raise exception.
elsif t_len > 128 or s_len > 128 then
raise_application_error(-20027,'Password length more than 128 bytes');
elsif s = t then
return(0);
else
for j in 1 .. (t_len+1) * (s_len+1) - 1 loop
d_col(j) := 0 ;
end loop;
for i in 0 .. s_len loop
d_col(i) := i;
end loop;
for j IN 1 .. t_len loop
d_col(j * (s_len + 1)) := j;
end loop;
for i in 1.. s_len loop
for j IN 1 .. t_len loop
if substr(s, i, 1) = substr(t, j, 1)
then
d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
else
d_col(j * (s_len + 1) + i) := LEAST (
d_col( j * (s_len+1) + (i-1)) + 1, -- Deletion
d_col((j-1) * (s_len+1) + i) + 1, -- Insertion
d_col((j-1) * (s_len+1) + i-1) + 1 ) ; -- Substitution
end if ;
end loop;
end loop;
dist := d_col(t_len * (s_len+1) + s_len);
end if;
return (dist);
end;
The good thing about this is that:
- the routines are transparent, which is how all good security models should be presented. Because then they are open to scrutiny and not reliant on any kind of obfuscation to be secure, and
- if the routines do not exactly meet your requirements, then you now have a well established starting point from which to build your own custom routines.
Of course, if you are just after a string distance function, you might be better off using the pre-supplied UTL_MATCH package.
and here is why I think that. I did a talk in Bangalore last week at the Oracle Code event there (which by the way was a wonderful event, so thanks to all that came along) on some SQL language techniques. After the talk, one of attendees came up to me, thanked me for the talk and said this (I’m paraphrasing):
These will not help you in any way.