OpenEdge Auditing Easy as Jean-Pierre Mathieu, P.Eng. Director of IT, Lumec Inc, division of Philips Session 140

© 2009 Progress Software Corporation. All rights reserved. Why Audit? Financial regulations SOX (Sarbanes-Oxley) ICS (Internal Control Standards) Other North American regulations NAFTA (North American Free Trade Agreement) C-TPAT (Customs-Trade Partnership Against Terrorism) Complex program debugging 2

© 2009 Progress Software Corporation. All rights reserved. What Are The Mandatory Features of Auditing? Nonrepudiation by the user Separation of duties Sealing of the audit data Flexibility For the programmer to add custom events and context information For the administrator to select what needs to be audited Archiving capacity Low impact on database transaction performance 3

© 2009 Progress Software Corporation. All rights reserved. Choices Made for This Case Study A user audit record has in fact four physical OpenEdge audit records and one or more value records Main: User, time, event, table, record key Group: ERP function, computer, custom info Context: Entity(company), tab, custom info Audited: Auditor, time, comments Data values: (one per field) Implementation of the code at dm2\custom\ datacustom.p level All SmartData Objects inherits this object Thus, there is no need to modify existing programs to create database triggered audit records 4

© 2009 Progress Software Corporation. All rights reserved. Choices Made for This Case Study Ability for the programmer to add custom group and context information if needed Have a separate audit database Use the _user table to authenticate user Use predefined OpenEdge auditing events whenever possible Build our own audit data viewing and reporting program 5

© 2009 Progress Software Corporation. All rights reserved. Sample of The Final Result 6 Filter Viewing tabs Record browser Main data Data value Group Context Auditor Push from Production to Audit

© 2009 Progress Software Corporation. All rights reserved. How to Implement Audit in OpenEdge? Audit data in your production database can grow very large, it is recommended that you create a dedicated audit database Create and start auditing in the Audit database Create the Audit DB using Data Administration or PRODB Execute the following to add a set of auditing tables to your Audit database by proutil Audit -C enableauditing area "Table area" indexarea "Indexarea" The most important tables are _aud-audit-data and its child table _aud- audit-data-value to store the triggering event, modified tables and fields, user, date-time and details. Also, a set of tables are included to store the auditing policies 7 Production Audit

© 2009 Progress Software Corporation. All rights reserved. How to Implement Audit in OpenEdge? Add special users to the Audit database Create the _Archiver_ and Compiler users in your Audit database: Using the Data Administration/Admin/Security/Edit Audit Permissions (created at the time you started auditing), grant the following permissions to Compiler: -Application Audit Event Inserter: can generate application audit events -Audit Administrator: can create,update, and delete audit policies and read audit data -Audit Data Archiver : can archive or load audit data -Audit Data Reporter: can read the audit data -This Compiler user can now compile any program dealing with audit tables 8

© 2009 Progress Software Corporation. All rights reserved. How to Implement Audit in OpenEdge? Add special users to the Audit database Grant Audit Data Archiver to _Archiver_ Note that an Audit Data Archiver can delete audit data from the database 9

© 2009 Progress Software Corporation. All rights reserved. How to Implement Audit in OpenEdge? Start auditing in your Production database Execute the following to add a set of auditing tables to your production (here called Production) database. Note you will have to stop your database to do so proutil production -C enableauditing area "Table area" indexarea "Indexarea" Add the _Archiver_ user in your production database Grant Audit Data Archiver to _Archiver_ This special user will push the audit data to the Audit database No Compiler user is required for this database since the Viewer program only deal with the Audit database Using the Audit Policy Maintenance Tool, add the table/fields/events that shall be audited 10

© 2009 Progress Software Corporation. All rights reserved. How to Implement Audit in OpenEdge? Pushing audit data to the Audit database To archive audit data from Production to a file proutil Production -C auditarchive uptodatetime -checkseal -nodelete - directory xxxxxx -userid yyyyyy -password zzzzzzz To load archived data in Audit database proutil Audit -C auditload xxxxxx\production.abd -checkseal -userid yyyyyy -password zzzzzz If the load was successful, delete data in Production database proutil Production -C auditarchive uptodatetime -directory nul: -userid yyyyyy -password zzzzzzz Where uptodatetime is a date-time meaning to archive everything up to this date xxxxxx is the folder were to write the archive file yyyyyy is a user Id having Audit Data Archiver permission on both DBs zzzzzz is the corresponding password of user yyyyyy 11

