Proxy user, indirect access to a (client) user * How to $ sqlplus dba@db SQL> create user tmp -- proxy identified by tmpspassword; SQL> alter user app -- client grant connect through tmp; SQL> select proxy, client from proxy_users; -- proxy is tmp, client is app $ sqlplus tmp[app]/tmpspassword@db SQL> show user --app It's important to get the terms right. The proxy is like a springboard over which you jump to get in the "real", client, account. ---------------------------------------------------------------------------------------------------------------------------------- * What sessions are created through proxy logon? select * from v$session where sid in (select sid from v$session_connect_info where authentication_type='PROXY'); ---------------------------------------------------------------------------------------------------------------------------------- * Can't drop proxy unless session established through it exits SQL> drop user tmp; drop user tmp * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected SQL> select * from gv$session where username = 'TMP'; no rows selected SQL> drop user tmp; drop user tmp * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected SQL> select * from proxy_users where proxy = 'TMP'; PROXY CLIENT AUT FLAGS ---------- ---------- --- ----------------------------------- TMP APP NO PROXY MAY ACTIVATE ALL CLIENT ROLES SQL> select os_username, sessionid, timestamp from dba_audit_trail where username = 'TMP' and timestamp > sysdate - 1/48; OS_USERNAME SESSIONID TIMESTAMP --------------- ---------- ----------------- johnsonm 747916114 20140305 13:22:47 SQL> select os_username, username, sessionid, timestamp from dba_audit_trail where proxy_sessionid = 747916114; OS_USERNAME USERNAME SESSIONID TIMESTAMP --------------- ---------- ---------- ----------------- johnsonm APP 747916115 20140305 13:22:47 SQL> select inst_id, sid, serial#, username, logon_time, program, machine from gv$session where audsid = 747916115; INST_ID SID SERIAL# USERNAME LOGON_TIME PROGRAM MACHINE ---------- ---------- ---------- ---------- ----------------- ------------------------- --------------------------- 2 258 199 APP 20140305 13:22:47 sqlplus.exe abc.example.com SQL> alter system kill session '258,199,@2' immediate; ... ---------------------------------------------------------------------------------------------------------------------------------- * Audit sqlplus yong[smjohnston]@db Audit record shows smjohnston logged on, not yong. Unique to a proxy session audit record, proxy_sessionid is not null: SQL> select os_username, username, userhost, timestamp, action_name, returncode, sessionid, proxy_sessionid from dba_audit_session where username in ('YONG','SMJOHNSTON') and timestamp > sysdate - 1/48; OS_USERNAME USERNAME USERHOST TIMESTAMP ACTION_NAME RETURNCODE SESSIONID PROXY_SESSIONID ----------- ---------- ----------------------- ----------------- ----------- ---------- ---------- --------------- yhuang SMJOHNTSON EXAMPLECOM\DCOTW9S8GFX1 20141103 14:20:17 LOGON 0 780199252 780199251 The logged-on session has audsid of v$session matching sessionid of dba_audit_(session|trail): SQL> select sid, username, osuser, terminal, logon_time from v$session where audsid = 780199252; SID USERNAME OSUSER TERMINAL LOGON_TIME ---- ---------- ------ ------------ ----------------- 2282 SMJOHNSTON yhuang DCOTW9S8GFX1 20141103 14:20:17 Before 12c, you may use the query at the bottom of this Oracle document Proxy Users and Auditing Proxy Users (Doc ID 782078.1) to find the sessions in the audit log. Beginning with 12c, unified_audit_trail (if you use it, as recommended) has the column dbproxy_username. So using this view, you'll see both the proxy account and the "real", client, account for this logon action on one row without a cumbersome self-join, e.g. select dbusername, event_timestamp, action_name from unified_audit_trail where dbproxy_username='TMP' order by 2; ---------------------------------------------------------------------------------------------------------------------------------- * Difference in privilege After you logon to the app (proxy's client) through the proxy account, you assume the identity of the app user and have all his privileges except when accessing an object through a DB link. You'll get ERROR at line 1: ORA-25430: connected user database links are not supported in proxy session