I must thank my fellow DBA Franky Weber Faust for his publication in his blog.
Introduction: – In this article, we are going to know about some other new features of Oracle 12c. Unified Auditing is a new feature in Oracle 12c. In Oracle 12c, a new database auditing Foundation has been introduced. Oracle Unified Auditing changes the fundamental auditing functionality of the database. In previous releases of Oracle, there were separate audit trails for each individual component. Unified Auditing consolidates all auditing into a single repository and view. This provides a two-fold simplification: audit data can now be found in a single location, and all audit data is in a single format.
In version, 12c came on the scene a new architecture for auditing the Oracle database, Unified Auditing. Until then we had 3 audit methods in the database: Fine Grained Auditing (FGA), Value Based Auditing (Audit with triggers) and Standard Database Auditing (AUDIT command). Auditing in Oracle has always been related to a large loss of performance due to recursive operations. With Unified Auditing there is a memory area where information is written first, it basically has the same concept as the redo log buffer, it first writes to memory (unified_audit_sga_queue_size) and then to disk in the tables of the new AUDSYS schema. The remaining audit methods can still be used and remain stored in the SYS schema in the AUD $ and FGA_LOG $ tables. Two new roles separate auditing roles: AUDIT_ADMIN for configuration and administration and AUDIT_VIEWER for viewing and analyzing audited data.
Most DBAs hate auditing and the reasons are reasonable: it’s annoying to set up, it impacts the performance of the environment, it’s not as secure until 12c because the audit data stays in the SYS schema and we have no other choice. Unified Auditing is actually safer because a new schema called AUDSYS stores auditing data is more performative because of the memory area allocated for this purpose and replaces almost all existing audit methods. That alone is a good reason to upgrade to 12c, but that’s not the focus of this article.
Reasons to use auditing in Oracle are mention below:-
- Enable accountability for actions. These include actions taken in a particular schema, table, or row, or affecting specific content.
- Deter users (or others, such as intruders) from inappropriate actions based on their accountability.
- Investigate suspicious activity. For example, if a user is deleting data from tables, then a security administrator can audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
- Notify an auditor of the actions of an unauthorized user. For example, an unauthorized user could be changing or deleting data, or the user has more privileges than expected, which can lead to reassessing user authorizations.
- Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
- Detect problems with an authorization or access control implementation. For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies generate audit records, then you will know the other security controls are not properly implemented.
- Address auditing requirements for compliance. Regulations such as the following have common auditing-related requirements:
- Sarbanes-Oxley Act
- Health Insurance Portability and Accountability Act (HIPAA)
- International Convergence of Capital Measurement and Capital Standards: a Revised Framework (Basel II)
- Japan Privacy Law
- European Union Directive on Privacy and Electronic Communications
Reasons to use Unified Auditing are mention below:-
- 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
With Unified Auditing, as the name says, Unified Audit, audit logs are queried in one place, in a single view called UNIFIED_AUDIT_TRAIL.
Unified Auditing supports the following auditing models:
- Standard database auditing;
- SYS operations auditing;
- Fine Grained Auditing (FGA);
- Data Pump;
- RMAN;
- Label Security (OLS);
- Database Vault (DV);
- Real Application Security (RAS);
- SQL * Loader Direct Load.
By default, Unified Auditing comes with Enterprise Edition, with no additional licensing required. There is no need to install it, since it is already built into the Database itself, but it is not fully enabled. This way you can work with two modes:
- Mixed Mode: option enabled by default. All audit features work as before version 12c, but new features are also available. Audit data can be queried either in views such as DBA_AUDIT_TRAIL or DBA_FGA_AUDIT_TRAIL, already known up to this version and in the new UNIFIED_AUDIT_TRAIL. When SYS auditing is enabled, the logs are still written to the directory configured through the AUDIT_FILE_DEST parameter.
- Pure Mode: must be enabled by doing a relink of the kernel and for that the database and other components must be stopped. Once Pure is enabled, or Full Mode is also called, all audit configurations prior to version 12c are ignored and the audited data is stored using Oracle SecureFiles. All audit data is found by referring to the UNIFIED_AUDIT_TRAIL view.
An example of new architecture presented at https://blogs.oracle.com/imc/entry/oracle_database_12c_new_unified:
To find out if Unified Auditing is enabled, run the following query:
SQL> col parameter for a30 SQL> col value for a10 SQL> select parameter, value from v $ option where parameter = 'Unified Auditing'; PARAMETER VALUE ------------------------------ ---------- Unified Auditing FALSE
We found that it is not enabled because the value returned was FALSE. This means that we are using Mixed Mode, so now let’s enable it to use Pure Mode. As previously mentioned we have to recompile the Oracle Kernel and for this, we have to stop all Oracle components. In this example, the database is on a Standalone Server with Grid Infrastructure 12c.
[Oracle @ loredata-srv ~] $ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-MAR-2016 22:44:34 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date MAR 10, 2016 22:17:22 Uptime 0 days 0 hr. 27 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0.2/grid/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/loredata-srv/listener/alert/log.xml Listening Endpoints Summary ... (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = loredata-srv.localdomain) (PORT = 1521)) (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC1521))) Services Summary ... Service "+ ASM" has 1 instance (s). Instance "+ ASM", status READY, has 1 handler (s) for this service ... Service "loredb.localdomain" has 1 instance (s). Instance "loredb", status READY, has 1 handler (s) for this service ... Service "mypdb.localdomain" has 1 instance (s). Instance "loredb", status READY, has 1 handler (s) for this service ... The command completed successfully
[Oracle @ loredata-srv ~] $ srvctl stop database -db loredb [Oracle @ loredata-srv ~] $ srvctl status database -db loredb The database is not running.
[Oracle @ loredata-srv ~] $ srvctl stop listener -listener listener [Oracle @ loredata-srv ~] $ srvctl status listener Listener is enabled Listener LISTENER is not running
There is no need to stop the ASM instance, as it is another binary, another ORACLE_HOME.
Now we just need to compile the kernel. First, make sure your environment variables:
[Oracle @ loredata-srv ~] $. Oraenv ORACLE_SID = [loredb]? Loredb The Oracle base remains unchanged with value / u01 / app / oracle [Oracle @ loredata-srv ~] $ cd $ ORACLE_HOME / rdbms / lib / [Oracle @ loredata-srv lib] $ pwd /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/
Then do the relink with the uniaud_on option (the return will be pretty messy as shown below):
[Oracle @ loredata-srv lib] $ make -f ins_rdbms.mk uniaud_on ioracle
/ Usr / bin / ar d /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a kzanang.o
/ Usr / bin / ar cr /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/libknlopt.a
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/kzaiang.o
Chmod 755 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin
– Linking Oracle
Rm -f /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/orald -o
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl, – disable-new-dtags -L
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ -L
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/ -L
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/stubs/ -Wl, -E
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/opimai.o
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ssoraed.o
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/ttcsoi.o
-Wl, – whole-archive -lperfsrv12 -Wl, – no-whole-archive /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/nautab.o
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naeet.o
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naect.o
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/naedhs.o /u01/app/oracle/product/12.1.0.2/dbhome_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.2/dbhome_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.2/dbhome_1/lib/libavserver12.a];
Then echo “-lavserver12”; Else echo “-lavstub12”; Fi “ if [-f
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libavclient12.a];
Then echo “-lavclient12”; Fi` -lknlopt -lslax12 -lpls12 -lrt
-lplp12 -ljavavm12 -lserver12 -lwwg `cat / u01 / app / oracle / product / 12.1.0.2 / dbhome_1 / lib / ldflags` -lncrypt12 -lnsgr12
-lnzjs12 -ln12 -lnl12 -lnro12 `cat / u01 / app / oracle / product / 12.1.0.2 / dbhome_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.2 / dbhome_1 / lib / ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat
/ U01 / app / oracle / product / 12.1.0.2 / dbhome_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.2/dbhome_1/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 / dbhome_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.2 / dbhome_1 / lib / sysliblist` -Wl, -rpath,
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib -lm `cat / u01 / app / oracle / product / 12.1.0.2 / dbhome_1 / lib / sysliblist`
-ldl -lm -L / u01 / app / oracle / product / 12.1.0.2 / dbhome_1 / lib
Test -f /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle || \
Mv -f /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracleO
Mv /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle
Chmod 6751 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle
Once compiled, just start the components again:
[Oracle @ loredata-srv ~] $ srvctl start listener -listener listener
[Oracle @ loredata-srv ~] $ srvctl status listener -listener listener
Listener LISTENER is enabled
The Listener LISTENER is running on the node (s): loredata-srv
[Oracle @ loredata-srv ~] $ srvctl start database -db loredb
[Oracle @ loredata-srv ~] $ srvctl status database -db loredb
The database is running.
Let’s see if the change really worked and enabled Unified Auditing:
SQL> col parameter for a30
SQL> col value for a10
SQL> select parameter, value from v $ option where parameter = ‘Unified Auditing’;
PARAMETER VALUE
—————————— ———-
Unified Auditing TRUE
Yeah, it worked. We’re definitely using Pure Mode. Let’s now check some existing policies:
SQL> col policy_name for a50
SQL> select distinct policy_name from sys.audit_unified_policies order by 1;
POLICY_NAME
————————————————–
ORA_ACCOUNT_MGMT
ORA_CIS_RECOMMENDATIONS
ORA_DATABASE_PARAMETER
ORA_DV_AUDPOL
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_RAS_SESSION_MGMT
ORA_SECURECONFIG
8 rows selected.
The following audit options are enforced through the ORA_SECURECONFIG policy:
SQL> col POLICY_NAME format A20 SQL> col POLICY_NAME format A20 SQL> col AUDIT_OPTION format A40 SQL> set PAGES 100 SQL> select POLICY_NAME, AUDIT_OPTION From AUDIT_UNIFIED_POLICIES Where policy_name = ‘ORA_SECURECONFIG’ order by 2;
POLICY_NAME AUDIT_OPTION ————————————————– —– ORA_SECURECONFIG ADMINISTER KEY MANAGEMENT ORA_SECURECONFIG ALTER ANY PROCEDURE ORA_SECURECONFIG ALTER ANY SQL TRANSLATION PROFILE ORA_SECURECONFIG ALTER ANY TABLE ORA_SECURECONFIG ALTER DATABASE ORA_SECURECONFIG ALTER DATABASE LINK ORA_SECURECONFIG ALTER PLUGGABLE DATABASE ORA_SECURECONFIG ALTER PROFILE ORA_SECURECONFIG ALTER ROLE ORA_SECURECONFIG ALTER SYSTEM ORA_SECURECONFIG ALTER USER ORA_SECURECONFIG AUDIT SYSTEM ORA_SECURECONFIG CREATE ANY JOB ORA_SECURECONFIG CREATE ANY LIBRARY ORA_SECURECONFIG CREATE ANY PROCEDURE ORA_SECURECONFIG CREATE ANY SQL TRANSLATION PROFILE ORA_SECURECONFIG CREATE ANY TABLE ORA_SECURECONFIG CREATE DATABASE LINK ORA_SECURECONFIG CREATE DIRECTORY ORA_SECURECONFIG CREATE EXTERNAL JOB ORA_SECURECONFIG CREATE PLUGGABLE DATABASE ORA_SECURECONFIG CREATE PROFILE ORA_SECURECONFIG CREATE PUBLIC SYNONYM ORA_SECURECONFIG CREATE ROLE ORA_SECURECONFIG CREATE SQL TRANSLATION PROFILE ORA_SECURECONFIG CREATE USER ORA_SECURECONFIG DROP ANY PROCEDURE ORA_SECURECONFIG DROP ANY SQL TRANSLATION PROFILE ORA_SECURECONFIG DROP ANY TABLE ORA_SECURECONFIG DROP DATABASE LINK ORA_SECURECONFIG DROP DIRECTORY ORA_SECURECONFIG DROP PLUGGABLE DATABASE ORA_SECURECONFIG DROP PROFILE ORA_SECURECONFIG DROP PUBLIC SYNONYM ORA_SECURECONFIG DROP ROLE ORA_SECURECONFIG DROP USER ORA_SECURECONFIG EXECUTE ORA_SECURECONFIG EXEMPT ACCESS POLICY ORA_SECURECONFIG EXEMPT REDACTION POLICY ORA_SECURECONFIG GRANT ANY OBJECT PRIVILEGE ORA_SECURECONFIG GRANT ANY PRIVILEGE ORA_SECURECONFIG GRANT ANY ROLE ORA_SECURECONFIG LOGMINING ORA_SECURECONFIG PURGE DBA_RECYCLEBIN ORA_SECURECONFIG SET ROLE ORA_SECURECONFIG TRANSLATE ANY SQL
46 rows selected.
To check the policies already enabled we will execute the following query:
SQL> col user_name for a20
SQL> SELECT * FROM SYS.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
2 rows selected.
By default auditing records are stored in the AUDSYS schema that has its segments in the SYSAUX tablespace:
SQL> select distinct owner, tablespace_name from dba_segments where owner = ‘AUDSYS’
OWNER TABLESPACE_NAME
—— ——————————
AUDSYS SYSAUX
1 row selected.
Let’s consult what we already have information so far consulting only the first 5 lines:
SQL> set pages 200 lin 200
SQL> col object_name for a30
SQL> col action_name for a20
SQL> col object_schema for a10
SQL> col sql_text for a70
SQL> col EVENT_TIMESTAMP for a30
SQL> select OS_USERNAME, TERMINAL, DBUSERNAME, ACTION_NAME, OBJECT_SCHEMA,
OBJECT_NAME, EVENT_TIMESTAMP, AUDIT_OPTION
From unified_audit_trail
Order by EVENT_TIMESTAMP
Fetch first 5 rows only;
OS_USERNAME TERMINAL DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME EVENT_TIMESTAMP AUDIT_OPTION
Eur-lex.europa.eu eur-lex.europa.eu ——— ———– —————————— ———-
Oracle SYS ALTER USER SYSTEM 16-JAN-16 05.06.51.700546 PM
Oracle SYS CREATE PLUGGABLE DATA PDB $ SEED 16-JAN-16 05.08.12.825891 PM ABASE
Oracle SYS ALTER PLUGGABLE DATA PDB $ SEED 16-JAN-16 05.08.24.888508 PM BASE
Oracle SYS ALTER PLUGGABLE DATA PDB $ SEED 16-JAN-16 05.10.42.029258 PM BASE
Oracle SYS ALTER PLUGGABLE DATA PDB $ SEED 16-JAN-16 05.11.42.806513 PM BASE
5 rows selected.
Let’s see below how to create these policies, but first I’d like to leave a tip …
Many policies can be created in the database, but it is best to limit the number of policies enabled.
The syntax used to create the Unified Auditing policies allows us to create a policy that covers all the audit settings we need for our database. You can create a few large policies rather than many small ones, this facilitates management and has the following advantages:
• Reduces the overhead associated with user login because each enabled policy needs to be loaded into the session’s UGA. The fewer policies enabled the less time to load information from them;
• Consequently, it also reduces the consumption of the UGA memory area per session;
• Few policies make it easy for Oracle’s internal check to know where the audit trail will be written.
The clause for creating the policies is as follows
CREATE AUDIT POLICY policy_name
{{Privilege_audit_clause [action_audit_clause] [role_audit_clause]}
| {Action_audit_clause [role_audit_clause]}
| {Role_audit_clause}
}
[WHEN audit_condition EVALUATE PER {STATEMENT | SESSION | INSTANCE}]
[CONTAINER = {CURRENT | ALL}];
Let’s see an example …
If you are not already familiar with Pluggable Databases, you can already practice this example.
First I’ll set my current session for MYPDB:
SQL> alter session set container = MYPDB;
Session altered.
If your PDB is not already open run the following command:
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
Then create the user and grant him privileges:
SQL> create user franky identified by oracle container = CURRENT;
User created.
SQL> grant create session, resource to franky;
Grant succeeded.
SQL> change user franky quota unlimited on USERS;
User altered.
Let’s connect with our new user and create a table:
[Oracle @ loredata-srv ~] $ sqlplus franky / oracle@loredata-srv.localdomain: 1521 / mypdb.localdomain
SQL * Plus: Release 12.1.0.2.0 Production on Fri Mar 11 23:09:02 2016
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, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options
SQL> create table test as select * from all_tables;
Table created.
In another session with the SYS user, create and enable the following policy for all users:
SQL> CREATE AUDIT POLICY MYPDB_USERS_POL ACTIONS SELECT, INSERT, UPDATE, DELETE ON FRANKY.TEST;
Audit policy created.
SQL> AUDIT POLICY MYPDB_USERS_POL;
Audit succeeded.
Let’s now create another user to select the data from the TEST table.
Still, in the SYS session create this new user:
SQL> create user JOHN identified by oracle container = current;
User created.
SQL> grant create session to john;
Grant succeeded.
SQL> grant all on franky.teste to john;
Grant succeeded.
In the other session, connect to the newly created JOHN user and see the data in the TEST table.
[Oracle @ loredata-srv ~] $ sqlplus john/oracle@loredata-srv.localdomain: 1521 / mypdb.localdomain
SQL * Plus: Release 12.1.0.2.0 Production on Sat Mar 12 02:05:00 2016
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, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options
SQL> select count (*) from franky.teste;
COUNT (*)
———-
101
Return to the SYS session and consult UNIFIED_AUDIT_TRAIL to see if the audit took effect.
SQL> select UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME,SYSTEM_PRIVILEGE_USED,SYSTEM_PRIVILEGE_USED, To_char (EVENT_TIMESTAMP, ‘DD-MON-YY HH: MI’) “DATE” From unified_audit_trail Where DBUSERNAME in (‘JOHN’) Order by 5;
UNIFIED_AUDIT_POLICIES DBUSERNA ACTION_N SYSTEM_PRIVILEGE_U DATE ———————- ——– ——– ———— —— ————— JOHN LOGON 12-MAR-16 01:49 JOHN LOGON 12-MAR-16 01:49 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:50 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:50 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:50 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:50 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:50 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:50 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:51 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:53 JOHN AUDIT 12-MAR-16 01:55 JOHN AUDIT 12-MAR-16 01:55 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:56 MYPDB_USERS_POL JOHN SELECT 12-MAR-16 01:56 MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 12, 2:00 p.m. MYPDB_USERS_POL JOHN SELECT MAR 13, 10:04 AM MYPDB_USERS_POL JOHN SELECT MAR 13, 10:04 AM MYPDB_USERS_POL JOHN SELECT MAR 13, 10:04 AM MYPDB_USERS_POL JOHN SELECT MAR 13, 10:04 AM MYPDB_USERS_POL JOHN SELECT MAR 13, 10:04 AM MYPDB_USERS_POL JOHN SELECT MAR 13, 10:04 AM MYPDB_USERS_POL JOHN SELECT MAR 13, 10:04 AM MYPDB_USERS_POL JOHN SELECT 13-MAR-16 10:06 MYPDB_USERS_POL JOHN SELECT MAR 13, 10:07 AM
43 rows selected.
If the data is still in memory you must execute the following package to force the data to be downloaded to the tables:
SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL
PL / SQL procedure successfully completed.
To delete the policy that we just use first we have to disable it and only then we can remove it, let’s see an example:
SQL> drop audit policy MYPDB_USERS_POL;
Drop audit policy MYPDB_USERS_POL
*
ERROR at line 1:
ORA-46361: Audit policy can not be dropped as it is currently enabled.
SQL> noaudit policy MYPDB_USERS_POL;
Noaudit succeeded.
SQL> drop audit policy MYPDB_USERS_POL;
Audit Policy dropped.
To check if the policy still exists we can refer back to the view:
SQL> col policy_name for a30
SQL> select distinct policy_name from sys.audit_unified_policies order by 1;
POLICY_NAME
——————————
ORA_ACCOUNT_MGMT
ORA_CIS_RECOMMENDATIONS
ORA_DATABASE_PARAMETER
ORA_DV_AUDPOL
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_RAS_SESSION_MGMT
ORA_SECURECONFIG
8 rows selected.
It’s time we cleaned the audit trail. For this we will see how many audited records we already have:
SQL> select count (*) from unified_audit_trail;
COUNT (*)
———–
519916
To perform the cleaning first let’s archive the last records and then purge:
SQL> exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (-
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –
LAST_ARCHIVE_TIME => sysdate)
PL / SQL procedure successfully completed.
SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (-
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –
USE_LAST_ARCH_TIMESTAMP => TRUE)
PL / SQL procedure successfully completed.
Let’s check if the records were actually removed:
SQL> select count (*) from unified_audit_trail;
COUNT (*)
———–
519473
We can see that few records have been removed, this is due to the use of the USE_LAST_ARCH_TIMESTAMP option as TRUE, which guarantees the records saved after the last filing.
The documentation explains:
USE_LAST_ARCH_TIMESTAMP accepts either of the following settings:
• TRUE deletes audit records created before the last archive timestamp. To check the last recorded timestamp, query the LAST_ARCHIVE_TS column of the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view for read-write databases and the
DBMS_AUDIT_MGMT.GET_LAST_ARCHIVE_TIMESTAMP function for read-only databases. The default value is TRUE. Oracle recommends that you set USE_LAST_ARCH_TIMESTAMP to TRUE.
• FALSE deletes all audit records without considering last archive timestamp. Be careful about using this setting, in case you inadvertently delete audit records that should have been deleted.
If we do with this option as FALSE we will actually see a removal of the lines:
SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (-
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –
USE_LAST_ARCH_TIMESTAMP => FALSE)
PL / SQL procedure successfully completed.
SQL> select count (*) from unified_audit_trail;
COUNT (*)
———-
20
When we use Standard Auditing, audit records are written directly to the AUD $ and FGA_LOG $ tables. For this reason, an audit has always been related to performance loss in Oracle, but in version, 12c Oracle Database uses an area of SGA memory where records are temporarily stored in a sort of queue and when this area is full the flush occurs, For the tables through the GEN0 process. It is an architecture similar to the log buffer. In this new architecture exists the possibility of loss of registers in case of the fall of the instance, because there may be records in memory that were not downloaded to the tables. If auditing is more important than performance then we can use Unified Auditing with immediate recording and this way we will not lose.
Let’s see how to enable immediate recording, see the procedure AUDIT_TRAIL_IMMEDIATE_WRITE:
SQL> EXEC 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);
PL / SQL procedure successfully completed.
To re-enable the use of SGA for queuing, observe the procedure AUDIT_TRAIL_QUEUED_WRITE:
SQL> EXEC 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);
PL / SQL procedure successfully completed.
PERFORMANCE TESTS
I used Swingbench to demonstrate the performance of audit methods.
Without any type of audit (in mixed mode):
Figure 1 – 10 users, average 7153 TPM, average response time 11ms.
Standard Auditing – Enabled audit for all user DML operations SOE:
SQL> alter session set container = mypdb;
Session altered.
SQL> AUDIT ALL STATEMENTS BY soe BY ACCESS;
Audit succeeded.
Figure 2 – 10 users, average 7028 TPM, average response time 13ms.
Unified Auditing – Policy enabled for all DML operations of user SOE and Queued Write:
SQL> CREATE AUDIT POLICY SOE_POL
ACTIONS DELETE, INSERT, UPDATE, SELECT
WHEN ‘SYS_CONTEXT (‘ ‘USERENV’ ‘,’ ‘SESSION_USER’ ‘) =’ ‘SOE’ ”
EVALUATE PER STATEMENT
CONTAINER = ALL;
Audit policy created.
SQL> AUDIT POLICY SOE_POL;
Audit succeeded.
Figure 3 – 10 users, average of 7499 TPM, average response time 7ms.
Unified Auditing – Policy enabled for all DML operations of the SOE user in Immediate Write:
SQL> EXEC 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);
PL / SQL procedure successfully completed.
Figure 4 – 10 users, average of 7326 TMP, average response time 7ms.
Swingbench was used with all standard parameters and 10 users logged into the same database and under the same conditions for about 10 minutes where I took the average transaction for the period. After each run I logged out and logged in again through the tool.
Summary:
| Type of audit | Response time (ms) | Average transactions per minute (10 users) | Duration of test |
| No audit (mixed mode bank) | 11 | 7153 | 10 minutes |
| Standard Auditing | 13 | 7028 | 10 minutes |
| Unified Auditing with queued write | 7 | 7499 | 10 minutes |
| Unified Auditing with immediate write | 7 | 7326 | 10 minutes |
Oracle Database 12c has several new features, we have seen one of them being Unified Auditing. We learned that we can create auditing policies to audit what we think necessary without harming the performance of the database and also look at that data in a view that centralizes everything. We can still audit the same operations as in versions prior to 12c, but now with more benefits. During the tests it was not possible to accurately verify the performance gain, since the environment was not overloaded and the competition of users was low, but nevertheless it was possible to identify a good gain compared to the architecture before version 12c.
Infrastructure used in the tests:
Host
Processor: Intel® Core ™ i7 3610QM
Memory RAM: Kingston HyperX Impact Black 16GB 1600MHz DDR3L
SSD: Kingston V300 240GB
OS: Oracle Enterprise Linux 6.6 x86_64
Guest
Hypervisor: Oracle VM VirtualBox 5.0.16
Processors: 4 CPUs
Memory RAM: 4GB
OS: Oracle Enterprise Linux 6.6 x86_64
Oracle Software: Oracle Grid Infrastructure 12.1.0.2, Oracle Database 12.1.0.2 Enterprise Edition and Oracle ASMLib 2.0.4
