Online Magazine

How do I integrate Oracle Unified Audit into SYSLOG?

Every database needs a security concept – and this in turn needs to be monitored. Oracle databases use Oracle Unified Audit for this. To decouple the audit from the database and thus reduce the risk of manipulation, you can integrate it into SYSLOG. How? I'll show you in this hack.

by Stefan Oehrli

Anyone who regularly deals with databases knows that a database needs a security concept. This not only classifies the data (e.g., as "critical", such as data about business processes), but also determines how securely the database must be configured (keyword "hardening") and who has access to what data (user and role concept).

In order to check whether the security measures taken are effective – for example, whether only those users who are allowed to access certain data can do so – the security concept must be monitored. At Oracle, this is done with the help of the database audit, including Oracle Unified Audit.

Oracle Unified Audit was introduced with Oracle 12c and is the future standard for any database audit. It has 2 advantages in particular: Firstly, audit records are now only stored in a single location. Secondly, Oracle Unified Audit monitors attempts to manipulate the audit, i.e., it records changes to audit records, such as deletions. The implementation of such an audit includes the following points:

Choice or configuration of the Oracle audit methodology
Definition of the rule for monitoring with audit policies
Housekeeping
Central evaluation and integration of the audit data, e.g., with the help of the integration in SYSLOG

Especially the last point is important: The integration of the audit records in SYSLOG decouples them from the database, which reduces the risk of manipulation. Moreover, the audit information can thus be collected and evaluated across several databases. As an additional benefit, audit data can be easily integrated into third-party systems such as Kafka, Splunk or Elastic Search with the help of SYSLOG. But how can you integrate Oracle Unified Audit into SYSLOG? Let's find out together.

Noob Hack

To forward audit event records directly to SYSLOG, Oracle provides two parameters:

UNIFIED_AUDIT_SYSTEMLOG specifies whether key fields of Unified Audit events are sent to SYSLOG. This parameter can also be set per pluggable database (PDB) in a multitenant environment.
UNIFIED_AUDIT_COMMON_SYSTEMLOG specifies whether key fields of common Unified Audit events are sent to SYSLOG. This parameter is for multitenant environments and the globally or commonly defined audit policies.

Figure 1 schematically shows the configuration of Oracle Unified Audit in a multitenant database with common and local user including the forwarding to SYSLOG. For single-tenant databases, the configuration of the local users of the blue PDB02 in the diagram applies:

_{Figure 1: Unified Audit in a multitenant database.}

TIP: While the complete audit records are always available in UNIFIED_AUDIT_TRAIL, there is only reduced information in SYSLOG. However, this information is usually sufficient for direct monitoring or alerting. Details can be queried after a SYSLOG entry in the database, if it has not yet been deleted.

Let's start with the actual SYSLOG configuration in a multitenant database. In the following example I use RSYSLOG on Oracle Enterprise Linux 7.

First, we need to define the appropriate SYSLOG facilities:

sudo vi /etc/rsyslog.conf *.info;mail.none;authpriv.none;cron.none;local2.none;local4.none /var/log /messages
# Unified Audit Rules
local2.info /var/log/oracle_common_audit_records.log
local4.info /var/log/oracle_audit_records.log
Then we restart the RSYSLOG service ...

sudo systemctl restart rsyslog.service... and implement a connection as SYS to CDB$ROOT. We also change UNIFIED_AUDIT_COMMON_SYSTEMLOG and UNIFIED_AUDIT_SYSTEMLOG in PDB1:

CONNECT / AS SYSDBA
SHOW PARAMETER unified_audit_common_systemlog
ALTER SYSTEM SET unified_audit_common_systemlog='local2.info' SCOPE= SPFILE;
ALTER SESSION SET CONTAINER=PDB1;
ALTER SYSTEM SET unified_audit_systemlog='local4.info' SCOPE=SPFILE;
Now we restart the entire container database:

CONNECT / AS SYSDBA STARTUP FORCE; SHOW PARAMETER unified_audit ®TIP: For a single-tenant database (local user), the configuration only includes the parameter UNIFIED_AUDIT_SYSTEMLOG.

Now that we have integrated the audit into SYSLOG, you can test your audit policies as well as the audit events. I will show you how to do this in the following pro hack.

Another useful Oracle hack:

How do you manage a database name directory with as little effort as possible?

For example, by using a 389 Directory Server.

Find out in this article, how to install such a server for an Oracle database.

Pro Hack

As shown in Figure 1, the distinction between common and local users makes sense especially in multitenant databases. Here it is possible to distinguish between the two types of users and to log the corresponding audit events separately. In the following, we will first test audit policies and then audit events – once via a login as a common user, then via a login as a local user.

To test the audit policies, we create a common audit policy in the root container of the multitenant database as an example ...

CONNECT / AS SYSDBA
CREATE AUDIT POLICY tvd_com_logon ACTIONS LOGON CONTAINER=ALL;
AUDIT POLICY tvd_com_logon;

In the pluggable database PDB1 we define another policy:

CONNECT / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
CREATE AUDIT POLICY tvd_loc_logon ACTIONS LOGON;
AUDIT POLICY tvd_loc_logon;

