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
![](/ArticleFiles/746/image001.jpg)
Figure 2: Elevated User Password Length
![](/ArticleFiles/746/image002.jpg)
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
![](/ArticleFiles/746/image003.jpg)
Figure 4: Password Same As Or Similar To User
![](/ArticleFiles/746/image004.jpg)
Figure 5: Password Should Contain At Least One Digit, One Character and One Punctuation
![](/ArticleFiles/746/image005.jpg)
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.