This post is about Unified Audit Trail In Oracle 12c . Few starting lines give the overview of ” Unified Audit Trail ” . 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.
SYS audit records ( which was written to os trail in traditional method, will now be written to db table)
Unified audit policies for different action/privilege/statement/role etc.
EXPDP/IMPDP events
RMAN events
Sql loader
TRADITIONAL VS UNIFIED
TRADITIONAL AUDITING
- Depends on DB init parameter like audit_trail,audit_sys_log.
- writes audit records to different trails depending upon audit type.
- sys records are written to os .aud files.
- auditing not possible for rman/expdp/sqllder
- each audit record was written to disk immediately, which causes i/o issue
- auditing need to enabled for each action/statement individually.
UNIFIED_AUDITING
- Independent of db parameter, by default, enabled.
- all audit trails are written to a single trail.
- sys records are written to unified_audit_trail.
- auditing can be enabled with db components like rman/datapump/sqlldr.
- If query write method is enabled(default), then all audit records will be queued in SGA and later will be flushed to disk, which improves performance.
- One policy can contain multiple actions/privilege/role audit option and which can enable 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 enabled 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 existing audit set up 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
SELECT value FROM v$option WHERE parameter = ‘Unified Auditing’;
VALUE
—————–
FALSE
FALSE – > MIXED AUDTING
TRUE –> PURE AUDITING:
How to change from MIXED to PURE auditing:
SELECT value FROM v$option WHERE parameter = ‘Unified Auditing’;
VALUE
—————–
FALSE
sqlplus / as sysdba <
NOTE – FOR RAC, linking need to be done all the nodes
WHAT IS UNIFIED AUDIT POLICY AND HOW IT WORKS:
A unified audit policy is a group of audit options with different conditions. It is 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
SQL> select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES;
POLICY_NAME
——————————————————————————-
ORA_CIS_RECOMMENDATIONS
ORA_RAS_POLICY_MGMT
ORA_RAS_SESSION_MGMT
ORA_LOGON_FAILURES
ORA_SECURECONFIG
ORA_DATABASE_PARAMETER
ORA_ACCOUNT_MGMT
But not all are enabled. Query AUDIT_UNIFIED_ENABLED_POLICIES to find, which policies are enabled.
Query to find which policies are enabled
POLICY_NAME
—————————————–
ORA_LOGON_FAILURES
ORA_SECURECONFIG
SQL> select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME=’ORA_SECURECONFIG’;
LOGMINING
TRANSLATE ANY SQL
EXEMPT REDACTION POLICY
PURGE DBA_RECYCLEBIN
ADMINISTER KEY MANAGEMENT
DROP ANY SQL TRANSLATION PROFILE
ALTER ANY SQL TRANSLATION PROFILE
CREATE ANY SQL TRANSLATION PROFILE
CREATE SQL TRANSLATION PROFILE
CREATE EXTERNAL JOB
CREATE ANY JOB
GRANT ANY OBJECT PRIVILEGE
EXEMPT ACCESS POLICY
CREATE ANY LIBRARY
GRANT ANY PRIVILEGE
DROP ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
ALTER DATABASE
GRANT ANY ROLE
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
DROP ANY TABLE
ALTER ANY TABLE
CREATE ANY TABLE
DROP USER
CREATE USER
AUDIT SYSTEM
ALTER SYSTEM
CREATE DATABASE LINK
DROP DATABASE LINK
ALTER USER
CREATE ROLE
DROP ROLE
SET ROLE
CREATE PROFILE
DROP PROFILE
ALTER PROFILE
ALTER ROLE
CREATE DIRECTORY
DROP DIRECTORY
ALTER DATABASE LINK
CREATE PLUGGABLE DATABASE
ALTER PLUGGABLE DATABASE
DROP PLUGGABLE DATABASE
EXECUTE
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 of 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
SQL> show user
USER is “BSSTDBA”
SQL> drop directory TEST;
Directory dropped.
Check the audit report
set lines 299
col SQL_TEXT for a23
col action_name for a18
col UNIFIED_AUDIT_POLICIES for a23
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME=’BSSTDBA’ and EVENT_TIMESTAMP > sysdate -1/24;
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– ——————————
LOGON ORA_LOGON_FAILURES 16-FEB-17 11.29.03.981436 PM
DROP DIRECTORY drop directory TEST2 ORA_SECURECONFIG 16-FEB-17 11.29.59.924533 PM
TEST CASE 2 : CREATE AUDIT POLICY WITH MULTIPLE AUDIT OPTIONS:
create audit policy test_case2
ACTIONS CREATE TABLE,
INSERT ON bsstdba.EMP_TAB,
TRUNCATE TABLE,
select on bsstdba.PROD_TAB;
set lines 299
col POLICY_NAME for a23
col AUDIT_OPTION for a12
col AUDIT_CONDITION for a12
col OBJECT_SCHEMA for a23
col OBJECT_NAME for a14
select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME=’TEST_CASE2′;
POLICY_NAME AUDIT_OPTION AUDIT_CONDITION OBJECT_SCHEMA OBJECT_NAME
———— ——————— —————— ————— ——————
TEST_CASE2 CREATE TABLE NONE NONE NONE
TEST_CASE2 TRUNCATE TABLE NONE NONE NONE
TEST_CASE2 INSERT NONE BSSTDBA EMP_TAB
TEST_CASE2 SELECT NONE BSSTDBA PROD_TAB
SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name=’TEST_CASE2′;
no rows selected
Unless we enable the policy, auditing conditions wont be evaluated
SQL> audit policy TEST_CASE2;
Audit succeeded.
SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name=’TEST_CASE2′;
POLICY_NAME
————
TEST_CASE2
Do some changes and generate audit report:
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME=’STCDBA’ and EVENT_TIMESTAMP > sysdate -1/24;
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– ——————————–
CREATE TABLE create table EMP_NUM as TEST_CASE2 17-FEB-17 09.19.16.054209 AM
select * from bsstdba.
emp_tab
EXCLUDE ONE USER FROM THE POLICY:
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME=’TEST_CASE2′;
USER_NAME POLICY_NAME ENABLED_OPT
————- ———— ———————–
ALL USERS TEST_CASE2 BY
SQL> audit policy TEST_CASE2 except stcdba;
audit policy TEST_CASE2 except stcdba
*
ERROR at line 1:
ORA-46350: Audit policy TEST_CASE2 already applied with the BY clause.
SQL> noaudit policy TEST_CASE2;
Noaudit succeeded.
SQL> audit policy TEST_CASE2 except stcdba;
Audit succeeded.
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME=’TEST_CASE2′;
USER_NAME POLICY_NAME ENABLED_OPT
————- ———— ———————–
STCDBA TEST_CASE2 EXCEPT
Now create a table from stcdba.
SQL> SQL> connect stcdba
Enter password:
Connected.
SQL> create table TEST4 ( empnum number);
Table created.
SQL> conn / as sysdba
Connected.
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME=’STCDBA’ and EVENT_TIMESTAMP > sysdate -1/24;
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
CREATE TABLE create table EMP_NUM as TEST_CASE2 17-FEB-17 09.19.16.054209 AM
select * from bsstdba.
emp_tab
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.
SQL> create AUDIT POLICY test_case3
ACTIONS DELETE ON bsstdba.EMP_TAB,
INSERT ON bsstdba.PROD_TAB,
UPDATE ON bsstdba.SAL_TAB
WHEN ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) = ”STCDBA”’
EVALUATE PER SESSION; 2 3 4 5 6
Audit policy created.
SQL> audit policy TEST_CASE3;
Audit succeeded.
SQL> select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME,CONDITION_EVAL_OPT FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME=’TEST_CASE3′;
POLICY_NAME AUDIT_OPTION AUDIT_CONDITION OBJECT_SCHEMA OBJECT_NAME CONDITION
———— ——————— —————— ————— —————— ———
TEST_CASE3 UPDATE SYS_CONTEXT(‘USERE BSSTDBA SAL_TAB SESSION
NV’, ‘SESSION_USER
‘) = ‘STCDBA’
TEST_CASE3 DELETE SYS_CONTEXT(‘USERE BSSTDBA EMP_TAB SESSION
NV’, ‘SESSION_USER
‘) = ‘STCDBA’
TEST_CASE3 INSERT SYS_CONTEXT(‘USERE BSSTDBA PROD_TAB SESSION
NV’, ‘SESSION_USER
‘) = ‘STCDBA’
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME=’TEST_CASE3′;
USER_NAME POLICY_NAME ENABLED_OPT
————- ———— ———————–
ALL USERS TEST_CASE3 BY
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’)
SQL>
CREATE AUDIT POLICY test_case4
ACTIONS insert on bsstdba.PROD_TAB,
update on bsstdba.SAL_TAB
WHEN ‘sys_context(”userenv”,”SESSION_USER”) not in ( ”STCDBA”,”TCSDBA”)’
EVALUATE PER STATEMENT;SQL> 2 3 4 5
Audit policy created.
SQL> AUDIT POLICY test_case4;
Audit succeeded.
SQL> SQL> select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME,CONDITION_EVAL_OPT FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME=’TEST_CASE4′;
POLICY_NAME AUDIT_OPTION AUDIT_CONDITION OBJECT_SCHEMA OBJECT_NAME CONDITION
———— ——————— —————— ————— —————— ———
TEST_CASE4 UPDATE sys_context(‘usere BSSTDBA SAL_TAB STATEMENT
nv’,’SESSION_USER’
) not in ( ‘STCDBA
‘,’TCSDBA’)
TEST_CASE4 INSERT sys_context(‘usere BSSTDBA PROD_TAB STATEMENT
nv’,’SESSION_USER’
) not in ( ‘STCDBA
‘,’TCSDBA’)
SQL> connect stcdba
Enter password:
Connected.
SQL> insert into BSSTDBA.PROD_TAB select * from BSSTDBA.PROD_TAB;
4 rows created.
SQL> SQL> commit;
Commit complete.
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where UNIFIED_AUDIT_POLICIES=’TEST_CASE4′;
no rows selected
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
CREATE USER TCSDBA IDENTIFIED BY TCSDBA;
GRANT DBA TO TCSDBA;
Enable audit for the role DBA
CREATE AUDIT POLICY ROLE_AUDIT roles dba;
audit policy ROLE_AUDIT;
Enable audit for the role DBA
CREATE AUDIT POLICY ROLE_AUDIT roles dba;
audit policy ROLE_AUDIT;
Do any dba activity and check report
USER is “TCSDBA”
SQL>
SQL>
SQL> truncate table bsstdba.emp_tab’;SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME=’TCSDBA’ and action_name like ‘TRUNCATE%’;ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————————- ———————– ——————————–
TRUNCATE TABLE truncate table bsstdba.emp_tab ORA_SECURECONFIG, ROLE_ 17-FEB-17 01.31.58.862039 PM
AUDIT
TEST CASE 6 ( FILTER BY HOSTNAME)
Now we can define to exclude auditing for few hosts
CREATE AUDIT POLICY test_case6
ACTIONS UPDATE ON OE.ORDERS, DELETE ON SALES.ORDERS
WHEN ‘SYS_CONTEXT (”USERENV”, ”HOST”) NOT IN
(”sec59-7”,”sec58-6”)’
EVALUATE PER STATEMENT;
DATAPUMP AUDITING
SQL> create audit policy expdp_aduit actions component=datapump export;
Audit policy created.
SQL> audit policy expdp_aduit;
Audit succeeded.
SQL> set lines 299
col POLICY_NAME for a23
col AUDIT_OPTION for a12
col AUDIT_CONDITION for a12
col OBJECT_SCHEMA for a23
col OBJECT_NAME for a14
SQL> select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME=’EXPDP_ADUIT’;
POLICY_NAME AUDIT_OPTION AUDIT_CONDIT OBJECT_SCHEMA OBJECT_NAME
———————– ———— ———— ———————– ————–
EXPDP_ADUIT EXPORT NONE NONE NONE
expdp dumpfile=test.dmp logfile=test.log directory=T tables=catalog.TRANSACTIONS
Export: Release 12.1.0.2.0 – Production on Fri Feb 17 11:38:41 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TABLE_01″: /******** AS SYSDBA dumpfile=test.dmp logfile=test.log directory=T tables=catalog.TRANSACTIONS
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported “CATALOG”.”TRANSACTIONS” 63.94 KB 689 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/export/home/oracle/test.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Fri Feb 17 11:39:19 2017 elapsed 0 00:00:34
SQL> select DBUSERNAME,DP_TEXT_PARAMETERS1 from UNIFIED_AUDIT_TRAIL where DP_TEXT_PARAMETERS1 is not null;
DBUSERNAME DP_TEXT_PARAMETERS1
—————————— ——————————————————————————
SYS MASTER TABLE: “SYS”.”SYS_EXPORT_TABLE_01″ , JOB_TYPE: EXPORT, METADATA_JOB_MO
DE: TABLE_EXPORT, JOB VERSION: 12.1.0.2.0, ACCESS METHOD: AUTOMATIC, DATA OPTI
ONS: 0, DUMPER DIRECTORY: NULL REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITI
ON OPTIONS: NONE
TEST_CASE7 ( AUDITING FOR SYSTEM PRIVILEGES):
We can enable auditing for system privileges as below.
create audit policy aud_syspriv_pol
PRIVILEGES alter any table;
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
<span style=”color: #000000;”>set lines 299</span>
col SQL_TEXT for a23
col action_name for a18
col UNIFIED_AUDIT_POLICIES for a23
select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME=’SYS’ and EVENT_TIMESTAMP > sysdate -1/24;
DROPPING AUDIT POLICY:
SQL> DROP AUDIT POLICY TEST_CASE5;
DROP AUDIT POLICY TEST_CASE5
*
ERROR at line 1:
ORA-46361: Audit policy cannot be dropped as it is currently enabled.
We can’t drop a policy when it is enabled.
SQL> NOAUDIT POLICY TEST_CASE5;
Noaudit succeeded.
SQL> DROP AUDIT POLICY TEST_CASE5;
Audit Policy dropped.
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
exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( –
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –
LAST_ARCHIVE_TIME => sysdate-30)
exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( –
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –
USE_LAST_ARCH_TIMESTAMP => TRUE)
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)
set lines 2999
col PARAMETER_NAME for a31
col PARAMETER_VALUE for a34
select * from dba_audit_mgmt_config_params;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
——————————- ———————————- ————————
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL
Role segregation:
AUDYS is the schema, which keeps audit trail info. No user can connect to this user including SYS
SQL> show user
USER is “SYS”
SQL> connect AUDSYS/AUDSYS
ERROR:
ORA-46370: cannot connect as AUDSYS user
Warning: You are no longer connected to ORACLE.
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)
