DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases

 Client Identifier (SET_IDENTIFIER and CLEAR_IDENTIFIER)

In many client-server applications it was common to allocate a different database user for each real user of the system. This made it simple to identify and audit the users at the database level. If applications used a single database user and managed security internally, this made identifying the real users of the system difficult. The issue was complicated further by multi-tier architectures that used connection pooling.

To counter this, Oracle 9iR1 introduced the SET_IDENTIFIER and CLEAR_IDENTIFIER procedures to allow the real user to be associated with a session, regardless of what database user was being used for the connection. 

CONN test/test

EXEC sys.DBMS_SESSION.set_identifier('ashish007');

COLUMN username FORMAT A20
COLUMN client_identifier FORMAT A20

SELECT USER , SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;

USER             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 ashish007

SQL>SELECT username, client_identifier FROM v$session WHERE username = 'TRGT';