Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 

Oracle DB Audit

 

The JSA DSM for Oracle DB Audit collects logs from an Oracle database.

The following table describes the specifications for the Oracle DB Audit DSM:

Table 1: Oracle DB Audit DSM Specifications

Specification

Value

Manufacturer

Oracle

DSM name

Oracle DB Audit

RPM file name

DSM-OracleDbAudit-JSA_version-build_number.noarch.rpm

Supported versions

9i, 10g, 11g, 12c (includes unified auditing)

Protocol

JDBC, Syslog

Event format

Name-Value Pair

Recorded event types

Audit records

Automatically discovered?

No

Includes identity?

Yes

Includes custom properties?

No

More information

Oracle website (htttps://www.oracle.com)

To integrate Oracle DB Audit with JSA, complete the following steps:

  1. If automatic updates are not enabled, download and install the most recent version of the following RPMs on your JSA console:

    • Protocol JDBC RPM

    • DSMCommon RPM

    • Oracle DB Audit DSM RPM

  2. Configure your Oracle DB Audit device to write audit logs.

  3. Add an Oracle DB Audit log source on the JSA Console. The following tables describe the parameters that require specific values to collect audit events from Oracle DB Audit:

    Table 2: Oracle DB Audit Syslog Log Source Parameters

    Parameter

    Value

    Log Source type

    Oracle RDBMS Audit Record

    Protocol Configuration

    Syslog

    Log Source Identifier

    Type a unique identifier for the log source.

    Table 3: Oracle DB Audit JDBC Log Source Parameters

    Parameter

    Value

    Log Source type

    Oracle RDBMS Audit Record

    Protocol Configuration

    JDBC

    Log Source Identifier

    Enter the value in <DATABASE>@<HOSTNAME> format.

    The <DATABASE> value must match the database name that is specified in the Database name field. The <HOSTNAME> value must match the IP or host name that is specified in the IP or Hostname field.

    Database Type

    Oracle

    Database Name

    The name of the database from where you collect audit logs.

    IP or Hostname

    The IP or host name of the Oracle database.

    Port

    The JDBC port. The JDBC port must match the listen port that is configured on the remote database.

    Username

    The user name of the user to connect to the database. The user must have AUDIT_ADMIN or AUDIT_VIEWER permissions.

    Password

    The password of the user to connect to the database.

    Table name

    The name of the table that contains the audit records that you want to collect.

    Compare Field

    For Oracle 9i or Oracle 10g Release 1, type JSA_time.

    For Oracle 10g Release 2, Oracle 11g, or Oracle 12c (non-unified auditing), type extended_timestamp.

    For Oracle 12c (unified auditing), type event_timestamp.

  4. Verify that JSA is configured correctly.

    The following table shows a sample normalized event message from Oracle Db Audit:

    Table 4: Oracle Db Audit Sample Message

    Event name

    Low level category

    Sample log message

    SELECT succeeded

    System Action Allow

    OS_USERNAME: "os_username" USERNAME: "username" USERHOST: "userhost" TERMINAL: "terminal" TIMESTAMP: "2017-04-05 21:04:02.0" OWNER: "owner" OBJ_NAME: "PARTIAL_ ALERT" ACTION: "3" ACTION_NAME: "SELECT" NEW_OWNER: "null" NEW_NAME: "null" OBJ_PRIVILEGE: "null" SYS_PRIVILEGE: "null" ADMIN_OPTION: "null" GRANTEE: "null" AUDIT_OPTION: "null" SES_ACTIONS: "null" LOGOFF_ TIME: "null" LOGOFF_LREAD: "null" LOGOFF_PREAD: "null" LOGOFF_ LWRITE: "null" LOGOFF_DLOCK: "null" COMMENT_TEXT: "null" SESSIONID: "260939" ENTRYID: "2" STATEMENTID: "2" RETURNCODE: "0" PRIV_USED: "null" CLIENT_ID: "null" ECONTEXT_ID: "null" SESSION_ CPU: "null" EXTENDED_TIMESTAMP: "2017-04-05 21:04:02.318133 America/Halifax" PROXY_SESSIONID: "null" GLOBAL_UID: "null" INSTANCE_ NUMBER: "0" OS_PROCESS: "9276" TRANSACTIONID: "null" SCN: "3842851" SQL_BIND: "null" SQL_ TEXT: "null" OBJ_EDITION_NAME: "null" DBID: "1432042433"

    AUDIT failed

    Failed Configuration Modification

    AUDIT_TYPE: "Standard" SESSIONID: "2309049191" PROXY_SESSIONID: "0" OS_USERNAME: "os_username" USERHOST: "userhost" TERMINAL: "terminal" INSTANCE _ID: "1" DBID: "1432042433" AUTHENTI CATION_TYPE: "(TYPE=(DATABASE)); " DBUSERNAME: "dbusername" DBPROXY_ USERNAME: "null" EXTERNAL_USERID: "null " GLOBAL_USERID: "null" CLIENT_PROGRAM_ NAME: "client_program_name" DBLINK_ INFO: "null" XS_USER_NAME: "null" XS_SESSIONID: "000000000000000000000000 000000000000000000000000000000000000000 000" ENTRY_ID: "3" STATEMENT_ID: "11" EVENT_TIMESTAMP: "2017-04-05 20:44:21. 29604" ACTION_NAME: "AUDIT" RETURN_CODE: "1031" OS_PROCESS: "1749" TRANSACTION _ID: "0000000000000000" SCN: "3841187 " EXECUTION_ID: "null" OBJECT_SCHEMA : "null" OBJECT_NAME: "null" SQL_TEXT : "audit all" SQL_BINDS: "null" APPLIC ATION_CONTEXTS: "null" CLIENT_IDENTIF IER: "null" NEW_SCHEMA: "null" NEW_ NAME: "null" OBJECT_EDITION: "null" SYSTEM_PRIVILEGE_USED: "null" SYSTEM_ PRIVILEGE: "null" AUDIT_OPTION: "CREAT E SESSION" OBJECT_PRIVILEGES: "null" ROLE: "null" TARGET_USER: "null" EXCLUDED_USER: "null" EXCLUDED_SCHEMA: "null" EXCLUDED_OBJECT: "null" ADDITI ONAL_INFO: "null" UNIFIED_AUDIT_POLIC IES: "null" FGA_POLICY_NAME: "null" XS_INACTIVITY_TIMEOUT: "0" XS_ENTITY_ TYPE: "null" XS_TARGET_PRINCIPAL_NAME: "null" XS_PROXY_USER_NAME: "null" XS_ DATASEC_POLICY_NAME: "null" XS_SCHEMA_ NAME: "null" XS_CALLBACK_EVENT_TYPE: "null" XS_PACKAGE_NAME: "null" XS_ PROCEDURE_NAME: "null" XS_ENABLED_ ROLE: "null" XS_COOKIE: "null" XS_NS _NAME: "null" XS_NS_ATTRIBUTE: "null" XS_NS_ATTRIBUTE_OLD_VAL: "null" XS_ NS_ATTRIBUTE_NEW_VAL: "null" DV_ACTION _CODE: "0" DV_ACTION_NAME: "null" DV _EXTENDED_ACTION_CODE: "0" DV_GRANTEE: "null" DV_RETURN_CODE: "0" DV_ACTION _OBJECT_NAME: "null" DV_RULE_SET_NAME: "null" DV_COMMENT: "null" DV_FACTOR_ CONTEXT: "null" DV_OBJECT_STATUS: "null" OLS_POLICY_NAME: "null" OLS_ GRANTEE: "null" OLS_MAX_READ_LABEL: "null" OLS_MAX_WRITE_LABEL: "null" OLS_MIN_WRITE_LABEL: "null" OLS_ PRIVILEGES_GRANTED: "null" OLS_PROG RAM_UNIT_NAME: "null" OLS_PRIVILEGES _USED: "null" OLS_STRING_LABEL: "null" OLS_LABEL_COMPONENT_TYPE: "null" OLS_LABEL_COMPONENT_NAME: "null" OLS_PARENT_GROUP_NAME: "null" OLS_OLD_VALUE: "null" OLS_NEW_VALUE: "null" RMAN_SESSION_RECID: "0" RMAN _SESSION_STAMP: "0" RMAN_OPERATION: "null" RMAN_OBJECT_TYPE: "null" RMAN _DEVICE_TYPE: "null" DP_TEXT_PARAMETER S1: "null" DP_BOOLEAN_PARAMETERS1: "null " DIRECT_PATH_NUM_COLUMNS_LOADED: "0"

Enabling Unified Auditing in Oracle 12c

To enable Unified Auditing in Oracle 12c, you must shut down the Oracle database, stop the Oracle listener service and then restart the Oracle database and Oracle Listener service.

You must have the AUDIT_SYSTEM system privilege or the AUDIT_ADMIN role to complete the following steps.

  1. Shut down the Oracle database by connecting to the database with SQLplus, and then type the following command:

    shutdown immediate

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

    lsnrctl stop

  3. If applicable, stop the Enterprise Manager by typing 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

  4. Relink Oracle DB with the uniaud option by typing the following commands:

    cd $ORACLE_HOME/rdbms/lib

    make -f ins_rdbms.mk uniaud_on ioracle

  5. Restart the Oracle database by connecting to the database with SQLplus, and then type the following command:

    startup

  6. Restart the Oracle listener service by typing the following command:

    lsnrctl start

  7. If applicable, restart the Enterprise Manager by typing 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

  8. To 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';

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

Configuring an Oracle Database Server to Send Syslog Audit Logs to JSA

Configure your Oracle device to send syslog audit logs to JSA.

  1. Log in to the Oracle host as an Oracle user.
  2. Ensure that the ORACLE_HOME and ORACLE_SID environment variables are configured properly for your deployment.
  3. Open the following file:

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

  4. Choose one of the following options:
    1. For database audit trails, type the following command:

      *.audit_trail='DB'

    2. For syslog, type the following commands:

      *.audit_trail='os'

      *.audit_syslog_level='local0.info'

      You must ensure that the syslog daemon on the Oracle host is configured to forward the audit log to JSA. For systems that run Red Hat Enterprise, the following line in the /etc/syslog.conf file affects the forwarding:

      local0.info @ qradar.domain.tld

      Where qradar.domain.tld is the host name of JSA that receives the events. The syslog configuration must be reloaded for the command to be recognized. On a system that runs Red Hat Enterprise, type the following line to reload the syslog configuration:

      kill -HUP /var/run/syslogd.pid

  5. Save and exit the file.
  6. To restart the database, connect to SQLplus and log in as sysdba:
  7. Shut down the database by typing the following line:

    shutdown immediate

  8. Restart the database by typing the following line:

    startup

  9. If you are using Oracle v9i or Oracle v10g Release 1, you must create a view that uses SQLplus to enable the JSA integration. If you are using Oracle 10g Release 2 or later, you can skip this step:

    CREATE VIEW qradar_audit_view AS SELECT CAST(dba_audit_trail.timestamp AS TIMESTAMP) AS qradar_time, dba_audit_trail.* FROM dba_audit_trail;

    If you are using the JDBC protocol, when you configure the JDBC protocol within JSA, use the following specific parameters:

    Table 5: Configuring Log Source Parameters

    Parameter Name

    Oracle v9i or 10g Release 1 Values

    Oracle v10g Release 2 and v11g Values

    Table Name

    JSA_audit_view

    dba_audit_trail

    Select List

    *

    *

    Compare Field

    JSA_time

    extended_timestamp

    Database Name

    For all supported versions of Oracle, the Database Name must be the exact service name that is used by the Oracle listener. You can view the available service names by running the following command on the Oracle host:

    lsnrctl status

    Note

    Ensure that the database user that JSA uses to query events from the audit log table has the appropriate permissions for the Table Name object.

  10. You can now configure JSA to receive events from an Oracle database: From the Log Source Type list, select the Oracle RDBMS Audit Record option.