How to become another User in SQLPlus

See this link for a much better way to achieve this in current releases, using proxy users...

A DBA frequently needs to become another user to test something or verify a problem.  Short of having to gain acess to that users password, we are asked can I su to that account, sort of like root does on unix.

This is an 'su.sql' script I use:

whenever sqlerror exit

column password new_value pw

    l_passwd varchar2(45);
    select password into l_passwd
      from sys.dba_users
     where username = upper('&1');

select password
  from sys.dba_users
 where username = upper( '&1' )

alter user &1 identified by Hello;
connect &1/hello
alter user &1 identified by values '&pw';
show user
whenever sqlerror continue

it starts by testing your access to the sys.dba_users table -- if that fails -- it exits SQLPlus.  If zero rows returned -- it exits SQLPlus.

It then selects the 'password' from the dba_users table and stuffs it into a macro variable "&pw"

We alter the user you want to become to have a known password (if that fails, we exit).

We 'fix' their password back after loggin in as them....

Note, you need to have access to dba_users and the alter user privelege.

All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.