11g auditing options and relevant tables and other information – for the new oracle dba




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

oracle auditing 11g

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.

 

Author: admin