The Geek Diary

ORA-28007: the password cannot be reused

by

The Problem

You attempt to alter a user’s password to a previously used one and get the following error when attempting to reuse the old password:

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

The password ‘med’ has been assigned to this user previously.

The Solution

The user is assigned a profile that defines the number of changes required before a password can be reused. The parameter in the profile is the limit PASSWORD_REUSE_MAX. Display its current value from the DBA_PROFILES dictionary view:

SQL> select resource_name, limit
  2  from dba_profiles
  3  where profile='TEST'
  4  and resource_name='PASSWORD_REUSE_MAX';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_MAX               3

If you do not want the user to change his password regularly, assign the user a profile with no password checking:

SQL> select profile, resource_name, limit
  2  from dba_profiles  
  3  where resource_type='PASSWORD' order by 1,2;

PROFILE    RESOURCE_NAME                    LIMIT
---------- -------------------------------- ------------------
DEFAULT    FAILED_LOGIN_ATTEMPTS            UNLIMITED
DEFAULT    PASSWORD_GRACE_TIME              UNLIMITED
DEFAULT    PASSWORD_LIFE_TIME               UNLIMITED
DEFAULT    PASSWORD_LOCK_TIME               UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX               UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME              UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION         NULL
TEST       FAILED_LOGIN_ATTEMPTS            3
TEST       PASSWORD_GRACE_TIME              10
TEST       PASSWORD_LIFE_TIME               60
TEST       PASSWORD_LOCK_TIME               .0006
TEST       PASSWORD_REUSE_MAX               3
TEST       PASSWORD_REUSE_TIME              1800
TEST       PASSWORD_VERIFY_FUNCTION         VERIFY_FUNCTION
SQL> alter user SCOTT profile default;
User altered.

Conclusion

The problem manifests because the users’ profile has been explicitly defined to limit the reuse of passwords.

To allow a user to change its password in 8i it is enough to set either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX to UNLIMITED. Starting with 9i in order to be able to change the passwords without restrictions it is needed to have both parameters set to UNLIMITED.

You May Also Like