Change the user password back to previous without knowing it

Change the user password back to previous without knowing it

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';