Switch to another Oracle User and back

Sometimes, the DBA needs to log into a user's account to make a change, such as to grant a privilege on a user's table to another user, but, may not know what the user's password is, or, may need to make changes to a set of users from a script, but, doesn't want to include their passwords in the script itself. 

Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose. 

Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value.  Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user" command to put the original password back.

The following SQL generates a password change script (setpw.sql) to set all users to a known password ("whatever"), and, another script (resetpw.sql) to set all users back to their original passwords.  This would be used as part of another script to generate the password change scripts, run setpw.sql, log in and make the changes that are needed, and immediately run resetpw.sql to put the passwords back.  Note that the users won't be able to log in during that time, since their passwords are not valid while you are running the script.


spool setpw.sql
select 'alter user ' || username || ' identified by whatever;' from dba_users;
spool off

spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users;
spool off
Generated Script setpw.sql
alter user SYS identified by whatever;
alter user SYSTEM identified by whatever;
alter user DES identified by whatever;
alter user ELAN identified by whatever;
Generated Script resetpw.sql
alter user SYS identified by values '5638228DAF52805F';
alter user SYSTEM identified by values 'D4DF7931AB130E37';
alter user DES identified by values 'ABFEC5AC2274E54D';
alter user ELAN identified by values 'B53CE8493EC6FB92';

Source & Credits: www.akadia.com