I remember attending my first Oracle Applications Users Group (OAUG) conference in 1999. I believe this was the year that Larry Ellison, CEO of Oracle Corporation, declared that the internet would change everything. The use of the internet in everyday business life was in full swing at that time and the dot.com boom was roaring with start ups in 90% of all garages in the bay area (slight exaggeration...), some of which had turned into enormously successful corporations. Indeed the internet has changed life throughout the world. Most of us arc just as lost without internet access as wc arc without our cell phones.
The advent and evolution of ERP (Enterprise Resource Planning) systems have had and are still having almost as significant an impact on organizations as the internet has had on the individual. ERP systems such as SAP and Oracle’s suite of products (E-Business Suite (EBS), PeopleSoft, JD Edwards, Siebel, Hyperion, Fusion, etc) continue to cause a dramatic change in business and IT processes.
Several unique characteristics of ERP systems impact the core foundation of Application Security as listed below:
- The technical architecture
- The lack of an ‘audit all’ function which causes EBS technical
- The application support model
- The focus on change management processes needed to support the application
ERP systems architecture
ERP systems such as Oracle E-Business suite have a three-tier architecture. The three distinct layers arc:
- Presentation Layer
- Application Layer
- Database Layer
Presentation layer
The presentation layer is the layer end users see. It consists of forms or html-based screens where data is ‘presented’ to an end user. The presentation layer ‘resides’ on the desktop of an end user and users can isert/update/delete data or view data in an inquiry mode.
The concept of a presentation layer being separate from a database layer was new to me when I first worked with the applications. My first experience with Oracle Applications as a client was on an 11.0.2 platform. Version 11.0.2 had some technical issues that caused the application to crash. When the application crashed, the system would lose any unsaved data. If 1 had entered data, but not yet saved it to the database layer, my data entered in the presentation layer on my desktop could not be requeried because it wasn’t saved and didn’t reside in the database. This loss of data was my first introduction to the distinct layers of an ERP system - and the fact that what a user sees on his/her desktop is a separate and distinct layer from the database.
Application layer
The second tier in Oracle’s architecture is the Application Layer. This layer is the brains of the system and contains the ‘rules and regulations’ of the application. Some would call this layer the ‘code’ of the system and it dictates what procedures are called and when. The application layer contains a LOT of dormant code. The code in an ERP system becomes active as certain modules are implemented and as the system is configured to use the code. For example, the ability to track encumbrances is available for any organization to use. However, generally speaking, for-profit companies never ‘use’ this code because they never enable encumbrance accounting. The way the system is configured determines which code is ‘utilized’ by an organization. This is a key point that we’ll discuss in much more detail later, including its impact on an organization’s change management process.
Database layer
How can a reformed "bean counter" give an adequate description of the database layer in a paragraph when many books have been written about the complexities of the Oracle database? While there arc a lot of internal controls impacting the database layer, the scope of this book is to understand the impact on security within the application layer. The database layer is where the saved data resides and has its own layer of security. That is, you can log into the database and manipulate the data without being granted an application login. As we will examine in more detail in subsequent books in this scries, you should separately consider the database layer and its security in assessing risk related to fraud and access to sensitive data.
The database layer is not just a static entity that stores data. There arc some database technologies, such as stored procedures and triggers, which perform actions. Let’s consider an example based on a trigger. Let’s say that a DBA with a database login wants to commit fraud. He knows that a certain supplier is paid via ACH (electronic payment from the organization through their bank). A trigger could be created (or he could update an existing trigger) that changes a legitimate bank account to a fictitious bank account when an invoice over $100,000 is entered for that supplier. The event on which the trigger would ‘fire’ is the saving of an invoice after it is entered through the application layer. The net result of this transaction is the DBA would be able to commit fraud without ever having logged into the application. His fraud would have been committed purely by having a database login.
The database layer is its own distinct entity. The security of the database needs to be evaluated independent of the application’s security. Ко risk assessment process or audit would be complete without evaluating both layers.
Another important point to note is that the database only stores data that the application is ‘built’ to store. As we will see later in the book, most of the application is built to store only ‘current’ information, not detailed changes. With few exceptions, the history of a transaction or the system’s setups is not stored in the database because the application layer isn’t built to store such information.
An example of how the three layers work together
Let’s look at a simplistic example of how the three layers work together. In our example, we will walk through how the system works when a user wants to enter a journal entry. Let’s first assume you, the user, has logged into the application and are looking at the Navigator form in Figure 1.
Figure 1 User logged into the application
At this point, the application layer has already performed its first function, Ё™ which is to display the Navigator form to you, the user, in the presentation layer. Next, when you click on the Enter journals menu (highlighted in Figure 1), the application layer responds by opening the Enter journals form (see Figure 2).
Figure 2 The application layer responds by opening the Enter journals form
When you start entering data, the application layer (of which the form is a part), knows which fields go with which table(s) / column(s) at the database layer.
In the ease of the Enter Journals form at least two tables have data written to them when a journal entry is entered by a user. Continuing our example, let’s press the New Journal button (assuming we are not going to enter batch information). We receive the form in figure 3.
Figure 3 New Journal button
When a user is entering information into the Journal field, the application layer knows that any data saved into this field will write a record to the table GLJE_HEADERS. Whereas when lines are entered, they are saved into the GL_JE_LINES table. Two other tables could have data written to them when a user enters a journal entry. The GL_JE_BATCHES table stores batch information (system actually defaults values if a user doesn’t enter Batch level information). If a code combination is being created, an entry could also be made into the GL_CODE_COMBINATIONS table.
In summary, the application layer and database layer are distinct entities. The presentation layer enables the user to interact with the other two layers. The application layer acts as the ‘brains’ of the system whereas the database layer is taking cues (i.e. where to store the data) from the application layer. A baseball analogy might work here. The application layer is the pitcher. It has the ball and knou's where it wants to throw it. The database layer is the catcher and catches the ball where it is thrown.
