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:
- The actual user name if by user auditing
- ANY CLIENT if access by a proxy on behalf of a client is being audited;
- 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:
Let's start auditing an object:
You can also be more specific and only update certain actions:
Verify that audit is enabled:
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:
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
Verify that queries on the table are now not being audited:
SELECT * FROM DBA_PRIV_AUDIT_OPTS; no rows selected
SELECT * FROM DBA_OBJ_AUDIT_OPTS; no rows selected
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
and
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