The term Information Security is no longer a buzzword. It has taken center stage and is playing a crucial role in dictating the IT policies and also which systems an organization should and should not use. Oracle being the world leader in the database market is certainly aware of this and that’s why every new release of the database contains more and more security related features. Auditing is certainly a key component of database security and it not only enables you to prevent intruders from outside the organization but also keeps a check on the actual database or application users. In this article we will provide an overview of Oracle Unified Auditing – a new and improved way in 12c to use auditing.
What is Unified Auditing in Oracle Database 12c?
Auditing is not new in 12c. It has been there with database server for quite sometime now. But it was a pain for DBA to manage auditing and auditing related data. There were many types of auditing for example Standard auditing, Fine Grained Auditing, Privileged user access etc. Not only you were required to enable or disable all of them individually but also all of them had their own location and format to store audit records. Unified auditing is introduced to mitigate all of these problems and also make the whole audit process more effective.
Starting from 12c you can now use Unified auditing to keep track of all audit data in a single audit trail. The unified auditing trail can have auditing data from following audit sources.
• Audit records (including SYS audit records) from unified audit policies and AUDIT settings
• Fine-grained audit records from the DBMS_FGAPL/SQL package
• Oracle Database Real Application Security audit records
• Oracle Recovery Manager audit records
• Oracle Database Vault audit records
• Oracle Label Security audit records
• Oracle Data Mining records
• Oracle Data Pump
• Oracle SQL*Loader Direct Load
All auditing data is stored inside the SYSAUX tablespace and made available in the read only format via a view named UNIFIED_AUDIT_TRAIL Oracle recommends that you store the audit data in a separate tablespace especially created for auditing purposes. You can change the default location of auditing using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_ LOCATION procedure.
The audit records are recorded in audit trail if the database is open for read write. However if for some reason the database is in read only mode then all the audit records are stored in a new format at the operating system level. The location for audit records under such situations is $ORACLE_BASE/audit/$ORACLE_SID.
Auditing is controlled by defining the audit policies. You can combine the audit settings to form a complete audit policy or you can use the predefined policies supplied by Oracle itself. You can also go ahead and create fine grained auditing policies for individual objects and users.
Mixed Mode Auditing
Auditing is enabled by default in 12c, unlike the previous versions where you had to turn it on manually. The default auditing mode is the mixed mode auditing. Mixed mode auditing allows you to use the traditional auditing alongside with the new Unified auditing. This serves as a good mediator for an easy and hassle free switch to the preffered Unified auditing.
The previous parameters used for different types of auditing are only valid if you are using mixed mode auditing.
Auditing for CDB and PDB
Auditing policies can be applied at the CDB level or they can be applied to the individual PDB’s databases. The only point worth noting is that you cannot have fine grained auditing policies for the root database. Fine grained policies are only applicable to the individual PDB’s.
Benefits of Unified Auditing
Below are the key benefits attached to Unified auditing.
• Once enabled, Unified auditing frees you from setting the different initialization parameters. Its is always on.
• As there is only one audit trail, managing the auditing data is whole a lot easier.
• The actual audit process actually gets a lot simpler. Now you have only one audit trail and only one view to query for all types of audit records. Whether it is the record for an insert statement or some action from the SYS user, all are in one location making the audit process simpler. The AUDIT_TYPE column shows the type of the audit record.
• The writing of the audit records to disk was also caused a problem for database performance as well. But Unified auditing works in a Queued Write mode which means that all the records are initially recorded in the SGA instead of the immediate write to disk. Although you can change this behavior but the former one is recommended and has huge performance gains.
• The new auditing policies has a lot of flexibility. It allow you to fine grain an audit policy and also to point out exclusions and exceptions from that particular policy.
Audit Roles
There are two new audit roles in 12c.
• AUDIT_ADMIN can create the new audit policies, able to define fine grained audit policies, view and manage all the audit data and perform other administrative actions related to auditing. The role should be granted to trusted persons only.
• AUDIT_VIEWER role is suited for external auditors. The role is only allowed to view audit data.
In the previous versions of the database it was possible for every user to enable/disable auditing on his own objects. This is not possible now.