Oracle 12c has implemented the unified auditing which is based off of system and user level policies.   This article describes the use of audit policies.

 

 

Audit Policy General Aspects.

1. A unified audit policy is a named group of audit setting that track a particular aspect of user behavior in DB.
2. The CREATE AUDIT POLICY is used to create unified audit policy.
3. Multiple audit policy can be active at any given time.
4. AUDIT and NOAUDIT statements enable and disable.
5. The AUDIT statement can include or exclude specific users.
6. AUDIT and NOAUDIT can enable audit application context values.
7. Activities that can be audited:
   1. USER ACCOUNTS, ROLES, and PRIVILEGEs
   2. OBJECT ACTION: drop table; running a procedure.
   3. APPLICATION CONTEXT VALUES.

 

8. You should group auditing into related policies and attempt to minimize the number of policies defined.
9. Many policies increase overhead in the UGA.
10. The CREATE AUDIT POLICY statement creates a first class object in the SYS schema not in the schema of the creator.
11. Once policy is created, it must be enabled with the AUDIT statement with POLICY clause.
12. CREATE AUDIT POLICY and AUDIT statement do not affect current sessions, user must disconnect and reconnect for auditing to start.
13. CLAUSES of the AUDIT statement.

BY — Used to apply the unified audit policy to one or more users.

AUDIT POLICY change_table_pol BY ocpuser;

 

EXCEPT — Used to exclude users from the unified audit policy.

AUDIT POLICY change_table_pol EXCEPT jtkirk, jlpicard;

 

WHENEVER SUCCESSFUL — Records only successful executions of the audited activity.

AUDIT change_table_pol WHENEVER SUCCESSFUL;

 

WHENEVER NOT SUCCESSFUL — Records only failed executions of the audited activity.

AUDIT change_table_pol WHENEVER NOT SUCCESSFUL;

 

Note the following:

WHENEVER — If the WHENEVER clause is omitted, then both failed and successful user activities are written to the audit trail.

 

BY/ EXCEPT — A unified audit policy can be enabled with either the BY clause or the EXCEPT clause, but not both simultaneously.

 

AUDIT… BY — If multiple AUDIT statements are executed on the same unified audit policy with different BY users, all of the specified users are audited.

 

AUDIT… EXCEPT — If multiple AUDIT statements are executed on the same unified audit policy with different EXCEPT users, only the last exception user list is used.

 

COMMON policies — Common unified audit policies can only be enabled from the root and only for common users.

 

LOCAL policies — Local audit policies can only be enabled from the PDB to which it applies.

 

14. No audit policy is necessary to record RMAN activities. RMAN activities are recorded automatically.

RMAN_SESSION_RECID — Recovery Manager session identifier. Together with the RMAN_SESSION_STAMP column, this column uniquely identifies the Recovery Manager job. RMAN_SESSION_STAMP — Timestamp for the session.

RMAN_OPERATION — The Recovery Manager operation executed by the job.

RMAN_OBJECT_TYPE — Type of objects involved in a Recovery Manager session.

RMAN_DEVICE_TYPE — Device associated with a Recovery Manager session.

 

 

 

Larry Catt

OCP