Home » Oracle » ORA-28007: the password cannot be reused

ORA-28007: the password cannot be reused

2 Comments / By /

We often come across the error ORA-28007: the password cannot be reused while changing the password to the same value as earlier for the users i.e reset the same password in oracle in our daily activities. This can happen with developers and DBA both.

ORA-28007: the password cannot be reused
Cause: The password cannot be reused for the specified number of days or for the specified number of password changes
Action: Try the password that you have not used for the specified number of days or the specified number of password changes Refer to the password parameters in the CREATE PROFILE statement

Reasons

This happens when you are attempting to alter a user’s password to a previously used one and your profile in user restricts that. This is controlled by PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

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

Let’s check the profile of the user

select username , profile from dba_users where username='TEST';
username    profile
------      -----
SCOTT      TECH_PW

SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='TECH_PW' ;
RESOURCE_NAME RESOURCE LIMIT
----------    --------  -----
COMPOSITE_LIMIT KERNEL DEFAULT
SESSIONS_PER_USER KERNEL DEFAULT
CPU_PER_SESSION KERNEL DEFAULT
CPU_PER_CALL KERNEL DEFAULT
LOGICAL_READS_PER_SESSION KERNEL DEFAULT
LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_TIME KERNEL DEFAULT
CONNECT_TIME KERNEL DEFAULT
PRIVATE_SGA KERNEL DEFAULT
FAILED_LOGIN_ATTEMPTS PASSWORD 5
PASSWORD_LIFE_TIME PASSWORD 90
PASSWORD_REUSE_TIME PASSWORD 60
PASSWORD_REUSE_MAX PASSWORD 10
PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT

So user can reuse the password after 60 days if the password has already been changed 10 times.

Default values can be found using below query

select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='DEFAULT';
RESOURCE_NAME RESOURCE LIMIT
----------    -------  -----
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD 7

Explanation of PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

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.

  • These two parameters must be set in conjunction with each other.
  • For these parameters to have any effect, you must specify an integer for both of them.
See also  Oracle Indexes and types of indexes in oracle with example

How it works on various values

  • When you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
  • If you have specified an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
  • If you set both of these parameters to UNLIMITED, then the database ignores both of them.
  • If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile

Solutions

  1. Change to a different password and this problem will go away
sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 16:16:05 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL> PASSWORD
Changing password for TEST
Old password:
New password:
Retype new password:
Password changed

2. You can altogether eliminate this error for forever by changing the profile toPASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX to unlimited

You can create either a new profile for this user with this specific setting or change the profile assigned only to make the changes

New Profile

SQL> CREATE PROFILE TEST_TMP LIMIT
PASSWORD_LIFE_TIME UNLIMITED
;

SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='TEST_TMP' ;
RESOURCE_NAME RESOURCE LIMIT
-----------   -------  -----
COMPOSITE_LIMIT KERNEL DEFAULT
SESSIONS_PER_USER KERNEL DEFAULT
CPU_PER_SESSION KERNEL DEFAULT
CPU_PER_CALL KERNEL DEFAULT
LOGICAL_READS_PER_SESSION KERNEL DEFAULT
LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_TIME KERNEL DEFAULT
CONNECT_TIME KERNEL DEFAULT
PRIVATE_SGA KERNEL DEFAULT
FAILED_LOGIN_ATTEMPTS PASSWORD 5
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD 10
PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT

alter user TEST profile TEST_TMP;

Now you should be able to reset to the same password

sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 16:16:05 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL> PASSWORD
Changing password for TEST
Old password:
New password:
Retype new password:
Password changed

Or

Alter user test identified by <old used pass>;

Change the existing Profile

select username , profile from dba_users where username='TEST';
username profile
------ -----
SCOTT TECH_PW

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='TECH_PW' AND RESOURCE_NAME='PASSWORD_REUSE_TIME';
LIMIT
----
60

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='TECH_PW' AND RESOURCE_NAME='PASSWORD_REUSE_MAX';
LIMIT
----
10

ALTER PROFILE TECH_PW LIMIT PASSWORD_REUSE_TIME UNLIMITED;
Profile altered.
ALTER PROFILE TECH_PW LIMIT PASSWORD_REUSE_MAX UNLIMITED;
Profile altered.

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='TECH_PW' AND RESOURCE_NAME='PASSWORD_REUSE_TIME';
LIMIT
----
Unlimited

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='TECH_PW' AND RESOURCE_NAME='PASSWORD_REUSE_MAX';
LIMIT
----
Unlimited

Now change the password to a previously used one

sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 16:16:05 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL> PASSWORD
Changing password for TEST
Old password:
New password:
Retype new password:
Password changed

Or

Alter user test identified by <old used pass>;

Hope you like a post on How to resolve ORA-28007: the password cannot be reused / how to reset the same password in oracle. Please do provide the feedback and let me know if any doubts

See also  What is subqueries in oracle

Related Articles

ORA-01017: invalid username/password; logon denied
alter user identified by values
Create User in Oracle
ora-00904 invalid identifier in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm
ORA-28000 the account is locked
ORA-03113
ORA-29285: file write error
Password Version in oracle

Related Posts

2 thoughts on “ORA-28007: the password cannot be reused”

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top