OraFAQ Forum: Server Administration » How To Get the username that is currently Proxy To

Home » RDBMS Server » Server Administration » How To Get the username that is currently Proxy To (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How To Get the username that is currently Proxy To [message #674664]

Thu, 07 February 2019 15:04

wtolentino
Messages: 422
Registered: March 2005

Senior Member

how can i get the username of the proxy that is currently connected to the database.

for example DemoTest connects to a database using proxy to DemoProxy.

Code: [Select all] [Show/ hide]

sqlplus DemoTest[DemoProxy]/TempPwd123@dbDevelopment

i need to see on the query that it is the DemoTest that is proxied as DemoProxy.

i tried using the v$session and v$session_connect_info in the hope of seeing the DemoTest will appear as the one who is using the DemoProxy but it is not.

Code: [Select all] [Show/ hide]

select * 
  from v$session join V$SESSION_CONNECT_INFO using (sid, serial#) 
 where authentication_type = 'PROXY';

example of results/output:

Code: [Select all] [Show/ hide]

SID	SERIAL#	USERNAME	STATUS	       SCHEMANAME	OSUSER	        MACHINE	        TERMINAL
------- ------- --------------- -------------- ---------------- --------------- --------------- -----------
1348	79436	DEMOPROXY	INACTIVE       DEMOPROXY	scott     	ORAMacIOS	ORAMacIOS
1348	79436	DEMOPROXY	INACTIVE       DEMOPROXY	scott    	ORAMacIOS	ORAMacIOS
1348	79436	DEMOPROXY	INACTIVE       DEMOPROXY	scott   	ORAMacIOS	ORAMacIOS

what i see on the result of the query is that the username is DEMOPROXY and schemaname is DEMOPROXY.

our purpose is to see all of the current users who are using a proxy. we would like to see the actual username who is using the proxy or connected to the proxy.

please help.

thank you.

Report message to a moderator

Re: How To Get the username that is currently Proxy To [message #674666 is a reply to message #674664]

Fri, 08 February 2019 03:49

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

You can do this,

Code: [Select all] [Show/ hide]

orclx>
orclx> conn scott[system]/tiger
Connected.
orclx> select sys_context('userenv','proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------------------------------------
SCOTT

orclx>

but I don't think it is possible to extract this from any v$ view vecause is session information, stored in the PGA, not something the SGA would know about.

Report message to a moderator

Re: How To Get the username that is currently Proxy To [message #674671 is a reply to message #674666]

Fri, 08 February 2019 07:13

wtolentino
Messages: 422
Registered: March 2005

Senior Member

is there any tables or views that i can use to get a list of users who are using proxies?

for example:

Code: [Select all] [Show/ hide]

SID	SERIAL#	USERNAME	STATUS	       PROXYTO	        OSUSER	        MACHINE	        TERMINAL
------- ------- --------------- -------------- ---------------- --------------- --------------- -----------
1348	79436	DEMOTEST	INACTIVE       DEMOPROXY	scott     	ORAMacIOS	ORAMacIOS
1349    79438   SCOTT           INACTIVE       DEMOPROXY	scott     	ORAMacIOS	ORAMacIOS
1355    79439   BILL            INACTIVE       DEMOPROXY	bill     	ORAWinOS	ORAWinOS

we would like to see a list of users who are using proxies. thanks.

Report message to a moderator

Re: How To Get the username that is currently Proxy To [message #674673 is a reply to message #674671]

Fri, 08 February 2019 07:16

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

we would like to see a list of users who are using proxies. thanks.

You haven't bothered to read and think about my previous reply, have you? I would like to see world peace (or at least no Brexit) but it ain't possible.

Report message to a moderator

Re: How To Get the username that is currently Proxy To [message #674675 is a reply to message #674673]

Fri, 08 February 2019 08:04

wtolentino
Messages: 422
Registered: March 2005

Senior Member

yes i did but i was trying to explain more in the hope of i could get some ideas. thanks.

Report message to a moderator

Re: How To Get the username that is currently Proxy To [message #674678 is a reply to message #674675]

Fri, 08 February 2019 10:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Below may be the equivalent to making 3 left hand turns instead of a single right turn, but consider to AUDIT PROXY USER

http://lmgtfy.com/?q=oracle+audit+proxy+user

Then query audit table for recent entries.

Report message to a moderator

Re: How To Get the username that is currently Proxy To [message #674721 is a reply to message #674678]

Tue, 12 February 2019 08:34

wtolentino
Messages: 422
Registered: March 2005

Senior Member

thanks so much saw some that might help. then found the dba_audit_trail might help with the use of column action_name = 'PROXY AUTHENTICATION ONLY'. from that table there is a column sessionid where can I link this column to other tables that might help me get the username, userid, or any user info? I tried to look but not sure.

Report message to a moderator

Re: How To Get the username that is currently Proxy To [message #675137 is a reply to message #674721]

Tue, 12 March 2019 07:10

wtolentino
Messages: 422
Registered: March 2005

Senior Member

the dba_audit_trail helps.

Report message to a moderator

Previous Topic:	Oracle database character set.
Next Topic:	Need Help for Temp Tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 03:16:34 CDT 2025