Oracle 12c introduces “Unified Audit Trail” a faster, easier to access and more secure audit system.
It optionally allows to stage the audit records in a dedicated memory buffer (UNIFIED_AUDIT_SGA_QUEUE_SIZE), where they are temporarily grouped before being written into the audit table via batch transactions.
This new audit configuration substantially reduces the transactional overhead generated by the auditing.
Important improvements have also done to simplify the utilization:
– One single audit trail for any audit data, in fact UNIFIED_AUDIT_TRAIL view replaces SYS.AUD$/DBA_AUDIT_TRAIL, SYS.FGA_LOGS$/DBA_FGA_AUDIT_TRAIL, DVSYS.AUDIT_TRAIL$, V$XML_AUDIT_TRAIL and the OS audit files in adump.
– All audit data stored in Oracle secure files.
– Role segregation between:
- DBA responsible to maintain free space and backup.
- AUDIT_ADMIN responsible to manage the audit policies and define the data retention.
- AUDIT_VIEWER in charge of the reports.
Unified Audit Trail introduces also new security options important to mention:
– It is activated with a kernel relink and it doesn’t require additional steps or parameters.
– The new AUDSYS table has a Read-Only Protection for all users. Even the DBA privilege can’t manipulate the audit records!
How to activate Unified Audit Trail
--Stop all Oracle processes: databases, listener and Enterprise Manager agent.
--Relink Oracle with the uniaud_on option.
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle
--Restart all Oracle processes: databases, listener and Enterprise Manager agent.
--Check is Unified Audit Trail is active
SQL> select * from v$option where PARAMETER='Unified Auditing';
INST_ID PARAMETER VALUE CON_ID
------- ------------------------------ ------------------- ----------
1 Unified Auditing TRUE 0
Optional, but strongly recommended it is possible to relocate the AUDIT segments from SYSAUX Tablespace to a dedicated one.
SQL> Create tablespace TBS_AUDIT datafile SIZE 2G AUTOEXTEND ON;
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_location_value => 'TBS_AUDIT');
END;
/
SQL> select OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, BYTES from dba_segments where TABLESPACE_NAME='TBS_AUDIT';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES
--------------- ------------------------------ ------------------------------ ------------------ ----------
AUDSYS CLI_SWP$1b2a49f1$1$1 HIGH_PART TABLE PARTITION 65536
AUDSYS CLI_SWP$1b2a49f1$1$1 PART_2 TABLE PARTITION 65536
AUDSYS CLI_LOB$1b2a49f1$1$1 HIGH_PART INDEX PARTITION 65536
AUDSYS CLI_TIME$1b2a49f1$1$1 HIGH_PART INDEX PARTITION 65536
AUDSYS CLI_LOB$1b2a49f1$1$1 PART_2 INDEX PARTITION 65536
AUDSYS CLI_TIME$1b2a49f1$1$1 PART_2 INDEX PARTITION 65536
AUDSYS CLI_SCN$1b2a49f1$1$1 PART_2 INDEX PARTITION 65536
AUDSYS SYS_IL0000091784C00014$$ SYS_IL_P241 INDEX PARTITION 65536
AUDSYS CLI_SCN$1b2a49f1$1$1 HIGH_PART INDEX PARTITION 65536
AUDSYS SYS_IL0000091784C00014$$ SYS_IL_P246 INDEX PARTITION 65536
AUDSYS SYS_LOB0000091784C00014$$ SYS_LOB_P244 LOB PARTITION 131072
AUDSYS SYS_LOB0000091784C00014$$ SYS_LOB_P239 LOB PARTITION 131072
12 rows selected.
The introduction of Audit Policies have brought flexibility and granularity on what it is possible to audit, here an example using Oracle sys_context function.
CREATE AUDIT POLICY hr_employees
PRIVILEGES CREATE TABLE
ACTIONS UPDATE ON HR.EMPLOYEES
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR_ADMIN'''
EVALUATE PER STATEMENT;
AUDIT POLICY hr_employees;
My Oracle Notes 