Simon Krenger

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.