Oracle: Workaround for PASSWORD UNEXPIRE

Before starting, here is the deal: While “ALTER USER simon PASSWORD EXPIRE” exists, the “PASSWORD UNEXPIRE” statement does not exist in Oracle, see the ALTER USER statement in the language reference. Now, there is a good reason that command does not exist.

Background

The password expiration mechanism is a method to provide security (see also Language Reference and Security Guide). 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.

Because of this, I highly discourage working around this mechanism. 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.

Workaround

For this workaround, we will use the “ALTER USER ... IDENTIFIED BY VALUES” statement that I already used in another article. Basically, we will set a new password that is equal to the current password, using the encrypted version of the password.

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

SQL> alter user simon password expire;

User altered.

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

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SIMON                          EXPIRED

1 row selected.

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

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

'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||SPARE4||';'||PASSWORD||''';'
----------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER USER SIMON IDENTIFIED BY VALUES 'S:ADEB92DE98CCE3A01033BFE530092B43AD1AE394220C93BA4ED3813C05C6;B0334ACB686B0325';

1 row selected.

SQL> ALTER USER SIMON IDENTIFIED BY VALUES 'S:ADEB92DE98CCE3A01033BFE530092B43AD1AE394220C93BA4ED3813C05C6;B0334ACB686B0325';

User altered.

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

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

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SIMON                          OPEN

1 row selected.

SQL> connect simon/tiger
Connected.

Comments

Oh no, new comments are currently disabled.
If you want to get in touch with me, please do so via e-mail:

Upgrade Oracle Database from 11g2 to 12c | The Fisherman DBA, on 2017-05-20 02:06:51 (Website)

[…] no prompt for a new password, and no way to unexpire. ¬†Fortunately I found a great workaround on a blog post by Simon Krenger. ¬†This method involves replacing the existing encrypted password with that same encrypted […]

Manish Nashikkar, on 2014-09-22 18:55:41

Thanks, got this link at the right time in needy situation. Worked very well in condition EXPIRED(GRACE) status and where password couldn't be changed. Thanks once again, Manish Nashikkar

Gustavo Patles, on 2014-07-11 20:29:44

Thanks Simon for this workaround you save my neck today... For David answer, if the problem is over an application users, perhaps you need to reuse the password, in this scenario, one way could be, first change the user profile, in order to permit reuse password, then apply Simon workaround and finally you alter the profile again in order to have all clean. Thanks again Simon.. Gustavo

craig-s, on 2014-06-24 16:59:49

Simon you have saved our necks and bought us some time to fix it properly. Thanks :)

Simon Krenger, on 2014-06-13 09:25:41 (Website)

Hei David, then just set a new password using ALTER USER ... IDENTIFIED BY mynewpassword;. The account will be automatically unexpired.

David, on 2014-06-13 02:27:58

What if the password cannot be reused?