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…
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…
|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 :
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
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 126.96.36.199.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 188.8.131.52.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.