Unified auditing released with 12c Oracle database provides audit solutions for a variety of sources. It provides a read-only table in AUDSYS schema in SYSAUX tablespace which should be changed to custom tablespace as a best practice. This information is then available through UNIFIED_AUDIT_TRAIL dictionary view.
Audit data is written to unified audit trail when database is write but cases where it’s not these records are written in directory $ORACLE_BASE/audit/$ORACLE_SID.
Enable Unified Auditing
In order to check whether unified auditing is already enabled v$option dictionary view can be used.
select value from v$option where parameter = 'Unified Auditing';
When value column comes with TRUE this means unified audit trail is enabled and for other value we need to enable it.
In order to enable the unified auditing we need to stop the database services and run the make command as shown below
srvctl stop database -d <db_unique_name>
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
srvctl start database -d <db_unique_name>
select value from v$option where parameter = 'Unified Auditing';
Unified Auditing Policies
In order to declare what all actions are going to get audited in unified audit trail, unified auditing policies needs to be created. Here is one sample of unified auditing as per CIS compliance for 19c database
create audit policy <policy name>
actions create user, alter user, drop user;
--roles
alter audit policy <policy name> add actions create role;
alter audit policy <policy name> add actions alter role;
alter audit policy <policy name> add actions drop role;
--grant/revoke
alter audit policy <policy name> add actions grant;
alter audit policy <policy name> add actions create revoke;
--profile
alter audit policy <policy name> add actions create profile;
alter audit policy <policy name> add actions alter profile;
alter audit policy <policy name> add actions drop profile;
--lockdown profile
alter audit policy <policy name> add actions create lockdown profile;
alter audit policy <policy name> add actions alter lockdown profile;
alter audit policy <policy name> add actions drop lockdown profile;
--db links
alter audit policy <policy name> add actions create database link;
alter audit policy <policy name> add actions alter database link;
alter audit policy <policy name> add actions drop database link;
--synonyms
alter audit policy <policy name> add actions create synonym;
alter audit policy <policy name> add actions alter synonym;
alter audit policy <policy name> add actions drop synonym;
--triggers
alter audit policy <policy name> add actions create trigger;
alter audit policy <policy name> add actions alter trigger;
alter audit policy <policy name> add actions drop trigger;
--libraries
alter audit policy <policy name> add actions create library;
alter audit policy <policy name> add actions alter library;
alter audit policy <policy name> add actions drop library;
-stored program units
alter audit policy <policy name> add actions create procedure, create function, create package, create package body;
alter audit policy <policy name> add actions alter procedure, alter function, alter package, alter package body;
alter audit policy <policy name> add actions drop procedure, drop function, drop package, drop package body;
--privileges
alter audit policy <policy name> add privileges select any dictionary;
alter audit policy <policy name> add actions all on audsys.unified_audit_trail;
alter audit policy <policy name> add actions alter system;
--logins
alter audit policy <policy name> add actions logon, logoff;
Enable unified audit policy is straight forward and this can done using:
audit policy <policy name>;Disable unified audit policy
noaudit policy <policy name>;To determine what all policies are enabled on the database
col user_name format A10
col policy_name format A60
select * from AUDIT_UNIFIED_ENABLED_POLICIES;
To determine all policies exists in the database enabled or disabled
col POLICY_NAME format A60
col AUDIT_OPTION format A40
set PAGES 100
select distinct POLICY_NAME
from AUDIT_UNIFIED_POLICIES;Query Audit Data
Once we have enabled unified auditing and enabled the policies we should know how we need to query this data to make use of it. This can be done using following queries
col action_name format A16
col policy_name format A18
col system_privilege_used format A20
col EVENT_TIMESTAMP format A30
select dbusername, action_name, system_privilege_used, event_timestamp, sql_text, return_code, audit_type, audit_option from unified audit_trail
where upper(DBUSERNAME)=('<USERNAME>') and action_name in ('LOGON','LOGOFF) order by EVENT_TIMESTAMP;Unified auditing in short has made things simple which were bit scattered all over the places with earlier releases. I would highly recommend to use this if anyone on older auditing styles looking at it’s simplicity and easy management.
YOU MAY ALSO LIKE MY OTHER BLOGS:
ORACLE DATABASE INTERVIEW QUESTIONS
GENERATE 10046 TRACE (SQL_TRACE) FOR PERFORMANCE ANALYSIS
