Oracle 12c new feature: Unified Audit

articles: 

Unified Audit is a major architectural change: fast, easy, and impossible for the DBA to bypass. On upgrade to Oracle 12c, you really should enable it. The earlier method that we all use is pretty awful.

Consider the standard audit that we all know. In particular, consider how bad the implementation really is. First, performance. Awful. If you configure audit for an action (UPDATE on a table, for example) when a session does an UPDATE on that table, the session has to write the audit record. In effect, this is an autonomous transaction: the session has to stop what it is doing, write a row to SYS.AUD$, generating redo and undo as it does this, and COMMIT. Then it can return to the work it was meant to be doing. That's a pretty bad hit on the performance of the statement. Second, think about how secure the audit trail really is. Anything written to the SYS.AUD$ table can changed by the DBA. That isn't very secure, is it? Sure, you can audit to the OS instead. And then the SysAdmin can remove it, which isn't much better. Furthermore, reading those OS audit records is an awful job. They are slow to write, too.
Unified audit solves both these problems. First, performance. It is astronomically fast. Why? because the session doesn't write the audit record to the table. All it does is put a message on a buffered queue. The performance hit of writing the record to the table and generating the undo and redo is taken by a background process, GEN0, which creates the audit record asynchronously with respect to the calling action. Removing the writing of audit from the calling session solves the performance problem. Second, the audit table really is impossible to hack. Not even SYS can bypass the controls.

Here's how to do it:

1. Relink the Oracle executable.
On Windows, copy in the appropriate DLL:

cd %ORACLE_HOME%\bin
copy orauniaud12.dll.dbl orauniaud12.dll
and restart the Windows service for all instances.

On Unix, relink:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
and restart your Oracle instances.

2. Configure Unified Audit policies
You need a role to do this, AUDIT_ADMIN. Just a simple example: the equivalent of AUDIT UPDATE ON SCOTT.EMP and AUDIT CREATE ANY TRIGGER by users SYS and SYSTEM is,

orclz>
orclz> create audit policy mypol1
  2  privileges create any trigger
  3  actions update on scott.emp;

Audit policy created.

orclz>
orclz> audit policy mypol1 by sys,system;

Audit succeeded.

orclz>

3. Query the audit trail
The audit trail is exposed through the view UNIFIED_AUDIT_TRAIL. You need a role to see this, AUDIT_VIEWER. Then:
orclz>
orclz> select dbusername,event_timestamp,sql_text from unified_audit_trail
  2  where unified_audit_policies='MYPOL1';

DBUSERNAME EVENT_TIMESTAMP    SQL_TEXT
---------- ------------------ ---------------------------------------------------
SYSTEM     10-MAY-14 11.58.45 update scott.emp set sal=1000 where ename='KING'
SYS        10-MAY-14 12.01.06 create trigger scott.trig after update on scott.emp

                              begin
                              null;
                              end;


orclz>

4.What about security?
The audit table is in a new Oracle maintained schema, and not even SYS can tamper with it:
orclz>
orclz> conn / as sysdba
Connected.
orclz> select table_name from dba_tables where owner='AUDSYS';

TABLE_NAME
--------------------
CLI_SWP$67b5bb1a$1$1

orclz> delete from audsys."CLI_SWP$67b5bb1a$1$1";
delete from audsys."CLI_SWP$67b5bb1a$1$1"
                   *
ERROR at line 1:
ORA-55941: DML and DDL operations are not allowed on table "AUDSYS"."CLI_SWP$67b5bb1a$1$1"


orclz> drop user audsys cascade;
drop user audsys cascade
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped


orclz>

The only way to trim the audit trail is with the DBMS_AUDIT_MGMT package, access to which can be limited with the usual discretionary access control. And, of course, any operation against the audit trail is itself audited.

All together, Unified Audit is an important new feature, and a good motivator for the 12c upgrade.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Comments

I came across an odd bit of behaviour when doing some testing today. I had enabled Unified Audit, with the audit trail in SYSAUX. Then I took SYSAUX off line (never mind why) and everything was fine until I did a shutdown. From then, I could not open the database. These errors:

ORA-02002: error while writing to audit trail
ORA-55907: Tablespace SYSAUX does not exist for secure file log
Clearly, the writing of necessary audit records failed so the open could not complete. Looking up ora-55907 in MOS produces exactly zero hits (perhaps there would be something if one logged on with an internal account?) Eventually I got passed this by using STARTUP UPGRADE which succeeded. Then I made SYSAUX read write, and everything is fine.

Interesting. Well, interesting if you are as dweebie a person as me.

Oracle's audit documentation recommend using another tablespace for audit. I wonder why it's not done out of the box.
create a tablespace AUDIT

alter user audsys default tablespace audit;

Then configure unified audit trail.

John,

I noticed your statement "The performance hit of writing the record to the table and generating the undo and redo is taken by a background process, GEN0, which creates the audit record asynchronously with respect to the calling action."

I am curious why you believe that it is GEN0 that does the writing. Is this your interpretation? Did you do some testing? Or is it documented some where?

Regards
Tim

Thank you for making me look this up, Tim. I don't remember the source I used when researching this. I cannot now find a definitive reference for my assertion that GEN0 is the relevant process. The closest I have is Oracle University course material, which is not necessarily correct. There are various third party references such as this blog https://petesdbablog.wordpress.com/2013/07/20/12c-new-feature-unified-auditing but again, there is no reason to assume that these are authoritative. They may well be relying on the same Oracle Uni source. If you or anyone else can determine with certainty the responsible process, I would be interested to know. I shall not edit the article yet.