© 2009 Progress Software Corporation. All rights reserved. How to Implement Audit Context in OpenEdge? Sample code extracted from beginTransactionValidate In adm2\custom\datacustom.p Check if the incoming database transaction will trigger an audit event cRowMod contains ADM2 code to identify the Smart Data Object transaction FOR EACH Production._aud-file-policy WHERE Production._aud-file- policy._File-name = cTableName NO-LOCK: lThisTableIsAudited = (CAN-DO("A,C":U,cRowMod) AND Production._aud-file-policy:_Audit-create-level > 0) OR (cRowMod = "U":U AND Production._aud-file-policy._Audit- update-level > 0) OR (cRowMod = "D":U AND Production._aud-file-policy_Audit-delete- level > 0). IF lThisTableIsAudited THEN LEAVE. END. /* FOR EACH Production._aud-file-policy …*/ 12

© 2009 Progress Software Corporation. All rights reserved. How to Implement Audit Context in OpenEdge? Sample code (continued) Do a similar loop for Production._aud-field-policy... Create the Group and Context records only if there is an incoming event IF lThisTableIsAudited THEN DO: AUDIT-CONTROL:BEGIN-EVENT- GROUP(cFunct,cDescr,cGroupCustom). AUDIT-CONTROL:SET-APPL- CONTEXT(cEntity,cDetail,cContextCustom). END. 13

© 2009 Progress Software Corporation. All rights reserved. How to Query The Four Audit Records of This Case Study? Querying the audit records EACH Audit._aud-audit-data NO-LOCK, FIRST Group_aud-audit-data WHERE Group_aud-audit- data._Audit-data-guid = Audit._aud-audit-data._Audit- event-group NO-LOCK, FIRST Context_aud-audit-data WHERE Context_aud-audit- data._Audit-data-guid = Audit._aud-audit- data._Application-context-id NO-LOCK, FIRST Audited_aud-audit-data WHERE COMPARE(Audited_aud- audit-data._Event-context, "=", _aud-audit-data._Audit- data-guid, "CASE-INSENSITIVE") AND Audited_aud-audit- data._Event-id = NO-LOCK, FIRST audit._aud-event WHERE audit._aud-event._Event-id = Audit._aud-audit-data._Event-id NO-LOCK, EACH audit._aud-audit-data-value OF Audit._aud-audit-data OUTER-JOIN NO-LOCK INDEXED-REPOSITION 14

© 2009 Progress Software Corporation. All rights reserved. How to Query The Four Audit Records of This Case Study? Querying the audit records (continued) Group, Context and Audited are buffers of Audit._aud-audit-data. Note the links between The Group buffer record Id and the _Audit-event-group field The Context buffer record Id and the _Application-context field The Audited buffer _Event-context field and the main record Id The custom event linked to audited records 15

© 2009 Progress Software Corporation. All rights reserved. Available Tools (OpenEdge Documentation) 16

© 2009 Progress Software Corporation. All rights reserved. Available Tools (Audit Permissions) 17

© 2009 Progress Software Corporation. All rights reserved. Available Tools (Audit Policy Maintenance) 18

© 2009 Progress Software Corporation. All rights reserved. Available Tools (Event Policy Maintenance) 19

© 2009 Progress Software Corporation. All rights reserved. Issues and Limitations Encountered During Implementation AppBuilder had to be customized to deal with tables beginning with an underscore At first, we thought the Group and Context records would be created only when a database transaction was initiated The policy tables are readable only by a user account having the Audit Administrator permission. That means the policy data must be read in a temp-table using this special account and then let the user log in Auditing filters like _Audit-file-policy._Audit-create/update/delete- criteria are part of the audit tables but not supported yet The audit tables are frozen. Even indexes cannot be added to their structure Audit data is not encrypted, but be in a future release 20

OpenEdge Auditing Easy as Jean-Pierre Mathieu, P.Eng. Director of IT, Lumec Inc, division of Philips Session 140