How to Enable Unified Auditing in Oracle 12c database

Unified Auditing:
Oracle 12c introduced the consolidated way of auditing Oracle database. It introduces the Simplicity with little or minimal overhead to database performance.

It comes with the following features.

• Simplicity
• Consolidation
• Security 
• It rely on read only audit trail table
• It audit all configuration related operations
• Seperation of duties 
• Performance
• Implemented using queue in Oracle SGA, leaving very overhead in database performance

Unified Auditing Architecture

1. User perform auditable action
2. Audit records in SGA based Queue in memory
3. either GEN0 process flush queue to disk on regular interval or you can perform manual flush on demand (EXECUTE SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL)
4. once data flushed to disk, its available via SYS.UNIFIED_AUDIT_TRAIL 

There two mode of setup Queued or

How to Enable :

You need outage to enable unified auditing as it will be done by relinking the Oracle Library.  Shutdown all oracle process before relinking.

oracle@dbserver01:~$. oraenv

ORACLE_SID = [CDB2] ? CDB2

The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbserver01:~$    

oracle@dbserver01:~$  lsnrctl stop 

oracle@dbserver01:~$  sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 22 06:15:47 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


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

SQL> shutdown immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.

oracle@dbserver01:~$ cd $ORACLE_HOME/rdbms/lib

oracle@dbserver01:/u01/app/oracle/product/12.1.0.2/rdbms/lib$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

/usr/bin/ar d /u01/app/oracle/product/12.1.0.2/rdbms/lib/libknlopt.a kzanang.o

/usr/bin/ar cr /u01/app/oracle/product/12.1.0.2/rdbms/lib/libknlopt.a /u01/app/oracle/product/12.1.0.2/rdbms/lib/kzaian

g.

o

ch

mod 755 /u01/app/oracle/product/12.1.0.2/bin

 - Linking Oracle

rm -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/oracle

/u01/app/oracle/product/12.1.0.2/bin/orald  -o /u01/app/oracle/product/12.1.0.2/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12.1.0.2/rdbms/lib/ -L/u01/app/oracle/product/12.1.0.2/lib/ -L/u01/app/oracle/product/12.1.0.2/lib/stubs/   -Wl,-E /u01/app/oracle/product/12.1.0.2/rdbms/lib/opimai.o /u01/app/oracle/product/12.1.0.2/rdbms/lib/ssoraed.o /u01/app/oracle/product/12.1.0.2/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.1.0.2/lib/nautab.o /u01/app/oracle/product/12.1.0.2/lib/naeet.o /u01/app/oracle/product/12.1.0.2/lib/naect.o /u01/app/oracle/product/12.1.0.2/lib/naedhs.o /u01/app/oracle/product/12.1.0.2/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.2/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.2/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /u01/app/oracle/product/12.1.0.2/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12  -lrt -lplp12 -ljavavm12 -lserver12  -lwwg  `cat /u01/app/oracle/product/12.1.0.2/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0.2/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.2/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0.2/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.2/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -L/u01/app/oracle/product/12.1.0.2/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.2/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.1.0.2/lib -lm    `cat /u01/app/oracle/product/12.1.0.2/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.1.0.2/lib

test ! -f /u01/app/oracle/product/12.1.0.2/bin/oracle ||\

           mv -f /u01/app/oracle/product/12.1.0.2/bin/oracle /u01/app/oracle/product/12.1.0.2/bin/oracleO

mv /u01/app/oracle/product/12.1.0.2/rdbms/lib/oracle /u01/app/oracle/product/12.1.0.2/bin/oracle

chmod 6751 /u01/app/oracle/product/12.1.0.2/bin/oracle

By Default oracle Defined Two policies (ORA_SECURECONFIG and ORA_LOGON_FAILURES)  get enabled. 

Check Current Enabled policy in database by default.

oracle@dbserver01:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 22 06:50:28 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> select * from audit_unified_enabled_policies;

USER_NAME                      POLICY_NAME                    ENABLED_ SUC FAI

------------------------------ ------------------------------ -------- --- ---

ALL USERS                      ORA_SECURECONFIG               BY       YES YES

ALL USERS                      ORA_LOGON_FAILURES             BY       NO  YES

Once unified auditing is enable all audit_xx parameters will be ignored and will have no impact.

Auditing can be enabled in two modes

