Auditing: Who Did What, When, Where, How?

1. DONE-07Auditing: Do You Care Who Did What, When, Where and How? (OpenEdge™ 10.1) Anthony Swindells Progress Fellow

2. Agenda Slide • Why auditing? • Auditing in OpenEdge™ overview • A sneak preview… • Using auditing in OpenEdge • Auditing best practices • Future thinking DONE-07, Auditing: Who Did What, When, Where, How?

3. Under Development D I S C L A I M E R D I S C L A I M E R • This talk includes information about potential future products and/or product enhancements. • What I am going to say reflects our current thinking, but the information contained herein is preliminary and subject to change. Any future products we ultimately deliver may be materially different from what is described here. DONE-07, Auditing: Who Did What, When, Where, How?

4. Core Business Services Vision Statement “Provide a comprehensive set of common business services that provide the core feature support of a modern SOA based application modeled on the OpenEdge Reference Architecture” DONE-07, Auditing: Who Did What, When, Where, How?

5. Core Service: AuditingMission Statement “Provide an auditing framework that can supply an uninterrupted trail of an application client’s access to its operations and data” DONE-07, Auditing: Who Did What, When, Where, How?

6. Customer KeyDrivers for Auditing 1. Compliance with international Government regulations, e.g. Sarbanes-Oxley Act, CFR Part 11, HIPAA, European Union’s Annex 11, European Union Data Protection Directive, etc. • 2. Secure auditing to support non-repudiation of audit data • 3. High performance, scalable and efficientstorage of audit data 4. Consistency across SQL, 4GL and database utilities DONE-07, Auditing: Who Did What, When, Where, How?

7. The Regulatory Compliance Challenge 21 CFR Part 11 Gramm-Leach-Bliley Act Basel II HIPPA EU Data Protection Directive Organizations today face a growing number of regulations that mandate the accuracy and reliability of information – not just SOX! Sarbanes-Oxley Act DONE-07, Auditing: Who Did What, When, Where, How?

8. Regulatory Compliance:Sarbanes-Oxley (SOX) Sarbanes-Oxley Act What is it? • Enacted July 30, 2002 • Designed to protect investors • Senior management and advisors personally accountable • Financial information must remain confidential and privileged • Data integrity and quality are key Section 404 — Internal Controls annual evaluation and documentation of the internal controls and procedures in place to produce financial information Section 302 — Certifying Results CEO and CFO to certify the existence of controls and sign-off on the organization’s financial statements Section 409 — Reporting DONE-07, Auditing: Who Did What, When, Where, How?

9. Regulatory Compliance:Example Auditor Objectives DONE-07, Auditing: Who Did What, When, Where, How?

10. Penalties for Non-Compliance:Sarbanes–Oxley Law! So, compliant apps help keep customers out of jail… • The law includes penalties of up to 20 years in prison for chief executives and chief finance officers and fines of up to $5m (£2.5m) per violation, per person DONE-07, Auditing: Who Did What, When, Where, How?

11. Compliance doesn’t impact me…? Think again! • Do you supply accounting system software? • Do you supply software for the healthcare industry? • Do you provide solutions to the government? • Do your applications contain confidential data? • Might somebody in the state of California buy your application? • Have you any growth aspirations? DONE-07, Auditing: Who Did What, When, Where, How?

12. “The Joy of SOX” Think about the opportunities! • Competitive advantage • Places a  in the compliance box • Enhanced functionality sells apps • Security • Audit trails • BI reporting • New product / service offerings • You can tell tales and not got into trouble • Protects whistleblowers DONE-07, Auditing: Who Did What, When, Where, How?

13. “The Joy of Red SOX” Think about the opportunities! • Competitive advantage • Places a  in the compliance box • Enhanced functionality sells apps • Security • Audit trails • BI reporting • New product / service offerings • You can tell tales and not got into trouble • Protects whistleblowers DONE-07, Auditing: Who Did What, When, Where, How?

14. Secure Auditing is Key to Compliance • Audit trails can tell you who did what, when, where and how • Must reflect the verifiable identify of the real application user • Must be complete, accurate and non-reputable • Prove audit policy and data has not been tampered with DONE-07, Auditing: Who Did What, When, Where, How?

15. Auditing Has a Cost! • Audit trails can generate large volumes of data – quickly! • Audit trails always add overhead • The more you audit – the bigger the performance overhead! • Must audit all methods of access to the application and data • Compromises integration otherwise DONE-07, Auditing: Who Did What, When, Where, How?

