Using Wazuh for Oracle Audit Monitoring

Oracle Fine-Grained Auditing (FGA) lets you audit access to specific columns and rows of a table based on their content. On its own, the audit trail stays inside the database. Feeding it into Wazuh puts Oracle audit events alongside the rest of your security monitoring, where they can be searched, reported on and alerted on.

This post covers the setup: the extract scripts on the database server, the Wazuh agent configuration, and the rule and decoder on the Wazuh server.

Why Integrate Oracle FGA with Wazuh?

• Centralised monitoring: Oracle audit records sit alongside the logs from everything else Wazuh monitors, so there’s one place to search and report.
• Near real-time detection: with the extract running every minute, suspicious database activity shows up in Wazuh within a minute or two and can trigger alerts.
• Compliance and investigations: access to sensitive data is logged and held outside the database, which helps with compliance reporting and gives you a trail to work from after an incident.

How to Integrate Oracle FGA with Wazuh

This assumes your Oracle FGA audit policies are already configured (we cover that here). The steps below set up the extract on the database server and the configuration in Wazuh.

Step 1: Create Oracle Audit Extract Scripts

We create two scripts to extract the audit trail data:

1. SQL script to extract the audit trail rows.
2. Bash script to be called from the Wazuh agent.

Both of these scripts need to be created on your target Oracle database server host.

SQL Script

The script below uses a timestamp value from the last run to filter the audit records that have been created since the last execution of the extractor:

REM Script name should be /oracle/admin/scripts/audit/db_audit_fga_activities.sql to be called from the bash script below.

set heading off
set feed off
set trimsp on
set linesize 3000
col sql_text for a2000
alter session set container=PDB_NAME;
var run_date varchar2(64)
exec :run_date := '&1';
select 'orafga: session_id='||session_id||' entryid='||entryid||' client_id='||nvl(client_id, 'null')||' sql_text='||sql_text
from dba_fga_audit_trail
where timestamp  > TO_TIMESTAMP_TZ(:run_date,'DD-MON-RR HH.MI.SS.FF6 AM TZH:TZM')
order by extended_timestamp asc;
spool /var/tmp/db_fga_audit_timestamp.tmp
set term off
select systimestamp from dual;
exit

Bash Script

The bash script is called from the Wazuh agent at the required interval. The bash script is executed by the root user and switches to the oracle unix user to run the required SQL:

#!/bin/bash

sudo -i -u oracle sqlplus -s / as sysdba @/oracle/admin/scripts/audit/db_audit_fga_activities.sql "`cat /var/tmp/db_fga_audit_timestamp.tmp`"

Step 2: Configure Wazuh Agent Command Module

This step is to configure the Wazuh agent on the Oracle database server to call the Oracle Audit extractor scripts at the required interval:

1. Open the Wazuh agent config file:

vi /var/ossec/etc/ossec.conf

2. Add the following code block:   

<wodle name="command">
    <disabled>no</disabled>
    <tag>orafga</tag>
    <command>/oracle/DBNAME/admin/scripts/audit/run_fga_audit_extract.sh</command>
    <interval>1m</interval>
    <run_on_start>yes</run_on_start>
    <timeout>0</timeout>
</wodle>

Note: I had to add this within the <ossec_config> tags to get it to work. This is an extract from my config file:

  <!-- System inventory -->
  <wodle name="syscollector">
    <disabled>no</disabled>
    <interval>1h</interval>
    <scan_on_start>yes</scan_on_start>
    <hardware>yes</hardware>
    <os>yes</os>
    <network>yes</network>
    <packages>yes</packages>
    <ports all="no">yes</ports>
    <processes>yes</processes>

    <!-- Database synchronization settings -->
    <synchronization>
      <max_eps>10</max_eps>
    </synchronization>
  </wodle>

  <wodle name="command">
    <disabled>no</disabled>
    <tag>orafga</tag>
    <command>/oracle/admin/scripts/audit/run_fga_audit_extract.sh</command>
    <interval>1m</interval>
    <run_on_start>yes</run_on_start>
    <timeout>0</timeout>
  </wodle>

  <sca>
    <enabled>yes</enabled>
    <scan_on_start>yes</scan_on_start>
    <interval>12h</interval>
    <skip_nfs>yes</skip_nfs>
  </sca>

