Building an Audit Trail in an Oracle EBS Environment

1 Building an Audit Trail in an Oracle EBS Environment Presented by: Jeffrey T. Hare, CPA CISA CIA. Webinar Logistics Hide and unhide the Webinar control panel by clicking on the arrow icon on the top right of your screen The small window icon toggles between a windowed and full screen mode Ask questions throughout the presentation using the chat dialog Questions will be reviewed and answered at the end of the presentation; I'll open the lines for interactive Q&A. During the presentation, we will be conducting a number of polls, please take the time to respond to all those that are applicable CPE will only be give to those that answer at least 3 of the 4 polls 2009 ERPS. Presentation Agenda Overview: Introduction Audit Trail Overview Audit Trail Example Audit Trail Technologies What to Audit Upcoming Webinars Other Comments Wrap Up 2009 ERPS. Introductions Jeffrey T. Hare, CPA CISA CIA. Founder of ERP Seminars and Oracle User Best Practices Board Written various white papers on Internal Controls and Security Best Practices in an Oracle Applications Environment Frequent contributor to OAUG's Insight magazine Experience includes Big 4 Audit , 6 years in CFO/Controller roles both as auditor and auditee In Oracle applications space since 1998 both as client and consultant Founder of Internal Controls Repository public domain repository Author Oracle E-Business Suite Controls: Application Security Best Practices Contributing author Best Practices in Financial Risk Management Published in ISACA's Control Journal (twice) and ACFE's Fraud Magazine 2009 ERPS.

2 Poll question: How are you identifying changes to application controls, security settings, and activity through SQL forms 2009 ERPS. Audit Trail Overview 2009 ERPS. Audit Trail Overview Disconnect between application and database layers Need to be concerned about application access as well as database access Audit Trail only kept where application is built to do so Lack of Audit all functionality to monitor privileged users Lack of detailed Audit Trail throughout the application In some cases as is the case with HR, update versus correct Example: change(s) to columns in a table can cause confusion related to changes made - Journal Sources example 2009 ERPS. Audit Trail Example 2009 ERPS. Audit Trail Example Audit Trail deficiencies Journal Sources Example: 2009 ERPS. Audit Trail Example Audit Trail deficiencies Journal Sources Example: After first change: 2009 ERPS. Audit Trail Example Audit Trail deficiencies Journal Sources Example: After second change: 2009 ERPS.

3 Audit Trail Example Journal Sources example data: Initial Value After First Change After Second Change Value Checked Unchecked Checked Updated by AUTOINSTALL JTH9891 JTH9891. Update date 03-Jan-2007 25-Aug-2008 25-Aug-2008. 21:52:09 16:43:58 16:45:31. The only thing we can tell from this is that JTH9891 made a change, but we have no idea WHAT changed. The values as of the second change are the same as the initial values! 2009 ERPS. Audit Trail Technologies 2009 ERPS. Audit Trail Technologies Overview: Row Who / Alerts Sign On Audit Snapshot Log Triggers 2009 ERPS. Audit Trail Technologies Row Who / Alerts What is it: Created by, creation date, last updated by, last updated date When it is useful Monitoring things you don't expect to change (however, when it does ). Within an Audit period, creation date and last updated date Transaction monitoring (high volume) some continuous controls monitoring (CCM) requirements 2009 ERPS. Audit Trail Technologies Row Who / Alerts Pros: Standard, embedded, no performance impact, no configuration Alerts can be proactive Cons Only contains values as of that point in time Alerts don't store values, therefore, cannot be audited 2009 ERPS.

4 Audit Trail Technologies Sign On Audit What is it: Profile option SignOn: Audit Level set to Form When is it useful: Tracking user logins and use of professional forms Tracking login of generic users such as SYSADMIN, job scheduling users where activity should be limited by policy and procedure 2009 ERPS. Audit Trail Technologies Sign On Audit Pros: Relatively little performance impact Useful for comparing login activity to activity logged by users to hold them accountable versus the policies / standards Cons Only tracks activity via professional forms (not OA. framework html pages), doesn't tell you WHAT the user did, just that they accessed the form 2009 ERPS. Audit Trail Technologies Snapshot What is it: Comparison of row who information between instances or between two points in time (prod versus 12/31 version). When is it useful: Identifying when something is changed that you wouldn't expect When comparisons are pre-mapped such as tools that compare objects between instances or versions Application support to identify when there is a configuration change ( what broke the process).

5 2009 ERPS. Audit Trail Technologies Snapshot Pros: Insignificant performance impact Useful for comparing significant volumes of data Useful for support purposes comparing data across instances or points in time when processes are broken Cons: Only tells you delta as of two points in time, can miss incremental changes between periods 2009 ERPS. Audit Trail Technologies Logs What are they: Various types of incremental data Could be traffic flowing across the network or technology inherent to the database (redo or for mirroring). When are they useful: High volume transaction tables 2009 ERPS. Audit Trail Technologies Logs Pros: Insignificant performance impact Cons: Typically unable to map metadata to capture important cross reference information about the change 2009 ERPS. Audit Trail Technologies Triggers What are they: Core database technology Use by System Administrator Audit Trail Advanced software packages: May allow metadata to be mapped Usually have a central repository for easier reporting and data management May allow for alerting of information When are they useful: Setups (key control configurations), Master Data, Security, Development; SQL Forms 2009 ERPS.

