Oracle E-Business Suite Technology Stack Auditing and Logging Features

Introduction

This chapter covers specifics for auditing and logging the various components of the Oracle E-Business Suite application and database tier technology stack.

Application Tier Technology Stack

Fusion Middleware Oracle HTTP Server (OHS)

The HTTP server delivered with Oracle E-Business Suite is an Apache based server called Oracle HTTP Server (OHS). The logging directives available for OHS are similar to the standard directives delivered with Apache.

Oracle E-Business Suite Release 12.2 uses an Apache 2.2 based OHS from the Fusion Middleware (FMW) 11R1g bundle. FMW can be configured to use one of two specific logging modes. Oracle E-Business Suite uses the "ora-text" mode.

OHS by default writes three log files: the console log, the access log and the error log.

The logging directives for OHS are defined in the httpd.conf and security2.conf files.

All OHS log files are written to the [component]/instance directory.

OHS Console Log

OHS writes general logging information to a main log file called console~OHS~1.log. The content of the log file is not configurable. The information written here includes:

Note: In a standard Apache environment, this information would be written to the error log file.

Note: If the following messages from ModSecurity Version 2.7 are observed in the log file, they can be ignored:

ModSecurity: WARNING Using transformations in SecDefaultAction is deprecated \

 (/u01/install/APPS/fs1/FMW_Home/webtier/instances/EBS_web_EBSDB_OHS1/config/OHS/EBS_web_EBSDB/security2.conf:49).

 ModSecurity: WARNING Using transformations in SecDefaultAction is deprecated \

 (/u01/install/APPS/fs1/FMW_Home/webtier/instances/EBS_web_EBSDB_OHS1/config/OHS/EBS_web_EBSDB/security2.conf:71).

OHS Access Log

All requests processed by OHS are logged in the access_log file. The location and content of access_log are defined by the LogFormat and CustomLog directives in the httpd.conf file. Oracle E-Business Suite uses the standard "common" format as follows.

LogFormat "%h %l %u %t \"%r\" %>s %b" common
 
 CustomLog "|${ORACLE_HOME}/ohs/bin/odl_rotatelogs ${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/access_log 43200" common

The fields in the log file correspond to the following:

%h - the IP address of the requesting client
%l - "ident" of requesting user (never used, always '-')
%u - logged in user (always '-' unless Basic-Auth is used)
%t - timestamp in square brackets - including time zone
%r - the requested URL path - including any querystring
%>s - the HTTP status code
%b - number of bytes in the response body 

The following is an example log line:

172.17.122.44 - - [10/Aug/2015:17:53:52 -0400] "GET /page.jsp?p1=search HTTP/1.0" 200 1197

OHS Error Log

The error_log is handled by the Oracle specific "odl-text" mode by default. This logging mode is controlled by the following directives:

OraLogDir "${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}"
OraLogMode odl-text
OraLogSeverity WARNING:32
OraLogRotationParams S 10:70 

The default name for the "error_log" file is "$COMPONENT_NAME," such as EBS_web_<SID>.log, where SID is the database SID for the environment.

The format of error log is normally quite free form. OHS's error log however logs many fixed fields as seen in the following example:

[2015-08-03T14:37:29.4226-04:00] [OHS] [ERROR:32] [OHS-9999] [core.c] [host_id: apps.example.com] [host_addr: 172.17.122.44]
 [tid: 139782812313344] [user: oracle] ecid: 005AcAsWJ122RPKLQUt1id0000Wx0002GF] [rid: 0] [VirtualHost: main] File does not exist: /u01/install/APPS/fs1/inst/apps/EBSDB_apps/portal/favicon.ico, referer: http://apps.example.com:8000/

OHS HTTPS Log

When OHS is HTTPS enabled, requests received on the HTTPS port will also be logged to "ssl" specific log files. These log files and their format is defined in ssl.conf.

Note: If you have not already done so, you should enable TLS. Refer to My Oracle Support Knowledge Document 1367293.1, Enabling TLS in Oracle E-Business Suite Release 12.2.

The HTTPS-specific log entries are written to the ssl_request_log file and defined by the following directives in the ssl.conf file:

CustomLog "|${ORACLE_HOME}/ohs/bin/rotatelogs \
  ${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/ssl_request_log 86400" \
  "%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"

The ssl_request_log file will record the SSL protocol and CipherSuite used for the connection.

The fields in the log file correspond to the following:

The following is an example line from the log file:

[10/Aug/2015:17:53:52 -0400] 172.17.122.44 TLSv1.2 SSL_RSA_WITH_AES_256_GCM_SHA384 "GET / HTTP/1.0" 1197

