How can I check which tables that are being audited in my database?

The experiences in this article is applicable to all Oracle versions using classic auditing setup.


To check which tables are currently being audited, use the views DBA_OBJ_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS.

DBA_PRIV_AUDIT_OPTS describes current system privileges being audited across the system and by user. USERNAME can hold three different values: 

1. The actual user name if by user auditing
2. ANY CLIENT if access by a proxy on behalf of a client is being audited; 
3. NULL for system-wide auditing

DBA_OBJ_AUDIT_OPTS describes auditing options on all objects. 

If no objects or system privileges is being audited, the queries will return zero rows as in the examples below:

SELECT *
FROM DBA_PRIV_AUDIT_OPTS;

no rows selected

SELECT *
FROM DBA_OBJ_AUDIT_OPTS;

no rows selected

Let's start auditing an object:

AUDIT ALL ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

You can also be more specific and only update certain actions:

AUDIT UPDATE,DELETE,INSERT ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

Verify that audit is enabled:

SELECT *
FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'SCOTT'
AND object_name='BASELINE_STG_TBL';

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/-	-/-	-/-	-/-	-/-	S/S

The notation 'S/S' in the different columns of the DBA_OBJ_AUDIT_OPTS is "frequency" where frequency is either "By Session" (S) or "By Access" (A).

The placement of the hyphen, the "S" or the "A" indicates under what circumstances the auditing should be performed, as outlined below:

-/-: no auditing
S/-: auditing whenever successful
-/S: auditing whenever not successful

As can be seen by the result above, by default, auditing is done by SESSION, both for successful and unsuccessful attempts.
Let's turn off auditing for SELECTs

NOAUDIT SELECT ON SCOTT.BASELINE_STG_TBL;

Noaudit succeeded.

Verify that queries on the table are now not being audited:

SELECT *
 FROM DBA_OBJ_AUDIT_OPTS
 WHERE OWNER = 'SCOTT'
 AND object_name='BASELINE_STG_TBL';

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/-	S/S	-/-	-/-	-/-	-/-	-/-	S/S

Notice that the SEL column now shows a hyphen (-) since the privilege is not audited anymore.

The following statement

AUDIT SELECT ON SCOTT.BASELINE_STG_TBL BY ACCESS WHENEVER NOT SUCCESSFUL;

Would result in

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/A	S/S	-/-	-/-	-/-	-/-	-/-	S/S

and

AUDIT SELECT ON SCOTT.COUNTRY_TABLE BY ACCESS WHENEVER SUCCESSFUL;

would result in

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	A/-	S/S	-/-	-/-	-/-	-/-	-/-	S/S

Keep in mind that mandatory auditing is always turned on. 

Oracle 12.2 Documentation for DBA_OBJ_AUDIT_OPTS can be found here