How to switch user in Oracle ?
One approach is to change the password :
SQL> connect / as sysdba Connected. SQL> select password from dba_users where username='SCOTT'; PASSWORD ------------------------------ F894844C34402B67 SQL> alter user scott identified by abc123; User altered. SQL> connect scott/abc123 Connected. SQL> create table t... SQL> connect / as sysdba Connected. SQL> alter user scott identified by values 'F894844C34402B67'; User altered.
but this is unfair. The user will be prevented from logging for a while, the password expire policy will be reset.
Sometimes you can simply use
SQL> alter session set current_schema=scott; Session altered.
but this does not really mean a “su”, because you do not change your privileges. You just change the “default” schema.
Another approach is to use the BECOME USER privilege and undocumented upicui OCI function. But this will not work in sqlplus.
Thanks to Jonathan Lewis post today Proxy Users, I could imagine using the proxy functionality in sqlplus to do a su
SQL> create user su identified by secret123; User created. SQL> alter user scott grant connect through su; User altered. SQL> connect su[scott]/secret123; Connected.
The connect username[proxy]/password@db is documented in the 10gR2 SQL*Plus reference