6 Audit Trail Technologies Triggers Pros: Allow for mapping of metadata Inherent technology within the application Captures detail changes and related metadata (most solutions) to provide an auditable system Cons: Can have a performance impact if deployed on high volume transaction tables. Therefore, performance impact needs to be evaluated and considered when using 2009 ERPS. Audit Trail Technologies Metadata Mapping Example: fnd_responsibility table: 2009 ERPS. Audit Trail Technologies Metadata Mapping Example: fnd_menus table: 2009 ERPS. Audit Trail Technologies Metadata Mapping Example: fnd_menus_tl table: 2009 ERPS. Poll 2: How are you baselining configurations and tracking changes related to automated controls? 2009 ERPS. Audit Trail : What to Audit 2009 ERPS. Audit Trail : What to Audit What to Audit : Form / Function Category Application Controls Journal Sources (GL), Journal Authorization Limits (GL), Approval Groups (PO), Adjustment Approval Limits (AR), Receivables Activities (AR), OM Holds (OM), Line Types (PO), Document Types (PO), Approval Groups (PO), Approval Group Assignments (PO), Approval Group Hierarchies (PO), Tolerances, Item Master Setups, Item Categories Affect Business Process Profile Options, DFFs, KFFs, Value Set Changes Development Concurrent Programs, Executables, Functions, SQL forms, Objects Security Menus, Roles, Responsibilities, Request Groups, Security Profiles, SQL forms such as Dynamic Trigger Maintenance, Define Profile Options, Alerts, Collection Plans, etc (see Metalink Note for more information on SQL forms).

7 Fraud Related Suppliers, Remit-To Addresses, Locations, Bank Accounts Poll 2: How are you baselining configurations and tracking changes related to automated controls? 2009 ERPS. Audit Trail Technologies Software providers: Trigger-based: Absolute Technologies: Application Auditor CaoSys: CS* Audit (part of CS*Compliance). Greenlight Technologies: RESQ. Oracle : Configuration Controls Governor; Audit Vault Log-based: Guardium, Lumigent Snapshot: Approva 2009 ERPS. Upcoming Webinars ERP Seminars TBD. Absolute Technologies: 7 Oct, 2 EDT - Application Auditor CaoSys: 6 Oct, 2 EDT CS*Compliance 2009 ERPS. Other Comments 2009 ERPS. Poll 3: Will you require a CPE. certificate for a professional designation such as CPA, CISA, CISM, or CIA? 2009 ERPS. Sample Risk Assessment Application Controls / SOD. Conflict Risk Description Typical Mitigating Controls Enter Journals Enter Journals vs. Journal Sources: Do not allow those involved in JE process vs Maintain User could override controls by to maintain Journal Sources.

8 No user Journal changing configuration "Require should have access to both of these Sources Journal Approval" which is set in the functions, including support users. Journal Sources form and Changes to Journal Sources should go determines which sources are through change management and required to go through the journal approved by appropriate personnel that approval process . This could also has reviewed and understands the impact lead to changing "Freeze Journals" of this change on the process and as Journal Sources which could controls related to journal entries.. allow a user to delete or change a Changes to Journal Sources should be JE from a subledger. Either change audited at the system level via a log- could lead to compromise in controls based or trigger-based mechanism. A. related to the journal entry approval change management Audit should be process. This could lead to a performed with a 100% sample size done compromise in the integrity of the by comparing actual changes pull from a financial statements and control system level Audit Trail to approvals in the violations under SOX.

9 Change management documentation by an independent auditor. 2009 ERPS. Sample Risk Assessment Application Control Configs Conflict Risk Description Typical Mitigating Controls Maintain Maintain Journal Authorization Changes to Journal Authorization Limits Journal Limits: Access allows a user to should go through change management Authorization define journal approval limits. Risk and approved by appropriate personnel Limits is unapproved changes to journal that has reviewed and understands the approval limits resulting in posted impact of this change on the process and journal entries not properly approved controls related to journal entries. by management and overriding Changes to Journal Authorization Limits defined controls. This could lead to should be audited at the system level via a compromise in the integrity of the a log-based or trigger-based mechanism. financial statements and control A change management Audit should be violations under SOX.

10 Performed with a 100% sample size done by comparing actual changes pull from a system level Audit Trail to approvals in the change management documentation by an independent auditor. 2009 ERPS. Wrap Up 2009 ERPS. Oracle Apps Internal Controls Repository Internal Controls Repository Content: White Papers such as Accessing the Database without having a Database Login, Best Practices for Bank Account Entry and Assignment, Using a Risk Based Assessment for User Access Controls, Internal Controls Best Practices for Oracle 's Journal Approval Process Oracle apps internal controls deficiencies and common solutions Mapping of sensitive data to the tables and columns Identification of reports with access to sensitive data Recommended minimum tables to Audit Not affiliated with Oracle Corporation 2009 ERPS. ERP Seminars Services Free one-hour consultation On-site seminars (1 - 2 days) custom tailored to your company's needs as well as various web-based seminars RFP / RFI management for Oracle -related GRC software SOD / UAC Third Party software projects / remediation Audit Trail software projects Controls review related to Oracle -related controls.