Login in User Schemas Without Having Password

Sometimes, we need to sign-on as a specific user to understand the exact nature of their problem.While it is easy to alter the user ID to make a new password, this is an inconvenience to the end-user because they have to re-set a new password .  However, as DBA we can extract the encrypted password from the dba_users views, save it, and re-set the password after we have finished with testing .

For example, assume that we need to sign-on as HARRY user and test their Oracle privileges:

We perform the following steps:


1.) Extract the encrypted password 
SQL> select 'alter user "'||username||'" identified by values  '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password  from  dba_users where username = 'HARRY';
OLD_PASSWORD
--------------------------------------------------------------------
alter user "HARRY" identified by values '15EC3EC6EAF863C'  ;


2.) Change HARRY’s password and sign-on for testing and perform all testing 
SQL> alter user HARRY identified by harry;
User altered .
SQL> conn harry/harry@noida
Connected

3.)  Reset the same Old Password 
When we have completed the testing we can set-back the original encrypted password using the output from the below query as 
SQL> alter user "HARRY" identified by values '15EC3EC6EAF863C' ; 




Enjoy      :-)