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.
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 eventsRMAN eventsSql loaderTRADITIONAL VS UNIFIED
TRADITIONAL AUDITINGUNIFIED_AUDITINGDepends on db init parameter like audit_trail,audit_sys_logIndependent of db parameter,bydefault enabledwrites audit records to different trails depending upon audit typeall audit trails are writeen to single trailsys records are written to os .aud filessys records are written to unified_audit_trailauditing not possible for rman/expdp/sqllderauditing can be enabled for db compoenents like rman/datapump/sqlldreach 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 performanceauditing 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
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:(relink library)
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-----------------
FALSE
sqlplus / as sysdba
shutdown immediate;
exit
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk unaiaud_on ioracle
sqlplus / as sysdba
startup
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-----------------
TRUE
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
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
SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES;
POLICY_NAME
-----------------------------------------
ORA_LOGON_FAILURES
ORA_SECURECONFIG
Query to check the audit options included in a policy:
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 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
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 classdba.EMP_TAB,
TRUNCATE TABLE,
select on classdba.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 classdba.
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 proddba;
audit policy TEST_CASE2 except proddba
*
ERROR at line 1:
ORA-46350: Audit policy TEST_CASE2 already applied with the BY clause.
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.
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
------------- ------------ -----------------------
PRODDBA 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 classdba.
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 classdba.EMP_TAB,
INSERT ON classdba.PROD_TAB,
UPDATE ON classdba.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.
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
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 CLASSDBA SAL_TAB SESSION
NV', 'SESSION_USER
') = 'PRODDBA'
TEST_CASE3 DELETE SYS_CONTEXT('USERE CLASSDBA EMP_TAB SESSION
NV', 'SESSION_USER
') = 'PRODDBA'
TEST_CASE3 INSERT SYS_CONTEXT('USERE CLASSDBA PROD_TAB SESSION
NV', 'SESSION_USER
') = 'PRODDBA'
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 classdba.PROD_TAB,
update on classdba.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 ( 'PRODDBA
','DEVDBA')
TEST_CASE4 INSERT sys_context('usere BSSTDBA PROD_TAB STATEMENT
nv','SESSION_USER'
) not in ( 'PRODDBA
','DEVDBA')
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;
Do any dba activity and check report
SQL> show user
USER is "DEVDBA"
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='DEVDBA' 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
(''sedbhost1'',''sedbhost2'')'
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" , JO