12c New Feature: Unified Auditing


SONY DSC

Oracle 12c Database provides many new security features. One of them is Unified Auditing. It replaces the Old Audit Views and Database Parameters with a new refreshing central implementation.

I have checked the license requirement but did not find any useable information. It seems that it will not mentioned in the Oracle Pricelist or the Oracle license Guide, but however i have checked against a Oracle Standard Edition and i was not able to activated.

Thanks to JMK who have highlighted that patch 17466854 is needed for Oracle Standard Edition to get Unified Audit to work.

So why to use Unified Auditing?

  • Consolidate all audit information into a single audit trail table
  • Improve audit performance
  • Simple configuration
  • Secure audit data for all RDBMS Options and other components like RMAN and Data Pump

Consolidate all audit information into a single audit trail table

Pre 12c you have many Objects where Audit information will be stored:

  • SYS.AUD$ / DBA_AUDIT_TRAIL
  • SYS.FGA_LOG$/DBA_FGA_AUDIT_TRAIL
  • DBA_COMMON_AUDIT_TRAIL
  • V$XML_AUDIT_TRAIL
  • DVSYS.AUDIT_TRAIL$
  • OS audit files

In Oracle 12c all Information will be stored in a single View named SYS.UNIFIED_AUDIT_TRAIL .Since different RDBMS components have different audit action  this View is also Extensible. For example Real Application Security, OLS or a normal RMAN Operation the View Add or Remove columns.

Improve audit performance

Oracle 12c stores all audit information SGA memory queues. When the queue will be Full then it will be flushed by a new Background Process GEN0. Because the storing auf Audit Information is not a synchronous process it will be less overhead to your sessions. OK i know what you want to say. Audit Records in the SGA and what will happen when the Instanz crash? This is correct, in that case you can loss some Audit Records. But i believe many customers can tolerate some loss of data in case of a instanz crash. You can configure two  queuing modes which controls performance versus Data Loss.

In the Immediate-Write mode the Audit Information is immediately written which cost more performance.

EXECUTE DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(-
 DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
 DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, -
 DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);

In the Queued-Write mode the Audit information is not written immediately to disk. This is the default mode.

EXECUTE DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(-
 DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
 DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, -
 DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);

Simple configuration

You can now create a audit policy. Audit policy is a group of named audit action. The fact that you can now group Audit operations make several configurations easier.

Sample:

SQL> CREATE AUDIT POLICY dppol1 ACTIONS COMPONENT=datapump export;
SQL> AUDIT POLICY dppol1;

Check you policy

SQL> col user_name format A15
SQL> col policy_name format A15
SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES
  2  where POLICY_NAME like '%DP%';

USER_NAME    POLICY_NAME    ENABLED_ SUC FAI
--------------- --------------- -------- --- ---
ALL USERS    DPPOL1        BY     YES YES

Secure audit data for all RDBMS Options and other components like RMAN and Data Pump

So let us export a Table let us check what we get.

[oracle@server1 uniaud]$ expdp system/welcome1 dumpfile=employees.dmp tables=HR.EMPLOYEES

Export: Release 12.1.0.1.0 - Production on Sat Jul 20 20:12:17 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=employees.dmp tables=HR.EMPLOYEES 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
(...)

After the export we check the Unfied Audit what we get.

select DBUSERNAME, DP_TEXT_PARAMETERS1,
 DP_BOOLEAN_PARAMETERS1
 from UNIFIED_AUDIT_TRAIL
 where DP_TEXT_PARAMETERS1 is not null;

DBUSERNAME
------------------------------
DP_TEXT_PARAMETERS1
--------------------------------------------------------------------------------
DP_BOOLEAN_PARAMETERS1
--------------------------------------------------------------------------------
SYSTEM
MASTER TABLE:  "SYSTEM"."SYS_EXPORT_TABLE_01" , JOB_TYPE: EXPORT, METADATA_JOB_M
ODE: TABLE_EXPORT, JOB VERSION: 12.1.0.0.0, ACCESS METHOD: AUTOMATIC, DATA OPTIO
NS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITION
OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TR
UE, JOB_RESTARTED: FALSE

