SOX Compliance: A Practical Look at Application Auditor

1
SOX Compliance A Practical Look at Application
Auditor

• Presented By
• Sunita Sarathy
• Product Manager
• Absolute Technologies, Inc

2
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

3
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

4
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

5
When Internal Controls arent met

1. Deficiency (No requirement to report it)
2. Significant Deficiency (Must be reported to the
   audit committee, but not to the public)
3. Material Weakness (Needs to be disclosed
   publicly, in company financial statements)

6
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!

7
IT Internal Controls

• Most companies adopt some or all of these Best
  Practices
• Documentation
• Approvals
• Separation of Duties
• Testing
• AUDITING

8
Why Audit?

• When critical or financial impacting data isnt
  audited properly
• financial statements may be incorrect due to
  mistakes, or fraud
• Auditors may identify inconsistencies as
  significant deficiency or material weakness

9
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

10
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
  SQLPlus
• Audit various database transactions
• Transactions are captured in the SYS.AUD table

11
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)

12
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 gt Record
  History, in the Oracle Applications Menu
• Columns can also be selected from within SQL

13
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

14
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

15
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

16
4. EBS Oracle Alerts

• Oracles Exception Reporting Tool
• Uses SQL statements to define exception
  conditions
• Can be Periodic (schedule based) or Event
  (creates a database trigger)

17
Limitations

• Cannot provide before and after values for
  changed columns
• Event Alerts fire on any change to a record
  within a defined table, generating unwanted
  transactions
• May cause Concurrent Request bottlenecks

18
5. EBS Audit Trail

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

19
Limitations

• No single audit table for ease of reporting
• Cant apply a condition to the trigger
• Cant toggle an audit on/off for a single table
• Cant 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

20
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

21
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

22
Application Auditor

• Source Table
• (FND_USER)

Transaction Details (Destination) Table
App Auditor
Source Table (AP_CHECKS)
Source Table (ORDER_HOLDS)
23
Audit Design

• Audit 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

24
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
25
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!

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

28
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

29
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)
30
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

31
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

32
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

33
Audit the Auditor
34
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

35
AA Customers (SIMG)

• Requirement
• Distinguish between updates made from SQLPlus,
  and updates within Oracle Apps
• Solution
• AAs Check Terminal feature allows the user to
  identify how the transaction was performed.

36
AA Customers (Harmonic)

• Requirement
• Transaction Monitoring
• Solution
• AA provides notification when unauthorized
  transactions occur

37
AA Customers (Tektronix)

• Requirement
• Track Sales Order Changes
• Solution
• AAs custom table option allows for audit records
  to be mapped to custom tables

38
Finally

• Application Auditor is highly performance
  optimizedno performance issues
• User friendly Forms Interface for Audit
  Configurations and Audit Transactions
• Two step audit process (Auditor and Audit
  Administrator)

39
Thank You!
40
Source Destination Tables
41
Source Columns
42
Reference Elements
43
Column Mapping
44
Audit Transactions Report
45
Audit Configuration Report