Transcript SOX Compliance – A Practical Look at Application

SOX Compliance: A
Practical Look at
Application Auditor
Presented By
Sunita Sarathy
Product Manager
Absolute Technologies, Inc
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 of Internal Controls
Auditor Test of Internal Controls
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
When Internal Controls
aren’t 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)
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!
IT Internal Controls

Most companies adopt some or all of these Best
Practices:
–
–
–
–
–
Documentation
Approvals
Separation of Duties
Testing
AUDITING
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
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




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
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)
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
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
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
SignOn
SignOn
SignOn
SignOn
Audit
Audit
Audit
Audit
Audit
Users
Responsibilities
Forms
Concurrent Requests
Unsuccessful Logins
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
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)
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
5. EBS – Audit Trail

Set the System Profile Option AuditTrail: Activate to

As System Administrator, select Security ->

Define applications, groups, tables and columns to
audit

Run Audit Trail Update Tables program to activate
auditing
Yes
AuditTrail -> Install
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
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
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
Application Auditor
Source Table
(FND_USER)
Source Table
(AP_CHECKS)
Source Table
(ORDER_HOLDS)
App
Auditor
Transaction
Details
(Destination)
Table
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
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
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!
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_FND_USER_NAME
D_TERMINAL
OLD_COLUMN_VALUE
NEW_COLUMN_VALUE
LAST_UPDATED_BY
TRANSACTED_DATE
FND_USER_NAME
TERMINAL
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
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
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
– Displays the various audit configurations defined through
Application Auditor
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
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
Audit the Auditor
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
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.
AA Customers (Harmonic)
Requirement –
Transaction Monitoring
Solution –
AA provides notification when unauthorized
transactions occur
AA Customers (Tektronix)
Requirement –
Track Sales Order Changes
Solution –
AA’s custom table option allows for audit records to be
mapped to custom tables
Finally



Application Auditor is highly performance
optimized…no performance issues
User friendly Forms Interface for Audit
Configurations and Audit Transactions
Two step audit process (Auditor and Audit
Administrator)
Thank You!
Source – Destination
Tables
Source Columns
Reference Elements
Column Mapping
Audit Transactions Report
Audit Configuration
Report