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;