Security

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;

Today I was asked to get the list of Oracle default password hashes. Immediately thought about Pete Finigan website which talks about all Oracle security issues.

Quickly I found this page with the list of hashes, but I was not convinced this would work on Oracle 11.1 and 11.2. This because with the latest Oracle versions the information can be in different columns (password, spare4 from sys.user$).

But then looking at MOS, I found the note Script to Check for Default Passwords Being Used for Common Usernames [ID 227010.1] where an interesting snipped is found:

The default password checker no longer exists in 11g, instead there is a
table in the data dictionary SYS.DEFAULT_PWD$ which contains the default
passwords and there is a view DBA_USERS_WITH_DEFPWD which shows which users
on the system are still using default passwords.

So it was now important to see if on my databases all the accounts which still have the defaults are locked (or better, deleted). A simple sql gave the answer:

SQL> select username , account_status
  2  from DBA_USERS_WITH_DEFPWD
  3  natural join dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APPQOSSYS                      EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
MDDATA                         EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
OLAPSYS                        EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
SCOTT                          EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR          EXPIRED & LOCKED
SPATIAL_WFS_ADMIN_USR          EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED

19 rows selected.

My company wants me to change my password to a more complex one.

Few days ago I saw a nice cartoon about ‘password strength’: http://xkcd.com/936/

Today I checked this Microsoft Security website to see how strong a password is: https://www.microsoft.com/security/pc-security/password-checker.aspx

I tried with the password: aaaaaaaaaaaaaaaaaaaaaaaaaaaa (about 28 times letter ‘a’). The result was: