Neil Chandler's DB Blog

A resource for Database Professionals

  • Home
  • About Neil
  • Video Tutorials
    • Why Has My Plan Changed. Top 7 Plan Stability Pitfalls and How To Avoid Them!
    • Oracle Database Stats – the easy way!
    • Oracle Database Statistics – When It’s Harder
    • Histograms Are Evil like Chocolate is Evil
    • Oracle Execution Plan Changes
    • Using JSON in Oracle
  • MASH Program
Posts Comments
  • Administration
    • oracle
    • Performance and Tuning
    • RAC
    • audit
  • UKOUG
  • Problem Solving
  • Management
  • Programming
  • SQL

Proxy Accounts Tracking

8th April 2016 Leave a comment

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.

Filed under Administration, audit, oracle, Security Tagged with account, audit, connect, dba_audit_session, dba_audit_trail, oracle, proxy, session

Oracle Ace Director

Categories

Archives

MyWebsite

  • Neil Chandler
Follow Neil Chandler's DB Blog on WordPress.com
My Tweets

Meta

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.com

Create a website or blog at WordPress.com