Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

Navigation
Guide That Contains This Content
[+] Expand All
[-] Collapse All

    Oracle Audit Records

    Oracle databases track audit events, such as, user login and logouts, permission changes, table creation, and deletion and database inserts.

    JSA can collect these events for correlation and reporting purposes by using the Oracle Audit DSM. For more information, see your Oracle documentation.

    Note: Oracle provides two modes of audit logs. JSA does not support fine grained auditing.

    Before You Begin

    Oracle RDBMS is supported on Linux only when syslog is used. Microsoft Windows hosts and Linux are supported when you use JDBC to view database audit tables. When you use a Microsoft Windows host, verify that database audit tables are enabled. These procedures are considered guidelines only. It is suggested that you have some experience with Oracle DBA before you complete the procedures in this document. For more information, see your vendor documentation.

    Before JSA can collect Oracle Audit events from an Oracle RDBMS instance, that instance must be configured to write audit records to either syslog or the database audit tables. For complete details and instructions for configuring auditing, see your vendor documentation.

    Note: Not all versions of Oracle can send audit events by using syslog. Oracle v9i and 10g Release 1 can send only audit events to the database. Oracle v10g Release 2 and Oracle v11g can write audit events to the database or to syslog. If you are using v10g Release 1 or v9i, you must use JDBC-based events. If you are using Oracle v10g Release 2, you can use syslog or JDBC-based events.

    To configure an Oracle Audit device to write audit logs to JSA, see Configuring Oracle audit logsYou can configure the device to write audit logs:. If your system includes a large Oracle audit table (greater than 1 GB), see Improving Performance with Large Audit Tables.

    Configuring Oracle Audit Logs

    You can configure the device to write audit logs:

    1. Log in to the Oracle host as an Oracle user (This user was used to install Oracle, for example, oracle).
    2. Make sure 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 command:

        *.audit_trail='os'

        *.audit_syslog_level='local0.info'

        You must make sure 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 (above) 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:

      For example,

      Enter user-name: sys as sysdba

    7. Shut down the database:

      shutdown immediate

    8. Restart the database:

      startup

    9. If you are using Oracle v9i or Oracle v10g Release 1, you must create a view, using 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 configuring the JDBC protocol within JSA, use the following specific parameters:

      Table 1: 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: Make sure that 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.

    Improving Performance with Large Audit Tables

    The size of the Oracle audit table affects the amount of time that JSA requires to process the DBA_AUDIT_TRAIL view.

    If your sys.sud$ table is large (close or exceeding 1 GB), extended processing time is required. To ensure JSA processes the large sys.sud$ table quickly, you must create an index and a new view.

    The maximum characters size for the SQL_BIND and SQL_TEXT fields is 2000.

    Note: If auditing is extensive or the database server is active, you might need to shut down the database to complete the following procedure.

    To create an index and a new view:

    1. Go to the following website to download the files:

      https://www.juniper.net/support/downloads/

    2. From the Software tab, select Scripts.
    3. Download the appropriate file for your version of Oracle:
      1. If you are using Oracle 9i or 10g Release 1, download the following file:

        oracle_9i_dba_audit_view.sql

      2. If you are using Oracle v10g Release 2 and v11g, download the following file:

        oracle_alt_dba_audit_view.sql

    4. Copy the downloaded file to a local directory.
    5. Change the directory to the location where you copied the file in Step 4.
    6. Log in to SQLplus and log in as sysdba:

      sqlplus / as sysdba

    7. At the SQL prompt, type one of the following commands, depending on your version of Oracle Audit:

      To create an index, the file might already be in use and must have exclusive access.

      1. If you are using Oracle 9i or 10g Release 1, type the following command:

        @oracle_9i_dba_audit_view.sql

      2. If you are using Oracle v10g Release 2 and v11g, type the following command:

        @oracle_alt_dba_audit_view.sql

    8. Make sure the database user who is configured in JSA has SELECT permissions on the view.

      For example, if the user is USER1:

      grant select on sys.alt_dba_audit_view to USER1;

    9. Log out of SQLplus.
    10. Log in to JSA.
    11. Update the JDBC protocol configuration for this entry to include the following entries:
      • Table Name Update the table name from DBA_AUDIT_TRAIL to sys.alt_dba_audit_view.

      • Compare Field Update the field from entended_timestamp to ntimestamp.

    12. Click Save.

    Modified: 2017-09-13