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