• Queued Write mode (Default ) - In this mode you might loose some audit data in case of instance crash (data which was not flushed to disk at time of instance crash).
• Immediate Write mode - this will ensure no audit data is lost. The audit records are written immediately.

Unified auditing is enabled in Queued Write mode by default to ensure minimal performance overhead.

How to switch mode:

.• Immediate Write mode:

SQL> 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);


• Queued Write mode:

SQL> 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);

Oracle 11g or 11gR2 Tablespace Encryption

Oracle 11g (11.2.0.1) Configure Tablespace Encryption using Safenet Hardware Security Module (LUNA SA 4.4.0 HSM)

Note: Step 1 to 4 need repeating on all the nodes in the cluster

Step 5 and 6 don't need repeat for RAC

Example : DATABASE SERVER Hostname is dbserver01
: HSM Device name is hsm01


1. Install HSM Client software on database Server dbserver01:


2. Register Oracle Database server (client) with HSM.

login as root on dbserver01:

su - root
password: *******

[root@dbserver01 bin]# cd /usr/lunasa/bin

-- Fetch server parameter from hsm01 to database server.

[root@dbserver01 bin]# /ctp admin@hsm01.domain.local:server.pem .

--- Verify Cerfiticate
[root@dbserver01 bin]# openssl x509 -in server.pem -noout -text

--- Register Hsm01 as authorised HSM Server.

[root@dbserver01 bin]# ./vtl addServer -n hsm01 -c server.pem

New server hsm01 successfully added to server list.

-- Verify Server added

[root@dbserver01 bin]# ./vtl listServers
Server: hsm01


-- Create HSM Client certificate from the Database Server

[root@dbserver01 bin]# ./vtl createCert -n dbserver01
Private Key created and written to: /usr/lunasa/cert/client/dbserver01Key.pem
Certificate created and written to: /usr/lunasa/cert/client/dbserver01.pem


--- upload client certificate to HSM

[root@dbserver01 bin]# ./ctp /usr/lunasa/cert/client/dbserver01.pem admin@hsm01.domain.local:
admin@hsm01.domain.local's password:
dbserver01.pem 100% 1172 1.1KB/s 00:00


---- Login to HSM Device via SSH or Local console

ssh admin@hsm01.domain.local
password: ***********

----- Register dbserver01 to HSM as client

[hsm01] lunash:>client reg -c dbserver01 -h dbserver01

'client register' successful.

Command Result : 0 (Success)


--- Assing HSM partition to dbserver01(client)

[hsm01] lunash:>client a -p Oracle -c dbserver01

'client assignPartition' successful.

Command Result : 0 (Success)


3. Verify that Oracle Database server can talk to HSM:


[root@gridcluster03 bin]# ./vtl verify

The following Luna SA Slots/Partitions were found:

Slot Serial # Label
==== ======== =====
1 951962001 Oracle

---- You have Successfully Added Database server to HSM as client and Now Oracle Server is ready to use HSM as wallet to Store Tablespace and Column Master keys.



4. Configure Oracle to use HSM.

--- copy the HSM Libarary to Oracle required location:

[root@dbserver01 bin]# mkdir -p /opt/oracle/extapi/64/hsm/safenet/4.4.0/

[root@dbserver01 bin]#cp /usr/lunasa/lib/libshim.so /opt/oracle/extapi/64/hsm/safenet/4.4.0/libshim.so

[root@dbserver01 bin]#chown -R oracle:dba /opt/oracle


--- Login as oracle and update sqlnet.ora

[root@dbserver01 bin]# su - oracle

[oracle@dbserver01 ]$ vi $ORACLE_HOME/network/admin/sqlnet.ora


Add the following line
------------------------------
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM))


5. Enable Tablespace Encryption in database by createing the Master key in HSM wallet.


[oracle@dbserver01 ]$ sqlpus "/ as sysdba"


----- Create wallet in HSM Use this command once only to create the master key. Every time you bounce database you need to open the wallet.

alter system set encryption key identified by "hsmpartition_password";



---- Create tablespace:

CREATE TABLESPACE TDE_TEST01 DATAFILE '+DATA' SIZE 10m ENCRYPTION using 'AES256' DEFAULT STORAGE (ENCRYPT);




6. Every Time you open start database open the wallet by following command.

alter system set encryption wallet open identified by "hsmpartition_password";


Note: Step 1 to 4 need repeating on all the nodes in the cluster