16. Introducing…Auditing in OpenEdge™ Surviving the Auditor! DONE-07, Auditing: Who Did What, When, Where, How?

17. OpenEdge Database Schema-Trigger Based Auditing Audit Policy Tools Audit Policy Manager API App DB Policy Data Audit Event Manager (schema triggers) Audit Data Manager 4GL Client Application Data Application Code Audit Data Security Manager Archive DB Application Code Archive Manager Report Manager Archive Daemon SQL Client Audit Data OfflineAuditData AuditReport DONE-07, Auditing: Who Did What, When, Where, How?

18. Auditing Architecture Overview DB Tools & Utilities Open Tools 4GL Client Audit Policy Tools (APMT) Application Code Audit Policy Subsystem API App DB Policy Data Audit Event Subsystem Audit Data Subsystem Application Data Audit Data Security Subsystem Application Internal Database Archive DB Application Code Archiving Subsystem Reporting Subsystem Archive Daemon SQL Client Audit Data OfflineAuditData AuditReport DONE-07, Auditing: Who Did What, When, Where, How?

19. Audit Policy MetaSchema Application Data Audit Data Multiple active policies Control by event Id Audit Policy Control by table / CUD operation Policy Data Event Policy File Policy Field Policy Audit Event Override individual fields Internal & application defined audit events DONE-07, Auditing: Who Did What, When, Where, How?

20. Audit Policy MetaSchema Multiple active policies Control by event id Control by table / CUD operation Override individual fields Internal & application defined audit events DONE-07, Auditing: Who Did What, When, Where, How?

21. Audit Data MetaSchema Policy Data Application Data Record client session information Client Session Configurable automated audit data with optional context & grouping Audit Data Audit Data Audit Data Values Optional old/new value recording DONE-07, Auditing: Who Did What, When, Where, How?

22. Audit Data MetaSchema Record client session information Configurable automated audit data with optional context & grouping Optional old/new value recording DONE-07, Auditing: Who Did What, When, Where, How?

23. What gets Audited? Audit Event Subsystem Database events Database • Record level events • Create event • Update event • Delete event • No need for schema triggers • controlled through file / field policy DONE-07, Auditing: Who Did What, When, Where, How?

24. What gets Audited? Audit Event Subsystem Internal events Internal • Authentication (login) • Database connections • Schema changes • Audit policy administration • Security administration • Database utilities • Start, stop, backup, restore, dump, load, copy, etc. • Audit archiving DONE-07, Auditing: Who Did What, When, Where, How?

25. What gets Audited? Audit Event Subsystem Application events Application • Application context • Audit event groups • Application defined events (ID > 32000) • For events with no corresponding database operation • Fully control granularity and detail, e.g. 1 audit record for dispatch of an order • Can be used for read auditing • Group into ranges to simplify reporting DONE-07, Auditing: Who Did What, When, Where, How?

26. Securing Audit Data • Separation of duty • audit administrator • Audit archiver • No updates to audit data • Prevents deletion of events, e.g. archive • Audit data is sealed to prevent tampering • Secured administration tools and utilities Security Subsystem DONE-07, Auditing: Who Did What, When, Where, How?

27. Recording the Real User • Trusted authentication systems / domains • Assert verified identity of real application user • not dependent on _user records • No blank user access to audit data! Security Subsystem DONE-07, Auditing: Who Did What, When, Where, How?

28. Managing Audit Data DB Tools & Utilities Audit Policy Tools (APMT) • PROUTIL commands to enable auditing • Unique database ID (GUID) and description • Audit Policy Maintenance Tool (APMT) • Open API • Securely deploy policies (via text dump or XML) • Secure dump/load options for audit data • Database options • Use application id for auditing • Trust application domain registry • Record authenticated client session DONE-07, Auditing: Who Did What, When, Where, How?

29. Archiving Audit Data Archiving Subsystem • Auditing can generate lots of data – fast! • Consider 2 billion row limit • Move audit data from short term to long term storage • Delete audit data no longer required • Fast audit archive (binary) • Select by date range • Output to bit bucket • Output to binary file • Ability to write custom archiver DONE-07, Auditing: Who Did What, When, Where, How?

30. Querying Audit Data Reporting Subsystem • Only users granted read permission • (Except SQL for Open Edge 10.1a) • Exposed as standard database tables • Requires knowledge of schema • Both application schema and metaschema • What are the identifying fields? • How is the context formatted? (Varies by event id) • Audit data searchable by • User id, event id, date, context, transaction, audit group, DB connection, client session • Beware dirty reads • Beware American format • Sample reports supplied DONE-07, Auditing: Who Did What, When, Where, How?

