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.
My assumption is that the cleanup job had removed all of the extents which had been allocated before the cleanup job started. The index create statement did not notice as the current extent removal attempt blocked (hence the job error) and future to-be created extents were not affected. However, most of the index had been “cleaned up”. #sadface
Neil Chandler's DB Blog 