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.