Using Proxy Connections with SQL Developer or SQLPLUS

When trying to debug for another user sometimes it is helpful to be able to log in as that user. In the past magic was done by admins by storing the encrypted passwords, setting to a new temporary password, logging in, and then resetting the old encrypted password back.

Luckily modern Oracle versions we have the concept of a Proxy user. This allows connecting as one user but masquerading as another user (once proper permissions have been granted of course).

So far I have done this with SQL Developer and SQLPLUS, instructions are below for a test case.

I included an abbreviated SCOTT user below, you can always install a more complete version via the demo scripts installed with your RDBMS server.

Setup

CREATE USER scott IDENTIFIED by tiger;
GRANT CONNECT, CREATE TABLE TO SCOTT;
CONNECT scott/tiger
CREATE TABLE test_tab (ID NUMBER PRIMARY KEY, TEXTFIELD VARCHAR2(255));

CREATE USER dave IDENTIFIED BY kitten; 
GRANT CONNECT TO DAVE;

ALTER USER SCOTT GRANT CONNECT THROUGH DAVE;

Now connect through DAVE into SCOTT user using SQLPLUS:

SQLPLUS DAVE[scott]/kitten@mydb
select user from dual;
USER
------------
SCOTT

select table_name from user_tables;
TABLE_NAME
------------
TEST_TAB

-- Hey! I'm logged in as DAVE but I am now acting like SCOTT! 

SQL Developer Method 1
Now in SQL Developer open the Connection Screen and enter:

• Connection Name: MYDB - Dave>Kitten
• Username: DAVE[scott]
• Password: kitten

SQL Developer Method 2
When setting up the connection you can also click the Proxy connection box, Select User Name and enter Username in "Proxy Client" box

Clean up after yourself!
Since this is only for temporary debugging be safe and remove the proxy privilege.

ALTER USER SCOTT REVOKE CONNECT THROUGH DAVE;

If interested in doing this full time I recommend reading the documentation in the References section below to be aware of all the pros and cons of using this connection method.

References
Oracle Documentation here: link
Blog Post about Proxy + SQLPLUS link