Collect Oracle DB logs

Supported in:

This document explains how to ingest Oracle DB logs to Google Security Operations using Bindplane. The parser extracts fields from SYSLOG messages, handling multiple formats using grok patterns and key-value parsing. It then maps these extracted fields to the Unified Data Model (UDM), enriching the data with static metadata like vendor and product names, and dynamically setting event types based on specific field values like ACTION and USERID. The parser also handles various data cleaning operations like replacing characters and converting data types.

Before you begin

Ensure that you have the following prerequisites:

  • Google SecOps instance
  • Windows 2016 or later, or a Linux host with systemd
  • If running behind a proxy, firewall ports are open
  • Privileged access (AUDIT_SYSTEM Role) to Oracle database

Get Google SecOps ingestion authentication file

  1. Sign in to the Google SecOps console.
  2. Go to SIEM Settings > Collection Agents.
  3. Download the Ingestion Authentication File. Save the file securely on the system where Bindplane will be installed.

Get Google SecOps customer ID

  1. Sign in to the Google SecOps console.
  2. Go to SIEM Settings > Profile.
  3. Copy and save the Customer ID from the Organization Details section.

Install the Bindplane agent

Windows installation

  1. Open the Command Prompt or PowerShell as an administrator.

  2. Run the following command:

    msiexec /i "https://github.com/observIQ/bindplane-agent/releases/latest/download/observiq-otel-collector.msi" /quiet

Linux installation

  1. Open a terminal with root or sudo privileges.

  2. Run the following command:

    sudo sh -c "$(curl -fsSlL https://github.com/observiq/bindplane-agent/releases/latest/download/install_unix.sh)" install_unix.sh

Additional installation resources

For additional installation options, consult the installation guide.

Configure the Bindplane agent to ingest Syslog and send to Google SecOps

  1. Access the configuration file:
    • Locate the config.yaml file. Typically, it's in the /etc/bindplane-agent/ directory on Linux or in the installation directory on Windows.
    • Open the file using a text editor (for example, nano, vi, or Notepad).

  2. Edit the config.yaml file as follows:

    receivers:
    udplog:
        # Replace the port and IP address as required
        listen_address: "0.0.0.0:514"

exporters:
    chronicle/chronicle_w_labels:
        compression: gzip
        # Adjust the path to the credentials file you downloaded in Step 1
        creds_file_path: '/path/to/ingestion-authentication-file.json'
        # Replace with your actual customer ID from Step 2
        customer_id: <customer_id>
        endpoint: malachiteingestion-pa.googleapis.com
        # Add optional ingestion labels for better organization
        ingestion_labels:
            log_type: 'ORACLE_DB'
            raw_log_field: body

service:
    pipelines:
        logs/source0__chronicle_w_labels-0:
            receivers:
                - udplog
            exporters:
                - chronicle/chronicle_w_labels

  3. Replace the port and IP address as required in your infrastructure.

  4. Replace <customer_id> with the actual customer ID.

  5. Update /path/to/ingestion-authentication-file.json to the path where the authentication file was saved in the Get Google SecOps ingestion authentication file section.

Restart the Bindplane agent to apply the changes

  • To restart the Bindplane agent in Linux, run the following command:

    sudo systemctl restart bindplane-agent

  • To restart the Bindplane agent in Windows, you can either use the Services console or enter the following command:

    net stop BindPlaneAgent && net start BindPlaneAgent

Enable Auditing for Oracle Database

  1. Connect to the Oracle Database with SQLplus.

  2. Shut down the database with the following command:

    shutdown immediate

  3. Stop the Oracle listener service by typing the following command:

    lsnrctl stop

  4. Optional: Only if applicable, stop the Enterprise Manager using the following commands:

    cd /u01/app/oracle/product/middleware/oms

export OMS_HOME=/u01/app/oracle/product/middleware/oms

$OMS_HOME/bin/emctl stop oms

  5. Link Oracle DB with the uniaud option using the following commands:

    cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_on ioracle

  6. Connect to the Oracle Database with SQLplus.

  7. Restart the database using the following command:

    startup

  8. Restart the Oracle listener service using the following command:

    lsnrctl start

  9. Optional: Only if applicable, restart the Enterprise Manager using the following commands:

    cd /u01/app/oracle/product/middleware/oms

