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 events
  • RMAN events
  • Sql loader
  • TRADITIONAL 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


    Related Topics

    how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c
    orapwd tool for password file in oracle
    Lock account automatically with INACTIVE_ACCOUNT_TIME
    PDB Lockdown Profiles in Oracle 12.2
    Unified audit trail in Oracle 12c
    how to send mail using utl_mail in oracle 11g
    Purge AUD$ table using DBMS_AUDIT_MGMT
    Open wallet automatically after starting the database
    How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
    ENABLE_DDL_LOGGING in oracle 12c

    You May Also Like

    Tablespace monitoring shell script
    Add database as target oracle 12c cloud control
    Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
    Apply database proactive bundle patch in RAC using manual process
    Change dbsnmp password for target db in oem 12c
    How to Downgrade oracle 19C Grid to 12C GRID
    how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c
    how to send mail using utl_mail in oracle 11g
    Get sid from ospid
    Shell script to report failed login attempt in oracle

    From This Website

    Steps for upgrading weblogic 12.1.2 to 12.1.3
    Shell script to delete old archives using RMAN
    Script to get cpu usage and wait event information in oracle database
    Apply patch on oracle 12.2 database ( Release update)
    Unified audit trail in Oracle 12c
    COMPRESSION in datapump oracle
    ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
    How to find execution history of an sql_id
    How to generate ADDM report
    ORA-32773: operation not supported for smallfile tablespace