DONE-07 Auditing: Do You Care Who Did What, When, Where and How? (OpenEdge

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

3
Under Development

• 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.

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

5
Core Service AuditingMission Statement

• Provide an auditing framework that can
• supply an uninterrupted
• trail of an application clients access
• to its operations and data

6
Customer Key Drivers for Auditing
1. Compliance with international Government
regulations, e.g. Sarbanes-Oxley Act, CFR Part
11, HIPAA, European Unions Annex 11, European
Union Data Protection Directive, etc.

• 2. Secure auditing to support non-repudiation of
  audit data
• 3. High performance, scalable and efficient
  storage of audit data

4. Consistency across SQL, 4GL and database
utilities
7
The Regulatory Compliance Challenge
Organizations today face a growing number of
regulations that mandate the accuracy and
reliability of information not just SOX!
Sarbanes-Oxley Act
8
Regulatory ComplianceSarbanes-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 organizations financial statements
Section 409 Reporting
9
Regulatory ComplianceExample Auditor Objectives
Example auditor objectives Logging and reporting
Determine who modified the database schema and when List all schema changes by date by user
Determine who has access to particular systems List of resources and the users who are authorized to access them
Ensure user accountability For a particular user, list their actions over a period of time
Ensure terminated employees access rights are terminated in a timely manner For users who are terminated, list access rights and when they were revoked
10
Penalties for Non-ComplianceSarbanesOxley Law!

• 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

11
Compliance doesnt 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?

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

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

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

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

16
IntroducingAuditing in OpenEdge
Surviving the Auditor!
17
OpenEdge Database Schema-Trigger Based Auditing

18
Auditing Architecture Overview


Audit Policy Subsystem
API

App DB
Policy Data
Audit Event Subsystem
Audit Data Subsystem
Application Data
Audit Data
Security Subsystem
Database
Internal
Application

Archive DB

Application Code
Archiving Subsystem
Reporting Subsystem
Archive Daemon
SQL Client
Audit Data
OfflineAuditData
AuditReport
19
Audit Policy MetaSchema
Multiple active policies
Control by event Id
Audit Policy
Control by table / CUD operation
Policy Data
File Policy
Event Policy
Field Policy
Audit Event
Override individual fields
Internal application defined audit events
20
Audit Policy MetaSchema
Multiple active policies
Control by event id
Control by table / CUD operation
Override individual fields
Internal application defined audit events
21
Audit Data MetaSchema
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
22
Audit Data MetaSchema
Record client session information
Configurable automated audit data with optional
context grouping
Optional old/new value recording
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

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

25
What gets Audited?
Audit Event Subsystem
Application events
Application

• Application context
• Audit event groups
• Application defined events (ID gt 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

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
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
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

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

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

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

32
Auditing Demo
33
Agenda Slide

• Why auditing?
• Auditing in OpenEdge overview
• A sneak preview
• Using auditing in OpenEdge
• Auditing best practices
• Future thinking

34
Auditing in OpenEdgeGetting 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 APMT to load / enable shipped policies

Proutil dbname C enableauditing area area1
indexarea Area2 deactivateidx
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

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-POLICYLOAD-DOMAINS(db)
37
Auditing in OpenEdgeGetting Started
Asserting the real application user

• Modify 4GL application authentication code

CREATE CLIENT-PRINCIPAL hCP hCpUSER-ID
aswindel hCpDOMAIN-NAME progress result
hCPSEAL(pass phrase) SECURITY-POLICYSET-CLIE
NT(hCP) hCPLOGOUT DELETE hCP
Audited
Audited
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-CONTROLSET-APPL-CONTEXT(audit-even
t-ctx,event-detail,user-data) id
AUDIT-CONTROLLOG-AUDIT-EVENT (event-id,
event-context, event-detail, user-data) AUDI
T-CONTROLCLEAR-APPL-CONTEXT
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)

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

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!
42
Dont Miss These BOFs

• Common Business Services Birds of a Feather
• Tue 600pm
• Auditing Birds of a Feather
• Wed 800am

43
Questions?
44
Thank you for your time!
45
(No Transcript)