Wednesday, February 26, 2014

12c New Feature: Unified Auditing

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.
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.
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. Don`t forget that it seems that this only works on a Oracle Enterprise Editon.
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

No comments:

Post a Comment