Audit oracle connections only for specific users

I have seen on few places in the net questions about how to disable logon and logoff audit for only one specific user (usually DBSNMP). This would really come in handy since some applications can have really excessive number of connections to the database. This should of course be addressed in other manner like creating a connection pool, but even then perhaps we don’t need audit for such users and want to keep our AUD$ table neat and clean with only system and named user account audit records.

While there is not a way (at leaset up to 11.2 version ) to disable a connect session audit on specific user, a workaround would be to disable all connect session audit and then re-enable for all users except DBSNMP. Here, like this:

--disable audit for log-on and log-off
noaudit create session;

-- enable audit for users that need to be audited
audit create session by sys;
audit create session by system;
audit create session by dsmoljanovic;

--checkĀ 
select USER_NAME, PRIVILEGE from DBA_PRIV_AUDIT_OPTS where privilege='CREATE SESSION';

USER_NAME      PRIVILEGE
-------------- ---------------
SYS            CREATE SESSION
SYSTEM         CREATE SESSION
DSMOLJANOVIC   CREATE SESSION

Not a very beautiful solution, but does the job. Only thing to remember is to execute audit record when creating a new user.

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.