Thursday, December 18, 2014

SUCCESS and FAILURE Columns in DBA_STMT_AUDIT_OPTS

I tested this in Oracle 10.2. DBA_STMT_AUDIT_OPTS records auditing options which are currently in effect. If no auditing has been requested, it will be empty:
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>
 
You can audit successful connections as follows:
 
SQL> audit create session by system whenever successful
  2  /
 
Audit succeeded.
 
SQL>
 
This will then be recorded in DBA_STMT_AUDIT_OPTS. The SUCCESS column will be set to BY ACCESS to show that only successful connections are being audited:   
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE
---------- --------------- ---------- ----------
SYSTEM     CREATE SESSION  BY ACCESS  NOT SET
 
SQL>
 
You can stop the auditing like this:
 
SQL> noaudit create session by system whenever successful
  2  /
 
Noaudit succeeded.
 
SQL>
 
…and the entry will disappear from DBA_STMT_AUDIT_OPTS:
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>
 
Here is how you audit unsuccessful connections. Note that in the context of the AUDIT and NOAUDIT commands, CREATE SESSION and CONNECT have the same meaning:
 
SQL> audit connect by system whenever not successful
  2  /
 
Audit succeeded.
 
SQL>
 
This will also be recorded in DBA_STMT_AUDIT_OPTS but the FAILURE column will be set to BY ACCESS to show that only failed connections are being audited:   
 
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE
---------- --------------- ---------- ----------
SYSTEM     CREATE SESSION  NOT SET    BY ACCESS
 
SQL>
 
As before, stopping the auditing removes the entry from DBA_STMT_AUDIT_OPTS:
 
SQL> noaudit connect by system whenever not successful
  2  /
 
Noaudit succeeded.
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>

No comments: