Sox compliance a practical look at application auditor
This presentation is the property of its rightful owner.
Sponsored Links
1 / 45

SOX Compliance: A Practical Look at Application Auditor PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

SOX Compliance: A Practical Look at 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Sox compliance a practical look at application auditor

SOX Compliance: A Practical Look at Application Auditor

Presented By

Sunita Sarathy

Product Manager

Absolute Technologies, Inc

Sarbanes oxley act

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

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

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

When Internal Controls aren’t met…

  • Deficiency (No requirement to report it)

  • Significant Deficiency (Must be reported to the audit committee, but not to the public)

  • Material Weakness (Needs to be disclosed publicly, in company financial statements)

Internal controls in it

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

IT Internal Controls

  • Most companies adopt some or all of these Best Practices:

    • Documentation

    • Approvals

    • Separation of Duties

    • Testing


Why audit

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

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

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



  • 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

2. EBS – Row Who

CREATION_DATE Date and Time row was created

CREATED_BY Oracle Applications user ID from FND_USER


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



  • 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

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



  • 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

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)



  • 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

5. EBS – Audit Trail

  • Set the System Profile Option AuditTrail: Activate to Yes

  • As System Administrator, select Security -> AuditTrail -> Install

  • Define applications, groups, tables and columns to audit

  • Run Audit Trail Update Tables program to activate auditing



  • 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

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

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

Application Auditor

Source Table



Details (Destination)




Source Table


Source Table


Audit design

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

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

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

Audit Mapping

(Source Columns)(Mapped Columns)







Source Table


Destination Table


Audit features

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

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 architecture1

Revision Architecture

  • Allows the separation of audits based on user criteria

  • Allows one-step compilation of all audits in a revision

Compiled Audits






Audit reporting

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

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

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

Audit the Auditor

Planned enhancements

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

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

AA Customers (Harmonic)

Requirement –

Transaction Monitoring

Solution –

AA provides notification when unauthorized transactions occur

Aa customers tektronix

AA Customers (Tektronix)

Requirement –

Track Sales Order Changes

Solution –

AA’s custom table option allows for audit records to be mapped to custom tables



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

Sox compliance a practical look at application auditor 1332980

Thank You!

Source destination tables

Source – Destination Tables

Source columns

Source Columns

Reference elements

Reference Elements

Column mapping

Column Mapping

Audit transactions report

Audit Transactions Report

Audit configuration report

Audit Configuration Report

  • Login