Oracle Audit Part 4 – Unified Auditing Problem

NOTE: Unified Audit is fixed from release 12.2. If you are not auditing, or still using traditional auditing, you should look to migrate to Unified Audit.

Part 4 in a loose series of blog posts about auditing; the new Oracle 12 feature. Unified auditing.

The intention behind Unified Auditing, as the name suggests, is to pull together all of the audit records from the disparate Oracle auditing locations into a single unified location. It captures standard audit information (including sys records normally written to the O/S),  FGA audit, record from Database Vault, Label Security, rman, etc.

Sounds great, and it is. 12.1 has a significant problem, but it seems a lot better in 12.2 (where I have not yet had time to spot any problems 🙂 )

The first thing that you need to know is that it is on by default.
It is gathering audit information in your 12C database right now, possibly duplicating any traditional auditing you may have switched-on yourself. By default the database is in “Mixed mode”, meaning you can use the old style of audit and unified auditing too. You need to re-link the oracle binary with the database down to enable exclusively-Unified auditing

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

(on windows, you need to move the correct DLL into place.)

Have a quick check of the oracle executable to see if it there. If the DB is up against an ORACLE_HOME, you can see if you are in exclusive mode using:

21:41:24 SYS @ UTF8 > SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

if the DB is not up (e.g. a new home you are just building) you can locate an enable string....

[ the following is not guaranteed to work ] 
grep "sopts_IsUnifiedAuditOn" $ORACLE_HOME/bin/oracle; echo $?
Binary file /u01/app/oracle/product/19/db_1/bin/oracle matches
0
(you will get return code 1 if it's not there, and so in mixed-mode)

So what is enabled by default?

1  select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME            ENABLED ENABLED_OPTION  ENTITY_NAME     ENTITY_ SUC FAI
--------------- ---------------------- ------- --------------- --------------- ------- --- ---
ALL USERS       ORA_SECURECONFIG       BY      BY USER         ALL USERS       USER    YES YES
ALL USERS       ORA_LOGON_FAILURES     BY      BY USER         ALL USERS       USER    NO  YES

What does that mean?

  1  select POLICY_NAME,AUDIT_OPTION,AUDIT_OPTION_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE
  2    from audit_unified_policies
  3   where policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
  4*  order by 1,2,3,4,5;

POLICY_NAME          AUDIT_OPTION                             AUDIT_OPTION_TYPE  OBJECT_SCHEMA             OBJECT_NAME               OBJECT_TYPE
-------------------- ---------------------------------------- ------------------ ------------------------- ------------------------- --------------------
ORA_LOGON_FAILURES   LOGON                                    STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY PROCEDURE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE        SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY TABLE                          SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE LINK                      STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PROFILE                            STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ROLE                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER USER                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     AUDIT SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     BECOME USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY JOB                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY LIBRARY                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY PROCEDURE                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY TABLE                         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DATABASE LINK                     STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DIRECTORY                         STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE EXTERNAL JOB                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PROFILE                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM                    SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ROLE                              STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY PROCEDURE                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY TABLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DATABASE LINK                       STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DIRECTORY                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE                  STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PROFILE                             STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ROLE                                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP USER                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      REMOTE_SCHEDULER_AGENT    ADD_AGENT_CERTIFICATE     PROCEDURE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      SYS                       DBMS_RLS                  PACKAGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXEMPT REDACTION POLICY                  SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE               SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY ROLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     LOGMINING                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     SET ROLE                                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     TRANSLATE ANY SQL                        SYSTEM PRIVILEGE   NONE                      NONE                      NONE

49 rows selected.

So that’s quite a lot of auditing on by default.

If you want to disable unified auditing policies, you need to use:

noaudit policy 
e.g. noaudit policy ora_secureconfig

So what’s the problem with Unified Auditing in 12.1?

Performance. It’s dreadful, and simply unusable.  Hwo about this simple select asking how much audit in the last 30 minutes from the UNIFIED_AUDIT_TRAIL view?

select count(*) from unified_audit_trail where EVENT_TIMESTAMP > systimestamp - interval '30' minutes;

It effectively blocked itself in a RAC environment, with one parallel query slave blocked by another – really not sure what was going on there but it never completed (well, I killed it after half a day).

I have seen a similar query in a non-RAC environment (or against table v$unified_audit_trail – which you really shouldn’t use as it can give an incomplete picture) run for well over an hour with a remarkably small data set. Not useful if you are trying to scrape content into an external monitor regularly…

MOS Article 2212196.1 explains how to resolve this in 12.1, by transferring all of the data to a table.

In 12.2, this is resolved by all data being stored in a table call AUDSYS.AUD$UNIFIED.
The table uses interval-based partitioning and is partitioned with a monthly interval:

...
PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH)
 (PARTITION "AUD_UNIFIED_P0"  VALUES LESS THAN (TIMESTAMP' 2014-07-01 00:00:00')
...

Because it is a table, we can index it.It works like a table. It’s quick.

In my next audit article, I’ll look to explain how unified auditing works in a bit more depth, now that the performance no longer renders it unusable to queries.