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)
How To Get the username that is currently Proxy To [message #674664] Thu, 07 February 2019 15:04 Go to next message
wtolentino
Messages: 271
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.

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.
select * 
  from v$session join V$SESSION_CONNECT_INFO using (sid, serial#) 
 where authentication_type = 'PROXY';

example of results/output:
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.
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 Go to previous messageGo to next message
John Watson
Messages: 7868
Registered: January 2010
Location: Global Village
Senior Member
You can do this,
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.
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 Go to previous messageGo to next message
wtolentino
Messages: 271
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:
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.
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 Go to previous messageGo to next message
John Watson
Messages: 7868
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.
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 Go to previous messageGo to next message
wtolentino
Messages: 271
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 26482
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.
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 Go to previous messageGo to next message
wtolentino
Messages: 271
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.
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 Go to previous message
wtolentino
Messages: 271
Registered: March 2005
Senior Member
the dba_audit_trail helps.
Previous Topic: Oracle database character set.
Next Topic: Need Help for Temp Tablespace
Goto Forum:
  


Current Time: Sat Apr 20 13:41:21 CDT 2019