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
Nice. I use the first method in a script, which hopefully keeps the downtime to a minimum.
I will have a play with the proxy in sqlplus at work next week.
Pingback: "BECOME USER" sous Linux | ArKZoYd
Pingback: switch user in Oracle | Laurent Schneider
Nice hint Laurent, thank you
Foued