PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME

I tested this on Oracle 11.2. I created a profile called FOR_ANDREW with PASSWORD_REUSE_MAX set to 1. This meant that I could not reuse a password until I had used one other password first:

 

SQL> conn / as sysdba

Connected.

 

SQL> create profile for_andrew

  2  limit password_reuse_max 1

  3  /

 

Profile created.

 

SQL>

 

I created a user called ANDREW and gave him the FOR_ANDREW profile:

 

SQL> create user andrew

  2  identified by old_password

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

I connected to the database as user ANDREW:

 

SQL> conn andrew/old_password

Connected.

SQL>

 

I tried to reuse the existing password but this failed as I had expected it would:

 

SQL> alter user andrew

  2  identified by old_password

  3  /

alter user andrew

*

ERROR at line 1:

ORA-28007: the password cannot be reused

 

SQL>

 

I used a different password before trying to reuse the original one. This failed with the same error, which I had not expected:

 

SQL> alter user andrew

  2  identified by new_password

  3  /

 

User altered.

 

SQL> alter user andrew

  2  identified by old_password

  3  /

alter user andrew

*

ERROR at line 1:

ORA-28007: the password cannot be reused

 

SQL>

 

I did some research and saw that the PASSWORD_REUSE_TIME was set to DEFAULT. This meant that it had the same value as the DEFAULT profile where it was set to UNLIMITED:

 

SQL> conn / as sysdba

Connected.

SQL> select limit from dba_profiles

  2  where profile = 'FOR_ANDREW'

  3  and resource_name = 'PASSWORD_REUSE_TIME'

  4  /

 

LIMIT

----------------------------------------

DEFAULT

 

SQL> select limit from dba_profiles

  2  where profile = 'DEFAULT'

  3  and resource_name = 'PASSWORD_REUSE_TIME'

  4  /

 

LIMIT

----------------------------------------

UNLIMITED

 

SQL>

 

According to the Oracle 11.1 documentation:

 

These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused.

 

It then went on to say:

For these parameter to have any effect, you must specify an integer for both of them. 

... which I found a bit misleading. However, it clarified this a few lines later as follows: 

If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password. 

I set PASSWORD_REUSE_TIME to 1 minute and checked that I still could not reinstate the original password:

SQL> alter profile for_andrew

  2  limit password_reuse_time 1/1440

  3  /

 

Profile altered.

 

SQL> conn andrew/new_password

Connected.

SQL> alter user andrew

  2  identified by old_password

  3  /

alter user andrew

*

ERROR at line 1:

ORA-28007: the password cannot be reused

 

SQL>

 

However, I waited for a minute and found that I could:

 

SQL> exec sys.dbms_lock.sleep(60);

 

PL/SQL procedure successfully completed.

 

SQL> alter user andrew

  2  identified by old_password

  3  /

 

User altered.

 

SQL>

 

I do not know which Oracle process controls this functionality but it seemed a bit hit and miss. When I repeated the test, I sometimes found I had to wait more than a minute before I could change the password back to its original value.