su in sqlplus

How to switch user in Oracle ?

One approach is to change the password :

SQL> connect / as sysdba
SQL> select password from dba_users where username='SCOTT';


SQL> alter user scott identified by abc123;

User altered.

SQL> connect scott/abc123
SQL> create table t...

SQL> connect / as sysdba
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;

The connect username[proxy]/password@db is documented in the 10gR2 SQL*Plus reference

4 thoughts on “su in sqlplus

  1. Paul

    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.

  2. Pingback: "BECOME USER" sous Linux | ArKZoYd

  3. Pingback: switch user in Oracle | Laurent Schneider

Comments are closed.