sox compliance a practical look at application auditor n.
Skip this Video
Loading SlideShow in 5 Seconds..
SOX Compliance: A Practical Look at Application Auditor PowerPoint Presentation
Download Presentation
SOX Compliance: A Practical Look at Application Auditor

Loading in 2 Seconds...

play fullscreen
1 / 45

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

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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
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)