ORA-28007: THE PASSWORD CANNOT NE REUSED

            


- Here will see how to deal with  => ORA-28007: the password cannot be reused - 

1. I got a request from client user, stating his account has been locked.
2. When I check the status of the account I found the below.
3. connect to SYS user & execute the below query of that database.

SQL> select password,username,account_status,profile from dba_users where username='TEST';

PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE

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

AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS

So, then I have unlocked the account and checked the same again & I found the below results.

SQL>  select password,username,account_status,profile from dba_users where username='TEST';

PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE

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

AB2Aa8AC9971521e3     TEST          EXPIRED(GRACE)                NONAPP_USERS

When I trying to reset his password same as his old password, using the encrypted password(AB2Aa8AC9971521e3). I'm getting below error.

SQL>  alter user TEST IDENTIFIED BY VALUES 'AB2Aa8AC9971521e3';

alter user TEST  IDENTIFIED BY VALUES 'AB2Aa8AC9971521e3'

*

ERROR at line 1:

ORA-28007: the password cannot be reused

SOLUTION:

Change the profile limit to unlimited like below.

Check the below first and make a note of them.

SQL> select * from dba_profiles where profile='NONAPP_USERS' and  RESOURCE_NAME in ('PASSWORD_REUSE_TIME');

     PROFILE                           RESOURCE_NAME              RESOURCE LIMIT

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

NONAPP_USERS     PASSWORD_REUSE_TIME              PASSWORD .0006

SQL> select * from dba_profiles where profile='NONAPP_USERS' and  RESOURCE_NAME in ('PASSWORD_REUSE_MAX');

     PROFILE                           RESOURCE_NAME              RESOURCE LIMIT

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

NONAPP_USERS            PASSWORD_REUSE_MAX               PASSWORD 4

Now change these values to unlimited.

SQL> select name from v$database;

NAME

---------

TCPT

SQL> alter profile NONAPP_USERS limit PASSWORD_REUSE_MAX UNLIMITED;

Profile altered.

SQL> alter profile NONAPP_USERS limit PASSWORD_REUSE_TIME UNLIMITED;

Profile altered.

Once the both changed to unlimited. Now try to alter the password. like below

SQL> alter user TEST  IDENTIFIED BY VALUES 'AB2Aa8AC9971521e3 ';

User altered.

Please check account status now.

SQL> select username,account_status from dba_users where username='TEST';

USERNAME                       ACCOUNT_STATUS

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

TEST                                             OPEN

Now we are able to ALTER the user password. Once password reset, alter profile limit like as previous. using the below queries.

SQL> alter profile NONAPP_USERS limit PASSWORD_REUSE_TIME 1/1440;

Profile altered.

SQL> alter profile NONAPP_USERS limit PASSWORD_REUSE_MAX 4;

Profile altered.

Here we are done :)

Thank You !