Setup Unified auditing in Oracle 19c

The following auditing modes are available for Oracle Database 12c, 18c, 19c:

• Unified Auditing—Recommended. See the following Oracle technical article for detailed instructions on how to enable unified auditing: Enabling Unified Auditing.
  Perform the following steps to configure Unified Auditing on your Oracle Database:
  1. Create and enable an audit policy to audit specific parameters across your Oracle Database.
  2. If needed, create and enable specific audit policies to audit successful data access and changes, user actions, component actions, etc.
• Mixed Mode—Default auditing in a newly installed database. It enables both traditional and the new Unified Auditing facilities.
  Note: I have noticed after in place upgrade to 19c mixed mode is not activated.

Unified auditing is Oracle’s newer way of auditing database activities. It is faster than mixed mode or traditional auditing.

How to setup Unified auditing in windows

• Stop all Oracle services
• Rename %ORACLE_HOME%/bin/orauniaud12.dll.dbl to %ORACLE_HOME%/bin/orauniaud12.dll.
• Start services

Verify

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

If the option works as expected, it returns:

PARAMETER VALUE
---------------- ----------
Unified Auditing TRUE

After this we can create the actual policy and settings for it. Connect with a user with audit_admin role to the database.

CREATE AUDIT POLICY BS_UNIFIED_AUDIT_POLICY PRIVILEGES CREATE USER;
AUDIT POLICY BS_UNIFIED_AUDIT_POLICY;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS CREATE USER;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER USER;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS DROP USER;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS CREATE ROLE;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER ROLE;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS DROP ROLE;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS GRANT;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS REVOKE;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS CREATE PROFILE;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER PROFILE;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS DROP PROFILE;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS CREATE DATABASE LINK;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER DATABASE LINK;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS DROP DATABASE LINK;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS CREATE SYNONYM;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER SYNONYM;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS DROP SYNONYM;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS SELECT ANY DICTIONARY;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALL on AUDSYS.AUD$UNIFIED;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS CREATE PROCEDURE,CREATE FUNCTION,CREATE PACKAGE,CREATE PACKAGE BODY;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER PROCEDURE,ALTER FUNCTION,ALTER PACKAGE,ALTER PACKAGE BODY;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS DROP PROCEDURE,DROP FUNCTION,DROP PACKAGE,DROP PACKAGE BODY;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER SYSTEM;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS CREATE TRIGGER;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS ALTER TRIGGER;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS DROP TRIGGER;
ALTER AUDIT POLICY BS_UNIFIED_AUDIT_POLICY ADD ACTIONS LOGON,LOGOFF;

Verify audit policies

select POLICY_NAME, ENABLED_OPTION  FROM audit_unified_enabled_policies;

SELECT policy_name,audit_option,condition_eval_opt,audit_condition
FROM   audit_unified_policies
WHERE  policy_name in ('BS_UNIFIED_AUDIT_POLICY')
ORDER BY 1,2;

Cleaning up logs

Set timestamp for archived logs in some script, e.g. after running backup script to mark that you have backed them up. I recommend to keep the archive time e.g. 180 days in the past as below to make sure all audit logs are backed up, in case backup malfunctions.

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME    =>  SYSDATE-180);
END;
/

Create a purge job for audit logs. Scheduler takes care of the running.

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  AUDIT_TRAIL_PURGE_INTERVAL  => 24,
  AUDIT_TRAIL_PURGE_NAME      => 'Audit_Trail_PJ',
  USE_LAST_ARCH_TIMESTAMP     => TRUE);
END;
/