Summary:
In this white paper, I will be discussing how SOX requirements impact technical development activities in Oracle Applications E-Business Suite implementation projects. Please note this white paper is just a guide based on my experiences working with SOX compliant clients. Personally I haven’t undergone any formal training related to SOX.
Sarbanes-Oxley Act of 2002 (SOX) is the law signed by signed by President Bush in 2002, is designed to "protect investors by improving the accuracy and reliability of corporate disclosures made pursuant to the securities laws" and to "deter and punish corporate and accounting fraud and corruption, ensure justice for wrongdoers, and protect the interests of workers and shareholders."
This law talks about security requirements. Ultimately, software security and application security requirements that you will most likely encounter will be based around DIGITAL DATA INTEGRITY and ACCOUNTABILITY. Although there is nothing new about these concepts, they provide a central theme within SOX.
Security:
Security Requirements Matrix (for Sarbanes-Oxley) important from the point of technical development in Oracle Applications
Statute | Summary | Threat | Possible Requirement |
Section 302—Corporate Responsibility for Financial Reports | Requires executives to certify the accuracy of corporate financial reports. | Unauthorized modification of data; data fraud | Authenticate data using strong data integrity controls—secure hash of data, row level encryption, provide detailed user level logging of access and data change events |
Section 404—Management Assessment of Internal Controls | Requires executives and auditors to confirm the effectiveness of internal controls for financial reporting. | Unauthorized access to data, data deletion | Robust access controls, interoperable with enterprise authentication, access and auditing |
SOX gives guidelines to secure data needed for financial reporting. While thinking about SOX concept we can consider SOX as WRITE event. SOX primarily focuses on WRITE events and not READ events. SOX is concerned about any or all changes to the financial data and the processing of financial data. The processing of financial data includes programs, reports, and configuration settings that may affect reporting of financial data. So a technical person should focus on all such customizations which touch financial data. Such customizations i.e. custom programs (Not Supplied by Oracle EBS) can be under scanner of SOX auditor.
Only authorised personnel should be allow to access and update the data. In Oracle Applications, this is controlled through creation of various reponsibilities respective to the duties performed by the individuals. Thus Oracle Responsibility Management forms important part of functional setups.
Implementing SOX data security requirement is a challange from technical point of view. It depends on how an auditor/individual interpretes the SOX guidelines from technical perspective.
Oracle Applications IT Controls
Let us discuss data security from Oracle Applications point of view. IT Controls can be grouped into General IT Controls and Application Controls. General IT controls include common IT services like disaster recover, data backup, change management, system development. Application Controls are embedded in the application and designed to achieve accuracy, data validation, authorization, reconciliation and approvals.
IT controls can be viewed in the form of following Matrix:
We will discuss few of the points mentioned in the matrix which impact Oracle Apps technical development and the way to address these control/security requirements.
AccessàSecurityàUser Management
There should be unique user account for accessing oracle applications for all individuals. There should not be existence of generic user accounts which are used by many persons. This is naturally helpful for auditing and tracking.
AccessàSecurityàSegregation of Duties
Oracle Application manages this requirement through creation of various responsibilities based on the duties performed by individuals. For example Purchasing Operation head who has authority to approve Purchase Order of highest amount need not have authority to create a purchase order.
System Administrators and Developers can have Query-Only/Read-Only functional responsibilities.
Developers should not have privileges to change profile options, concurrent programs and anything related to application setup. All changes to these things should go through proper change control mechanism and approval process.
There can be many Oracle Applications instances e.g. Development instance, Patch Instance, Integration/Test instance and Production Instance. Developers generally get access to all the screens except user creation and responsibility assignment screen on development instance. Access to all other instances is restricted. Even in development instance if access is needed for developer then it needs approval from IT Manager. This helps to maintain to control over data. It’s frustrating for developers but its mandatory.
AccessàSecurityàDatabase Security
APPS account/schema: APPS account and all the Oracle Applications database accounts should be restricted to DBAs only for Production and other restricted instances except the development instance. For Production instance, individual DBAs and support staff are given will read-only database access. There is should not be any Custom Program development in APPS schema.
Custom Schema:For customization/interface development, custom applications are registered in Oracle E-Business Suite with its own custom schema e.g. Custom INV Application is registered with schema XXINV.
Database security and segregation of responsibilities can be clubbed together to have multiple custom schemas i.e. one schema each for each custom application so custom purchasing schema should perform operations relate to purchasing and not inventory.
SOX talks about data security so naturally it talks about un-authorized data changes by custom developed objects which need to be restricted. To achieve this requirement, Customer Schemas like XXINV (Custom Inventory Application) doesn’t have INSERT/UPDATE/DELETE on Oracle APPS base tables. Now Oracle EBS always communicates with external systems through interfaces and APIs. So, custom schemas are given INSERT/UPDATE/DELETE permissions on Oracle Apps Open Interface tables, which allow external data to be imported to Oracle EBS. Seeded Oracle Import APIs and concurrent programs validate this data before importing it into Oracle EBS thus fulfilling SOX requirements.
To implement more control on data, Custom Application schema is given access module specific interface tables e.g. XXINV schema is given with INSERT/UPDATE/DELETE sprivileges on inventory (INV) interface tables, similarly XXPO schema is given with INSERT/UPDATE/DELETE privileges on purchasing (PO) interface tables.
There are cases when just interface tables are not sufficient to bring external data to Oracle Apps and/or to implement specific business requirement. In this case generally developers should TAR with Oracle and try to get solution/seeded API for updating base tables.
Since SOX talks about restricting un-authorized access to data, proper authorization by IT Higher Management can authorize INSERT/UPDATE on base tables and/or execute privileges to APIs which update base tables as exception. Proper change control mechanism is necessary for SOX compliance.
AccessàSecurityàOS Security
All access to the standard Oracle operating system accounts oracle and applmgr should be controlled and the appropriate logs maintained to identify the individual accessing these shared accounts.
We talked about custom applications, custom schemas, now on Operating system level Custom Applications have custom directory structures are maintained. All users other than oracle and applmgr have read-only access to all directories. In developers get WRITE access only to custom directory structures and that too only on development machines/instances. On restricted instances developer access can be strictly read-only.
On production instances write access is given to interface unix accounts only on those directories which interact with external systems through ASCII files. e.g. Item data files are received from external system called mapics, then we can have a interface user account called mapics which will have read/write access to directory $XXINV_TOP/datafiles/in etc. etc.
All access to interface accounts should be controlled and the appropriate logs maintained and monitored to ensure only authorized processes and users are transmitting interface files.
Change Management
Migration from development instances to protected instances (viz. UAT Instance, PROD Instance) should go through change management process i.e Application Change Request and Approval process. This holds good for all changes
- Application Object Migration (Forms/Reports)
- Application Changes (LOOKUPs, PROFILEs,CONC Programs,MENUs etc)
- Patch Application
- Database Objects (Tables, indexes, Packages, functions etc)
- Operating system directory changes (e.g. new directories for new interface requirements)
Even in a well-controlled change management process, emergency changes are perfectly acceptable as long as there is a defined and documented process for such changes.
Monitoring and Troubleshooting
Monitoring and troubleshooting is often included in the scope of SOX compliance because a poorly managed environment could affect the accuracy and completeness of financial reporting. As an example, a daily interface program for journal entries that does not complete successfully and is not detected, may result in a misstatement (probably not material) of financials results.
MonitoringàApplication Auditing
- Oracle Applications AuditTrails is required for key user management tables like FND_USER, FND_RESPONSIBILITY, FND_FORM_FUNCTIONS, FND_MENUS, FND_RESP_FUNCTIONS, FND_USER_RESP_GROUPS, etc. to maintain a history of changes.
- FND_ORACLE_USERID should be audited to have a history of password changes to the registered Oracle Applications database accounts, since changing of these passwords is a manual control and a balancing detective control is required
MonitoringàDatabase Auditing
By default, the Oracle Database and Oracle Applications are not compliant with SOX. In the default installation, there is no auditing enabled for either the Oracle Database or Oracle Applications. Oracle Applications maintains creation and last modified information for almost every record, but generally does not provide any history of changes to records. For SOX compliance, a history of changes to critical configuration settings and controls is required.
DBAs should enable auditing of user accounts SYS and SYSTEM
The FND_PROFILE_OPTIONS and FND_PROFILE_OPTION_VALUES tables can not be audited using the Oracle Applications AuditTrail functionality, therefore, custom database triggers need to be created to track changes to these two tables.
No other auditing should be mandatory for SOX compliance, but it is recommended to enable the following database audits: ALTER SYSTEM, ALTER DATABASE, and PUBLIC DATABASE LINK.
While designing interface programs, monitoring should be built in the design so that failures/errors in daily interfaces can be reported and actions can be taken to resolve the issues.
Conclusion:
From Oracle Applications Technical development Point of view we can conclude on following points:
- No Custom Program Development in APPS schema of Oracle E-Business Suite instance.
- One custom schema per custom application (e.g. XXINV for Custom Inventory Application) where all Custom Programs/Objects can be created.
- Custom schemas should not have WRITE access (INSERT/UPDATE/DELETE privileges) on Oracle Apps Base Tables
- Custom Schemas should have WRITE access only to interface tables related to respective application which is being customized e.g. Custom Inventory Application schema (XXINV) should have WRITE privileges to Inventory (INV) interface tables e.g. MTL_SYSTEM_ITEMS_INTERFACE and not Purchasing Interface table. This is required for building inbound interfaces to Oracle E-Business Suite.
- In case business requirement needs a custom program to update Oracle Apps base table, it has to be done through an seeded API (This privilege should be approved by the IT management)
- In case seeded API is not available to update base table, developers should raise Service Request (SR/TAR) with Oracle to confirm the non-availability of seeded API to update base table. To fulfill the business requirement formal IT higher management approval is needed as (Exception) to allow WRITE access on certain Oracle Apps Base Table.
- Proper Monitoring should be incorporated in design/development of interface programs to track failures and build audit trail.
- Change Control mechanism should be in place to perform any change in restricted Oracle Apps instances like User Acceptance Testing Instance/Production Instance etc.
- Developer cannot have System Administrator responsibility on any Oracle E-Business Suite instance.
- Support Staff can have read-only functional responsibilities to Oracle Applications.
- Developers can have functional responsibilities in development instance through formal approval from IT higher Management.
References:
- DBA Guide to Understanding Sarbanes-Oxley - White paper by Stephen Kost
- Sarbanes-Oxley (SOX)-Impact on Security In Software - White Paper by Keith Pasley
About The Author:
Prasad Bhogle is a independent Oracle Apps Technical consultant with overall 13+ Years of experience in Oracle Technologies which includes 8+ Years in Oracle Applications. Currently working for Pune based organization as handling various roles like Project Manager/Solutions Architect for multiple Oracle Apps projects.
Apps2Fusion Articles 
Comments
Very informative article indeed. I have couple of questions though.
1) No Custom Program Development in APPS schema of Oracle E-Business Suite instance. --> When you say custom program development do you mean by DB objects like table, packages, triggers etc. I thought Oracle standard is to write all custom packages into APPS schema. Am I wrong here?
2) One custom schema per custom application (e.g. XXINV for Custom Inventory Application) where all Custom Programs/Object s can be created --> In all my previous implementations we had one custom schema/applicat ion for all customizations. I found it very convenient to maintain. You can not possibly have 200 custom schemas/applica tions for every single Oracle module. In case of one schema/applicat ion for all customization are we breach of SOX compliance?
Nice article.
I am having 5 yrs exp in Java/J2EE technology. I would like to learn oracle apps technically. Can you pls able to provide details of good training institute in chennai.
Regar ds,
Mades.
http://www.developer.com/security/article.php/3320861
Tx,
Claudia
would you please share ur contact details to my mail id as wanted some guideline.
:)thanks in advance.
ambika
RSS feed for comments to this post