1 / 47

SOX Compliance with Application Auditor

SOX Compliance with Application Auditor. Presented By Sunita Sarathy Product Manager Absolute Technologies, Inc. At SROAUG, Los Angeles, March 24, 2006 v2. Highlights. Sarbanes Oxley Common knowledge? Your situation? Internal Controls IT Best Practices for SOX Compliance

peggy
Download Presentation

SOX Compliance with 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 with Application Auditor Presented By Sunita Sarathy Product Manager Absolute Technologies, Inc. At SROAUG, Los Angeles, March 24, 2006 v2

  2. Highlights • Sarbanes Oxley • Common knowledge? • Your situation? • 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 Internal Controls • Auditor - Test Internal Controls

  5. What are Internal Controls? • Measures adopted by an Organization to: • Ensure integrity and reliability of information • Ensure Compliance with policies, laws and regulations • Safeguard assets • Promote economic and efficient use of resources • Accomplish established objectives and goals • Mature controls are recognized by: • Real-time monitoring • Continuous improvement, enterprise risk management • Automation support, ability to make rapid changes to controls

  6. When Internal Controls are missing or inadequate • Control Deficiency • Remote likelihood of undetected material misstatement in financials • No requirement to report it • Significant Deficiency • Adversely affects processes, more than remote likelihood of consequential misstatement • Must be reported to the audit committee, but not to the public • Material Weakness • Significant deficiency, possible material misstatement • Needs to be disclosed publicly, in company financial statements

  7. How is IT Affected? • 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. Internal Controls in IT • Best Practices in the development cycle: • Documentation • Approvals • Segregation of Duties (SOD) • Testing • AUDITING

  9. Why Audit? • If you don’t properly audit transactions that impact (a) financial data, and (b) application setups … … there is exposure that mistakes or fraudulent activity may be undetected … … resulting in incorrect financial statements • Auditors may identify inconsistencies as significant deficiency or material weakness

  10. How data is changed in Oracle eBusiness Suite • In Oracle, data can be modified through two mechanisms: • eBusiness Suite of Applications • Directly at the database level, through tools such as SQL*Plus, TOAD, SQL*Navigator, etc • Most conventional Auditing options audit one or the other method

  11. Auditing in 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 * Part of Oracle’s products prior to SOX legislation, oriented toward instrumentation and debugging.

  12. 1. Database Audit Feature • Set audit_trail parameter = TRUE in init.ora file • Execute SQL audit commands from SYSTEM user in SQL*Plus. Transactions are captured in SYS.AUD$ table Limitations • No Before and After values for changes. No standard reporting, or form level access to data • User Notification not possible, as table is owned by SYS

  13. 2. EBS – Row Who • Creation_Date, Created_By, Last_Updated_By, Last_Update_Date, Last_Update_Login • Navigate to Help > Record History, in the Oracle Applications Menu, or select from within SQL Limitations • Only records identities of Initial and Last User • Does not store Old and New Values • Cannot handle changes made by processes external to the security of Oracle Applications

  14. 3. EBS – End User Access • System profile option “Sign-On: Audit Level” controls the level of end user access auditing • Audit using standard reports like SignOn Audit Users, SignOn Audit Responsibilities, SignOn Audit Forms, etc Limitations • Only audits user access, or end user usage of specified forms • Does not audit changes at the database level

  15. 4. EBS – Oracle Alerts • Oracle’s Exception Reporting Tool • Use SQL statements to define exception conditions • Can be Periodic (schedule based) or Event (creates a database trigger) Limitations • Event Alerts fire on any change to a record within a defined table, generating unwanted transactions • May cause Concurrent Request bottlenecks

  16. 5. EBS – Audit Trail • Set System Profile Option AuditTrail: Activate =Yes • As System Administrator, select Security > AuditTrail > Install • Define applications, tables and columns to audit • Run Audit Trail Update Tables program to activate Limitations • Can’t toggle audits On/Off for selected tables • Can’t capture data outside the scope of the audited table

  17. Keys to SOX Compliance • The Audit triggering process should be automated • Audit trail (record of transaction, the activity & data) should be meaningful and comprehensive • Audit Reporting should be convenient • The Auditing Application should be secure

  18. Enter Application Auditor (Aa) • Comprehensive auditing solution • Can be installed and configured in less than an hour • Create Audit Configurations, for tables and columns to be audited • User Interface • Defines the work flow of defining, creating, configuring, installing, using, and reporting audits • Based on Oracle Developer tools, familiar look & feel • Simplifies audit reporting – all audit trail records go to one table • All audits are created in custom Aa schema

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

  20. Create Audit Config • Select a Source Table - the table to be audited • Register standard Aa Destination table • Identify Source Columns - Columns to be tracked • Aa automatically collects standard Reference information for each record • Create Conditions, if any, to limit auditing • 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!

  21. 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_EMAIL EMAIL D_TERMINAL TERMINAL Source Table (FND_USER) Destination Table (ai_ce_change_trx)

  22. Audit Design • App Auditor 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

  23. 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

  24. Audit Features • Single audit table stores – • Before and After values of Source Column • Source Table and Column name • Trigger Action (Insert, Update or Delete) • Primary Key of Source Table • Who changed Column and When • Reference additional column values from Source table • Embedded SQL to select additional data from other tables • Audit Notification can be set up via email

  25. Revision Architecture • Aa uses Revisions to create separate audit bins • Audits may be migrated across revisions, across schemas, 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

  26. 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)

  27. 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 • Facilitates review discussion with external auditor • Documents all audit configurations defined in Application Auditor • View Transactions Form • Displays the various audited transactions created as a result of triggered audits

  28. SOX Audit Package • Pre-defined set of 80+ table level audits, based on key setup and transaction tables that can impact Financial reporting and controls in Oracle eBusiness Suite • Package can be loaded and compiled within minutes

  29. Aa Administrator • Audit the Auditor! • Create and maintain Aa Audit users • Track changes to database objects in any schema • Maintain Admin email accounts, which receive a copy of all email notifications sent from Aa • Define content for Aa email alerts

  30. Audit the Auditor

  31. Aa CustomerSilicon Image

  32. Aa CustomerHarmonic

  33. Aa CustomerTektronix

  34. Finally… • Highlights • Can audit database and Oracle E-Business Suite transactions • Email Notification when audit is triggered • Auditing can be limited to user defined criteria • Custom Schema to ensure audit integrity and security • Application Auditor is highly performance optimized…no performance issues • User-friendly Forms Interface • Audit security maximized by dual role auditing (Auditor and Audit Administrator)

  35. Thank You! www.absolute-tech.com

  36. Source – Destination Tables

  37. Source Columns

  38. Reference Elements

  39. Conditions

  40. Column Mapping

  41. Audit Transactions Report

  42. Audit Configuration Report

  43. View Transactions

  44. AUD$ Table

  45. EBS Row Who

  46. EBS – End User Access

  47. Audit Trail > Install

More Related