Workaround for PASSWORD EXPIRED or EXPIRE(GRACE)

Had a Challenge that wanted to keep Same Password to keep Application Running because even application team didn't know at how many places this password was used. When I checked the password it was in EXPIRE (GRACE) Status, 

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='WYSE';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
WYSE                           EXPIRED(GRACE)                                                 DEFAULT

While "ALTER USER WYSE PASSWORD EXPIRE" exists, the “PASSWORD UNEXPIRE” statement does not exist in Oracle.

The password expiration mechanism is a method to provide security. Using this mechanism, users are automatically prompted to change their password after a certain period, defined in the profile the user is assigned. This way, compromised passwords can only be used for a certain period, afterwards the password needs to be changed.

The method described here should only be used when there is no way that you can change passwords or need to keep an application running. (Here I had to keep same password because this password had been hard coded at application level)

Workaround

For this workaround, I used the “ALTER USER ... IDENTIFIED BY VALUES” statement. 

Log in as a user that was granted the DBA privilege. Verify that the account_status of the user is EXPIRED orEXPIRE(GRACE):

SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='WYSE';

'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||SPARE4||';'||PASSWORD||''';'
-------------------------------------------------------------------------------------------------------------
ALTER USER WYSE IDENTIFIED BY VALUES 'S:AC4C23B91E11B3129D237FDF0EDB9166549E978408BC9795C5B3EA97ADF8;E6077E624E866DEA';

Now, use this dynamic SQL query to get the encrypted password for the user “WYSE” (from sys.user$) and construct the ALTER USER statement to reset the password:

SQL> ALTER USER WYSE IDENTIFIED BY VALUES 'S:AC4C23B91E11B3129D237FDF0EDB9166549E978408BC9795C5B3EA97ADF8;E6077E624E866DEA';

User altered.

This will change the account status back to OPEN and I should be able to log in using your old password:

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='WYSE';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE

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

WYSE                           OPEN                                                 DEFAULT

SQL> conn wyse

Enter password:

Connected.

SQL> show user

USER is "WYSE"

This I found Interesting and useful at quite sometime, hence Thought to Share with You - Manish