Other OHS Logging

OHS also loads additional modules that may generate log output if explicitly enabled. They are mod_security and mod_rewrite.

Mod_security will write log information to the Apache error log file. In Oracle E-Business Suite Release 12.2, the log file is named EBS_web_<SID>.log, where <SID> is the SID of the database. By default, mod_security will add log entries to this log file whenever it denies a request.

The following is an example of mod_security logging to the error log:

[2015-08-03T14:37:29.4226-04:00] [OHS] [ERROR:32] [OHS-9999] [core.c] [host_id: apps.example.com] [host_addr: 172.17.122.44]

 [tid: 139782812313344] [user: oracle] [ecid: 00576lqFfjS1rYHqysfd6G0000V700028o] [rid: 0] [VirtualHost: main]  [client 10.211.207.102]

 ModSecurity: Access denied with code 400 (phase 2). Pattern match "['<>\\";\\\\\\\\]" at ARGS:locale.

 [file "/u01/install/APPS/fs1/FMW_Home/webtier/instances/EBS_web_EBSDB_OHS1/config/OHS/EBS_web_EBSDB/security2.conf"] [line "63"] [id "100020"]

 [hostname "apps.example.com"] [uri "/OA_HTML/help/state"] [unique_id "Vb@06awReiwAAAioEm0AAAAF"]

ModSecurity can also generate audit and debug logging information in separate log files defined in security2.conf.

ModSecurity audit logging are off by default. Audit logging can be turned by setting the following parameter in security2.conf, then bouncing the Apache server:

SecAuditEngine On

ModSecurity audit logging is written to the sec_audit_log file. Information in the audit_log is useful for diagnostic purposes as it will log the full request and the response body. All parameters, including the POST payload of the request, are available.

Caution: Be careful if enabling this audit logging feature. Many of the logged parameters may be considered sensitive. ModSecurity in Oracle E-Business Suite Release 12.2 blanks out a number of well-known password parameters before writing the log entry; however, there is other information that may be considered sensitive. You should consider the audit log as highly sensitive and restrict access.

ModSecurity debug logging is off by default and should not be enabled. If enabled, it can be used for debugging rules in security2.conf. Debug logging if enabled is written to the sec_debug_log file.

Similarly mod_rewrite has a debug log file called mod_rewrite.log. Mod_rewrite debug logging is disabled by default and should not be enabled. If enabled, it can used for debugging mod_rewrite actions from any of the OHS .conf files.

Oracle Database

Database Listener

The listener log file contains audit trail information that enables you to collect and analyze network usage statistics, as well as information indicating the following:

You can use the audit trail information to view trends and user activity by first storing it in a table and then collating it in a report format. To import the data into a table, use an import utility such as SQL*Loader.

Format of the Listener Log Audit Trail

Audit Trail formats text into the following fields:

Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code

For more information, refer to "Analyzing Listener Log Files" in the Oracle Database Net Services Administrator's Guide.

Enable TNS Listener Logging

To enable logging, in $TNS_ADMIN/listener.ora, set the following parameters:

LOG_STATUS = ON
LOG_DIRECTORY_$ORACLE_SID = $TNS_ADMIN
LOG_FILE_$ORACLE_SID = $ORACLE_SID

 For example,
LOG_STATUS = ON
LOG_DIRECTORY_VIS12 = /u/db/tech_st/10.2.0/network/admin/VIS12_dbs01
LOG_FILE_VIS12 = VIS12

Where VIS12 is the LISTENER_NAME.

Note: This configuration is done by default with Oracle E-Business Suite Release 12.2.

Database Alert Log

The alert log is an XML file that is a chronological log of messages and errors. For the database, the alert log includes messages about the following:

For more information, see "Managing Diagnostics Data" in the Oracle Database Administrator's Guide.

The alert log is a chronological log of messages and errors, and includes the following items:

For more information, refer to "Monitoring the Database" in the Oracle Database Administrator's Guide.

Database Auditing

Database auditing includes monitoring and recording of configured database actions. You can base auditing on individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on. This section includes recommendations for database auditing for an Oracle E-Business Suite database.

This section provide audit recommendations for an Oracle E-Business Suite database as follows:

Any additional audit options generate significant entries of little value. Oracle E-Business Suite dynamically creates, alters and drops objects (tables, index, packages, etc.) on a regular basis. Auditing additional actions provides little meaningful information.

