Proxy Accounts Tracking

If you are using Proxy accounts to access users, for example as a DBA to do a release to a schema owner, how do you know the login was via proxy instead of directly with the password? This information is handily recorded in the audit, assuming you have audit switched on and use “audit session”.

I would recommend that everyone does this anyway, as it’s a useful tool when troubleshooting connection problems – you can easily see if the connection is making it to the database (not getting beyond the listener) which narrows the problem scope. If it does get to the database, you can see the returncode of why the connection has a problem; ORA-1005 (no password), 1017 (wrong password) or 28000’s (expired password, locked account, etc)

Lets have a look at what we get in the audit record when logging on using proxy authentication. I will create a “schema owner”, give the DBA “neil” permission to proxy to it, and then see what we get in the audit trail. I’ll truncate the audit trail first, so it’s nice and clean.

SQL> connect sys/oracle as sysdba
Connected.

SQL> truncate table sys.aud$;
Table truncated.

SQL> drop user schema_owner;
User dropped.

SQL> create user schema_owner identified by icantrememberthepassword;
User created.

SQL> grant connect,resource,select any dictionary to schema_owner;
Grant succeeded.

SQL> alter user schema_owner grant connect through neil;
User altered.

SQL> show user
USER is "SYS"

SQL> connect neil[schema_owner]/neil
Connected.

SQL> show user
USER is "SCHEMA_OWNER"

SQL> select username,returncode,action_name,sessionid,proxy_sessionid
SQL> from dba_audit_session;

USERNAME        RETURNCODE ACTION_NAME                  SESSIONID  PROXY_SESSIONID
~~~~~~~~~~~~~~- ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~~~~~~
SCHEMA_OWNER             0 LOGON                           9530094         9530093

Only 1 record in there. I can see the login, but where’s the proxy session identified? We need to use dba_audit_trail, not dba_audit_session

SQL> select username,returncode,action_name,comment_text,sessionid,proxy_sessionid from dba_audit_trail order by sessionid;
USERNAME        RETURNCODE ACTION_NAME                  COMMENT_TEXT                                                                                         SESSIONID  PROXY_SESSIONID
~~~~~~~~~~~~~~- ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~~~~~~
NEIL                     0 PROXY AUTHENTICATION ONLY    Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=48287))        9530093
SCHEMA_OWNER             0 LOGON                        Authenticated by: PROXY; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=48287))           9530094         9530093

And there you go. The session and the proxy used to authenticate into it.