How To: Enable Password Security/Complexity Check

We need to make the password of Oracle user more robust and make sure that the password are complex enough so that the chance of breaking them is minimized.

Oracle provides a script called UTLPWDMG.sql located in $ORACLE_HOME/rdbms/admin which can be used to manage the complexity of database password.

This script basically creates to functions verify_function_11G (new in 11g) and verify_function (older version).

Lets see what verify_function_11G function does, its input parameters are username, new password and old password.

Checks that this scripts enforces on passwords:

• Makes sure that the length of the password is more than 8.
• Makes sure that the username and password are not same e.g. username is SCOTT and the password is SCOTT. This check involves that the password can be in any case (UPPER CASE/LOWER CASE/Combination of Both) of the username, it will still reject the password. It basically compares lower(username)=lower(new_password).
• Makes sure that the password is not the reverse of the username. Checks for the case also, same as above.
• Makes sure that the password is not the same as the database name.
• Checks for the simplicity of the password, but I think this check is not so robust, as oracle just checks it against some pre-defined keywords: ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install'). Can't complain also, as its not really possible to compare it with the dictionary. These are some very common 8 letter passwords, people use.
• Makes sure that the password is not "oracle". This is a seperate check in the function, it could have been part of the above check.
• Makes sure that the combination is alpha numeric, basically atleast 1 character and 1 number.
• Makes sure that the new password differs from the new password by atleast 3 characters.

I believe these are great checks to make sure that the new password are complex enough. we can also add more check if required, like add more keywords we dont want as password, check for upper case and lower case characters, and enforce that the password should have Upper case characters, make sure that teh password also has special characters like !,@,#,$,% etc etc.

After this function get compiled, the script alters the default profile to start using this function:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

PASSWORD_LIFE_TIME 180 --> Days after which you need to change password.
PASSWORD_GRACE_TIME 7 --> The time in days allowed after password expire, grace time.
PASSWORD_REUSE_TIME UNLIMITED --> This parameter makes sure that you cannot use the same password again.
PASSWORD_REUSE_MAX UNLIMITED --> This parameter makes sure that you cannot use the same password again.
FAILED_LOGIN_ATTEMPTS 10 --> Allowed password retries in case of entering wrong password.
PASSWORD_LOCK_TIME 1 --> Lock the user for 1 Day if it makes 10 failed password attempts.
PASSWORD_VERIFY_FUNCTION verify_function_11G; --> Set the password managment function.

Now talking about verify_function, or the older password management function provided by oracle:

Checks that this scripts enforces on passwords:

• Makes sure that the username and password are not same e.g. username is SCOTT and the password is SCOTT. This check involves that the password can be in any case (UPPER CASE/LOWER CASE/Combination of Both) of the username, it will still reject the password. It basically compares lower(username)=lower(new_password).
• Makes sure that the length of the password is more than 4.
• Checks for the simplicity of the password, oracle just checks it against some pre-defined keywords:('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd')
• Makes sure that the combination is alpha numeric with special character, basically atleast 1 character, 1 number and 1 special character. Special characters allowed: '!"#$%&()``*+,-/:;<=>?_'
• Makes sure that the new password differs from the new password by atleast 3 characters.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 10
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

PASSWORD_REUSE_TIME 60 --> Can you the same password after 60 days.
PASSWORD_REUSE_MAX 10 --> Can use the same password after using 10 different passwords after 60 days.

After running the above script the default profile is set to verify_function_11G. The alter profile with verify_function is commented.
But incase you want to revert to verify_function, you can do an alter profile as above.