Oracle Get Current Username When Using a Proxy User

Question

Is it possible to get the true user name of a user in OracleDB when the user in question is connected via a proxy user account?

True User Name: i_am_the_true_user_name Proxy User Name: i_am_the_proxy_user_name

What I have tried:

1. Query: Select user from dual;

Desired Result: i_am_the_true_user_name Actual Result: i_am_the_proxy_user_name

2. Query: SELECT username FROM user_users;

Desired Result: i_am_the_true_user_name Actual Result: i_am_the_proxy_user_name

Answer 1

Try this:

select sys_context('userenv','proxy_user') from dual;

The proxy account name (the developer’s personal account) is available in the system session context, and can be automatically made visible in the v$session view through a database trigger so that the DBA can tell who is connected to shared accounts at all times. Note: make sure you're not using the DBMS_SESSION.SET_IDENTIFIER functionality for anything else before implementing this.

CREATE OR REPLACE TRIGGER db_session_trig 
AFTER LOGON ON DATABASE
  v_proxy_user varchar2;
BEGIN
  v_proxy_user := sys_context('userenv','proxy_user');
  if v_proxy_user is not null then
    dbms_session.set_identifier(v_proxy_user);
  end if;
END;

select username, osuser, client_identifier 
from v$session where username='I_AM_THE_TRUE_USER_NAME';

USERNAME                OSUSER    CLIENT_IDENTIFIER 
----------------------- --------- ----------------------
I_AM_THE_TRUE_USER_NAME oracle    I_AM_THE_PROXY_USER_NAME