hp gen10 oracle12c,Oracle 12c new feature: Unified Audit

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