Streamlining Oracle Audit Records to SIEM Tools using JSON
Unified Auditing is the strategic Oracle Database audit framework and should be used to audit activity in Oracle Database 12.2 or greater. When it comes to sharing these audit records with SIEM tools, rsyslog daemon used mostly.
The main concern is that The following fields are NOT recorded in syslog of Unified Audit Trail:
– OS_USERNAME
– USERHOST
– TERMINAL
– SYSTEM_PRIVILEGE_USED
– SQL_TEXT
– SQL_BINDS
This is documented in Missing Audit Information In The Unified Audit Trail Records Sent To SYSLOG (Doc ID 2520613.1). As it is also stated in the document;
Most common SYSLOG configurations have a limitation of 1024 bytes, and only limited key fields can be captured in the SYSLOG. This limitation can impact the granularity and completeness of the log data captured and transmitted. Although it can be configured with the $MaxMessageSize variable in rsyslog.conf, Oracle limits what is shared with rsyslog daemon internally.
In the Oracle 19c Database Security Guide Table 28-1 maps the names given to the unified audit records fields that are written to SYSLOG and the Windows Event Viewer to the corresponding column names in the UNIFIED_AUDIT_TRAIL view.
I will show a really simple way to share these audit records with SIEM tools like WAZUH according to the our needs. We may use SQL/JSON functions like json_object and json_array to stream to a file in NDJSON (Newline Delimited JSON) format. Most of these SIEM tools agents can read json files.
You may change selected columns according to the your security policies. Here is mine.
| #SQL query to generate JSON output | |
| SPOOL audit.log APPEND | |
| SELECT '{ "oracle_audit": ' || | |
| json_object( | |
| 'AUDIT_TYPE' VALUE AUDIT_TYPE, | |
| 'SESSIONID' VALUE SESSIONID, | |
| 'OS_USERNAME' VALUE OS_USERNAME, | |
| 'USERHOST' VALUE USERHOST, | |
| 'TERMINAL' VALUE TERMINAL, | |
| 'INSTANCE_ID' VALUE INSTANCE_ID, | |
| 'DBID' VALUE DBID, | |
| 'AUTHENTICATION_TYPE' VALUE AUTHENTICATION_TYPE, | |
| 'DBUSERNAME' VALUE DBUSERNAME, | |
| 'CLIENT_PROGRAM_NAME' VALUE CLIENT_PROGRAM_NAME, | |
| 'DBLINK_INFO' VALUE DBLINK_INFO, | |
| 'ENTRY_ID' VALUE ENTRY_ID, | |
| 'EVENT_TIMESTAMP' VALUE EVENT_TIMESTAMP, | |
| 'ACTION_NAME' VALUE ACTION_NAME, | |
| 'RETURN_CODE' VALUE RETURN_CODE, | |
| 'OS_PROCESS' VALUE OS_PROCESS, | |
| 'TRANSACTION_ID' VALUE TRANSACTION_ID, | |
| 'OBJECT_SCHEMA' VALUE OBJECT_SCHEMA, | |
| 'OBJECT_NAME' VALUE OBJECT_NAME, | |
| 'SQL_TEXT' VALUE REPLACE(REPLACE(REPLACE(SQL_TEXT, CHR(0), ''), CHR(10), ''), CHR(13), ''), | |
| 'SQL_BINDS' VALUE REPLACE(REPLACE(REPLACE(SQL_BINDS, CHR(0), ''), CHR(10), ''), CHR(13), ''), | |
| 'SYSTEM_PRIVILEGE_USED' VALUE SYSTEM_PRIVILEGE_USED, | |
| 'UNIFIED_AUDIT_POLICIES' VALUE UNIFIED_AUDIT_POLICIES, | |
| 'RMAN_OPERATION' VALUE RMAN_OPERATION, | |
| 'RMAN_OBJECT_TYPE' VALUE RMAN_OBJECT_TYPE, | |
| 'RMAN_DEVICE_TYPE' VALUE RMAN_DEVICE_TYPE | |
| RETURNING CLOB) || | |
| ' }' AS json_output | |
| FROM unified_audit_trail; | |
| SPOOL OFF |
You can use a shell script to execute the SQL statement and schedule it with cron to run periodically. This approach allows you to easily share the results with a SIEM agent after a straightforward configuration.
| #!/bin/bash | |
| # Set Oracle environment variables | |
| source /home/oracle/.bashrc | |
| export ORAENV_ASK=NO | |
| export ORACLE_SID=bltdb1 | |
| . oraenv bltdb1 | |
| #export ORACLE_PDB_SID=testpdb | |
| # Execute SQL query and spool output to file | |
| $ORACLE_HOME/bin/sqlplus -s /nolog << EOF | |
| conn / as sysdba | |
| SET LONG 50000 | |
| SET LONGCHUNKSIZE 50000 | |
| SET LINESIZE 32767 | |
| SET PAGESIZE 0 | |
| SET FEEDBACK OFF | |
| SET TRIMSPOOL ON | |
| SET TAB OFF | |
| SET SERVEROUTPUT OFF | |
| SET VERIFY OFF | |
| SET HEADING OFF | |
| SET TERMOUT OFF | |
| SET ECHO OFF | |
| @audit_record_spooler.sql | |
| EOF |
Hope it helps.
Osman’s DBlog 
Leave your comment