SOX Compliance with Application Auditor

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 CEOs and CFOs 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 dont 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 SQLPlus, TOAD, SQLNavigator, 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 Oracles 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
  SQLPlus. 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 gt 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

• Oracles 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 gt
  AuditTrail gt Install
• Define applications, tables and columns to audit
• Run Audit Trail Update Tables program to activate
• Limitations
• Cant toggle audits On/Off for selected tables
• Cant 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
  optimizedno 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 gt Install