1 / 46

SOX Compliance: A Practical Look at Application Auditor

SOX Compliance: A Practical Look at Application Auditor. Presented By Sunita Sarathy Product Manager Absolute Technologies, Inc. Highlights. Sarbanes Oxley Internal Controls IT Best Practices for SOX Compliance Auditing Options in Oracle Application Auditor. Sarbanes Oxley Act.

harper
Download Presentation

SOX Compliance: A Practical Look at Application Auditor

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SOX Compliance: A Practical Look at Application Auditor Presented By Sunita Sarathy Product Manager Absolute Technologies, Inc

  2. Highlights • Sarbanes Oxley • Internal Controls • IT Best Practices for SOX Compliance • Auditing Options in Oracle • Application Auditor

  3. Sarbanes Oxley Act • SOX – Signed into law on July 30, 2002 as a result of various accounting scandals • Section 404 requires public companies to attest to the effectiveness of their internal controls over financial reporting • Section 302 requires that CEO’s and CFO’s vouch for the integrity of their financial statements

  4. Section 404 Compliance • Compliance with SOX 404 has 4 steps • Identify Key Internal Controls • Document the identified Internal Controls • Management Test of Internal Controls • Auditor Test of Internal Controls

  5. Internal Controls • What is an Internal Control? • Objectives of Internal Controls • Ensure integrity and reliability of information • Compliance with policies, laws and regulations • Safeguarding of assets • Economical and efficient use of resources • Accomplishment of established objectives and goals

  6. When Internal Controls aren’t met… • Deficiency (No requirement to report it) • Significant Deficiency (Must be reported to the audit committee, but not to the public) • Material Weakness (Needs to be disclosed publicly, in company financial statements)

  7. Internal Controls in IT • SOX Section 404 - “Management has to ensure appropriate internal controls of financial reporting” • Most companies have software applications that impact Financial Reporting, like Oracle, SAP etc • Therefore, most IT Applications would need to be regulated as per SOX requirements!

  8. IT Internal Controls • Best Practices in the development cycle: • Documentation • Approvals • Separation of Duties • Testing • AUDITING

  9. Why Audit? When critical or financial impacting data isn’t audited properly… …financial statements may be incorrect due to mistakes, or fraud • Auditors may identify inconsistencies as significant deficiency or material weakness

  10. Auditing Oracle There are several auditing options in Oracle: • Oracle Database – Audit Feature • eBusiness Suite – Row Who Columns • eBusiness Suite – End User Access • eBusiness Suite – Oracle Alerts • eBusiness Suite – Audit Trail • Absolute Technologies Application Auditor

  11. 1. Database Audit Feature • Set audit_trail parameter = TRUE in init.ora file and restart the database • Execute SQL audit commands from SYSTEM user in SQL*Plus • Audit various database transactions • Transactions are captured in the SYS.AUD$ table

  12. Limitations • Does not provide before and after values for column changes • No standard reporting, or form level access to data • No way to provide user notification, as the audit table is owned by SYS (cannot define triggers on SYS tables)

  13. 2. EBS – Row Who CREATION_DATE Date and Time row was created CREATED_BY Oracle Applications user ID from FND_USER LAST_UPDATE_LOGIN Login ID from FND_LOGINS LAST_UPDATE_DATE Date and Time row as last updated LAST_UPDATED_BY Oracle Applications user ID from FND_USERS • Can be accessed by selecting Help > Record History, in the Oracle Applications Menu • Columns can also be selected from within SQL

  14. Limitations • Only stores the identities of the user that created the record, and the user that made the latest change • Does not store old and new values of the changed columns • Cannot handle changes made by processes external to the security of Oracle Applications • Information is stored within the subject table, making it less convenient for centralized audit reporting

  15. 3. EBS – End User Access • The system profile option “Sign-On: Audit Level” controls the level of end user access auditing • The valid settings are None, User, Responsibility, and Form. ‘Form’ represents maximum auditing • The standard reports for end-user auditing are: • SignOn Audit Users • SignOn Audit Responsibilities • SignOn Audit Forms • SignOn Audit Concurrent Requests • SignOn Audit Unsuccessful Logins

  16. Limitations • Only audits end user usage of specified forms • Does not audit changes at the database level • Does not audit any form activity or database transaction that may be of interest to ensure compliance. Only audits user access

  17. 4. EBS – Oracle Alerts • Oracle’s Exception Reporting Tool • Uses SQL statements to define exception conditions • Can be Periodic (schedule based) or Event (creates a database trigger)

  18. Limitations • Event Alerts fire on any change to a record within a defined table, generating unwanted transactions • May cause Concurrent Request bottlenecks

  19. 5. EBS – Audit Trail • Set the System Profile Option AuditTrail: Activate to Yes • As System Administrator, select Security -> AuditTrail -> Install • Define applications, groups, tables and columns to audit • Run Audit Trail Update Tables program to activate auditing

  20. Limitations • No single audit table for ease of reporting • Can’t apply a condition to the trigger • Can’t toggle an audit on/off for a single table • Can’t capture data outside the scope of the audited table, like foreign table column values for ease of reporting • No single record holds the before and after detail of changed column values

  21. Key to SOX Compliance • The greater the degree of automation in the development process, the better. • Automate audit triggering, and the capturing of audit data. • Ease of audit reporting

  22. Enter Application Auditor • Application Auditor is a comprehensive auditing solution that can be installed and configured within minutes • Standard, user-friendly interface based on Oracle Developer tools • Simplifies audit reporting, as all audit records go to one table

  23. Application Auditor Source Table (FND_USER) Transaction Details (Destination) Table App Auditor Source Table (AP_CHECKS) Source Table (ORDER_HOLDS)

  24. Audit Design • Engine dynamically creates trigger-procedure combination • Database Objects are created in the AA schema • Trigger is defined on Source Table, to be fired upon change to Source Columns • Procedure collects… • Before and After Values of Source Columns • Reference Columns and other identifying Elements … and inserts them into the Transactions table

  25. Audit Flow Source Table is Changed Table based Trigger fires, calls Procedure Procedure collects Old and New Values of Changed Column, and other Reference Columns Inserts audit data into Destination Table

  26. Create an Audit • Select a Source Table - the table to be audited • Register the standard AA Destination table, which will store all audited data • Identify Source Columns - the Columns that we want tracked in the Source Table • AA automatically collects standard reference information for each record • AA maps the Source and Reference Column values to columns in the standard Destination Audit Table. • Compile the configuration - It is now ready to audit!

  27. Audit Mapping (Source Columns) (Mapped Columns) START_DATE* OLD_COLUMN_VALUE START_DATE* NEW_COLUMN_VALUE LAST_UPDATED_BY LAST_UPDATED_BY TRANSACTED_DATE TRANSACTED_DATE D_FND_USER_NAME FND_USER_NAME D_TERMINAL TERMINAL Source Table (FND_USER) Destination Table (ai_ce_change_trx)

  28. Audit Features • Single audit table stores – • Before and After values of column • Table and Column name • Trigger Action (Insert, Update or Delete) • Primary Key of Table • When and Who changed the column value • Reference additional column values within the same table at time of change • Embedded SQL can select additional values from other tables upon change

  29. Revision Architecture • Uses Revisions to create separate audit bins • Audits may be migrated across revisions, or even across database instances. • Migrate Audit from Revision 1 to Revision 2 • Migrate entire Revision from Dev to Prod instance • Only one compiled revision can exist at a point in time

  30. Revision Architecture • Allows the separation of audits based on user criteria • Allows one-step compilation of all audits in a revision Compiled Audits Revision (example) Development Revision (example)

  31. Audit Reporting • Audit Transactions Report • Displays the old and new values of the column, the database user who updated the record, and the identity of the terminal used to make the change • Audit Configurations Report • Displays the various audit configurations defined through Application Auditor

  32. SOX Compliant Audit Package • Pre-defined set of 65 audits, based on significant Setup and Financial Impacting tables in Oracle eBusiness Suite • Package can be loaded and compiled within minutes

  33. AA Administrator • Audit the Auditor! • Track users created in AA schema • Track changes to database objects in AA schema • Administrator email account holds a copy of all email notifications sent from AA

  34. Audit the Auditor

  35. Planned Enhancements • Increased audit flexibility – allow a Destination Object Type ‘Procedure’ • Allow users to audit and prevent unauthorized transactions • Audit DDL for ANY schema • Audit all transactions for a User

  36. AA Customers (SIMG) Requirement – Distinguish between updates made from SQL*Plus, and updates within Oracle Apps Solution – AA’s Check Terminal feature allows the user to identify how the transaction was performed.

  37. AA Customers (Harmonic) Requirement – Transaction Monitoring Solution – • AA provides notification when unauthorized transactions occur • Condition feature allows tracking to be limited based on user criteria • Changes made via external processes • Changes made by a specific user

  38. AA Customers (Tektronix) Requirement – Track Sales Order Changes Solution – AA’s custom table option allows for audit records to be mapped to custom tables

  39. Finally • Application Auditor is highly performance optimized…no performance issues • User friendly Forms Interface for Audit Configurations and Audit Transactions • Audit security maximized by dual role auditing (Auditor and Audit Administrator)

  40. Thank You!

  41. Source – Destination Tables

  42. Source Columns

  43. Reference Elements

  44. Column Mapping

  45. Audit Transactions Report

  46. Audit Configuration Report

More Related