OVERVIEW:

                With oracle 12c, unified auditing has been introduced. It consolidates all audit trails into a single audit trail table.

It will capture audit records from below sources.

  1. SYS audit records ( which was written to os trail in traditional method, will now be written to db table)
  2. Unified audit policies for different action/privilege/statement/role etc.
  3. EXPDP/IMPDP events
  4. RMAN events
  5. Sql loader

TRADITIONAL VS UNIFIED

 

TRADITIONAL AUDITINGUNIFIED_AUDITING
Depends on db init parameter like audit_trail,audit_sys_logIndependent of db parameter,bydefault enabled
writes audit records to different trails depending upon audit typeall audit trails are writeen to single trail
sys records are written to os .aud filessys records are written to unified_audit_trail
auditing not possible for rman/expdp/sqllderauditing can be enabled for db compoenents like rman/datapump/sqlldr
each audit record was written to disk immediately, which causes i/o issueIf querywrite method is enabled(default), then all audit records will queued in sga
and later will be flushed to disk , which improves performance
auditing need to enabled for each action/statement individually.One policy can contain mulitiple actions/privilge/role audit option and
which can enabled or disables easily

There are two types of unified auditing mode in oracle 12c.

MODE OF UNIFIED_AUDITING:

1. Mixed auditing – By default it is enable in 12c. It enables to use both traditional auditing and unified auditing methods. I.e. apart from traditional auditing we can use all the features of unified auditing. Once we are comfortable with the unified concept, we can migrate exiting audit setup to unified policy , we can enable pure auditing.
This serves as a good mediator for an easy and hassle free switch to the preferred Unified auditing.
2. Pure auditing – Once pure auditing is enabled. We cannot use the traditional auditing methods.

Which unified auditing mode enabled for my database

FALSE – > MIXED AUDTING
TRUE –> PURE AUDITING:
How to change from MIXED to PURE auditing:(relink library)

NOTE – FOR RAC, linking need to be done all the nodes

WHAT IS UNIFIED AUDIT POLICY AND HOW IT WORKS:

Unified audit policy is like a group of audit options with different conditions. It is like a ROLE which is a group of privileges.
For enabling auditing , first need to create a policy with different audit options and then need to enable or disable for all or few users depending upon the requirement.
All the audit records will be stored in unified_audit_trail table. By default 7 audit policies will be present in a 12c database.
DEFAULT POLICIES IN 12C DATABASE

But not all are enabled. Query AUDIT_UNIFIED_ENABLED_POLICIES to find, which policies are enabled.
 Query to find which policies are enabled

 Query to check the audit options included in a policy:

Even if no new policy is created in database, Audit action of the above audit options will be recorded in unified_audit_trail.

Below are few test cases on unified audit policy :

TEST CASE 1 : ( default audit option):

DROP DIRECTORY , which is one of the audit option of the default policy ORA_SECURECONFIG.

Connect to bsstdba and drop a directory

Check the audit report

TEST CASE 2 : CREATE AUDIT POLICY WITH MULTIPLE AUDIT OPTIONS:

Unless we enable the policy, auditing conditions wont be evaluated

Do some changes and generate audit report:

EXCLUDE ONE USER FROM THE POLICY:

Once audit policy is enabled, if we try to enable again, it will throw error. So to change the audit condition, disable and enable with new condition.

Now create a table from stcdba.

We can see the new audit action ( CREATE TABLE TEST4 is not recorded in audit trail table) as expected.
We can mention success/failure condition similar to traditional auditing:

audit policy TEST_CASE2 whenever successful;
audit policy TEST_CASE2 Whenever not successful;

3. TEST_CASE 3 :

Create an audit policy, to audit delete on table bsstdba.EMP_TAB,insert on bsstdba.PROD_TAB and update on bsstdba.SAL_TAB TABLE BY user STCDBA.

This can be achieved by using the same method of test_case2, But here we will define the condition in the audit policy itself, instead of mentioning it while enabling audit.

EVALUATE PER refers to the following options:
 STATEMENT evaluates the condition for each relevant auditable statement that
occurs.
 SESSION evaluates the condition only once during the session, and then caches
and re-uses the result during the remainder of the session. Oracle Database
evaluates the condition the first time the policy is used, and then stores the result
in UGA memory afterward.
 INSTANCE evaluates the condition only once during the database instance
lifetime. After Oracle Database evaluates the condition, it caches and re-uses the
result for the remainder of the instance lifetime. As with the SESSION evaluation,
the evaluation takes place the first time it is needed, and then the results are stored
in UGA memory afterward

TESTCASE_4:

Create an audit policy, to audit insert on bsstdba.PROD_TAB and update on bsstdba.SAL_TAB TABLE WHEN USER_NAME NOT IN (‘STCDBA’,’TCSDBA’)

No audit record found for stcdba as expected.

TEST_CASE 5 ( AUDITING ROLE)

It will audit all users using a particular ROLE

Create an user with dba privs

 Enable audit for the role DBA

 Do any dba activity and check report

TEST CASE 6 ( FILTER BY HOSTNAME)

Now we can define to exclude auditing for few hosts

DATAPUMP AUDITING:

TEST_CASE7 ( AUDITING FOR SYSTEM PRIVILEGES):

We can enable auditing for system privileges as below.

NOTE- Instead of privilege keyword , I have created policy with action keyword in test_case2 , and it is showing similar result.

SYS AUDITING:

With mix auditing, sys audit records will be written to both os level and unified_trail also if audit action policy is enable.

As per Oracle doc:
FOR MIXED AUDITING
Administrative user sessions generate SYS audit records. These records are written if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.This process writes the records only to the traditional audit trails. However, when unified audit policies are enabled for administrative users,
these unified audit records are also written to unified audit trail.

FOR PURE UNIFIED AUDITING:
All sys audit records will be written to UNIFIED_AUDIT_TRAIL TABLE ONLY

DROPPING AUDIT POLICY:

We can’t drop a policy when it is enabled.

PURGE AUDIT TRAIL:

Purging mechanism is similar to that of previous dbms_audit_mgmt(11g) , Only we need to set the audit_trail_type to unified

Refer – Purge audit table using dbms_mgmt

AUDIT WRITE MODE:

Oracle 12c writes audit records first to SGA. And then periodically flush the queue to AUDSYS schema audit table in sysaux table.

MODES:

1. QUEUED-WRITE MODE (DEFAULT) – PERIODICALLY FLUSHED TO DISK
2. IMMEDIATE-WRITE MODE – IMMEDIATELY FLUSHED ( PRE 12C MODE, NOT RECOMMENDED)

Role segregation:

AUDYS is the schema, which keeps audit trail info. No user can connect to this user including SYS.

Below two new roles are present in 12c

AUDIT_ADMIN – For creating and managing audit policies
AUDIT_VIEWER – For viewing audit reports ( Mostly for auditors)