Continuous Audit - Jedge Information Security
City of AtlantaContinuous AuditA Case StudyWednesday, August 24th 20119:00 am – 10:50 pmDamien Berahzer, CISADamien Berahzer ISACA Atlanta – Geek Week 20111
.Damien Berahzer ISACA Atlanta – Geek Week 20112
Before we begin Questions Time Checkers Response CardsDamien Berahzer ISACA Atlanta – Geek Week 20113
Who are you?Damien Berahzer ISACA Atlanta – Geek Week c1. Public Accounting Firm2. Internal IT Auditor3. Internal FinancialAuditor4. Internal EverythingAuditor5. Boutique Shop6. Security or Compliance4
Session Objectives Continuous Auditing concepts/discussion Tools used to develop an in-house solution fortesting SOD Examine CA examples Automation criteria, challenges, benefits,sacrifices and skills sets needed What’s NextDamien Berahzer ISACA Atlanta – Geek Week 20115
CA, the beginningDamien Berahzer ISACA Atlanta – Geek Week 20116
We are performing CA58%Damien Berahzer ISACA Atlanta – Geek Week 2011No42%Yes1.Yes2.No7
Dollar, Dollar Bills Ya’ll00,8 100.00,00NEproCC7 yearsDamien Berahzer ISACA Atlanta – Geek Week 20118
Continuous Auditing The IIA’s Global Technology Audit Guide(GTAG) on continuous auditing defines it as“any method used by auditors to performaudit-related activities on a more continuousor continual basis.” ISACA defines CA as an approach that allowsIS auditors to monitor system reliability on acontinuous basis and to gather selective auditevidence through the computer.Damien Berahzer ISACA Atlanta – Geek Week 20119
Is hiring 25 new employees to performmonthly reconciliations CA?82%1.Yes2.NoDamien Berahzer ISACA Atlanta – Geek Week 2011NoYes18%10
Continuous Auditing Model, framework, methodIncreased frequencyTechnology driven / automationOverall audit integrationAllows for less time *Damien Berahzer ISACA Atlanta – Geek Week 201111
Continuous Auditing continuedCONTINUOUS KAssessmentAUDITORS’ CONTINUOUS AUDITINGContinuousMonitoringManagement’s ProcessAdapted from the Global Technology Audit GuideDamien Berahzer ISACA Atlanta – Geek Week 201112
Continuous Auditing continuedContinuous Auditing Comes of Age, Gerard Brennan, 2008Damien Berahzer ISACA Atlanta – Geek Week 201113
Continuous Auditing continuedContinuous Auditing Reexamined, Norman Marks, 2010Continuous risk andcontrol assurance (CRCA)is far more than anapplication of continuousauditing or monitoring; itis a top-down model thatstarts with enterprisegoals and objectives,moves on to risks to theobjectives and thecontrols required tomanage the risks, andincludes the mining ofdata that can provideindicators of risk andcontrol healthDamien Berahzer ISACA Atlanta – Geek Week 201114
Damien’s borrowed CA FrameworkCONTINUOUS ISKAssessmentAUDITORS’ CONTINUOUS AUDITINGDamien Berahzer ISACA Atlanta – Geek Week 2011ContinuousMonitoringManagement’s Process15
Team Key Stakeholders– Audit Committee– City Council Finance Exec.– Procurement IT StaffNever: It Can’t bedoneYes: What will it taketo get it done– Facilitate data access– Provide expertise that can be leveraged– Audit Instance Audit Staff– Develop CA Test– Motivated, Understands Querying, Knowledge of Programming Finance Staff (listed as main advocate first year)– Disappointing ParticipationDamien Berahzer ISACA Atlanta – Geek Week 201116
Tools (selected) SQL Developer (Develop, leverage)– PL/SQL– Data extraction via queries– Data manipulation via programming language– Free My Oracle Support (research) Oracle (develop, leverage) Discoverer Plus (leverage)Damien Berahzer ISACA Atlanta – Geek Week 201117
SQL DeveloperOracle Database Account: Read Access to all schemas, Grant Session Access,Create Access in own schemaTNS NameDamien Berahzer ISACA Atlanta – GeekWeek 201118
SQL DeveloperThese tables and views belong to the userlogged into the SQL developer Applicationand have authenticated to the Oracleinstance.Tables and Views needed for CA existunder the APPS, APPLSYS and any otherschema defined by your organizationand are found under the Other UsersFolderISACA Atlanta – GeekDamien BerahzerWeek 201119
SQL DeveloperDamien Berahzer ISACA Atlanta – GeekWeek 201120
SQL DeveloperDamien Berahzer ISACA Atlanta – GeekWeek 201121
SQL DeveloperDamien Berahzer ISACA Atlanta – GeekWeek 201122
My Oracle SupportMy Oracle Support—award winning nextgeneration support platform—uses personalizedand proactive support capabilities to helpaccelerate the business value of Oracle solutions,lower the cost of ownership, and enable fasterproblem resolution. Oracle eTRM (Oracle eBusiness Suite ElectronicTechnical Reference Manual – eTRM). According toOralce:– eTRM is a pl/sql utility that reads design informationin an Oracle database and displays its output in htmlformat.Damien Berahzer ISACA Atlanta – GeekWeek 201123
Discoverer PlusOracle Discoverer Plus is a data access tool. You useit to view the information in your company'sdatabases. The whole purpose of Discoverer is tohelp you--the business professional--view the datayou want from a database, analyze it to supportyour business decisions, and create reports to keeptrack of things.– Examine SQL statements for the reports created by ITfor departments.– This allowed quick identification of tables and viewsholding data for types of audit tests.– Created queries based off of established reports.Damien Berahzer ISACA Atlanta – Geek Week 201124
Discoverer PlusDamien Berahzer ISACA Atlanta – Geek Week 201125
What to develop as a CA Test(65 options to choose from: ask for it if you like)AreaCA mentsBlank Payee AddressPaymentsPayee Address Matches Current EmployeeJournal EntryJE per executiveJournal EntryWrite off JE greater than a pre-defined valueJournal EntryWeekend/holiday entriesProcurementTransaction splitting to bypass limitsProcurementFormer employee SSN same as vendor Tax IDProcurementdebit balancesMMControlsSegregation of dutiesHMxOtherInactive vendorsHMxPayrollAdvance leave balancesMMxProcuremntwaivers of procurement rulesHLxCashCashier voidsHLDamien Berahzer ISACA Atlanta – Geek Week 2011LHMxM26
What we selectedSODDuplicate PaymentsInactive VendorsAdvanced Sick LeavePO Approval overrideWeekend Journal Entries (Executives MakingEntries) ACH Multiple payment analysis (payments goingto the same bank account for multiple people) Damien Berahzer ISACA Atlanta – GeekWeek 201127
Segregation of Duties 1. Control policy according to which no person shouldbe given responsibility for more than one relatedfunction. For example, the person responsible forpurchasing should not also be responsible for itspayment. 2. Methods and procedures established as an internalcheck on activities through separation of (1) custody ofassets from accounting personnel, (2) authorization oftransactions from custody of associated assets, and (3)operational responsibilities from record-keepingresponsibilities.Damien Berahzer ISACA Atlanta – GeekWeek 201128
SOD MatrixxxxxDamien Berahzer ISACA Atlanta – Geek Week 201129
SOD MatrixDamien Berahzer ISACA Atlanta – Geek Week 201130
Track the risk associatedwith the SOD Conflict Open and Close Periods versus ImportJournals– This allows access to open and close journalperiods and enter journals via the import journalsfeature. There is a risk that a previous periodcould be opened and fraudulent journal entriesmade. Opening and closing periods should begiven to a limited number of users.Damien Berahzer ISACA Atlanta – Geek Week 201131
SOD Version 2 StreamlineWrite Query to Extract Responsibilities Table DataWrite Query to Extract Functions TL Table DataWrite Query to Extract Functions Table DataImport Responsibilities Table Data into MS AccessImport Functions TL Table Data into MS AccessInvest the time and utilize DIT expertise todevelop one statement that produces a listof responsibilities with the associatedfunctions. We'll have one query that can bestored in a Graphical application such asthose listed aboveImport Functions Table Data into MS AccessObtain and Format DataUse query to delete repeating dashes *---------* forImported Responsibilities Table DataUse query to delete repeating dashes *---------* forImported Functions TL Table DataUse query to delete repeating dashes *---------* forImported Functions Table DataUse Update Query to remove spaces from Application ID inthe Responsibilities Table DataCreate intermediate consolidated between Functions andFunctions TL table data functions to be tied toresponsibilities.Create intermediate Table needed by Script that hasconsolidated functions tied to responsibilities.Damien Berahzer ISACA Atlanta – Geek Week 201132
Oracle Logical nususerresponsibilitiesDamien Berahzer ISACA Atlanta – Geek Week 2011Sub menus33
SOD: Key Oracle TablesAPPS.FND USER RESP GROUPS DIRECTAPPLSYS.FND COMPILED MENU FUNCTIONSAPPLSYS.FND FORM FUNCTIONS TLAPPLSYS.FND FORM FUNCTIONSAPPLSYS.FND USERAPPLSYS.FND RESPONSIBILITYAPPLSYS.FND RESPONSIBILITY TLAPPS.PER ALL PEOPLE F PAPFAPPS.PER ALL ASSIGNMENTS FAPPS.PER ALL ASSIGNMENTS DDamien Berahzer ISACA Atlanta – Geek Week 201134
SOD: Conflict Arrays1st Function Name1st User Function Name2nd Function Name2nd User Function Name1PN APXVDMVDEnter SuppliersAP APXIIFIXOpen Interface Invoices2PN APXVDMVDEnter SuppliersAP APXTSBNKBank Transmission Setup Info3PO POXPCATNExceed Price TolerancesRCV RCVRCERCReceiptsDamien Berahzer ISACA Atlanta – Geek Week 201135
SOD: Pseudo Code(271 lines of actual code) Create a temporary table to hold data Define parallel arrays for conflict pairs Loop through each conflict pair (For Loop)– Select users with access to the first function in pair IN (Select users from list above to second conflict pair)– Insert partial record (first function results) into temptable Use temp table to obtain final results includingthe 2nd conflict pairDamien Berahzer ISACA Atlanta – Geek Week 201136
Duplicate PaymentsPattern 3: VendorNames are similar.VENDOR NAMEINVOICEIDPattern1: InvoiceNumbersPattern1: Invoicerepeat (they are identical)Numbers repeat(they are identical)INVOICENUMBERINVOICEAMOUNTPattern 2: InvoiceAmounts repeat(they are identical)AMOUNTPAIDINVOICEDATEPAYSTATUSTHIS USA LLC9124007 I00PP03572566363 01-SEP-10YTHIS USA, INC9129227 I00PP03572566363 01-SEP-10YCOURTNEY, COXY9103604 BD1PP07103158158 17-AUG-10YCOXY, COURTNEY9103625 BD1PP07103158158 17-AUG-10YDamien Berahzer ISACA Atlanta – Geek Week 201137
Duplicate Payments:Key Oracle Tables APPS.AP INVOICES ALLAPPS.HR OPERATING UNITSAPPS.PO VENDORSAPPLSYS.FND USERDamien Berahzer ISACA Atlanta – Geek Week 201138
Duplicate Payments: Pseudo Code(147 lines of actual code) Create a 2 temporary tables to hold data First Table– Select invoice details IN (Select invoice number)– IN (Select records having count invoice number and count invoiceamount greater than 1) Second Table: Select distinct invoice numberfrom table above and loop– Select invoice details where invoice IN (Select invoice number having count amount greater than1)Damien Berahzer ISACA Atlanta – Geek Week 201139
Inactive Vendors:Key Oracle Tables APPS.PO HEADERS ALLAPPS.PO VENDORSAPPS.AP INVOICES ALLDamien Berahzer ISACA Atlanta – Geek Week 201140
Inactive Vendors: Pseudo Code(106 lines of actual code) Create a 2 temporary tables to hold data First Table hold all invoices paid where thevendor ID– NOT IN (Select vendor ID with activity in the last365 days)– Order by Date Second Table: For each Distinct Vendor IDSelect first row (rownum 1) and insert intotable.Damien Berahzer ISACA Atlanta – Geek Week 201141
Inactive Vendors:what it revealed We identified 2,383 vendors with no invoiceactivity for the past 365 vendor type field blank for 63% of the recordspulled duplicate entries for vendorsDamien Berahzer ISACA Atlanta – Geek Week 201142
Oracle Alerts and Triggers Oracle Alerts: Oracle Alerts are used to monitor unusual or critical activitywithin a designated database. The flexibility of ALERTS allows a databaseadministrator the ability to monitor activities from table space sizing toactivities associated with particular applications (i.e. AP, GL, FA). Alertscan be created to monitor a process in the database and to notify aspecific individual of the status of the process. Oracle Triggers: Oracle lets you define procedures called triggers that runimplicitly when an INSERT, UPDATE, or DELETE statement is issued againstthe associated table or, in some cases, against a view, or when databasesystem actions occur. You can write triggers that fire whenever one of thefollowing operations occurs:– DML statements (INSERT, UPDATE, DELETE) on a particular table or view– DDL statements (CREATE or ALTER primarily) issued either by a particularschema/user or by any schema/user in the database– Database events, such as logon/logoff, errorsDamien Berahzer ISACA Atlanta – Geek Week 201143
Oracle Alert: Auto Approve PO(Periodic Alert)Damien Berahzer ISACA Atlanta – Geek Week 201144
Oracle Alert: Auto Approve PODamien Berahzer ISACA Atlanta – Geek Week 201145
Oracle Alert: Auto Approve PODamien Berahzer ISACA Atlanta – Geek Week 201146
Oracle Alert: Auto Approve PODamien Berahzer ISACA Atlanta – Geek Week 201147
Oracle Alert: Advanced Sick LeaveDamien Berahzer ISACA Atlanta – Geek Week 201148
AutomationSo easyDamien Berahzer ISACA Atlanta – Geek Week 201149
I promise, as anintelligent leader in theIT field to rise to thechallenge of developingand implementingcontinuous auditautomation.89%DisaeeAgrDamien Berahzer ISACA Atlanta – GeekWeek 2011gree11%1. Agree2. Disagree50
Automation Steps Identify key team members– Finance staff– IT staff– Audit staff Start SmallBuild and Test programLook for false positivesRevise, re-perform, reiterateDamien Berahzer ISACA Atlanta – Geek Week 201151
What’s nextSlide Intentionally left blankDamien Berahzer ISACA Atlanta – Geek Week 201152
The IIA's Global Technology Audit Guide (GTAG) on continuous auditing defines it as "any method used by auditors to perform audit-related activities on a more continuous or continual basis." ISACA defines CA as an approach that allows IS auditors to monitor system reliability on a continuous basis and to gather selective audit
The quality audit system is mainly classified in three different categories: i Internal Audit ii. External Audits iii. Regulatory Audit . Types Of Quality Audit. In food industries all three audit system may be used to carry out 1. Product manufacturing audit 2. Plant sanitation/GMP audit 3. Product Quality audit 4. HACCP audit
AUDIT OF DEKALB COUNTY DATA CENTER PHYSICAL SECURITY AUDIT REPORT NO. 2018-007-IT John Greene Chief Audit Executive FINAL REPORT What We Did In accordance with the Office of Independent Internal Audit's (OIIA) Annual Audit Plan, we conducted a performance audit of the DeKalb County Data Center Physical Security.
INTERNAL AUDIT Example –Internal audit report [Short Client Name] Internal Audit Report Rev. [Rev Number] STEP ONE: Audit Plan Process to Audit (Audit Scope): Audit Date(s): Lead Auditor: Audit #: Auditor(s): Site(s) to Audit: Applicable Clauses of [ISO 9001 or AS9100] S
4.1 Quality management system audit 9.2.2.2 Quality management system audit - except: organization shall audit to verify compliance with MAQMSR, 2nd Ed. 4.2 Manufacturing process audit 9.2.2.3 Manufacturing process audit 4.3 Product audit 9.2.2.4 Product audit 4.4 Internal audit plans 9.2.2.1 Internal audit programme
CHAPTER 12 Internal Audit Charters and Building the Internal Audit Function 273 12.1 Establishing an Internal Audit Function 274 12.2 Audit Charter: Audit Committee and Management Authority 274 12.3 Building the Internal Audit Staff 275 (a) Role of the CAE 277 (b) Internal Audit Management Responsibilities 278 (c) Internal Audit Staff .
Internal Audit Boot Camp Session 2: Phases of an Audit Program . IA Boot Camp 03/17/21 National Indian Gaming Commission Page 17 of 26 . It is important to understand and include audit steps within your audit program. Audit steps can be updated and created during the planning phase. Audit steps provide the auditor with the proper guidance to
An internal audit must be planned in advance and a schedule created for each internal audit process. The Management Meetings can be used to plan the audit and to record the results of each internal audit process. When planning the internal audit, consideration to following criteria shall be included when planning an internal audit:
writing requires a clear line of thought, use of evidence or examples to illustrate your reflections, and an analytical approach. You are aiming to strike a balance between your personal perspective, and the requirements of good academic practice and rigorous thinking. This means: developing a perspective, or line of reasoning