Note: With Oracle Database 12c, Unified Auditing is introduced. Prior to Oracle Database 12c, auditing is configured using the AUDIT_TRAIL database parameter, this is called traditional auditing. Oracle E-Business Suite is currently only certified for "traditional auditing".

For more information about database auditing, see "Introduction to Auditing" in the Oracle Database Security Guide.

Configure the Database for Auditing

To configure traditional auditing, set AUDIT_TRAIL to DB, OS or TRUE in init.ora,. Consult with the Applications Database Administrator before setting this value to TRUE. When set to OS, the database stores its audit records on the file system. Set parameter AUDIT_FILE_DEST to the directory where the audit records should be stored. When not set, AUDIT_FILE_DEST defaults to $ORACLE_HOME/rdbms/audit.

Administrative user sessions generate SYS audit records. These records are written if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE. This process writes the records only to the traditional audit trails. However, when unified audit policies are enabled for administrative users, these unified audit records are also written to unified audit trail.

Recommended actions:

SQL> alter system set audit_file_dest=$ORACLE_HOME/rdbms/audit
SQL> alter system set audit_trail=os scope=spfile;
SQL> alter system set SYS_OPERATIONS=true

Then, schedule downtime to restart the database for the parameters to take effect.

Note: Audit data may contain confidential or privacy related data. Restrict audit trail access appropriately.

Database Sessions

Monitoring and auditing database sessions provides valuable information on database activity and is the only way to identify certain types of attacks (for example, password guessing attacks on an application schema). By auditing database sessions, suspicious connections to highly privileged schemas may be identified.

Schema Changes

Audit any changes to the standard Oracle E-Business Suite database schemas or creation of new schemas. As rare events, these changes may indicate inappropriate or malicious activity.

System Auditing

In an Oracle E-Business Suite database, it is recommended that the following SQL statement and privileges be audited:

The following statements will implement the auditing recommendations for database sessions, schema changes, and system auditing:

SQL> AUDIT SESSION
SQL> AUDIT USER;
 
SQL> AUDIT SYSTEM AUDIT; -- Audit statements themselves
SQL> AUDIT ALTER SYSTEM by ACCESS; -- Audit alter system statements
SQL> AUDIT SYSTEM GRANT by ACCESS; -- Audit System grant privileges
SQL> AUDIT SYSDBA by ACCESS; -- Audit SYSDBA privileges
SQL> AUDIT SYSOPER by ACCESS; -- Audit SYSOPER privileges
 
 
SQL> AUDIT DATABASE LINK; -- Audit create or drop database links
SQL> AUDIT PUBLIC DATABASE LINK; -- Audit create or drop public database links
SQL> AUDIT DIRECTORY;
 
SQL> AUDIT ALTER ANY ROLE by ACCESS; -- Audit alter any role statements
 
SQL> AUDIT CREATE ROLE by ACCESS; -- Audit create role statements
SQL> AUDIT DROP ANY ROLE by ACCESS; -- Audit drop any role statements
SQL> AUDIT ALTER ANY ROLE by ACCESS; -- Audit alter any role statements
 
SQL> AUDIT CREATE USER by ACCESS; -- Audit create user statements
SQL> AUDIT DROP USER by ACCESS; -- Audit drop user statements
SQL> AUDIT ALTER USER by ACCESS; -- Audit alter user statements
 
 
SQL> AUDIT PROFILE by ACCESS; -- Audit changes to profiles
SQL> AUDIT PUBLIC SYNONYM by ACCESS; -- Audit public synonyms statements

The previous commands may be found as an attachment in My Oracle Support Knowledge Document 2069190.1, Security Configuration and Auditing Scripts for Oracle E-Business Suite.

Object Level Auditing

You can audit actions performed on specific objects, such as INSERT, or UPDATE statements on specific tables. The audit can include both DDL and DML statements that were used on the object. The following sections provide recommendations for objects that should be audited for INSERT, UPDATE and DELETE actions.

Users

FND_USER
WF_USERS

Responsibilities, Roles, and Privileges

WF_USER_ROLES
WF_LOCAL_USER_ROLES
WF_USER_ROLE_ASSIGNMENTS
FND_MENUS
FND_MENU_ENTRIES
FND_RESP_FUNCTIONS
FND_GRANTS
FND_DATA_GROUPS
FND_DATA_GROUP_UNITS
FND_COMPILED_MENU_FUNCTIONS
FND_FORM
FND_FORM_FUNCTIONS
FND_RESPONSIBILITY
FND_OBJECT_INSTANCE_SETS

JTF Responsibilities, Roles, and Privileges

