Connect Through a Proxy User Account

If you have required privileges, it’s possible to connect as a user account without knowing or changing his password. This is called proxy connection. To authorize a user account to connect using a proxy account, use the GRANT CONNECT THROUGH clause of the ALTER USER statement.

Let’s set say we don’t know password of SCOTT user. I connect with my dba account and set “GOKHAN” user as a proxy for SCOTT:

ALTER USER scott GRANT CONNECT THROUGH gokhan;

1	ALTER USER scott GRANT CONNECT THROUGH gokhan;

Now we can connect using the GOKHAN proxy user:

CONNECT gokhan[scott]
Enter password: <i>gokhan_password</i>
Connected.

SELECT USER FROM DUAL;

USER
------------------------------
SCOTT

CONNECT gokhan[scott]

Enter password: <i>gokhan_password</i>

Connected.

SELECT USER FROM DUAL;

USER

------------------------------

SCOTT

Use the REVOKE CONNECT THROUGH clause of ALTER USER to disallow a proxy connection:

ALTER USER scott REVOKE CONNECT THROUGH gokhan;

CONNECT gokhan[scott]
Enter password:
ERROR:
ORA-28150: proxy not authorized to connect as client

ALTER USER scott REVOKE CONNECT THROUGH gokhan;

CONNECT gokhan[scott]

Enter password:

ERROR:

ORA-28150: proxy not authorized to connect as client

Please share

13 Comments

Balwan Singh

January 23, 2013 Reply

Great and helpful article Gokhan. Thanks a lot for your efforts.
- Gokhan Atil
  
  January 25, 2013
  
  Balwan, you are welcome! I’m happy to see it helps.
Matheus Sato

April 12, 2013 Reply

Hello,

What are the dictionary tables related to proxy privileges ?
How I find witch user have access to connect (as another user) using proxy?

Best Regards,
Matheus
- Alexandr
  
  April 20, 2015
  
  Hi! Use dba_proxies view.
Friedhold Matz

May 9, 2013 Reply

Hello,

many thanks,
I got the error ORA-28150 trying Oracle WFS and
solved this with your tip.

Best Regards,
Friedhold
- Gokhan Atil
  
  June 2, 2013
  
  You are welcome, Friedhold 🙂
ahmed

May 30, 2013 Reply

Hi Gokhan,

is there any way to audit “gokhan” user activities on the DB?
- Gokhan Atil
  
  June 2, 2013
  
  Yes there is. Please check Oracle documents:
  
  http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm
  
  And don’t forget to check DBA_PRIV_AUDIT_OPTS
Boris

May 26, 2014 Reply

Gokhan,

I like your article and I like your CSS to show samples. you know – text boxes with toggle/copy icons, etc. I would like to use the same approach for my page about Oracle VPD. Can I use your CSS?
- Gokhan Atil
  
  May 28, 2014
  
  Boris, I use “Crayon Syntax Highlighter” plugin for wordpress. You may download it from:
  
  https://github.com/aramk/crayon-syntax-highlighter
Mehmet Simsek

December 22, 2014 Reply

to get the username you can use the following package:

SYS_CONTEXT(‘USERENV’,’PROXY_USER’)
Christian Klammer

October 21, 2015 Reply

Hello Gokhan,

very usefull … thank you so much!

Christian
kirillk2

February 3, 2016 Reply

Hello, how revoke privilege “alter user <current_schema_client>” from logged proxy user?