Tips for Oracle DBA: Password Verification Function

Password Verification Function is one of the profile password parameter setting. This setting is using Oracle function to set the password hardening.

In order to implement this Password Verification Function, first you need to create the function under SYS schema.

The default / template of password verification function verify_function_11G can be found in here $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Following function is extracted from the file.

CREATE OR REPLACE FUNCTION verify_function_11G (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20001, 'Password length less than 8');
END IF;

-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to user name ');
END IF;
END LOOP;

-- Check if the password is same as the username reversed

FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;

-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to server name ');
END IF;
END LOOP;

-- 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 ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;

-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20007, 'Password too simple ');
END IF;
END LOOP;

-- Check if the password contains at least one letter, one digit
-- 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(-20008, 'Password must contain at least one digit, one character');
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 endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, and one character');
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);

differ := abs(differ);
IF differ < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

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(-20011, 'Password should differ from the \
old password by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

Customize the Password Verification Function

Each profile can be assigned 1 function only. And 1 function can be assigned to multiple profile, you can create multiple function with different password hardening setting.

There are few parts in the scripts where you can carefully edit to your requirement for example you allow user to set password less than 5 then you can change the 8 to 5, and also reflect the error message accordingly. Or if you do not want to restrict user password length you can comment out entire IF clause with -- or delete entire lines.

-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20001, 'Password length less than 8');
END IF;

Another example is if you do not want user to use password that so simple, you can add or modify from here accordingly.

-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;

Create Password Verification Function

In my example I will create edba_admin_verify_function

Following is the scripts, I created the sql script /tmp/edba_admin_verify_function.sql which contrain following lines

CREATE OR REPLACE FUNCTION edba_admin_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

-- Check for the minimum length of the password
-- IF length(password) < 8 THEN
-- raise_application_error(-20001, 'Password length less than 8');
-- END IF;

-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to user name ');
END IF;
END LOOP;

-- Check if the password is same as the username reversed

FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;

-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to server name ');
END IF;
END LOOP;

-- 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 ('welcome1', 'database1', 'account1',
'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1',
'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;

-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20007, 'Password too simple ');
END IF;
END LOOP;

-- Check if the password contains at least one letter, one digit
-- 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(-20008, 'Password must contain at least one digit, one character');
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 endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, and one character');
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);

differ := abs(differ);
IF differ < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

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(-20011, 'Password should differ from the \
old password by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

login as SYS and execute the sql script

To verify the result of the creation you can use following SQL command

SELECT owner, object_name, object_type, status FROM dba_objects WHERE object_name = 'EDBA_ADMIN_VERIFY_FUNCTION';

Now you can create profile with the verification function or using the ALTER PROFILE to assigned the function to the created profile .

In my example I create new profile all inheriting the DEFAULT profile setting except the PASSWORD_VERIFY_FUNCTION.

CREATE PROFILE edba_admin
LIMIT
PASSWORD_VERIFY_FUNCTION edba_admin_verify_function
;

To view the result you can use following SQL command

SELECT * FROM dba_profiles WHERE profile = 'EDBA_ADMIN' ORDER BY resource_type, resource_name;

Now I have profile with password verification function. To Test it I need to create a user with that profile.

CREATE USER edba_admin IDENTIFIED BY rejected_password PROFILE edba_admin;

The password verification function reject the password because it failed the hardening test.
Please note that the verification process will only take place when Oracle trying to store the password, therefore if you assigned the new profile with new verification function to users you must set the password to expiry forcing the users to recreate new password.
You can do that by using following SQL command

ALTER USER username PASSWORD EXPIRE;

What happen if you drop the function while there are profile that using the function?

Oracle Database 11g do not have the capacity to check the dependency, the profile will still assume the function is exists, however when the user with that profile try to change password the user will encounter following error

SQL> connect edba_admin/rejected_password
ERROR:
ORA-28001: the password has expired

Changing password for edba_admin
New password:
Retype new password:
ERROR:
ORA-07443: function for password verification not found

Password changed
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-01012: not logged on

Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Connected.

Following example have following situation
1. edba_admin profile is assigned to user test_admin
2. edba_admin is a profile with edba_admin_verify_function verification function

I using following SQL command to baseline my test.

SELECT a.username, b.profile, b.resource_name, b.limit
FROM dba_users a, dba_profiles b
WHERE a.username = 'TEST_ADMIN'
AND b.resource_name = 'PASSWORD_VERIFY_FUNCTION'
AND a.profile = b.profile;