Understanding the Oracle resource profile parameters PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME

This post talks about the change of behavior for the PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME resource profile parameters. It describes how PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME should be used together to control password reuse.

Intended Behaviour

Set either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX to unlimited and the password can NEVER be reused. When you change the password with the same password, any attempt generates the expected error ORA-28007, whichever command you use, SQL*Plus command PASSWORD or SQL ALTER USER …IDENTIFIED by.

Examples of PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME Used Together

1. If both PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME are unlimited, it means that this parameter is ignored. This is so for the backward compatibility also.

2. If PASSWORD_REUSE_MAX is set to 10 and PASSWORD_REUSE_TIME is set to UNLIMITED, the user can never reuse his/her password.

3. If PASSWORD_REUSE_TIME is set to 1 month and PASSWORD_REUSE_MAX is set to UNLIMITED, the user can never reuse his/her password.

4. If PASSWORD_REUSE_TIME is set to 1 month and PASSWORD_REUSE_MAX is set to 10, the user is allowed to reuse his/her password after 1 month if the user has changed the password 10 times.

Test with PASSWORD_REUSE_TIME Set to UNLIMITED

SQL> CREATE PROFILE profile_name_here LIMIT PASSWORD_REUSE_TIME unlimited PASSWORD_REUSE_MAX 2;

Profile created.

SQL> CREATE USER [username] IDENTIFIED BY [password] PROFILE profile_name_here;
User created.

SQL> GRANT connect TO <username>;
Grant succeeded.</username>

SQL> connect [username]/[password]
Connected.

SQL> alter user [username] identified by [password]1;
User altered.

SQL> alter user [username] identified by [password]2;
User altered.

SQL> alter user [username] identified by [password]3;
User altered.

Verify

SQL> alter user <username> identified by <password>;
alter user <username> identified by <password>
*
ERROR at line 1:
ORA-28007: the password cannot be reused</password></username></password></username>

Test with PASSWORD_REUSE_MAX Set to UNLIMITED

SQL> connect system/[password]
Connected.

SQL> CREATE PROFILE profile_name_here
LIMIT profile_name_here .0104 -- 15 minutes
PASSWORD_REUSE_MAX unlimited;

Profile created.

SQL> CREATE USER [username]3 IDENTIFIED BY [password] PROFILE profile_name_here;
User created.

SQL> GRANT connect TO [username]3;
Grant succeeded.

SQL> connect [username]3/[password]
Connected.

Verify

Wait for 15 mins and verify:

SQL> alter user <username>3 identified by <password>;
alter user <username>3 identified by <password>
*
ERROR at line 1:
ORA-28007: the password cannot be reused</password></username></password></username>