This was first published on https://blog.dbi-services.com/12c-unified-auditing-and-audit_traildb-in-mixed-mode (2017-02-23)
Republishing here for new followers. The content is related to the the versions available at the publication date
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
This exactly what happens with Unified Auditing. Because of this compatibility requirement, it is declined in two modes:
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 FALSEBut there may be something enabled if the old auditing is enabled, because it is actually a mixed mode.
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.
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.
To validate this blog post, I’ve tested all scenarios on 12.2.0.1 with the combination of:
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.
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:
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 348then 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.
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
Hi,
How to drop the audsys schema.?
If we cant drop then pls help me to clear the data occupied in audsys schema.
Regards, Preethi