Oracle RDBMS Audit Record
The JSA DSM for Oracle RDBMS Audit Record collects logs from an Oracle database.
The following table describes the specifications for the Oracle RDBMS Audit Record DSM:
Specification |
Value |
---|---|
Manufacturer |
Oracle |
DSM name |
Oracle RDBMS Audit Record |
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 |
To integrate Oracle RDBMS Audit Record with JSA, complete the following steps:
-
If automatic updates are not enabled, download and install the most recent version of the following RPMs from the Juniper Downloads onto your JSA console:
-
Protocol JDBC RPM
-
DSMCommon RPM
-
Oracle RDBMS Audit Record DSM RPM
-
-
Configure your Oracle RDBMS Audit Record device to write audit logs.
-
If JSA does not automatically detect the log source, add an Oracle RDBMS Audit Record log source on the JSA Console. The following tables describe the parameters that require specific values to collect audit events from Oracle RDBMS Audit Record:
Table 2: Oracle RDBMS Audit Record 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 RDBMS Audit Record 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.
-
-
Verify that JSA is configured correctly.
Note:Due to formatting issues, paste the message format into a text editor and then remove any carriage return or line feed characters.
The following table shows a sample normalized event message from Oracle RDBMS Audit Record:
Table 4: Oracle RDBMS Audit Record 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: "xxxxxx" 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: "xxxxxxxxxx"
AUDIT failed
Failed Configuration Modification
AUDIT_TYPE: "Standard" SESSIONID: "xxxxxxxxxx" PROXY_SESSIONID: "0" OS_USERNAME: "os_username" USERHOST: "userhost" TERMINAL: "terminal" INSTANCE_ID: "1" DBID: "xxxxxxxxxx" AUTHENTICATION_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: "000000000000000000000000000000000000000000000 000000000000000000000" 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" APPLICATION_CONTEXTS: "null" CLIENT_IDENTIFIER: "null" NEW_SCHEMA: "null" NEW_NAME: "null" OBJECT_EDITION: "null" SYSTEM_PRIVILEGE_USED: "null" SYSTEM_PRIVILEGE: "null" AUDIT_OPTION: "CREATE SESSION" OBJECT_PRIVILEGES: "null" ROLE: "null" TARGET_USER: "null" EXCLUDED_USER: "null" EXCLUDED_SCHEMA: "null" EXCLUDED_OBJECT: "null" ADDITIONAL_INFO: "null" UNIFIED_AUDIT_POLICIES: "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_PROGRAM_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_PARAMETERS1: "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.
Shut down the Oracle database by connecting to the database with SQLplus, and then type the following command:
shutdown immediate
Stop the Oracle listener service by typing the following command:
lsnrctl stop
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
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
Restart the Oracle database by connecting to the database with SQLplus, and then type the following command:
startup
Restart the Oracle listener service by typing the following command:
lsnrctl start
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
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.
Log in to the Oracle host as an Oracle user.
Ensure that the ORACLE_HOME and ORACLE_SID environment variables are configured properly for your deployment.
Open the following file:
${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Choose one of the following options:
For database audit trails, type the following command:
*.audit_trail='DB'
-
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 hostname 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
Save and exit the file.
To restart the database, connect to SQLplus and log in as sysdba:
Shut down the database by typing the following line:
shutdown immediate
Restart the database by typing the following line:
startup
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.
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.