Oracle password verify function
This week I had to implement a password verify function at a client.
My sources of inspiration were both:
– Stefan Oehrli blog entry on [Oracle 12c new password verify function]
– Mike Smithers blog entry on [Mama Mia ! Oracle Database Password Complexity and Seventies Euro-Pop]
From Mike Smithers I specially like the small PL/SQL to test the password function. I dare to reproduce here:
set lines 130
set serveroutput on size unlimited
spool verify_test.log
DECLARE
--
-- script to test check_password_fn
--
TYPE typ_passwords IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
tbl_passwords typ_passwords;
l_old_password VARCHAR2(30) := 'Waterloo1';
l_dummy BOOLEAN;
BEGIN
--
-- setup list of passwords to test with...
--
tbl_passwords(1) := 'simple'; -- too short
tbl_passwords(2) := 'mike1234'; -- same as user
tbl_passwords(3) := '4321ekim'; -- mike backwards
tbl_passwords(4) := '1oolretaW'; -- old password backwards
tbl_passwords(5) := 'Waterloo18'; -- too similar to old password
tbl_passwords(6) := 'Brotherhood_0f_Man'; -- dictionary
tbl_passwords(7) := 'P455w0rd'; -- simple
tbl_passwords(8) := 'Sm0king_15_0nly_Vice'; -- should pass.
tbl_passwords(9) := 'm1K31234'; -- should fail - too similar to user
tbl_passwords(10) := 'W4terl001'; -- should fail - too similar to old password
tbl_passwords(11) := 'the_day_before_you_came'; -- should fail - does not have any uppercase or numbers
tbl_passwords(12) := 'ONLYSeventeen'; -- should fail - no numbers
tbl_passwords(13) := '48840000'; -- should fail - no letters
tbl_passwords(14) := 'ABBA0000'; -- should fail - no lowercase letters
tbl_passwords(15) := 'abba0000'; -- should fail - no uppercase letters
tbl_passwords(16) := 'Mamma_Mia1'; -- should pass
-- tbl_passwords(1) := 'W4terl001';
-- tbl_passwords(2) := 'P455w0rd'; -- simple
--
-- Nested block required to account for failures...
--
FOR i IN 1..tbl_passwords.COUNT LOOP
BEGIN
l_dummy := check_password_fn(
username => 'MIKE',
old_password => l_old_password,
new_password => tbl_passwords(i));
DBMS_OUTPUT.PUT_LINE('Test '||i||' - password allowed.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test '||i||' ERROR : '||SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Test run completed.');
END;
/
spool off
One the requirements I had was:
– The password should include three of the four following sets: English uppercase letters, English lowercase letters, base 10 digits, special characters like ‘$’,’#’,’!’,’?’
The password function provided by Oracle does not allows to say this choice of “three out of four”, so I developed myself and added to the Oracle default function the following code:
--
-- Finally, make sure that the password contains
-- characters from 3 of the four categories
-- UPPERCASE, lowercase, numbers and punctuation characters.
--
IF
SIGN(REGEXP_INSTR(new_password, '[[:upper:]]'))+
SIGN(REGEXP_INSTR(new_password, '[[:lower:]]')) +
SIGN(REGEXP_INSTR(new_password, '[[:digit:]]')) +
SIGN(REGEXP_INSTR(new_password, '[[:punct:]]')) < 3
THEN
RAISE_APPLICATION_ERROR( -20006,'Password must contain characters from three of the following four categories: English uppercase, lowercase, base 10 digits, non-alphabetic characters like ! $ # %');
END IF;