you are a new oracle dba and would like to know about the various auditing options in an oracle database and also the tables which you could verify to find out the existing enabled audit options. In 11g the default auditing level has increased .
more info can be found in the oracle documentation
Listing Active Statement Audit Options
The following query returns all the statement audit options that are set:
SELECT * FROM DBA_STMT_AUDIT_OPTS;
USER_NAME AUDIT_OPTION SUCCESS FAILURE
——————– ——————- ———- ———
JWARD DROP ANY CLUSTER BY ACCESS BY ACCESS
SWILLIAMS DEBUG PROCEDURE BY ACCESS BY ACCESS
MSEDLAK ALTER RESOURCE COST BY ACCESS BY ACCESS
Listing Active Privilege Audit Options
The following query returns all the privilege audit options that are set:
SELECT * FROM DBA_PRIV_AUDIT_OPTS;
USER_NAME PRIVILEGE SUCCESS FAILURE
——————- ——————– ——— ———-
ALTER USER BY ACCESS BY ACCESS
Listing Active Object Audit Options for Specific Objects
The following query returns all audit options set for any objects with names that start
with the characters emp and that are contained in the schema of laurel:
SELECT * FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = ‘LAUREL’ AND OBJECT_NAME LIKE ‘EMP%’;
OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC …
—– ———– ——— — — — — — — — — …
LAUREL EMP TABLE S/S -/- -/- A/- -/- S/S -/- -/- …
LAUREL EMPLOYEE VIEW -/- -/- -/- A/- -/- S/S -/- -/- …
The view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:
■ A dash (-) indicates that the audit option is not set.
■ The S character indicates that the audit option is set BY ACCESS.
■ The A character indicates that the audit option is set BY ACCESS.
■ Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER
NOT SUCCESSFUL, separated by a slash (/). For example, the DELETE audit option for
laurel.emp is set BY ACCESS for successful DELETE statements and not set at all for
unsuccessful DELETE statements.