Hello all,
I`ve been struggling with this for the last couple of days. Basically what I am trying to achieve is detect real user ID/name behind proxy sessions. So the details are below :
Database version : DEFINE
ORELEASE = "1101000700" (CHAR)
Proxy user name : PROXY_USER
So the situation here is that the original database user connects through the proxy user and afterwords the activity cannot be tracked accurately. I do not actually know who is the real user behind PROXY_USER. Most sessions are done through PROXY_USER, as the model is :
CREATE USER PROXY_USER IDENTIFIED BY <PASSWORD>
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO PROXY_USER ;
ALTER USER PROXY_USER QUOTA unlimited ON USERS;
ALTER USER FIRST_USER GRANT CONNECT THROUGH PROXY_USER
ALTER USER SECOND_USER GRANT CONNECT THROUGH PROXY_USER
I did quite an extensive read on the matter and unfortunately all suggests that oracle does not yet have inbuilt mechanism track activity and real user on behalf of which proxy does connect.
This leads to a lot of confusion , for example there are four normal database users that are hidden behind the proxy user and I cannot track which session to which application belongs.
To make things even harder the sys_context function uses only USERENV [http://psoug.org/reference/sys_context.html] to detect proxy_users and session ID. Views I revised and that do contain some info on current proxy sessions are :
DBA_PROXIES All proxy connections
USER_PROXIES Connections the current user can proxy through
PROXY_USERS Users that can do proxy connections
V$SESSION_CONNECT_INFO Network connection information
V$SESSION PROGRAM/MODULE shows proxy-user
None of them does the trick or at least I had limited success in viewing the real sid and schema user behind proxy_user.
Another idea , that in my opinion is best to be done is using :
"EXEC DBMS_SESSION.SET_IDENTIFIER(‘<CID>’);
The variable <CID> should be replaced with the client identifier of the user that connected to theapplication. Whether or not a proxy user connects to the database, Oracle can now accurately trackactions within the database to the correct identity."
I cannot use efficiently set_context because around 7 or 8 users are allowed to connect through proxy.
PROXY CLIENT AUTH AUTHORIZATION_CONSTRAINT PROXY_AUTHORITY
----------- ---------- ----- ----------------------------------- ---------
PROXY_USER DB_USER_1 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
PROXY_USER DB_USER_2 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
PROXY_USER DB_USER_3 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
PROXY_USER DB_USER_4 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
PROXY_USER DB_USER_5 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
PROXY_USER DB_USER_6 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
PROXY_USER DB_USER_7 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
PROXY_USER DB_USER_8 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
Any IDEAS , hints , thoughts on how I can dynamically view the connections for all users ? This would mean to identify the original user/schema behind the proxy user . Is there any join of vies that could help ? I tried a lot of variants via v$session and v$processes including
V$SESSION_CONNECT_INFO and audit_trail but in vain.
Thank you.
Regards,
Emil Bolokanov