Oracle 12c Unified Auditing – Part 1

With Oracle 12c it is all getting better! And that includes the new auditing possibilities as well, which can be grouped as “Unified Auditing”. First of all unified auditing basically does not mean anything more but the previous functions, standard auditing and fine-grained auditing, being combined. This means that we are dealing with three (!) different methods now, because the “former” ones still exist, even if they are disabled by default – despite what is said in several documents. I can also just partly agree on the claim, that the same commands as in Oracle 11g are being recorded. Only a failed logon is being monitored for example, but not a successful logon – read more lately.

There are several significant improvements compared to the previous auditing functions. These include among others audit records not being written in tables right away, but first existing in the SGA only. Those who find this too insecure can, of course, overwrite this procedure so the audit data is written in the table like in every regular table right away. And this is the second change already: in Version 12c a partitioned table in the AUDSYS scheme is being used for the audit data. The table, with the cryptic name “CLI_SWP…”, is located in the SYSAUX by default and should better be relocated to a separate table space e.g. AUDITTS.

Activating Unified Auditing

IIn a few blogs and also in the Oracle documentation it is said that you can switch the database to Unified Auditing i.e. to go without the “former” functions. Therefore the Oracle kernel has to be relinked. I would postpone this, as the Unified Auditing does not yet master the full extent of the previous fine-grained auditing (see Oracle 12c Database Security Guide: “If you want to audit specific columns or use event handlers, use fine-grained auditing”).

The following Query could give the impression that unified auditing is not active; this impression is wrong. The “FALSE” just means that unified auditing is not being used exclusively but the “former” auditing can be used additionally.

SELECT parameter, value
  FROM v$option
 WHERE parameter = 'Unified Auditing';

PARAMETER            VALUE
-------------------- --------------------
Unified Auditing     FALSE

The next improvement affects the administration of unified auditing in general. Until now it was necessary to do a initialization, if you for example wanted to relocate the audit table or wanted to delete data sets by “PURGE” (see also the blog:  Manage Audit Data). With unified auditing this is not necessary or possible anymore. You can just ignore the following command and error message.

BEGIN
   dbms_audit_mgmt.init_cleanup(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      default_cleanup_interval => 24);
END;
/ 
BEGIN
   ERROR at line 1:
   ORA-46250: Invalid value for argument 'AUDIT_TRAIL_TYPE'
   ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 177
   ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 605
   ORA-06512: at line 2

Note:

There are a few documents, which say that the parameter AUDIT_TRAIL has got no notability in unified auditing. That is wrong! If the Parameter is not set to “DB”, no unified auditing data is being written and some commands in this context do not work:

execute DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
   ERROR at line 1:
   ORA-46276: DBMS_AUDIT_MGMT operation on unified audit trail failed
   ORA-55906: Secure file log [id: 0 name: ORA$AUDIT_NEXTGEN_LOG] does not exist
   ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1746
   ORA-06512: at line 1

Therefore you should definitely set the parameter to “DB” to use unified auditing.

Relocating the Table

The relocation of the tables is quite simple:

CREATE TABLESPACE auditts
   DATAFILE SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;
   
BEGIN
   dbms_audit_mgmt.set_audit_trail_location(
   audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
   audit_trail_location_value => 'AUDITTS');
END;
/

Deleting Audit Data

The procedure for deleting audit data is the same as the one already known from former versions (see blog: Manage Audit Data). That means the audit records, which can be deleted, are marked first (SET_LAST_ARCHIVE_TIMESTAMP) and either a purge job is performed afterwards or the data sets are deleted directly by a clean procedure.

Unfortunately there is a messy bug, but a patch is already existing for it: Patch 18743542; 12C UNIFIED AUDIT TRAIL, CANNOT DELETE LAST_ARCHIVE_TIME..

Here again how to delete audit records (the initialization is inapplicable, as I already mentioned before):

BEGIN
   dbms_audit_mgmt.set_last_archive_timestamp(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
	  last_archive_time => to_date('13.01.2015 13:22:00','DD.MM.YYYY HH24:MI:SS'));
END;
/

SELECT last_archive_ts 
  FROM dba_audit_mgmt_last_arch_ts;

LAST_ARCHIVE_TS                     
-----------------------------------
13-JAN-15 01.22.00.000000 PM +00:00

BEGIN
   dbms_audit_mgmt.clean_audit_trail(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
	  use_last_arch_timestamp => TRUE);
END;

In this example the purge job is not called up, but the deletion performed directly. But it seems as if nothing happens to start with… Wait for it!

In the second part we are working on the actual auditing.