To check which audit policies are enabled, we do the following:

SET LINESIZE WINDOW
COL policy_name FOR A14
COL entity_name FOR A20
SELECT * FROM audit_unified_enabled_policies;
POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI
TVD_COM_LOGON BY USER ALL USERS USER YES YES
TVD_LOC_LOGON BY USER ALL USERS USER YES YES

Login as common user

TIP: To ensure clean test conditions and to better see what you are entering for the test in the audit, I recommend deleting the UNIFIED_AUDIT_TRAIL in the root container CDB$ROOT as well as in the pluggable database PDB1 before executing the code for testing:

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;
/

Now we create an audit event in PDB1:

CONNECT system@TCIS19C

Afterwards, we can query UNIFIED_AUDIT_TRAIL in PDB1.

TIP: If you do the query with SQLPlus by default, you can copy the following code block first for a nicer look:

SET LINESIZE 160 PAGESIZE 200
COL event_timestamp FOR A20
COL dbusername FOR A10
COL os_username FOR A10
COL action_name FOR A20
COL return_code FOR 999999
COL object_name FOR A20
COL unified_audit_policies FOR A30

SELECT
to_char(event_timestamp,'dd.mm.yyyy hh24:mi:ss') event_timestamp,
sessionid,
dbusername,
os_username,
action_name,
return_code,
object_name,
unified_audit_policies
FROM unified_audit_trail
ORDER BY event_timestamp;
EVENT_TIMESTAMP SESSIONID DBUSERNAME ACTION_NAME OBJECT_NAME UNIFIED_AUDIT_POLICIES
01.09.2022 07:05:01 4036001073 SYS EXECUTE DBMS_AUDIT_MGMT
01.09.2022 07:05:26 1521526562 SYSTEM LOGON TVD_COM_LOGON
01.09.2022 07:05:40 1430860507 SYS LOGOFF BY CLEANUP

To check the SYSLOG file, we perform the following steps:

host sudo grep -i 1521526562 /var/log/oracle_common_audit_records.log
Apr 1 07:05:26 localhost journal: Oracle Unified Audit[10178]: LENGTH: '204'
TYPE:"4" DBID:"1612911514" SESID:"1521526562" CLIENTID:"" ENTRYID:"1”
STMTID:"1" DBUSER:"SYSTEM" CURUSER:"SYSTEM" ACTION:"100" RETCODE:"0"
SCHEMA:"" OBJNAME:"" PDB_GUID:"86B637B62FDF7A65E053F706E80A27CA"

Login as local user

To test the login as local user, we create an audit event in PDB1 and log in as user SCOTT:

CONNECT scott/tiger@pdb1.trivadislabs.com

Then we query UNIFIED_AUDIT_TRAIL in PDB1:

SELECT
to_char(event_timestamp,'dd.mm.yyyy hh24:mi:ss') event_timestamp,
sessionid,
dbusername,
os_username,
action_name,
return_code,
object_name,
unified_audit_policies
FROM unified_audit_trail
ORDER BY event_timestamp;
EVENT_TIMESTAMP SESSIONID DBUSERNAME ACTION_NAME OBJECT_NAME UNIFIED_AUDIT_POLICIES
01.09.2022 07:19:16 3934031462 SYS EXECUTE DBMS_AUDIT_MGMT TVD_LOC_LOGON
01.09.2022 07:19:45 2440243087 SYSTEM LOGON TVD_LOC_LOGON, TVD_COM_LOGON
01.09.2022 07:21:08 2448415323 SCOTT LOGON TVD_LOC_LOGONFinally, we control the SYSLOG file one more time:

host sudo grep -c -i 2448415323 /var/log/oracle_common_audit_records.log 0
host sudo grep -i 2448415323 /var/log/oracle_audit_records.log
Apr 1 07:21:08 localhost journal: Oracle Unified Audit[11705]: LENGTH: '201'
TYPE:"4" DBID:"817014372" SESID:"2448415323" CLIENTID:"" ENTRYID:"1" STMTID:"1"
DBUSER:"SCOTT" CURUSER:"SCOTT" ACTION:"100" RETCODE:"0" SCHEMA:"" OBJNAME:""
PDB_GUID:"B8E3D716A96C1507E0530100007F363B"

Conclusion

We did it! In this hack, we integrated an Oracle Unified Audit into SYSLOG and created and tested audit policies as well as audit events for common and local users.

The integration into SYSLOG has the following advantages:

We have decoupled the audit data from the database, and they are now available to us in SYSLOG in a reduced form. The full audit data is still in the Unified Audit and is available there for comprehensive evaluations via SQL.
From SYSLOG, we can easily forward the audit data to third-party systems such as Kafka, Elastic Search or Splunk. In order for Kafka or Elastic Search to read the data directly from the databases, I would have to access the database directly using JDBC and develop the corresponding query myself. With Splunk, you would also have to implement access via JDBC yourself or buy the Splunk Agent for Oracle. SYSLOG, on the other hand, is relatively easy to use without additional licensing costs.