Increased scrutiny of Organizations adherence to regulatory guidance like SOX, Hippa & PCI DSS has made audit of critical and privileged actions in the database more likely to be mandatory than optional. Therefore forcing database or software vendors to implement auditing with piecemeal approach. Oracle is no different with various ways to audit & various locations to store them in various formats. For example some of the auditing options available with Oracle 11g are mandatory auditing, standard database auditing , Data Vault auditing & FGA and so on ….
Staying complaint is expensive , time consuming and complex. Some of the complexities involved with auditing has been simplified with unified auditing available with Oracle12c. In this blog , we will discuss about Unified auditing in 4 part series. Here is the 1st part.
So what is unified auditing ?
Unified Auditing is selective and effective auditing of database actions to a single unified audit trail inside of the database using policies and conditions. Most of unified auditing features are implemented using DBMS_AUDIT_MGMT or Oracle Grid Control. DBMS_AUDIT_MGMT is not a new to some/most of us as it is available with Oracle11g. It is enhanced in Oracle12c to support unified auditing. Unified auditing supports auditing of following components.
- Real Application Security
- Database Vault
- Oracle Label Security
- Data Mining
- Data Pump
My next question why do we need a unified auditing? What is wrong with existing auditing? Why couldn’t we audit everything in the database and get done with it and make our auditors happy. What actually stopped us from auditing everything in the database? The main reasons were performance impact caused by auditing followed by security. Now we have the burden on us to prove that all our audit is safe, correct and not tampered with. And finally where do I store all the audit data ; Getting additional storage for database itself is cumbersome…. Now you need space to store all of audit data.
Let us look at how unified auditing measures itself against the 3 metrics: Performance, Security & Storage
As per Oracle, Unified auditing results in negligible overhead. Why ? Because it is implemented in SGA using named Queues. Dequeing is continuous with each audit client (Example: RMAN, Oracle Label Security) having two separate queues so that the client can continue to write to the second queue while the first queue is being dequeued to database table by background process GEN0. if needed audit Data can also be manually flushed to the database using procedure FLUSH_UNIFIED_AUDIT_TRAIL.
Example: EXECUTE DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
As usual we have the choice of synchronous and asynchronous dequeuing. In unified audit terms, they are called as Immediate write mode and queued write mode.
Audit records are immediately written to the trail and there is a performance overhead associated with this approach
Example: EXECUTE DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY (DBM_MS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_MODE);
The named queue in the SGA is dequeued periodically. In this mode , data could be lost after an instance crash and therefore its a trade-in for data loss for performance. How cruel …. never can have everything
Example: EXECUTE DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY (DBM_MS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_MODE);
Summary: Performance is better mainly because of queued-write mode architecture; Using immediate-write mode will cause performance impact but not as significant pre12c auditing.
Let us look at other metrics in Part-II of this series. I will post the link once I am ready.
Tagged: .FLUSH_UNIFIED_AUDIT_TRAIL, AUDIT_TRAIL_QUEUED_MODE, DBMS_AUDIT_MGMT, DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY