This chapter covers specifics for auditing and logging the various components of the Oracle E-Business Suite application and database tier technology stack.
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 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:
Start and stop of the OHS instance
Any warnings about the OHS configuration observed during startup
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).
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
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/
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:
%t - timestamp in square brackets - including time zone
%h - the IP address of the requesting client
%{SSL_PROTOCOL}x - The protocol
%{SSL_CIPHER}x - the CipherSuite
%r - the requested URL path - including any querystring
%b - number of bytes in response body
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
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 headers. 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.
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:
A client connection request
A RELOAD, START, STOP, STATUS, or SERVICES command issued by the Listener Control utility
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.
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.
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.
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:
Critical errors (incidents)
Administrative operations, such as starting up or shutting down the database, recovering the database, creating or dropping a tablespace, and others.
Errors during automatic refresh of a materialized view
Other database events
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:
All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors (ORA-00060) that occur
Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements
Messages and errors relating to the functions of shared server and dispatcher processes
Errors occurring during the automatic refresh of a materialized view
The values of all initialization parameters that had nondefault values at the time the database and instance start
For more information, refer to "Monitoring the Database" in the Oracle Database Administrator's Guide.
Database auditing includes monitoring and recording of configured database actions. You can base auditing on individual actions, such as the type of SQL statement processed, or on combinations of data that can include the user name, application, time, and so on.
Unified Auditing is recommended for Oracle E-Business Suite Release 12.2.11 or for Oracle E-Business Suite Releases 12.2.3 through 12.2.10 with the application of the EBS System Schema Migration Consolidated Patch. See My Oracle Support Knowledge Document 2777404.1, Enabling Unified Auditing in Oracle E-Business Suite Release 12.2 with Oracle Database 19c or 12c, for more information.
Traditional auditing is recommended for Oracle E-Business Suite releases prior to 12.2.11 if the minimum required for Unified Auditing with Oracle E-Business Suite Release 12.2 have not been met. See My Oracle Support Knowledge Document 2793599.1, Traditional Database Auditing with Oracle E-Business Suite Release 12.2, for more information.
Any additional database auditing configured beyond what is recommended in the aforementioned My Oracle Support knowledge documents generate significant entries of little value. Oracle E-Business Suite dynamically creates, alters, and drops objects (tables, index, packages, and so forth) on a regular basis. Auditing additional actions provides little meaningful information and could affect the performance of your environment.
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.
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.
Consolidate database audit trail into secure centralized repository
Detect and alert on suspicious activities, including privileged users
Out-of-the box compliance reports for SOX, PCI, and other regulations
For example: privileged user audit, entitlements, failed logins, regulated data changes
Integrate with Oracle E-Business Suite security system
Monitor inbound SQL activity in passive mode
Alert security operations of unexpected activity
Run standard or develop custom reports
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:
Accessing a table between 9pm and 6am or on Saturday and Sunday
Using an IP address from outside the corporate network
Selecting or updating a table column
Modifying a value in a table column
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.