12c Unified Auditing and AUDIT_TRAIL=DB in mixed mode

By Franck Pachot

.
Oracle enables some auditing by default, and if you don’t do anything, the tables where it is stored will grow in SYSAUX. Don’t wait to get an alert when it is too late. Everything that fills something automatically must be managed to archive or purge automatically. If not, one day you will have a problem.

Imagine that you have 5 features doing something similar but in a different way because they were implemented one at a time. You want to stop this and have only 1 unified feature. That’s great. But you are also required to maintain compatibility with previous version, which means that you actually implemented a 5+1=6th feature 🙁

Unified Auditing

This exactly what happens with Unified Auditing. Because of this compatibility requirement, it is declined in two modes:

• The ‘mixed mode’ that keeps all compatibility as the 5+1 case in my example
• The ‘pure mode’ that do not take care of the past and is actually the one that unifies all. The real ‘Unified’ one.

You are in ‘mixed mode’ by default and you see it as if there is nothing new enabled:

SQL> select parameter,value from v$option where parameter='Unified Auditing';
 
PARAMETER         VALUE
---------         -----
Unified Auditing  FALSE

But there may be something enabled if the old auditing is enabled, because it is actually a mixed mode.

AUDIT_TRAIL=DB

Let me explain. I use the old auditing:

SQL> show parameter audit
NAME                         TYPE    VALUE
---------------------------- ------- --------------------------------
audit_trail                  string  DB

This means that I have the default audits (such as logon, logoff, ALTER/CREATE/DROP/GRANT ANY, and so on.
In addition to that, I enabled the audit of create table:

SQL> audit create table;
Audit succeeded.

I do some of these stuff and I can see info in the old audit trail:

SQL> select action_name,sql_text from dba_audit_trail;
 
ACTION_NAME   SQL_TEXT
-----------   --------
CREATE TABLE
LOGON
SELECT
LOGON
LOGOFF

If you are in that case, you probably manage this trail. Our recommandation is either to disable audit, or to manage it.

But once upgraded to 12c, did you think about managing the new unified audit trail?

SQL> select audit_type,unified_audit_policies,action_name,return_code,count(*) from unified_audit_trail group by audit_type,unified_audit_policies,action_name,return_code order by 1,2,3;
---- ------ ------------------------------------------------------------------ ---- ------------------- ----- -- --------------------------------------------------------- ----- -- ------
AUDIT_TYPE  UNIFIED_AUDIT_POLICIES  ACTION_NAME  RETURN_CODE  COUNT(*)
Standard    ORA_LOGON_FAILURES      LOGON        0            2
Standard    ORA_LOGON_FAILURES      LOGON        1017         1
Standard    ORA_SECURECONFIG        CREATE ROLE  0            1
Standard    ORA_SECURECONFIG        DROP ROLE    0            1
Standard                            EXECUTE      0            1

Even with Unified Auditing set to off, some operations are audited when AUDIT_TRAIL=DB. If you don’t want them you have to disable them:

noaudit policy ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

As you see, in mixed mode the new unified auditing is enabled, and AUDIT_TRAIL is not ignored. This is the mode to use until you have migrated all your policies and audit trail queries to the new one. However you can see that in mixed mode, there is no double auditing but only new default policies. The old policies are only logged to the old audit trail.

But if you don’t use auditing, then you don’t want the mixed mode.

uniaud_on

This is done with an instance shutdown, relinking onLinux or renaming a ddl on Windows.

SQL> shutdown immediate;
ORACLE instance shut down.
SQL> host ( cd $ORACLE_HOME/rdbms/lib ; make -f ins_rdbms.mk uniaud_&2 ioracle ORACLE_HOME=$ORACLE_HOME )
/usr/bin/ar d /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/12.2.0/dbhome_1/bin
 
 - Linking Oracle
...

And then you are in ‘pure mode’:

SQL> select parameter,value from v$option where parameter='Unified Auditing';
 
PARAMETER         VALUE
---------         -----
Unified Auditing  TRUE

In that mode, AUDIT_TRAIL is ignored and you will never see new rows in the old AUD$:

SQL> select action_name,sql_text from dba_audit_trail;
 
no rows selected

However, as in the mixed mode you will have to manage the new audit trail. My best recommandation is to keep it and add a purge job. One day you may want to have a look at unsuccessful logins of the past few days. But you still have the choice to disable the default polices, and then the only things you will see are the operations done on the trail:

AUDIT_TYPE  UNIFIED_AUDIT_POLICIES  ACTION_NAME  SQL_TEXT
----------  ----------------------  -----------  --------
Standard                            EXECUTE      BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;^@
Standard                            EXECUTE      BEGIN dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.audi
Standard                            EXECUTE      BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;^@

The reason is that if a hacker getting super administrator rights has tried to whipe his traces, then at least this suspect operation remains.

Test it

To validate this blog post, I’ve tested all scenarios on 12.2.0.1 with the combination of:

• audit_trail=db or audit_trail=none
• uniaud_on or uniaud_off
• audit or noaudit policy for ORA_SECURECONFIG and ORA_LOGON_FAILURES

For each combination, I’ve purged both audit trails (AUD$ and AUD$UNIFIED) and run a few statements that are logged by default or by explicit audit.

So what?

Basically, the recommandation is still the same as before: either disable the audit or schedule a purge. There is no purge by default because auditing is different than logging. When your security policy is to audit some operations, they must not be purged before being archived, or processed.

When you upgrade to 12c:

1. If you want to manage only the old audit, then you should disable ORA_LOGON_FAILURES and ORA_SECURECONFIG.
2. If you want to manage both, then add a job to purge the unified audit trail (audit_trail_type=>dbms_audit_mgmt.audit_trail_unified).
3. If you don’t use the old auditing, then enable the ‘pure mode’. But then, AUDIT_TRAIL=NONE is ignored, so:
4. If you don’t use the new unified auditing, then disable ORA_LOGON_FAILURES and ORA_SECURECONFIG.
5. Or use the new unified auditing and set a job to purge it regularly.

And control the growth of SYSAUX:

SQL> select occupant_name,schema_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like 'AUD%';
 
OCCUPANT_NAME  SCHEMA_NAME  OCCUPANT_DESC          SPACE_USAGE_KBYTES
-------------  -----------  -------------          ------------------
AUDSYS         AUDSYS       AUDSYS schema objects  1280
AUDIT_TABLES   SYS          DB audit tables        0

SYS ‘DB audit tables’ is the old one, filled in ‘mixed mode’ only. AUDSYS ‘AUDSYS schema objects’ is the new unified one, filled in both modes.

But I have something to add. The default policies do not audit something that you are supposed to do so frequently, it should not fills hundreds of MB before several decades.
If you get this during the last hour:

SQL> select audit_type,unified_audit_policies,action_name,return_code,count(*) 
  2  from unified_audit_trail where event_timestamp>sysdate-1
  3  group by audit_type,unified_audit_policies,action_name,return_code
  4  order by count(*);
AUDIT_TYPE  UNIFIED_AUDIT_POLICIES  ACTION_NAME  RETURN_CODE  COUNT(*)  
----------  ----------------------  -----------  -----------  --------
Standard                            AUDIT        0                2         
Standard                            EXECUTE      0                4     
Standard      ORA_SECURECONFIG      CREATE ROLE  0             9268    
Standard    ORA_LOGON_FAILURES      LOGON        1017           348  

then the problem is not auditing but an attack, either from a hacker of because of your application design connecting for each execution or running DDL all the time.