This document provides the security configuration and auditing scripts for Oracle E-Business Suite.

The most current version of this document can be obtained in My Oracle Support Knowledge Document 2069190.1.

Section 1: Overview
Section 2: Oracle E-Business Suite Security Configuration Checks
Section 3: Oracle E-Business Suite Auditing Scripts

There is a change log at the end of this document.

Section 1: Overview

This document has two sets of scripts attached: EBSSecConfigChecks.zip and EBSAuditScripts.zip.

EBSSecConfigChecks.zip

EBSSecConfigChecks.zip implements a selection of checks for the advice in the Oracle E-Business Suite Secure Configuration Guide documentation found in:

• Oracle E-Business Suite Security Guide Release 12.2
• My Oracle Support Knowledge Document 403537.1, Secure Configuration for Oracle E-Business Suite Release 12
• My Oracle Support Knowledge Document 189367.1, Secure Configuration for Oracle E-Business Suite Release 11i

EBSAuditScripts.zip

EBSAuditScripts.zip is a set of scripts which can be used for configuring, auditing, checking the audit status, or querying audit records through SQL.

Section 2: Oracle E-Business Suite Security Configuration Checks

This section describes the zip archive EBSSecConfigChecks.zip.

EBSSecConfigChecks.sql is a driver that runs all other SQL scripts. The checks implemented in SQL are:

• Check Profile Errors - EBSCheckProfileErrors.sql
• Check Profile Warnings - EBSCheckProfileWarnings.sql
• Check Missing Profiles - EBSCheckProfileMissing.sql
• Check if new Security Features (in 12.2) are enabled - EBSCheckSecurityFeatures.sql
• Check Application Users With Default Passwords - EBSCheckUserPasswords.sql
• Check DB Users With Default Passwords - EBSCheckDBPasswords.sql
• Secure APPLSYSPUB - EBSCheckApplsyspubPrivs.sql
• Migrate to Password Hash - EBSCheckHashedPasswords.sql
• Use Secure Flag on DBC File (Implement Server Security) - EBSCheckServerSecurity.sql
• Enable Application Tier Secure Socket Layer (SSL) - EBSCheckSSL.sql
• Encrypt Credit Card Data - EBSCheckCCEncryption.sql
• Separation of Duties: Review Access To "Sensitive Administrative Pages" - EBSCheckSensitivePageAccess.sql
• Check status of 12.2 security features - EBSCheckSecurityFeatures.sql

The checks implemented as shell scripts are:

• Validate that Forms Block Characters is set correctly - EBSCheckFormsBlockChar.sh
• Turn on ModSecurity - EBSCheckModSecurity.sh

The shell scripts need to be run individually and require curl to be installed and available. Documentation for running these is available by executing them without any arguments.

Installing the SQL Scripts

The EBSSecConfigChecks.zip archive file unzips all the scripts to a new directory EBSSecConfigChecks.

You can install them on either the database server or on the app-tier, they just need SQL*Net connection to the database.

If you downloaded the zip to your home directory you can simply unzip it right there and the run from the new directory:

Running the SQL Scripts

All the scripts are designed to run as APPS against the database.

You can choose to initially have EBSSecConfigChecks.sql run all the SQL scripts to get an idea of what tasks remain. You can then fix any issues one by one and rerun just the script that pointed out the issue you are currently addressing.

The following is an example of one way to run the script:

The EBSSecConfigChecks.sql has an exit at the end. Therefore, after providing the APPS password, the script runs to the end and sqlplus exits.

Note that EBSSecConfigChecks.sql creates a spool file EBSSecConfigChecks.txt in the current directory.

Review the results in EBSSecConfigChecks.txt.

If you rerun the individual scripts, you may want to copy the column specs from EBSSecConfigChecks.sql and set them in sqlplus before running the scripts.

If you have a number of instances to check - and a trusted web server - you can avoid copying the scripts to each server.

You can run them from the web server as follows:

Section 3: Oracle E-Business Suite Auditing Scripts

This section describes the audit scripts included in the zip archive EBSAuditScripts.zip.

Documentation on the various auditing features that can be used in Oracle E-Business Suite and more information on these scripts can be found in the Oracle E-Business Suite Security Guide Release 12.2.

EBSAuditScripts.zip contains a variety of scripts which provide guidance for configuring Oracle E-Business Suite to follow our auditing guidance. It also contains example queries which show how to query various auditing records.

Scripts Contained in EBSAuditScripts.zip

Configure DB Auditing

• SystemPrivAuditing.sql - Configure System and Privilege auditing for the Database
• EBSObjectAuditing.sql - Configure Object level auditing per Oracle E-Business Suite guidance

Check the Auditing and Logging Settings

• EBSCheckAuditingSettings.sql - Check the Oracle E-Business Suite profiles and DB configuration settings against the recommended settings

Login and Session Queries

• SessLoginResponsibilites.sql - Session query showing current responsibilities and functions, joining in relevant Login rows
• LoginSessResponsibilites.sql - Login query showing current responsibilities and functions, joining in relevant Login rows (more verbose)
• v$sesssion_by_Fnd_User.sql - Query demonstrating population of Oracle E-Business Suite connection tagging context in v$session
• v$sesssion_last_sql_by_Fnd_User.sql - Query leveraging Oracle E-Business Suite connection tagging to pull the last SQL out of v$session by FND User

Page Access Tracking Queries

• PAT_sessions_by_date.sql - Query Summary of Page Access Tracking session by date
• PAT_sessions_by_user.sql - Query Summary of Page Access Tracking sessions by FND user
• PAT_session_flow.sql - Detail page flow for a given user's sessions for the last 30 days

Other Queries

• ProfileWhoColumnExample.sql - Example of WHO column joins against the profile values table
• UnsuccessfulLogins.sql - Query showing unsuccessful logins for local users in Oracle E-Business Suite

Installing the SQL Scripts

The EBSAuditScripts.zip archive file unzips all the scripts to a new directory EBSAuditScripts.

You can install them on either the database server or on the app-tier, they just need SQL*Net connection to the database.

If you downloaded the zip to your home directory, you can simply unzip it right there and the run from the new directory:

Running the SQL Scripts

All the scripts are designed to run as APPS against the database. Alternatively, you can run them against a read-only account that has access to the associated tables. If you do so, you may need to alter the current schema context: