• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

ORA-28007: the password cannot be reused

by admin

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Create Undo Tablespace for a Newly Added RAC Instance (ORA-30012)
  2. How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
  3. tuned-adm and Oracle
  4. RMAN: SET NEWNAME Command Using SQL
  5. How to Change SYS and SYSTEM Passwords in Oracle Database
  6. Queries to find out the SQL which is using these temporary tablespace
  7. Cron Script does not Execute as Expected from crontab – Troubleshoot
  8. Oracle RMAN : Block-Level Media Recovery (Basics and Example)
  9. Beginners Guide to RMAN compression for backups
  10. Beginners Guide to Flash Recovery Area in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • cf: Command-line tool to manage apps and services on Cloud Foundry
  • certutil: Manage keys and certificates in both NSS databases and other NSS tokens
  • cdk: A CLI for AWS Cloud Development Kit (CDK)
  • cd: Change the current working directory

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright