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

    Type a name for the log source. The name can't contain spaces and must be unique among all log sources of the log source type that is configured to use the JDBC protocol.

    If the log source collects events from a single appliance that has a static IP address or host name, use the IP address or host name of the appliance as all or part of the Log Source Identifier value; for example, 192.168.1.1 or JDBC192.168.1.1. If the log source doesn't collect events from a single appliance that has a static IP address or host name, you can use any unique name for the Log Source Identifier value; for example, JDBC1, JDBC2.

    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

    Enter the JDBC port. The JDBC port must match the listener port that is configured on the remote database. The database must permit incoming TCP connections. The valid range is 1 - 65535.

    The defaults are:

    • MSDE - 1433

    • Postgres - 5432

    • MySQL - 3306

    • Sybase - 1521

    • Oracle - 1521

    • Informix - 9088

    • DB2 - 50000

    If a database instance is used with the MSDE database type, you must leave the Port field blank.

    Username

    A user account to connect to the database. The user must have AUDIT_ADMIN or AUDIT_VIEWER permissions.

    Password

    The password that is required to connect to the database.

    Predefined Query

    Select a predefined database query for the log source. If a predefined query is not available for the log source type, administrators can select the none option.

    Table name

    The name of the table or view that includes the event records. The table name can include the following special characters: dollar sign ($), number sign (#), underscore (_), en dash (-), and period (.).

    Select List

    The list of fields to include when the table is polled for events. You can use a comma-separated list or type an asterisk (*) to select all fields from the table or view. If a comma-separated list is defined, the list must contain the field that is defined in the Compare Field.

    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.

    Use Oracle Encryption

    Oracle Encryption and Data Integrity settings is also known as Oracle Advanced Security.

    If selected, Oracle JDBC connections require the server to support similar Oracle Data Encryption settings as the client.

  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 Audit Logs to JSA

Configure your Oracle device to send 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.