How we enable Unified Auditing in Oracle Database
Certifications:
- How can we increase performance on Oracle GoldenGate Replicat target with parallelism? - 19 March 2025
- How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025
- How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
In previous article we had seen how to activate it Auditing in Oracle Database, in this article we will see how to enable it Unified Auditing which allows us to put filters and so that we can record.
In contrast to classic Auditing, we can, if we want, exclude machines that we don't want to be recorded either SESSION either ana STATEMENT and activating it to choose whether to only those that have succeeded in their execution or not (WHENEVER SUCCESSFUL). Accordingly, we can choose to record only specific users or only specific machines for this policy. We can also choose whether it records all statements or ONLY TOPLEVEL like a procedure.
How is it activated?
First you will need to stop the database and with the following command activate it Unified Auditing:
sqlplus / as sysdba; shutdown immediate; exit; cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk uniaud_on ioracle sqlplus / as sysdba; startup;
In the event that we want to record actions with the administrator user SYS we should change the following parameter and restart:
sqlplus / as sysdba; ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE; shutdown immediate; startup;
Then we clear the Unified Audit Trail with the following command:
begin
dbms_audit_mgmt.flush_unified_audit_trail(
flush_type => dbms_audit_mgmt.flush_current_instance,
container => dbms_audit_mgmt.container_all);
end;
/
How do I store the audit trail files in a separate tablespace
If we want to use a different tablespace with other datafiles, run the following command:
create tablespace AUDIT_TS datafile '/oracle/oradata/orcl/audit_ts_001.dbf' size 10m autoextend on next 64m maxsize 32767m; BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, audit_trail_location_value => 'AUDIT_TS'); END; /
How we maintain Unified Auditing
If we want to create a job that will delete records older than 30 days automatically, we run the following:
BEGIN DBMS_SCHEDULER.create_job ( job_name => 'JOB_PURGE_AUDIT_RECORDS', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC(SYSTIMESTAMP)-30); DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => TRUE); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;', end_date => NULL, enabled => TRUE, comments => 'Update last_archive_timestamp'); END; /
How we build Unified Audit Policies
Create Select Policy
With the following code we can create a policy that will record the successful select statements in two tables that do not come from two specific machines:
CREATE AUDIT POLICY stratos_selects
ACTIONS
SELECT ON stratos.customers,
SELECT ON stratos.products
WHEN 'SYS_CONTEXT(''USERENV'', ''HOST'') NOT IN (''oracledev1'', ''oracledev2'')'
EVALUATE PER SESSION;
audit policy stratos_selects WHENEVER SUCCESSFUL;
--noaudit policy stratos_selects ;
--drop audit policy stratos_selects ;
Create Login Monitoring Policy
With the following code we can create a policy that will record successful connections with two users that do not come from two specific machines:
CREATE AUDIT POLICY monitor_connects
ACTIONS LOGON
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') IN (''STRATOS'', ''HR'') and SYS_CONTEXT(''USERENV'', ''HOST'') NOT IN (''oracledev1'', ''oracledev2'')'
EVALUATE PER SESSION;
audit policy monitor_connects WHENEVER SUCCESSFUL;
--noaudit policy monitor_connects;
--drop audit policy monitor_connects;
Create DDL / DML Policy
With the following code we can create a policy that will record successful DDL and DML actions that do not come from two specific machines:
CREATE AUDIT POLICY stratos_changes
ACTIONS
TRUNCATE TABLE,
DROP TABLE,
ALTER TABLE,
DROP VIEW,
ALTER VIEW,
INSERT ON stratos.customers,
UPDATE ON stratos.customers,
DELETE ON stratos.customers,
INSERT ON stratos.products,
UPDATE ON stratos.products,
DELETE ON stratos.products
WHEN 'SYS_CONTEXT(''USERENV'', ''HOST'') NOT IN (''oracledev1'', ''oracledev2'')'
WHENEVER SUCCESSFUL;
EVALUATE PER SESSION;
audit policy stratos_changes WHENEVER SUCCESSFUL;
--noaudit policy stratos_changes;
--drop audit policy stratos_changes;
If we want to add or remove an option from the recording we can with the following:
ALTER AUDIT POLICY stratos_changes ADD ACTIONS DELETE ON stratos.logging DROP ACTIONS DELETE ON stratos.housekeeping;
How we see the existing Unified Audit Policies
With the following query we can see them Unified Audit Policies which we have made with the parameters we have set:
SELECT * FROM AUDIT_UNIFIED_POLICIES --where policy_name='STRATOS_CHANGES' ;
To see which policies are active, run the following query:
SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;
By default, the two standard policies are enabled and we can disable them with the following:
noaudit policy ORA_SECURECONFIG; noaudit policy ORA_LOGON_FAILURES;
How we see information from Unified Audit Policies
To see the information that Auditing has recorded with the name of the machine, the user OS, the database user, the program and the exact statement that was run, run the following:
SELECT
event_timestamp
,OS_USERNAME
,USERHOST
,DBUSERNAME
,client_program_name
,action_name
,object_schema
,object_name
,sql_text
,sql_binds
,unified_audit_policies
FROM UNIFIED_AUDIT_TRAIL
WHERE unified_audit_policies is not null and UNIFIED_AUDIT_POLICIES not in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
ORDER BY EVENT_TIMESTAMP DESC;
By running the following query we can see per policy and per action how many records have been recorded:
select audit_type,unified_audit_policies,action_name,return_code,count(*) from unified_audit_trail where event_timestamp>sysdate-1 group by audit_type,unified_audit_policies,action_name,return_code order by count(*);
To see the Unified Audit Trail configuration, run the following:
select * from DBA_AUDIT_MGMT_CONFIG_PARAMS where AUDIT_TRAIL='UNIFIED AUDIT TRAIL';
Finally, with the following query we can see the space occupied by Auditing:
select occupant_name,schema_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like 'AUD%';
