Closure
Closure
Home » Databases » Oracle Database » How to enforce password policy in an Oracle database

How to enforce password policy in an Oracle database

How to enforce password policy in an Oracle database
, , , Leave a comment
How to enforce password policy in an Oracle database
Latest posts by Stratos Matzouranis (see all)

As we know in an Oracle database through the User Profiles we can set restrictions on groups of users as to when and if a code expires (PASSWORD_LIFE_TIME), how many days it has to change before it stops working (PASSWORD_GRACE_TIME), after how many days it can be reused (PASSWORD_REUSE_TIME) and after how many failed attempts it locks (FAILED_LOGIN_ATTEMPTS). However, by default we cannot set restrictions regarding the size and complexity of the code.

In this article we will see how we install the builtin functions that allow us this feature and what options we have.

With the installation we have the possibility to activate one of the two functions per user profile.

ora12c_verify_function

By activating it ora12c_verify_function the following rules will be met:

  • The code must be at least 8 characters.
  • The code must have at least 1 letter.
  • The code must have at least 1 number.
  • The code must not contain the username
  • The code must not contain the username in reverse
  • The code must not contain the server name
  • Password cannot be common like 'oracle' and 'password1' .
  • The code must not differ by at least 3 characters from the previous code.

ora12c_strong_verify_function

By activating it ora12c_strong_verify_function the following rules will be met:

  • The code must be at least 9 characters.
  • The code must have at least 2 capital letters.
  • The code must have at least 2 lowercase letters.
  • The code must have at least 2 numbers.
  • The code must have at least 2 special characters.
  • The code must not differ by at least 3 characters from the previous code.

The installation

Through the sqlplus tool, execute the following command to install the functions:

sqlplus / as sysdba

@$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

But with the installation it will be activated automatically ora12c_verify_function in the default profile, to disable it, run the following:

alter profile default limit password_verify_function null;
alter profile default limit password_grace_time unlimited;
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_LOCK_TIME unlimited;

However, if some codes did not meet the criteria, e.g. if the code has passed 180 days, it will return to status EXPIRED(GRACE) as the script automatically changed the PASSWORD_LIFE_TIME parameter to 180.

In this case we can generate the commands to re-pass the same codes to those users who belonged to the profile DEFAULT and have returned to status EXPIRED(GRACE) with the following script:

select 'ALTER USER ' ||USERNAME|| ' IDENTIFIED BY VALUES ''' ||spare4|| ''';'  from dba_users du 
inner join sys.user$ su on du.username=su.name
where 1=1
and profile='DEFAULT'
and account_status='EXPIRED(GRACE)';

If during the user's connection an incompatibility issue appears with the password file, then we recreate it through the command line by setting the user's password sys and format=12:

orapwd file=$ORACLE_HOME/dbs/orapwdbname force=y password=passw0rd format=12

To enable it ora12c_verify_function in a user profile we run the following command:

alter profile profile_name limit password_verify_function ora12c_verify_function;

To enable it ora12c_strong_verify_function in a user profile we run the following command:

alter profile profile_name limit password_verify_function ora12c_strong_verify_function;

To see all the policies in a user profile, run the following query:

select * from dba_profile where PROFILE='DEFAULT';
How to enforce password policy in an Oracle database
01

As we see in this case it has been activated as PASSWORD_VERIFY_FUNCTION the function ora12c_verify_function.

Sources:

Share it

Leave a reply