You may be familiar with the noaudit / audit commands in Oracle, but how do you check the current settings?
noaudit all or audit all;
Simple. Just check the DBA_STMT_AUDIT_OPTS table.
SQL> audit all;
Audit succeeded
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS order by user_name,audit_option;
USER_NAME | PROXY_NAME | AUDIT_OPTION | SUCCESS | FAILURE
----------+------------+-------------------+-----------+----------
NULL | NULL | ALTER SYSTEM | BY ACCESS | BY ACCESS
NULL | NULL | CLUSTER | BY ACCESS | BY ACCESS
NULL | NULL | CONTEXT | BY ACCESS | BY ACCESS
NULL | NULL | CREATE SESSION | BY ACCESS | BY ACCESS
NULL | NULL | DATABASE LINK | BY ACCESS | BY ACCESS
NULL | NULL | DIMENSION | BY ACCESS | BY ACCESS
NULL | NULL | DIRECTORY | BY ACCESS | BY ACCESS
NULL | NULL | INDEX | BY ACCESS | BY ACCESS
NULL | NULL | MATERIALIZED VIEW | BY ACCESS | BY ACCESS
NULL | NULL | MINING MODEL | BY ACCESS | BY ACCESS
NULL | NULL | NOT EXISTS | BY ACCESS | BY ACCESS
NULL | NULL | PROCEDURE | BY ACCESS | BY ACCESS
NULL | NULL | PROFILE | BY ACCESS | BY ACCESS
NULL | NULL | PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS
NULL | NULL | PUBLIC SYNONYM | BY ACCESS | BY ACCESS
NULL | NULL | ROLE | BY ACCESS | BY ACCESS
NULL | NULL | ROLLBACK SEGMENT | BY ACCESS | BY ACCESS
NULL | NULL | SEQUENCE | BY ACCESS | BY ACCESS
NULL | NULL | SYNONYM | BY ACCESS | BY ACCESS
NULL | NULL | SYSTEM AUDIT | BY ACCESS | BY ACCESS
NULL | NULL | SYSTEM GRANT | BY ACCESS | BY ACCESS
NULL | NULL | TABLE | BY ACCESS | BY ACCESS
NULL | NULL | TABLESPACE | BY ACCESS | BY ACCESS
NULL | NULL | TRIGGER | BY ACCESS | BY ACCESS
NULL | NULL | TYPE | BY ACCESS | BY ACCESS
NULL | NULL | USER | BY ACCESS | BY ACCESS
NULL | NULL | VIEW | BY ACCESS | BY ACCESS
27 row(s) returned
SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS order by user_name,privilege;
USER_NAME | PROXY_NAME | PRIVILEGE | SUCCESS | FAILURE
----------+------------+----------------+-----------+----------
NULL | NULL | ALTER SYSTEM | BY ACCESS | BY ACCESS
NULL | NULL | AUDIT SYSTEM | BY ACCESS | BY ACCESS
NULL | NULL | CREATE SESSION | BY ACCESS | BY ACCESS
3 row(s) returned
noaudit all;
will remove this audit priv and statment:
SQL> noaudit all;
Noaudit succeeded
SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS order by user_name,privilege;
No rows returned
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS order by user_name,audit_option;
No rows returned
