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.

13 thoughts on “Oracle 12c Unified Auditing – Part 1”

  1. 21:31:12 MyLaptop:SYS@xxxx> BEGIN
    2 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    4 LAST_ARCHIVE_TIME => ’01-AUG-2016 12:30:00.00′);
    5 END;
    /

    PL/SQL procedure successfully completed.

    21:33:35 MyLaptop:SYS@xxxx> select count(*) from unified_audit_trail;

    COUNT(*)
    ———-
    144119

    21:39:33 MyLaptop:SYS@xxxx> exec DBMS_AUDIT_MGMT.clean_audit_trail(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED);

    PL/SQL procedure successfully completed.

    21:43:18 MyLaptop:SYS@xxxx> select count(*) from unified_audit_trail;

    COUNT(*)
    ———-
    144122

    So, what am I missing? I know 99% of the audit records are from months ago.

  2. Hi,
    As mentioned in the blog that if audit_trail is not set to DB no unified auditing data is being written and some commands in this context do not work and example given as ….
    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

    But in my 12c database with version 12.1.0.2 the audit_trail value is NONE and I tried executing ..
    execute DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

    SQL> select value from v$parameter where name = ‘audit_trail’;

    VALUE
    ——————————————————————————–
    NONE

    SQL> execute DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

    PL/SQL procedure successfully completed.

    Thanks,
    Sovan

  3. Have you tried on oracle 12c?:
    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;
    /

    In 12cR2 (not sure about 12cR1), it moves some of the information to AUDITTS; however, some of the LOBs and PARTITIONS remain as SYSAUX when you look at the details.

  4. Johannes Ahrends

    There are some changes in 12.2 because of the inability to query from unified auditing if the amount of data grows. I haven’t tested it yet but will comment here as soon as I’m done with it.

  5. Hi Shannon, do you have any idea to relocate these LOBs and PARTITIONS from SYSAUX to AUDITTS? It is interesting that RMAN DUPLICATE will return ERRORS when it try to recover the database. Opened Oracle SR, it seemed they have no clue what’s going on. thanks

  6. Johannes Ahrends

    There is no other way then to use dbms_audit_mgmt. But that will move the LOBs and Partitions as well

  7. How easy (or hard) is it for a DBA to tamper with the audit logs if Unified Auditing in pure mode is being used?

  8. The work of dynamic data masking is to protect personally identifiable data. Dynamic data masking does not require any additional server resources.

  9. We see a lot of information of audit setup and purge but never about archiving old audit data for later processing. Is there a best practice to save the audit records before purging them out of the unified audit records?

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top