JTF_AUTH_PRINCIPALS_B
JTF_AUTH_PRINCIPAL_MAPS
JTF_AUTH_ROLE_PERMS
JTF_AUTH_PERMISSIONS_B

Security Configuration

PER_SECURITY_PROFILES
FND_PROFILE_OPTION_VALUES
FND_ORACLE_USERID
FND_NODES
FND_EXECUTABLES
FND_APPLICATION
FND_DOCUMENT_ENTITIES
FND_PRINTER_DRIVERS

Flexfield Configuration

FND_ID_FLEX_SEGMENTS
FND_ID_FLEXS
FND_ID_FLEX_SEGMENTS
FND_FLEX_VALIDATION_EVENTS
FND_FLEX_VALIDATION_QUALIFIERS
FND_FLEX_VALIDATION_RULES
FND_FLEX_VALIDATION_RULE_LINES
FND_FLEX_VALIDATION_RULE_STATS
FND_FLEX_VALIDATION_TABLES
FND_FLEX_VALUE_RULES
FND_FLEX_VALUE_RULE_LINES
FND_FLEX_VALUE_RULE_USAGES
FND_FLEX_VALUE_SETS
FND_DESCRIPTIVE_FLEXS 
FND_DESCR_FLEX_CONTEXTS 
FND_DESCR_FLEX_COLUMN_USAGES

Concurrent Request Configuration

FND_REQUEST_GROUPS
FND_REQUEST_GROUP_UNITS
FND_CONCURRENT_PROGRAMS

The commands to enable the recommended auditing for the above objects may be found as an attachment in My Oracle Support Knowledge Document 2069190.1, Security Configuration and Auditing Scripts for Oracle E-Business Suite.

Audit Trail Maintenance

Archive and purge the audit trail on a regular basis, at least every 90 days. The database connection entries take up significant space. Backup the audit file before purging.

Consider storing audit records in a separate system for analysis and reporting. Oracle's Audit Vault product can be used for this purpose.

Optional Oracle Technology Integrations

Oracle Audit Vault and Database Firewall

Oracle Audit Vault automates the consolidation of audit data into a secure repository, enabling efficient monitoring and reporting. Oracle Audit Vault is a powerful solution providing a secure repository, built-in reporting, event alerting, and separation-of-duty. Built on Oracle's industry leading technology, Oracle Audit Vault uses Oracle data security to protect audit data end-to-end. The latest release of Oracle Audit Vault provides enhanced out-of-the-box compliance reporting and audit collection, including support for Microsoft SQL Server 2000 & 2005, IBM DB2 Unix, Linux, and Windows 8.2 & 9.5, and Sybase ASE 12.5 & 15.0 databases.

Central to Oracle Audit Vault is a secure data repository built on Oracle's industry leading data warehousing technology and secured with Oracle's industry leading security products. Built-in reporting and event alerting help businesses improve their ability to comply with external regulations and internal policies by lessening the time and effort required to detect potential problems and demonstrate that mandated controls are in effect and working. Data security administrators and auditors can manage, compare and provision Oracle database auditing settings across the enterprise directly from the Oracle Audit Vault console, lowering overall maintenance costs.

Security and Scalability

Audit data is an important record of business activity. Audit data must be protected against modification to ensure the integrity of reports and investigations based on the audit data. Oracle Audit Vault stores audit data in a secure repository built using Oracle's industry leading database security technology. Timely transfer of audit data from source systems to Oracle Audit Vault is critical to close the window on intruders who may attempt to modify audit data and cover their tracks. Oracle Audit Vault can be configured to transfer audit data on a near real time basis. Oracle Audit Vault protects audit data during transfer over the network and within Oracle Audit Vault. During transfer from the source systems, audit data can be encrypted, preventing anyone from reading or tampering with the data during transmission.

Inside Oracle Audit Vault access to audit data is strictly controlled based on the principle of separation-of-duty. Oracle Real Application Clusters (RAC) can optionally be licensed for Oracle Audit Vault, enabling additional scalability and high availability.

Fine-Grained Auditing

Fine-grained auditing allows detailed conditions to trigger auditing. Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for (for ex: all access to a salary table with the salary is greater than X amount). Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify.

In general, fine-grained audit policies are based on simple, user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a row, the query is audited.

You can use fine-grained auditing to audit the following types of actions:

Fine-grained auditing creates a more meaningful audit trail, one that includes only very specific actions that you want to audit. It excludes unnecessary information that occurs if each table access was recorded. For more information, refer to "About Fine-Grained Auditing" in the Oracle Database Security Guide.