Change the user password back to previous without knowing it
                                                 Last update (2008-05-10 13:52:56)
                                                                                                                   Date added (2008-03-01 14:21:22)

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';
Reviews
Filters
Search