export OMS_HOME=/u01/app/oracle/product/middleware/oms

$OMS_HOME/bin/emctl start oms

  10. Verify that unified auditing is enabled, connect to the Oracle database with SQLplus, and then type the following command:

    select * from v$option where PARAMETER = 'Unified Auditing';

  11. Verify that the command returns one row with VALUE equal to "TRUE".

Configure Syslog for the Oracle Database

  1. Sign in to the Oracle instance.

  2. Open the following file using vi:

    vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

  3. Enter the following commands for syslog configuration:

    *.audit_trail='os'
*.audit_syslog_level='local0.info'

  4. Ensure that the syslog daemon on the Oracle host is configured to forward the audit log.

  5. On Red Hat Enterprise, open the following file /etc/syslog.conf using vi and enter the following line:

    local0.info @ <bindplane-ip>:514

  6. Save and exit the file:

    :wq

  7. On Red Hat Enterprise, type the following command to reload the syslog configuration:

    kill -HUP /var/run/syslogd.pid

  8. Connect to SQLplus and login as sysdba to restart:

    sys as sysdba

  9. Shut down the database with the following command:

    shutdown immediate

  10. Restart the database using the following command:

    startup

UDM mapping table

Log Field UDM Mapping Logic
ACTION security_result.action_details The value of ACTION from the raw log is directly mapped to this UDM field. Additional logic is applied to determine security_result.action and security_result.description based on the value of ACTION (e.g., 100 maps to ALLOW and Success).
ACTION_NAME metadata.product_event_type Directly mapped.
ACTION_NUMBER additional.fields[action_number].value.string_value Directly mapped with the key Source Event. Also used in combination with other fields to derive metadata.event_type and metadata.product_event_type.
APPLICATION_CONTEXTS additional.fields[application_contexts_label].value.string_value Directly mapped with the key APPLICATION_CONTEXTS.
AUDIT_POLICY additional.fields[audit_policy_label].value.string_value or additional.fields[AUDIT_POLICY_#].value.string_value If AUDIT_POLICY contains a comma, it's split into multiple labels with keys like AUDIT_POLICY_0, AUDIT_POLICY_1, etc. Otherwise, it's mapped directly with the key AUDIT_POLICY.
AUDIT_TYPE additional.fields[audit_type_label].value.string_value Directly mapped with the key AUDIT_TYPE.
AUTHENTICATION_TYPE metadata.event_type, extensions.auth.type Used to derive metadata.event_type as USER_LOGIN if auth_type (extracted from AUTHENTICATION_TYPE) is not empty and other conditions are met. extensions.auth.type is set to AUTHTYPE_UNSPECIFIED.
CLIENT_ADDRESS principal.ip, principal.port, network.ip_protocol, intermediary[host].user.userid IP, port, and protocol are extracted using grok patterns. If a username is present in the CLIENT_ADDRESS field, it's mapped to intermediary[host].user.userid.
CLIENT_ID target.user.userid Directly mapped.
CLIENT_PROGRAM_NAME additional.fields[client_program_name_label].value.string_value Directly mapped with the key CLIENT_PROGRAM_NAME.
CLIENT_TERMINAL additional.fields[CLIENT_TERMINAL_label].value Directly mapped with the key CLIENT_TERMINAL.
CLIENT_USER target.user.user_display_name Directly mapped.
COMMENT$TEXT additional.fields[comment_text_label].value.string_value Directly mapped with the key comment_text after replacing '+' with ':'.
CURRENT_USER additional.fields[current_user_label].value.string_value Directly mapped with the key current_user.
CURUSER additional.fields[current_user_label].value.string_value Directly mapped with the key current_user.
DATABASE_USER principal.user.user_display_name Directly mapped if not empty or /.
DBID metadata.product_log_id Directly mapped after removing single quotes.
DBNAME target.resource.resource_type, target.resource.resource_subtype, target.resource.name Sets resource_type to DATABASE, resource_subtype to Oracle Database, and maps DBNAME to name.
DBPROXY_USERRNAME intermediary[dbproxy].user.userid Directly mapped.
DBUSERNAME target.user.user_display_name Directly mapped.
ENTRYID target.resource.attribute.labels[entry_id_label].value Directly mapped with the key Entry Id.
EXTERNAL_USERID additional.fields[external_userid_label].value.string_value Directly mapped with the key EXTERNAL_USERID.
LENGTH additional.fields[length_label].value.string_value Directly mapped with the key length.
LOGOFF$DEAD target.resource.attribute.labels[LOGOFFDEAD_label].value Directly mapped with the key LOGOFFDEAD.
LOGOFF$LREAD target.resource.attribute.labels[LOGOFFLREAD_label].value Directly mapped with the key LOGOFFLREAD.
LOGOFF$LWRITE target.resource.attribute.labels[LOGOFFLWRITE_label].value Directly mapped with the key LOGOFFLWRITE.
LOGOFF$PREAD target.resource.attribute.labels[LOGOFFPREAD_label].value Directly mapped with the key LOGOFFPREAD.
NTIMESTAMP# metadata.event_timestamp Parsed and converted to RFC 3339 or ISO8601 format.
OBJCREATOR target.resource.attribute.labels[obj_creator_label].value Directly mapped with the key OBJ Creator.
OBJNAME target.resource.attribute.labels[obj_name_label].value Directly mapped with the key OBJ Name.
OS_USERNAME principal.user.user_display_name Directly mapped.
OSUSERID target.user.userid Directly mapped.
PDB_GUID principal.resource.product_object_id Directly mapped.
PRIV$USED additional.fields[privused_label].value.string_value Directly mapped with the key privused.
PRIVILEGE principal.user.attribute.permissions.name Directly mapped.
RETURN_CODE security_result.summary Directly mapped. Logic is applied to derive security_result.action and security_result.description.
RETURNCODE security_result.summary Directly mapped. Logic is applied to derive security_result.action and security_result.description.
RLS_INFO additional.fields[rls_info_label].value.string_value Directly mapped with the key RLS_INFO.
SCHEMA additional.fields[schema_label].value.string_value Directly mapped with the key schema.
SESSIONCPU target.resource.attribute.labels[SESSIONCPU_label].value Directly mapped with the key SESSIONCPU.
SESSIONID network.session_id Directly mapped.
SESID network.session_id Directly mapped.
SQL_TEXT target.process.command_line Directly mapped.
SQLTEXT target.process.command_line Directly mapped.
STATEMENT target.resource.attribute.labels[statement_label].value Directly mapped with the key STATEMENT.
STATUS security_result.summary Directly mapped. Logic is applied to derive security_result.action and security_result.description.
SYSTEM_PRIVILEGE_USED additional.fields[system_privilege_used_label].value.string_value Directly mapped with the key SYSTEM_PRIVILEGE_USED.
TARGET_USER additional.fields[target_user_label].value.string_value Directly mapped with the key TARGET_USER.
TERMINAL additional.fields[CLIENT_TERMINAL_label].value Directly mapped with the key CLIENT_TERMINAL.
TYPE additional.fields[type_label].value.string_value Directly mapped with the key type.
USERHOST principal.hostname, principal.administrative_domain Hostname and domain are extracted using grok patterns.
USERID principal.user.userid Directly mapped.
device_host_name target.hostname Directly mapped.
event_name metadata.product_event_type Directly mapped after converting to uppercase.
file_name target.file.full_path Directly mapped.
hostname principal.hostname Directly mapped.
length additional.fields[length_label].value.string_value Directly mapped with the key length.
log_source_name principal.application Directly mapped.
message Various Used for grok parsing to extract several fields.
returncode RETURNCODE Directly mapped.
src_ip principal.ip Directly mapped.
t_hostname target.hostname Directly mapped.
(Parser Logic) metadata.vendor_name Hardcoded to Oracle.
(Parser Logic) metadata.product_name Hardcoded to Oracle DB.
(Parser Logic) metadata.event_type Determined based on the values of ACTION, ACTION_NUMBER, source_event, OSUSERID, USERID, SQLTEXT, AUTHENTICATION_TYPE, DBUSERNAME, device_host_name, database_name. Defaults to USER_RESOURCE_ACCESS if no specific condition is met.
(Parser Logic) metadata.product_event_type Determined based on the values of ACTION, ACTION_NUMBER, source_event, p_event_type, ACTION_NAME.
(Parser Logic) metadata.log_type Hardcoded to ORACLE_DB.
(Parser Logic) extensions.auth.mechanism Set to USERNAME_PASSWORD under certain conditions based on ACTION, ACTION_NUMBER, source_event, and OSUSERID.
(Parser Logic) extensions.auth.type Set to AUTHTYPE_UNSPECIFIED under certain conditions based on ACTION, ACTION_NUMBER, and AUTHENTICATION_TYPE.
(Parser Logic) security_result.description Derived from RETURNCODE or STATUS.
(Parser Logic) security_result.action Derived from RETURNCODE or STATUS.
(Parser Logic) target.resource.attribute.labels Several labels are added based on the presence and values of various log fields.
(Parser Logic) additional.fields Several fields are added as key-value pairs based on the presence and values of various log fields.
(Parser Logic) intermediary Created and populated based on the presence and values of DBPROXY_USERRNAME and CLIENT_ADDRESS.
(Parser Logic) network.ip_protocol Derived from protocol extracted from CLIENT_ADDRESS using an include file parse_ip_protocol.include.

Changes

2025-03-18

Enhancement:

  • Added Grok pattern to parse SYSLOG logs.
  • Mapped org_id and comp_id to additional.fields.
  • Mapped host_addr to principal.ip and principal.asset.ip.
  • Mapped host_id to principal.hostname and principal.asset.hostname.
  • Mapped level to security_result.severity_details.

2025-02-12

Enhancement:

  • Added date filter to support new pattern of syslog logs.

2025-01-15

Enhancement:

  • Mapped ID to metadata.event_timestamp.

2024-12-19

Enhancement:

  • Added support for new pattern of syslog logs.

2024-12-12

Enhancement:

  • Added gsub pattern to handle new format of KV logs.
  • Added a new Grok pattern to handle new format of KV logs.
  • Mapped ORACLE_DB to metadata.log_type.

2024-10-25

Enhancement:

  • If ACTION is GRANT, then set metadata.event_type to USER_RESOURCE_UPDATE_PERMISSIONS.
  • If ACTION is DROP or DELETE, then set metadata.event_type to USER_RESOURCE_DELETION.
  • If ACTION is CREATE, then set metadata.event_type to USER_RESOURCE_CREATION.
  • If ACTION is ALTER or INSERT, then set metadata.event_type to USER_RESOURCE_UPDATE_CONTENT.

2024-09-25

Enhancement:

  • Added support for new pattern of KV logs.

2024-07-24

Enhancement:

  • Mapped AUDIT_POLICY as a single string to additional.fields instead of splitting it into multiple values.

2024-06-06

Enhancement:

  • Minor change in principal.user.user_display_name.

2024-05-30

Enhancement:

  • Added support for exadata fields.

2024-04-03

Enhancement:

  • Added support for some attributes which were not covered.

2023-10-25

Enhancement:

  • Mapped LENGTH, LOGOFFDEAD, LOGOFFLREAD, LOGOFFLWRITE, LOGOFFPREAD, SESSIONCPU, CLIENT_TERMINAL to target.resource.attribute.labels.
  • Mapped ACTION to security_result.summary.
  • Set security_result.description to Success when RETURNCODE is 0 or STATUS is 0.
  • Set security_result.description to Failure when RETURNCODE is either 1 or -1, or STATUS is either 1 or -1.
  • Mapped principal.ip and principal.port from CLIENT_ADDRESS.

2022-10-13

Enhancement:

  • Added grok pattern to handle SYSLOG and KV logs.

2022-08-01

Enhancement:

  • Added mapping for following fields:
  • hostname mapped to principal.hostname.
  • if returncode is 0 then security_result.action mapped to ALLOW else if it is -1 then mapped to BLOCK.
  • ACTION mapped to metadata.product_event_type.
  • DATABASE USER mapped to principal.user.user_display_name.
  • PRIVILEGE mapped to principal.user.attribute.permissions.
  • CLIENT USER mapped to target.user.user_display_name.
  • file_name mapped to target.file.full_path.
  • event_name mapped to metadata.product_event_type.
  • ACTION_NUMBER mapped to event.idm.read_only_udm.additional.fields.
  • length mapped to event.idm.read_only_udm.additional.fields.
  • DBID mapped to metadata.product_log_id.

Need more help? Get answers from Community members and Google SecOps professionals.