Change the user password back to previous without knowing it

Summary As a DBA you may need to log in as another user, for example to test an application after doing some workarounds to solve a problem. But you don't know the password and you don't want to wait. You can change the password, as dba, but how to change it back to previous? 
The right way to proceed is:

    * note down the current (old) password, as found in the table dba_users
    * modify the password with the command ALTER USER IDENTIFIED BY
    * connect using the new password
    * do what you wanted to do
    * reset the password with the clause IDENTIFIED BY VALUES

All the steps by example For our example we will use the HELPDESK user with password oracle123

CREATE USER helpdesk IDENTIFIED BY oracle123;
GRANT CREATE SESSION TO helpdesk;

As a DBA you don't know the real password for user HELPDESK, but you can find the encoded password in the view dba_users

SELECT username, PASSWORD, 'alter user ' || username || ' identified by values ' 
|| '''' || PASSWORD || '''' ||';' alter_user_sql
FROM dba_users
WHERE username  = 'HELPDESK';

so the encoded value is 414AD71995BE5241, modify the password to your own

ALTER USER helpdesk IDENTIFIED BY helpdesk;

Connect as helpdesk with password helpdesk. Do your work, if you finish, change back the password

ALTER USER HELPDESK IDENTIFIED BY VALUES '414AD71995BE5241';

 

Leave Comment