Oracle EBS: Audit and Control Evaluation (My experience)

As an IT control officer or an IT auditor you will at one time have the challenging opportunity of reviewing and evaluating the controls of your organisation's Oracle E-Business suite. This could really be a daunting task mostly if you have NOT been trained on Oracle E-Business suite administration. It can a times, presents itself as a complex application.

This was my position at my workplace. I had to research and build a suitable control work plan/audit plan which of course  I will gladly share.

Please note that this is compilation based on research and actual experience. I will however appreciate positive and constructive criticism/feedback on this post.

Summarizing Oracle's E-Business Suite

Oracle's E-Business Suite (also referred to as Oracle EBS) is a compilation of business applications for managing and automating processes for an enterprise. The collection of applications are in the following categories:

·         Enterprise resource planning (ERP),

·         Human resource management system (HRMS)

·         Customer relationship management (CRM), and

·         Supply-chain management (SCM)

The beauty of Oracle's E-Business Suite is that it utilizes Oracle's core Relational Database Management System (RDBMS) where each application shares and processes data. This attribute gives Oracle EBS a major advantage over its competitors.

So if you are already familiar with Oracles RDBMS the ease of this back-end review will be greatly enhanced.

Identifying the risk

Before starting any audit or control review, the risk of the application or environment should first of all be assessed.

So I came up with these risk. (This is just my own pick. You can add more):

·         Exploitation of vulnerabilities inherent in an unpatched Oracle EBS application.

·         Ineffective user management leading to unauthorized privileged access.

·         Poor configuration management.

·         Poor migration process leading to exposure of sensitive production data.

·         Unprotected/ ill-monitored host operating system leading to compromise.

My work will be based on these risks.

This post is quite lengthy and thus will have 3 parts.
At the end of the third part, you will get the opportunity to download a start-up work program I  created  that will aid you with your review.

So lets get started

Things you will need:

·         Database access to Oracle EBS. Ensure your DBA grants you the following permissions:

ü  “SELECT_CATALOG_ROLE”:  This role grants you read-only access to every object in the database.

ü  “SELECT ANY TABLE”: This is a system privilege that allows you to view any table in the database.

·         SQLNAVIGATOR or any other sql runtime environment

Our review will center around three (3) environments (This is general for all application reviews):

·         The Oracle EBS Application

·         The Database

·         The Host Operating System

The screenshot below shows the basic architecture of an Oracle EBS and areas that need to be secured

1.    Get the current version of Oracle EBS that your organization is using

To achieve this use the following script:

select RELEASE_NAME from APPLSYS.fnd_product_groups;

2.    Obtain all the Oracle EBS products installed with their application patch level

This can be achieved by using the following script

select fa.APPLICATION_SHORT_NAME, atl.APPLICATION_NAME,DECODE(fpi.STATUS, 'I','Installed','S','Shared', 'N', 'Inactive', fpi.STATUS) Status , fpi.PATCH_LEVEL

from APPLSYS.fnd_product_installations fpi, APPLSYS.FND_APPLICATION fa, APPLSYS.FND_APPLICATION_TL atl

where fa.APPLICATION_ID=fpi.APPLICATION_ID

and fa.APPLICATION_ID=atl.APPLICATION_ID

Sample of the result can be seen below

APPLICATION_SHORT_NAME	APPLICATION_NAME	STATUS	PATCH_LEVEL
DPP	Oracle Price Protection	Installed	R12.DPP.B.3
MTH	Oracle Manufacturing Operations Center	Inactive	R12.MTH.B.3
AD	Applications DBA	shared	R12.AD.B.5

So what does all these jargons mean? I will explain:

APPLICATION_SHORT_NAME: This is the short code for the application name

APPLICATION_NAME: This is the full name of the application

STATUS: This is the installation status of which there are three of them stated below

·         Installed: Application is fully installed

·         Inactive: Application has not been installed

·         Shared: Application has not been fully implemented but only partially installed for dependent application.

PATCH_LEVEL: This is the current patch level of the application.

It can be interpreted as follows:

• The first 3 characters represents the Application/Product version.
• The next 3 characters represents the application or product short name.
• The second to the last characters represents the Codeline. This is an upgrade that contains new features.
• The last character is the number of bugfixes introduced for the codeline.

In this example “R12.DPP.B.3” can be interpreted as:

Version 12 for application DPP (Oracle Price Protection) with codeline B on the 3^rd bugfix.

Click here to get more details.

Work with your Oracle EBS admin to identify the latest patches as it is not an environment accessible by anyone on the oracle support site. Click here for additional resources.

This ends the first part. 

Click here to proceed to Part 2 of this blog post.