Enforce Password Complexity for users in Oracle Database 11g/12c
create or replace 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;
create or replace 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;
create or replace FUNCTION ora12c_verify_function14
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
pw_lower varchar2(256);
db_name varchar2(40);
i integer;
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
IF NOT ora_complexity_check(password, chars => 14, UPPER => 2, LOWER => 2,
digit => 2, special => 2) THEN
RETURN(FALSE);
END IF;
-- Check if the password contains the username
pw_lower := NLS_LOWER(password);
IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN
raise_application_error(-20002, 'Password contains the username');
END IF;
-- Check if the password contains the username reversed
reverse_user := '';
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF instr(pw_lower, NLS_LOWER(reverse_user)) > 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 instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN
raise_application_error(-20004, 'Password contains the server name');
END IF;
-- Check if the password contains 'oracle'
IF instr(pw_lower, 'oracle') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains ' company'
IF instr(pw_lower, ' company') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains 'password'
IF instr(pw_lower, 'password') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is too simple
IF instr(pw_lower, 'abc') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, 'xyz') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '123') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '&') > 0 THEN
raise_application_error(-20006, 'Password must not contain & ');
END IF;
IF instr(pw_lower, '@') > 0 THEN
raise_application_error(-20006, 'Password must not contain @ ');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF pw_lower IN ('hello123','welcome1','database1', 'account1', 'user1234',
'password1', 'oracle123', 'computer1',
'abcdefg1', 'change_on_install') 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;
create or replace FUNCTION ORA12C_VERIFY_FUNCTION8
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
pw_lower varchar2(256);
db_name varchar2(40);
i integer;
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
IF NOT ora_complexity_check(password, chars => 8, UPPER => 1, LOWER => 1,
digit => 1, special => 1) THEN
RETURN(FALSE);
END IF;
-- Check if the password contains the username
pw_lower := NLS_LOWER(password);
IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN
raise_application_error(-20002, 'Password contains the username');
END IF;
-- Check if the password contains the username reversed
reverse_user := '';
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF instr(pw_lower, NLS_LOWER(reverse_user)) > 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 instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN
raise_application_error(-20004, 'Password contains the server name');
END IF;
-- Check if the password contains 'oracle'
IF instr(pw_lower, 'oracle') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains ' company'
IF instr(pw_lower, ' company') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains 'password'
IF instr(pw_lower, 'password') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is too simple
IF instr(pw_lower, 'abc') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, 'xyz') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '123') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '&') > 0 THEN
raise_application_error(-20006, 'Password must not contain & ');
END IF;
IF instr(pw_lower, '@') > 0 THEN
raise_application_error(-20006, 'Password must not contain @ ');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF pw_lower IN ('hello123','welcome1','database1', 'account1', 'user1234',
'password1', 'oracle123', 'computer1',
'abcdefg1', 'change_on_install') 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;
create or replace 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;
create or replace 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;
create or replace FUNCTION ora12c_verify_function14
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
pw_lower varchar2(256);
db_name varchar2(40);
i integer;
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
IF NOT ora_complexity_check(password, chars => 14, UPPER => 2, LOWER => 2,
digit => 2, special => 2) THEN
RETURN(FALSE);
END IF;
-- Check if the password contains the username
pw_lower := NLS_LOWER(password);
IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN
raise_application_error(-20002, 'Password contains the username');
END IF;
-- Check if the password contains the username reversed
reverse_user := '';
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF instr(pw_lower, NLS_LOWER(reverse_user)) > 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 instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN
raise_application_error(-20004, 'Password contains the server name');
END IF;
-- Check if the password contains 'oracle'
IF instr(pw_lower, 'oracle') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains ' company'
IF instr(pw_lower, ' company') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains 'password'
IF instr(pw_lower, 'password') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is too simple
IF instr(pw_lower, 'abc') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, 'xyz') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '123') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '&') > 0 THEN
raise_application_error(-20006, 'Password must not contain & ');
END IF;
IF instr(pw_lower, '@') > 0 THEN
raise_application_error(-20006, 'Password must not contain @ ');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF pw_lower IN ('hello123','welcome1','database1', 'account1', 'user1234',
'password1', 'oracle123', 'computer1',
'abcdefg1', 'change_on_install') 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;
create or replace FUNCTION ORA12C_VERIFY_FUNCTION8
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
pw_lower varchar2(256);
db_name varchar2(40);
i integer;
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
IF NOT ora_complexity_check(password, chars => 8, UPPER => 1, LOWER => 1,
digit => 1, special => 1) THEN
RETURN(FALSE);
END IF;
-- Check if the password contains the username
pw_lower := NLS_LOWER(password);
IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN
raise_application_error(-20002, 'Password contains the username');
END IF;
-- Check if the password contains the username reversed
reverse_user := '';
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF instr(pw_lower, NLS_LOWER(reverse_user)) > 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 instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN
raise_application_error(-20004, 'Password contains the server name');
END IF;
-- Check if the password contains 'oracle'
IF instr(pw_lower, 'oracle') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains ' company'
IF instr(pw_lower, ' company') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password contains 'password'
IF instr(pw_lower, 'password') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is too simple
IF instr(pw_lower, 'abc') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, 'xyz') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '123') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
IF instr(pw_lower, '&') > 0 THEN
raise_application_error(-20006, 'Password must not contain & ');
END IF;
IF instr(pw_lower, '@') > 0 THEN
raise_application_error(-20006, 'Password must not contain @ ');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF pw_lower IN ('hello123','welcome1','database1', 'account1', 'user1234',
'password1', 'oracle123', 'computer1',
'abcdefg1', 'change_on_install') 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;
