How to write Unified Audit Trail Records to SYSLOG 

With the introduction of Oracle Unified Audit, Oracle has completely redesigned the process of logging audit events. With the new unified audit trail, there is only one place where audit records are stored. Ok, the audit trail exists per PDB and for a read only database additionally somehow as a binary overflow file. However, the times are over in which we had to deal with AUD$, FGA_LOG$ or DVSYS.AUDIT_TRAIL$ tables and all kinds of audit files. Everything is all right, isn’t it?

Unfortunately not quite. Oracle left out the possibility to write audit records to SYSLOG in the first implementations of Oracle Unified Audit. With the release Oracle 18c later 19c this functionality was added again step by step. But before you get too excited and start switching the audit trail back to SYSLOG, you need to take a look at one or two things. The new initialisation parameters UNIFIED_AUDIT_SYSTEMLOG and UNIFIED_AUDIT_COMMON_SYSTEMLOG do not work quite the same as AUDIT_SYSLOG_LEVEL used to. In this blog post, we’ll take a closer look at audit configuration in the context of SYSLOG.

Background

Even with the use of the SYSLOG configuration for Oracle Unified Audit, the Unified Audit Infrastructure as well as the audit policies must be configured as before. In this respect, nothing changes with SYSLOG forwarding. With the new initialisation parameters below, Oracle only enables that the audit records are additionally forwarded to SYSLOG in a reduced form.

  • UNIFIED_AUDIT_SYSTEMLOG specifies whether key fields of unified audit records will be written to the SYSLOG utility (on UNIX platforms) or to the Windows Event Viewer (on Windows). In a CDB, this parameter is a per-PDB static initialisation parameter. This parameter is available from Oracle 18c.
  • UNIFIED_AUDIT_COMMON_SYSTEMLOG specifies whether key fields of unified audit records generated due to common audit policies will be written to the SYSLOG utility. This parameter is available only from Oracle 19c.

The following sketch shows the schematic structure of the unified audit configuration with ¡ forwarding in an Oracle Multitenant Database. Whereby the following has been configured:

  • Common audit policy COMMON_ORA_LOGON for audit action LOGON
  • Local audit policy LOCAL_ORA_LOGON for audit action LOGON
  • UNIFIED_AUDIT_SYSTEMLOG parameter set to SYSLOG facility local0.info
  • UNIFIED_AUDIT_COMMON_SYSTEMLOG parameter set to SYSLOG facility local1.info
Unified Audit with SYSLOG configuration in an Oracle Multitenant Database

The following use cases are drawn in the sketch:

Common User Login to CDB$ROOT

  1. A common user is logged into the CDB$ROOT and COMMON_ORA_LOGON does create an audit event for audit action LOGON
  2. A single audit record is created in the local UNIFIED_AUDIT_TRAIL of CDB$ROOT
  3. Additionally An audit record is created in the SYSLOG facility local1.warning

Common User Login to PDB01

  1. A common user is logged into the PDB01 and COMMON_ORA_LOGON as well LOCAL_ORA_LOGON does create an audit event for audit action LOGON
  2. A single audit record is created in the local UNIFIED_AUDIT_TRAIL of PDB01 
  3. Additionally an audit record is created in the SYSLOG facility local1.info

Local User Login to PDB01

  1. A local user is logged into the PDB02 and LOCAL_ORA_LOGON does create an audit event for audit action LOGON
  2. A single audit record is created in the local UNIFIED_AUDIT_TRAIL of PDB02
  3. Additionally an audit record is created in the SYSLOG facility local0.warning

Note: An audit record is created in UNIFIED_AUDIT_TRAIL as well as in SYSLOG. This means that you also have to define a corresponding housekeeping for UNIFIED_AUDIT_TRAIL.

Setup Unified Audit with SYSLOG Integration

Configure SYSLOG

First we do have to configure corresponding SYSLOG destinations for our database audit information. In the following I will not go into the detailed configuration of SYSLOG respectively RSYSLOG. We only extend the configuration with two additional log files. The first thing to do is to edit the /etc/rsyslog.conf file as root user. We add two new local facilities under the RULES section.

sudo vi /etc/rsyslog.conf

# Unified Audit Rules
local0.info            /var/log/oracle_common_audit_records.log
local1.info            /var/log/oracle_audit_records.log

Afterwards the RSYSLOG service must be restarted.

sudo systemctl restart rsyslog.service

Audit Initialisation Parameters

The Oracle initialisation parameter for the audit configuration requires an instance restart and has to be modified via CDB$ROOT. Below we just change UNIFIED_AUDIT_COMMON_SYSTEMLOG optionally we can also forward local audit records to SYSLOG by setting UNIFIED_AUDIT_SYSTEMLOG.

  • Connect as SYS to CDB$ROOT and change UNIFIED_AUDIT_COMMON_SYSTEMLOG
