How To Set Password Policy in Oracle

dbtut January 23, 2019 ORACLE

The password of Oracle database users is usually set to the same as the user name. Or passwords are set very simply. That means serious security weakness.

To prevent users from setting simple passwords , we need to change the PASSWORD_VERIFY_FUNCTION parameter in the profile to be a procedure specified by US or verify_function_11G function provided by Oracle.

When verify_function_11G is installed, the following parameters will be automatically set for the profile named DEFAULT:

PASSWORD_VERIFY_FUNCTION	verify_function_11G
PASSWORD_LIFE_TIME	180
PASSWORD_GRACE_TIME	7
FAILED_LOGIN_ATTEMPTS	10
PASSWORD_LOCK_TIME	1

In this way, the user password will be forced to be changed every 180 days.

After 180 days of the last password change, the user will be warned to change the password for 7 days. If the User has not changed his/her password for 7 days, the user status will be expired and the user could not be login.

If we have created a profile, we need to change the PASSWORD_VERIFY_FUNCTION parameter as follows.

SQL> ALTER PROFILE PROFILE_NAME LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11G;

1	SQL> ALTER PROFILE PROFILE_NAME LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11G;

Install the verify_function_11G function:

We can install the verify_function_11G function by running utlpwdmg.sql in the $ORACLE_HOME/rdbms/admin directory.

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql;

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

SQL>

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql;

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

SQL>

After the function is installed, if changes in the DEFAULT profile will cause problems, it is useful to make some corrections in DEFAULT Profile.

Because there was no such thing as invalid passwords before installation. After the function is installed, the passwords will expire 180 days after the user’s last password change. This 180 days is not 180 days after the function is created. This is the time after the most recent password change. So in this case, the user password can be expire in a few days.

For detailed information about Oracle Profiles, I suggest you read the below articles.

“How To Create Oracle Profiles“,

“How To Change User Profile On Oracle”

and you should read the below article about user password expire.

“Oracle Password Expired Error”,

“How to Unlock an Expired Oracle User Whose Password is Unknown”