3. Restart the Wazuh agent:

systemctl restart wazuh-agent.service

For the config parameters we set the following:

<tag>orafga</tag> (this will be matched on the Wazuh server for the decoder configuration).

<command>/oracle/admin/scripts/audit/run_fga_audit_extract.sh</command> (call a bash script – keeps the config simple and clean).

<interval>1m</interval> (run the audit extractor at 1 minute intervals. Consider your requirements around how urgent you need the audit data v SQL workload to extract the audit information).

<timeout>0</timeout> 0 (wait indefinitely for the extract SQL to complete)

Step 3: Configure Wazuh Server Rule

We assign the Oracle FGA audit records to the audit and audit_command groups. This will allow the Oracle audit trail records to be visible under System Auditing in the Wazuh dashboard.

Perform the following steps on the Wazuh server.

1. Add the local rule:

vi /var/ossec/etc/rules/local_rules.xml

2. Add the following config:

<group name="audit,audit_command,">
  <rule id="100010" level="6">
    <decoded_as>orafga</decoded_as>
    <description>Oracle FGA Audit Event</description>
  </rule>
</group>

For the config parameters we set the following:

<group name=”audit,audit_command,”> (assigns the extracted Oracle audit trail records to the audit and audit_command groups. Makes it easy to view and search these records in the Wazuh dashboard from the System Auditing menu).

<rule id=”100010″ level=”6″> (assigns the unique rule id and level – set as required for your environment)

<decoded_as>orafga</decoded_as> (sets the decoder name – this will be configured in the next step)

<description>Oracle FGA Audit Event</description> (set as required for your environment)

Step 4: Configure Wazuh Server Decoder

The Wazuh decoder parses and extracts the incoming Oracle audit records and extracts and assigns each field in the audit record to a Wazuh field. We do it this way to make it easy to search and report on different audit records.

Note: I have only included a subset of fields from the dba_fga_audit_trail view to keep this example simple.

1. Open the custom decoder config file:

vi /var/ossec/etc/decoders/local_decoder.xml

2. Add the following config:

<decoder name="orafga">
  <prematch>^orafga: </prematch>
  <regex type="pcre2" offset="after_prematch">.*session_id=(\w+) .*entryid=(\w+) .*client_id=(\w+) .*sql_text=(.*)</regex>
  <order>session_id,entry_id,client_id,sql_text</order>
</decoder>

3. Restart the Wazuh server manager:

systemctl restart wazuh-manager

For the config parameters we set the following:

<decoder name=”orafga”> (matches the decoded_as parameter for the local rule created previously)

<prematch>^orafga: </prematch> (this matches the audit records coming from the SQL extract with the “orafga:” prefix)

<regex type=”pcre2″ offset=”after_prematch”>.*session_id=(\w+) .*entryid=(\w+) .*client_id=(\w+) .*sql_text=(.*)</regex> (extract each of the fields from the Oracle audit row)

<order>session_id,entry_id,client_id,sql_text</order> (create the matching Wazuh fields for each audit record)

Step 5: View the Oracle Audit Trail in Wazuh

The following steps demonstrate filtering and viewing the Oracle audit trail records:

A: From the Wazuh dashboard select the System Auditing menu.

B: From the Agent filter select the Oracle database server host. This will filter any activities on the following screens to just that host.

C: Select the ‘Events’ tab from the Audit dashboard screen. This should show all the audit related events.

D: Click on the ‘+Add filter’ button to filter just the Oracle audit records. Note the field is “location” and the value is “command_orafga”.

E: The events tab will show the Oracle audit records that have been loaded into Wazuh.

F: Expand and view the Oracle audit records. Note that the data.* fields contain the fields extracted from the Oracle audit record. You can filter, report, alert etc on the individual fields.

Summary

With the extract scripts, the agent command, and the rule and decoder in place, Oracle FGA records land in Wazuh as searchable fields within a minute of being written. From there you can filter, report and alert on them alongside everything else Wazuh monitors. The example only extracts a subset of the audit trail fields, so add the ones your reporting needs.