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


  • 78 Views
  • 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

    • AUDITING


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


Limitations

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

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


Limitations1

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

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


Limitations2

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

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)


Limitations3

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

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


Limitations4

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

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

(FND_USER)

Transaction

Details (Destination)

Table

App

Auditor

Source Table

(AP_CHECKS)

Source Table

(ORDER_HOLDS)


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)

START_DATE*OLD_COLUMN_VALUE

START_DATE*NEW_COLUMN_VALUE

LAST_UPDATED_BYLAST_UPDATED_BY

TRANSACTED_DATETRANSACTED_DATE

D_FND_USER_NAMEFND_USER_NAME

D_TERMINALTERMINAL

Source Table

(FND_USER)

Destination Table

(ai_ce_change_trx)


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

Revision

(example)

Development

Revision

(example)


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


Finally

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)


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