Also don`t forget if you did not see your records you may need to flush them to disk. For this case use this statement.

SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL

PL/SQL procedure successfully completed.

So how to configure Unified Auditing. This is done on OS Level be relinking the Oracle executeable. My first thought was that this can be done bei the chopt command. But no. You can read MOS Note 1567006.1 How To Enable The New Unified Auditing In 12c for more Information.

Unlike other Oracle Database components, you do not create a unified audit policy for Oracle RMAN events. The UNIFIED_AUDIT_TRAIL view automatically captures commonly audited Oracle RMAN events.

Here is a short example of turning on Unified Auditing.

First we check the current status of this option.

select con_id,parameter,value from v$option where parameter = 'Unified Auditing'

    CON_ID PARAMETER            VALUE
---------- -------------------- --------
         0 Unified Auditing     FALSE

Next shutdown all running processes of your Oracle Home (Database and Listener). Next relink your oracle executeable. The directory here is $ORACLE_HOME/rdbms/lib

[oracle@server1 lib]$ make -f ins_rdbms.mk uniaud_on ioracle
/usr/bin/ar d /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/kzaiang.o 
chmod 755 /u01/app/oracle/product/12.1.0/db_ee_1/bin

 - Linking Oracle 
rm -f /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/oracle
/u01/app/oracle/product/12.1.0/db_ee_1/bin/orald  -o /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/ -L/u01/app/oracle/product/12.1.0/db_ee_1/lib/ -L/u01/app/oracle/product/12.1.0/db_ee_1/lib/stubs/   -Wl,-E /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/opimai.o /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.1.0/db_ee_1/lib/nautab.o /u01/app/oracle/product/12.1.0/db_ee_1/lib/naeet.o /u01/app/oracle/product/12.1.0/db_ee_1/lib/naect.o /u01/app/oracle/product/12.1.0/db_ee_1/lib/naedhs.o /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/config.o  -lserver12 -lodm12 -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12  -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12  -lrt -lplp12 -lserver12 -lclient12  -lvsn12 -lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12.1.0/db_ee_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /u01/app/oracle/product/12.1.0/db_ee_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12  -lrt -lplp12 -ljavavm12 -lserver12  -lwwg  `cat /u01/app/oracle/product/12.1.0/db_ee_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0/db_ee_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /u01/app/oracle/product/12.1.0/db_ee_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0/db_ee_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12   -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12"; fi` -L/u01/app/oracle/product/12.1.0/db_ee_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12  -lgeneric12 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12  -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12  -laio -lons    `cat /u01/app/oracle/product/12.1.0/db_ee_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.1.0/db_ee_1/lib -lm    `cat /u01/app/oracle/product/12.1.0/db_ee_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.1.0/db_ee_1/lib
test ! -f /u01/app/oracle/product/12.1.0/db_ee_1/bin/oracle ||\
           mv -f /u01/app/oracle/product/12.1.0/db_ee_1/bin/oracle /u01/app/oracle/product/12.1.0/db_ee_1/bin/oracleO
mv /u01/app/oracle/product/12.1.0/db_ee_1/rdbms/lib/oracle /u01/app/oracle/product/12.1.0/db_ee_1/bin/oracle
chmod 6751 /u01/app/oracle/product/12.1.0/db_ee_1/bin/oracle

After you start the database Unified Audit Option should be set to true.

select con_id,parameter,value from v$option where parameter = 'Unified Auditing'

    CON_ID PARAMETER            VALUE
---------- -------------------- --------
         0 Unified Auditing     TRUE

Also as i said before. The Audit Information is read only and is placed under the AUDSYS User. This Account is something special because you cannot login into it.

SQL> alter user AUDSYS identified by welcome1 account unlock;

User altered.

SQL> conn audsys/welcome1
ERROR:
ORA-46370: cannot connect as AUDSYS user

Warning: You are no longer connected to ORACLE.

Check the Table which is read only

SQL> select table_name from dba_tables where owner = 'AUDSYS';

TABLE_NAME
--------------------------------------------------------------------------------
CLI_SWP$7cf2fd15$1$1

As always comments are welcome.

4 Gedanken zu “12c New Feature: Unified Auditing

  1. Hi,
    Why we need to turn on unified auditing? It seems to work (in your example, we see audit info from the export, before we relink with make -f ins_rdbms.mk uniaud_on ioracle ).
    So what happens exactly here, what do we gain when we relink ?

    • Hello,
      when you did not relink your binaries or copy a ddl on Windows Unified Auditing is configured in mixed mode. When a database is created, mixed auditing mode is used by default through the predefined enabled policy ORA_SECURECONFIG. But unified auditing mode is not yet
      enabled. After you have done all your audit configuration you can switch to the unified audit mode (with the relink command). After enabling
      unified audit mode, none of the old audit instance parameters (AUDIT_TRAIL,AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS,AUDIT_SYSLOG_LEVEL) are used. There is also no possibility to write audit records to OS or syslog.

      Hope this answer your question

  2. Just recently I opened a SR with Oracle as unified auditing should be supported with a standard edition.
    It’s bug 17466854, so the corresponding patch is available on MOS.

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s