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:
Post a Comment