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.
No comments:
Post a Comment