Infrastructure at your Service

Franck Pachot

12c Unified Auditing and AUDIT_TRAIL=DB in mixed mode

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.

 

One Comment

  • Franck,
    thank you for this summary!
    if you want to keep your SYSAUX small, I’d recommend to go to a dedicated tablespace for AUDIT segments:
    dbms_audit_mgmt.set_audit_trail_location
    is capable to do so (in both audit methods).
    Hope this helps to keep audit usable,
    berx

     

Leave a Reply


six − 3 =

Franck Pachot
Franck Pachot

Technology Leader