su in sqlplus

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

2 thoughts on “su in sqlplus”

  1. 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.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>