Acknowledgement
First of all I want to extend a huge thanks to Roger Rowe for assisting with this function. Roger is by far the most knowledgeable Oracle DBA I have ever had the honor of working with!
Introduction
I was recently in a situation where we needed to enforce a password strength policy for elevated users with an Oracle database such as system accounts, system administrators, backup operators, etc. In the context of this example I am demonstrating accounts with an eight- character password and the other account I previously mentioned that must have a fifteen character password.
Figure 1: Basic User Password Length
Figure 2: Elevated User Password Length
As you can see in the above two figures, the password strength varies via the role assigned. To create the function to enforce the desired password strength, execute the following PL/SQL statement. Note: Be sure you are logged in with the account sys/<password> as DBA.
Listing 1: Password Verify Function
CREATE OR REPLACE FUNCTION password_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
nPwdLength number;
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
select decode(count(*),0,8,15)
into nPwdLength
from dba_role_privs a
where a.granted_role = 'DBA'
start with a.grantee = username
connect by prior a.granted_role = a.grantee;
-- Check for the minimum length of the password
IF length(password) < nPwdLength THEN
raise_application_error(-20002, 'Password length less than ' || nPwdLength);
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password',
'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
At this point you may execute a password change via calling this function and passing in the appropriate parameters or execute the following to enforce this policy.
Listing 2: Alter Default Parameters for Password Management
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION password_verify_function;
As you can see from the following screens, this policy works nicely.
Figure 3: Password Too Simple
Figure 4: Password Same As Or Similar To User
Figure 5: Password Should Contain At Least One Digit, One Character and One Punctuation
In closing I have displayed how to enforce a password policy based on a given role. In this case the database administrator is required to have at a minimum of fifteen characters whereas all other uses must have at a minimum of eight characters. You can easily modify these restrictions by changing the password strength from listing 1, for example:
Listing 3: Modified Password Restriction
select decode(count(*),0,4,6)
into nPwdLength
from dba_role_privs a
where a.granted_role = 'DBA'
start with a.grantee = username
connect by prior a.granted_role = a.grantee;
Notice in the above listing a DBA is required to have at a minimum of six characters whereas all other uses must have at a minimum of four characters. I hope that I have demonstrated how easy it can be to enforce a password policy on an Oracle database now it is your turn to prevent that user from using something like user/123456 which we all know happens more often than not.
