Search
Generic filters
Exact matches only
Filter by Custom Post Type

Unified Auditing In Oracle Database : A quick example

What is Unified Auditing

Unified Audit  is a new auditing feature introduced in Oracle 12.1 . It consolidates all audit trails into a single unified audit trail table, improve audit performance, secure audit data for all RDBMS and other components like RMAN and Data Pump, and simplify audit configuration.

Once enabled , all the auditing information will be stored in a read only table AUDSYS. The audit information can be viewed via data dictionary view SYS.UNIFIED_AUDIT_TRAIL

How it workes

Unified Audit works in three simple steps

  1. Create Audit Policy
  2. Enable Unified Auditing
  3. Enable Audit Policy
  4. Viewing the audit data.

Creating Audit Policy

Audit Policy is set of  audit options that helps  to audit user actions in the database by using system, object, or role privileges.

Audit Policy is created by ” Create Audit Policy Command” .The user should have granted with  “AUDIT_ADMIN” Role to create audit policies 

How to enabled Unified Auditing

From 12.1 Oracle supports two types of auditing modes

  1. Mixed Auditing Mode
  2. Unified Auditing Mode

Mixed audit mode ( default mode) , allows traditional audit engine as well as new audit engine to work simultaneously .  We can run all traditional audit commands as well as create audit policies as in Unified Audit.

Run the below command to find the Audit mode
SQL >SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
If the result is ‘FALSE’ , it means the Mixed Audit Mode is enabled. If the result is “TRUE”, it means Unified Audit Mode is enabled. By default mixed audit mode will be enabled . Let’s see how to enabled Unified Audit Mode.

Enable Unified Auditing

Perform the following steps as oracle software owner.
$ sqlplus / as sysdba 
SQL > SHUTDOWN IMMEDIATE;
SQL > EXIT


$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

$ sqlplus / as sysdba 
SQL > STARTUP;
SQL > EXIT 
SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

Enable Mixed Audit Mode

To enable the Mixed Audit mode , execute the below steps as Oracle Software Owner.
$ sqlplus / as sysdba 
SQL > SHUTDOWN IMMEDIATE;
SQL > EXIT


$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_off ioracle

$ sqlplus / as sysdba <<EOF
SQL> STARTUP;
SQL> EXIT
SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

Enabling Audit Policy

Even though. we have created audit policy , it will not be in effect until its enabled . The below example shows how to enable audit policy

SQL> audit policy create_table_policy;

The above command enables the audit policy “create_table_policy” and audits whenever the user actions uses the  privileges associated by the policy . For eg: when an user creates the table .In this case , “create table ” privilege is used.

Viewing the Unified Audit Data

Unified audit data can be viewed from the data dictionary view SYS.UNIFIED_AUDIT_TRAIL. For viewing  this information the user should have assigned with the role AUDIT_VIEWER

Dropping The Audit Policy

    1. Disable The audit Policy
SQL> Noaudit policy create_table_policy; 
    1. Drop the audit Policy
SQL> Drop audit policy create_table_policy; 

The complete Example.

In this example , we will enable the unified audit to audit whenever a table table is created in this database. Here we assume that , Unified Audit is enabled at the database . If not , please enable before proceeding with this example.
$ sqlplus "/ as sysdba"

SQL> create audit policy create_table_policy privileges create table,create any table;

SQL> audit policy create_table_policy;

SQL> connect wysheid/wysheid

SQL> create table mytab(name varchar2(100));

View and spool the output to csv format.

In this example , we will spool the output of unified audit in a CSV format, so that it can be viewed on  MS EXCEL.

Run below commands from SQL Prompt .The user should be granted with AUDIT_VIEWER database role.

COL action_name FOR A20
COL event_timestamp FOR A30
COL dbusername FOR A10
COL object_schema FOR A10
COL object_name FOR A20
SET COLSEP '#'
Set linesize 200
spool unified_audit.csv

SELECT action_name,
dbusername,
action_name,
object_schema,
object_name,event_timestamp
FROM unified_audit_trail
ORDER BY event_timestamp;
spool off;
exit;

Liked this Article.?

We have lot more interesting posts like this!  Join more than 5000 subscribers who stays ahead on technology.

By entering your email, you agree to the Terms and Conditions and Privacy Policy and cookies usage

You Make like the below posts

About The Author

Share on

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest

Leave a comment

Your email address will not be published. Required fields are marked *

Can't Find What You Are Looking For?.

Please enter the details below. We will try our best for you :-)

We have more amazing posts

Try searching here

lick edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

We assure our best to meet up to your expectations

Your Feedback will help us

Glad That You Liked It

Your Feedback will help us to improve