Transcript SOX Compliance – A Practical Look at 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
Auditing Options in Oracle
Application Auditor
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
Section 404 Compliance

1.
2.
3.
4.
Compliance with SOX 404 has 4 steps
Identify Key Internal Controls
Document the identified Internal Controls
Management - Test Internal Controls
Auditor - Test Internal Controls
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
When Internal Controls
are missing or inadequate
1.
Control Deficiency
–
–
–
Significant Deficiency
–
–
1.
Remote likelihood of undetected material misstatement in
financials
No requirement to report it
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
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!
Internal Controls in IT

Best Practices in the development cycle:
–
–
–
–
–
Documentation
Approvals
Segregation of Duties (SOD)
Testing
AUDITING
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
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
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.
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
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
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
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
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
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
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
Application Auditor
Source Table
(FND_USER)
Source Table
(AP_CHECKS)
Source Table
(ORDER_HOLDS)
App
Auditor
Transaction
Details
(Destination)
Table
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!
Audit Mapping
Source Table
Destination Table
(FND_USER)
(ai_ce_change_trx)
(Source Columns)
(Mapped Columns)
START_DATE*
START_DATE*
LAST_UPDATED_BY
TRANSACTED_DATE
D_EMAIL
D_TERMINAL
OLD_COLUMN_VALUE
NEW_COLUMN_VALUE
LAST_UPDATED_BY
TRANSACTED_DATE
EMAIL
TERMINAL
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
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
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
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
Revision Architecture
Compiled Audits
Revision
(example)


Development
Revision
(example)
Allows the separation of audits based on user
criteria
Allows one-step compilation of all audits in a
revision
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
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
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
Audit the Auditor
Aa Customer
Silicon Image
Requirement
Differentiate updates made from
 SQL*Plus
 Oracle Apps
Solution
Aa’s Check Terminal feature allows the
user to identify how the transaction
was performed.
Aa Customer
Harmonic
Requirement
Monitor selected users’ transactions
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
Aa Customer
Tektronix
Requirement
Track Sales Order changes for
separate business and financial review
Solution
Aa’s custom table option allows for
audit records to be mapped to
separate audit trail table
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)
Thank You!
www.absolute-tech.com
Source – Destination
Tables
Source Columns
Reference Elements
Conditions
Column Mapping
Audit Transactions Report
Audit Configuration
Report
View Transactions
AUD$ Table
EBS Row Who
EBS – End User Access
Audit Trail > Install