Security Configuration and Auditing Scripts for Oracle E-Business Suite

Security Configuration and Auditing Scripts for Oracle E-Business Suite (Doc ID 2069190.1)

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

Document requires 2 scripts 

• EBSSecConfigChecks.zip 
• EBSAuditScripts.zip

EBSAuditScripts.zip

EBSAuditScripts.zip 

s 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:

$ unzip EBSSecConfigChecks.zip
$ cd EBSSecConfigChecks/

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:

$ sqlplus APPS @EBSSecConfigChecks.sqlSQL*Plus: Release …Copyright (c)…Enter password:Connected to:Oracle Database…With the Partitioning, OLAP, Data Mining and Real Application Testing options**************************************************** Check: Security Profiles: Configuration ERRORS***************************************************

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.

Oracle E-Business Suite Auditing Scripts

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

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

Resource Usage Evaluation

• webusage.awk - This is an awk script which generates a summary of web resources used from available Apache access logs. The summary can be leveraged using the WLDataMigration utility to identify custom resources as well as populate web usage data for the Allowed Resources feature initially available in Oracle E-Business Suite Release 12.2.7.
  
  This awk script is to be run from the command line and designed for the default Apache log format in 12.2. It will need to be modified if that log format has been changed.
  
  See the "Evaluating Usage in Access Logs" section in the 12.2.7 version of the Oracle E-Business Suite Security Guide (Part No. 22952-18) for information on when and how to use webusage.awk.

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:

$ unzip EBSAuditScripts.zip
$ cd EBSAuditScripts/

Running the SQL Scripts

All the SQL 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:

alter session set current_schema=APPS