CONNECT / AS SYSDBA
SHOW PARAMETER unified_audit_common_systemlog
ALTER SYSTEM SET unified_audit_common_systemlog='local0.info' SCOPE=SPFILE;
  • Restart the whole container database
STARTUP FORCE;
SHOW PARAMETER unified_audit_common_systemlog

As of now, audit records for common audit events are forwarded to the appropriate SYSLOG facility.

Audit Policies

For simplicity, we test the audit configuration with a few simple audit policies for the audit action LOGON. Of course, audit policies can be defined for any actions.

  • Create a common audit policy to log all logon events of common users in CDB$ROOT or any PDB.
CONNECT / AS SYSDBA
CREATE AUDIT POLICY common_ora_logon ACTIONS LOGON CONTAINER=ALL;
AUDIT POLICY common_ora_logon;
  • Check which audit policies are enabled.
SET LINESIZE WINDOW
COL policy_name FOR A16
COL entity_name FOR A11
SELECT * FROM audit_unified_enabled_policies;
 
POLICY_NAME	 ENABLED_OPTION  ENTITY_NAME ENTITY_ SUC FAI
---------------- --------------- ----------- ------- --- ---
COMMON_ORA_LOGON BY USER	 ALL USERS   USER    YES YES
  • Create a local audit policy to log all logon events of local users in a particular PDB.
ALTER SESSION SET CONTAINER=pdb1;
CREATE AUDIT POLICY local_ora_logon ACTIONS LOGON;
AUDIT POLICY local_ora_logon;
  • Check which audit policies are enabled. We can now see that the local audit policy as well as the common audit policy from before are active in the PDB.
SET LINESIZE WINDOW
COL policy_name FOR A16
COL entity_name FOR A11
SELECT * FROM audit_unified_enabled_policies;
 
POLICY_NAME	 ENABLED_OPTION  ENTITY_NAME ENTITY_ SUC FAI
---------------- --------------- ----------- ------- --- ---
LOCAL_ORA_LOGON  BY USER	 ALL USERS   USER    YES YES
COMMON_ORA_LOGON BY USER	 ALL USERS   USER    YES YES

Test Audit Configuration

Let’s purge the audit trail in CDB$ROOT as well PDB1 have not too much information in the trail.

CONNECT / AS SYSDBA
BEGIN
    dbms_audit_mgmt.clean_audit_trail(
    audit_trail_type         =>  dbms_audit_mgmt.audit_trail_unified,
    use_last_arch_timestamp  =>  FALSE);
END;
/
 
ALTER SESSION SET container=PDB1;
BEGIN
    dbms_audit_mgmt.clean_audit_trail(
    audit_trail_type         =>  dbms_audit_mgmt.audit_trail_unified,
    use_last_arch_timestamp  =>  FALSE);
END;
/

First we do a login as user SYSTEM to CDB$ROOT of database TDB19C

sqlplus system@TDB19C

Lets check what we do see in the view UNIFIED_AUDIT_TRAIL

SET LINESIZE 160 PAGESIZE 200
COL event_timestamp FOR A17
COL dbusername FOR A10
COL action_name FOR A17
COL return_code FOR 999999
COL unified_audit_policies FOR A30
 
SELECT
    to_char(event_timestamp,'dd.mm.yy hh24:mi:ss') event_timestamp,
    sessionid,
    dbusername,
    action_name,
    return_code,
    unified_audit_policies
FROM unified_audit_trail
ORDER BY event_timestamp;
 
EVENT_TIMESTAMP    SESSIONID DBUSERNAME ACTION_NAME	  RETURN_CODE UNIFIED_AUDIT_POLICIES
----------------- ---------- ---------- ----------------- ----------- ------------------------------
22.03.22 09:07:03 1430860507 SYS	LOGOFF BY CLEANUP	    0
23.03.22 14:49:24 2404020191 SYS	EXECUTE 		    0
23.03.22 14:49:44 2578688223 SYSTEM	LOGON			    0 COMMON_ORA_LOGON

Lets see what we do have in the SYSLOG log file

host sudo grep -i 2578688223 /var/log/oracle_common_audit_records.log
Mar 23 14:49:44 localhost journal: Oracle Unified Audit[17838]: LENGTH: '204' TYPE:"4" DBID:"1612911514" SESID:"2578688223" CLIENTID:"" ENTRYID:"1" STMTID:"1" DBUSER:"SYSTEM" CURUSER:"SYSTEM" ACTION:"100" RETCODE:"0" SCHEMA:"" OBJNAME:"" PDB_GUID:"86B637B62FDF7A65E053F706E80A27CA"

The action number can be locked up in the table AUDIT_ACTIONS

SELECT * FROM audit_actions WHERE action=100;      

    ACTION NAME
---------- ----------------------------
       100 LOGON

Now lets see what happens when we login as SYSTEM into PDB1

CONNECT system@pdb1.trivadislabs.com

We now do see an audit record in the UNIFIED_AUDIT_TRAIL of the PDB. Active Policy for this common user is LOCAL_ORA_LOGON and COMMON_ORA_LOGON.

SET LINESIZE 160 PAGESIZE 200
COL event_timestamp FOR A17
COL dbusername FOR A10
COL action_name FOR A11
COL return_code FOR 999999
COL unified_audit_policies FOR A33
 
SELECT
    to_char(event_timestamp,'dd.mm.yy hh24:mi:ss') event_timestamp,
    sessionid,
    dbusername,
    action_name,
    return_code,
    unified_audit_policies
FROM unified_audit_trail
ORDER BY event_timestamp;

EVENT_TIMESTAMP    SESSIONID DBUSERNAME ACTION_NAME RETURN_CODE UNIFIED_AUDIT_POLICIES
----------------- ---------- ---------- ----------- ----------- ---------------------------------
23.03.22 14:54:22 1216566979 SYS	EXECUTE 	      0
23.03.22 14:55:01 3827730564 SYSTEM	LOGON		      0 LOCAL_ORA_LOGON, COMMON_ORA_LOGON

Lets see what we do have in the SYSLOG logfile

host sudo grep -i 3827730564 /var/log/oracle_common_audit_records.log
Mar 23 14:55:01 localhost journal: Oracle Unified Audit[18210]: LENGTH: '203' TYPE:"4" DBID:"817014372" SESID:"3827730564" CLIENTID:"" ENTRYID:"1" STMTID:"1" DBUSER:"SYSTEM" CURUSER:"SYSTEM" ACTION:"100" RETCODE:"0" SCHEMA:"" OBJNAME:"" PDB_GUID:"B8E3D716A96C1507E0530100007F363B"

As a final test, we log into PDB1 as local user SCOTT.

connect scott/tiger@pdb1.trivadislabs.com

There is now a new audit record for SCOTT in the UNIFIED_AUDIT_TRAIL of the PDB. Active policy for this local user is LOCAL_ORA_LOGON.

SET LINESIZE 160 PAGESIZE 200
COL event_timestamp FOR A17
COL dbusername FOR A10
COL action_name FOR A11
COL return_code FOR 999999
COL unified_audit_policies FOR A33
 
SELECT
    to_char(event_timestamp,'dd.mm.yy hh24:mi:ss') event_timestamp,
    sessionid,
    dbusername,
    action_name,
    return_code,
    unified_audit_policies
FROM unified_audit_trail
ORDER BY event_timestamp;

EVENT_TIMESTAMP    SESSIONID DBUSERNAME ACTION_NAME RETURN_CODE UNIFIED_AUDIT_POLICIES
----------------- ---------- ---------- ----------- ----------- ---------------------------------
23.03.22 14:54:22 1216566979 SYS	EXECUTE 	      0
23.03.22 14:55:01 3827730564 SYSTEM	LOGON		      0 LOCAL_ORA_LOGON, COMMON_ORA_LOGON
23.03.22 14:59:26 2954396682 SCOTT	LOGON		      0 LOCAL_ORA_LOGON

Because we have only set the parameter UNIFIED_AUDIT_COMMON_SYSTEMLOG and regular audit policies are not forwarded to SYSLOG, we do not find an entry in the corresponding SYSLOG log file.

host sudo grep -i 2954396682 /var/log/oracle_common_audit_records.log
host sudo grep -i 2954396682 /var/log/oracle_audit_records.log

Conclusion

Although many things have been simplified with Oracle Unified Audit, it is easy to lose Although much has been simplified with Oracle Unified Audit, it is easy to lose track of all the common and local audit policies in an Oracle multitenant environment. An up-to-date audit concept that takes these special cases into account is absolutely essential. This includes the use of the new initialisation parameters for SYSLOG integration. Although the information in SYSLOG is nowhere near as rich as in UNIFIED_AUDIT_TRAIL itself, this feature allows easy integration of Oracle audit events into a central repository, e.g. Splunk, Elastiksearch or similar, to create a simple audit dashboard. The true source of the audit data with information on the complete SQL statements, detailed user information etc. remains reserved for the Unified Audit trail in the database.

Pluggable Database with Unified Audit and SYSLOG configuration

I have mentioned SYSLOG and RSYSLOG alternately in the blog post. The Oracle feature is basically for SYSLOG and all services based on SYSLOG. Whereas on my OCI Compute instance, where I did my tests, RSYSLOG is configured and used.

References