31. Auditing in OpenEdge™ Key Value-Add Why use it in place of own solution? • Common built-in auditing for both SQL/4GL clients • Flexible audit policy management • Secure audit data, policy and utilities • Separation of duty • Purposed audit permissions • Verified user identity • Secure utilities and sealed data • Internal audit events (utilities, schema changes, etc.) • Performance, performance, performance • High performance archiving – for enterprise only • Multi-platform DONE-07, Auditing: Who Did What, When, Where, How?

32. Auditing Demo… DONE-07, Auditing: Who Did What, When, Where, How?

33. Agenda Slide • Why auditing? • Auditing in OpenEdge overview • A sneak preview… • Using auditing in OpenEdge™ • Auditing best practices • Future thinking DONE-07, Auditing: Who Did What, When, Where, How?

34. Auditing in OpenEdge™Getting Started Enabling auditing for internal events • Upgrade client / database to OpenEdge™ 10.1a • Enable auditing • Connect to database as the DBA • Set up database security key via data admin • Run APMTto load / enable shipped policies Proutil dbname –C enableauditing area area1 [indexarea Area2] [deactivateidx] DONE-07, Auditing: Who Did What, When, Where, How?

35. Auditing in OpenEdgeGetting Started Enabling auditing for database events • Run APMT • Add new policies for application schema • Configure tables / fields to audit • Enable policies • Run application as normal • Query audit data via sample or custom reports DONE-07, Auditing: Who Did What, When, Where, How?

36. Auditing in OpenEdgeGetting Started Asserting the real application user • Modify database options to use application user id for auditing • Setup authentication system / domains • Add application startup code to load trusted domains SECURITY-POLICY:LOAD-DOMAINS(db) DONE-07, Auditing: Who Did What, When, Where, How?

37. Auditing in OpenEdgeGetting Started Asserting the real application user • Modify 4GL application authentication code CREATE CLIENT-PRINCIPAL hCP hCp:USER-ID = “aswindel” hCp:DOMAIN-NAME = “progress” … result = hCP:SEAL(“pass phrase”) SECURITY-POLICY:SET-CLIENT(hCP) … hCP:LOGOUT DELETE hCP Audited Audited DONE-07, Auditing: Who Did What, When, Where, How?

38. Auditing in OpenEdgeGetting Started Supplying context / application events • Set / clear context at appropriate places in application • Add code to record application events ctx-id = AUDIT-CONTROL:SET-APPL-CONTEXT(audit-event-ctx[,event-detail[,user-data]]) … id = AUDIT-CONTROL:LOG-AUDIT-EVENT (event-id, event-context[, event-detail[, user-data]]) … AUDIT-CONTROL:CLEAR-APPL-CONTEXT DONE-07, Auditing: Who Did What, When, Where, How?

39. Auditing Best Practices • Consider application database as short term storage for audit data • Do not enable audit indexes • Use separate storage area for audit data • Archive often! • Use purposed database for audit archive / reporting • Only audit what is absolutely necessary – tune with APMT • Plan for reporting • Group event ids into ranges • Structure context consistently • Leverage audit event groups • Coding style even more important (assigns, record scope, transaction scope) DONE-07, Auditing: Who Did What, When, Where, How?

40. OpenEdge Auditing Futures (beyond R10.1a) Current thinking • Selective audit policy for specific data • Read auditing • More standard reporting • Direct archiving to OpenEdge database • SQL audit policy administration • Native execution context • IDE integration DONE-07, Auditing: Who Did What, When, Where, How?

41. In Summary • Govt. regulations & security are a concern and an opportunity • Start preparing for them NOW! • Upgrade to OpenEdge™ 10.1 when it ships • OpenEdge auditing helps you survive the auditor • OpenEdge auditing adds value to your existing applications for minimal effort Avoid Jail! DONE-07, Auditing: Who Did What, When, Where, How?

42. Don’t Miss These BOFs… • Common Business Services Birds of a Feather Tue 6:00pm • Auditing Birds of a Feather Wed 8:00am DONE-07, Auditing: Who Did What, When, Where, How?

43. Questions? DONE-07, Auditing: Who Did What, When, Where, How?

44. Thank you for your time! DONE-07, Auditing: Who Did What, When, Where, How?

45. DONE-07, Auditing: Who Did What, When, Where, How?