Transcript DONE-07, Auditing: Do You Care Who Did What, When, Where and

DONE-07
Auditing: Do You Care
Who Did What, When,
Where and How?
(OpenEdge™ 10.1)
Anthony Swindells
Progress Fellow
Agenda Slide






Why auditing?
Auditing in OpenEdge™ overview
A sneak preview…
Using auditing in OpenEdge
Auditing best practices
Future thinking
2 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Under Development


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.
D I S C L A I M E R
3 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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”
4 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Core Service: Auditing
Mission Statement
“Provide an auditing framework that can
supply an uninterrupted
trail of an application client’s access
to its operations and data”
5 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Customer Key Drivers 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 efficient storage
of audit data
4. Consistency across SQL, 4GL and database utilities
6 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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
21 CFR Part 11
Gramm-Leach-Bliley
Act
Basel II
HIPPA
EU Data Protection
Directive
7 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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 302 — Certifying Results
CEO and CFO to certify the
existence of controls and signoff on the organization’s
financial statements
Section 404 — Internal
Controls
annual evaluation and
documentation of the
internal controls and
procedures in place to
produce financial
information
Section 409 — Reporting
8 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Regulatory Compliance:
Example 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
9 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Penalties for Non-Compliance:
Sarbanes–Oxley 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
So, compliant apps help keep
customers out of jail…
10 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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?
11 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
“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
12 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
“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
13 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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 nonreputable
– Prove audit policy and data has not been
tampered with
14 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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
15 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Introducing…
Auditing in OpenEdge™
Surviving the Auditor!
16 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
OpenEdge Database
Schema-Trigger Based Auditing
Archive
Daemon
Offline
Audit
Data
17 © 2005 Progress Software Corporation
Report
Manager
Application
Code
Audit Event
Manager
(schema
triggers)
Audit Data
Manager
Security Manager
Policy
Data
Application
Code
SQL Client
App DB
Audit
Data
Application
Data
Audit Policy
Tools
Archive
Manager
4GL Client
API
Audit Policy
Manager
Archive DB
Audit
Data
Audit
Report
DONE-07, Auditing: Who Did What, When, Where, How?
Auditing Architecture Overview
Offline
Audit
Data
18 © 2005 Progress Software Corporation
Policy
Data
Audit
Data
Application
Data
Security Subsystem
API
Archive
Daemon
Reporting
Subsystem
Application
Code
App DB
Audit Data
Subsystem
Archiving
Subsystem
SQL Client
Application
Code
Database
Audit Policy
Tools (APMT)
Audit Event
Subsystem
Internal
Open Tools
Audit Policy
Subsystem
Application
4GL Client
DB Tools &
Utilities
Archive DB
Audit
Data
Audit
Report
DONE-07, Auditing: Who Did What, When, Where, How?
Audit Policy MetaSchema
_aud-audit-policy
_Audit-policy-guid
includes
Audit
Policy
_aud-event-policy
_Audit-policy-guid (FK)
_File-Name (IE1.1)
_Owner (IE1.2)
Event
Policy
_Event-level
_Event-criteria
_Audit-create-level
_Audit-create-criteria
_Audit-update-level
_Audit-update-criteria
_Audit-delete-level
_Audit-delete-criteria
_Audit-read-level
_Audit-read-criteria
_Create-event-id (FK) (IE2.1)
_Update-event-id (FK) (IE3.1)
_Delete-event-id (FK) (IE4.1)
_Read-event-id (FK) (IE5.1)
File
Policy
is controlled by
_Event-id
record creates on
record updates on
_Event-type (IE1.1)
_Event-name (IE1.2)
_Event-description (IE2.1)
record reads on
record deletes on
Audit
Event
Control by table
Policy/ CUD
Data
operation
_aud-file-policy
_Audit-policy-guid (FK)
_Event-id (FK) (IE1.1)
_aud-event
includes
Internal & application
defined audit events
19 © 2005 Progress Software Corporation
includes
Audit
Data
Application
Data
Control by
event Id
Multiple active policies
_Audit-policy-name (AK1.1)
_Audit-policy-description (IE1.1)
_Audit-data-security-level
_Audit-custom-detail-level
_Audit-policy-active (IE2.1)
_aud-field-policy
_Audit-policy-guid (FK)
_File-Name (FK) (IE1.1)
_Owner (FK) (IE1.2)
_Field-Name (IE1.3)
Field
Policy
_Audit-create-level
_Audit-update-level
_Audit-delete-level
_Audit-read-level
_Audit-identifying-field
Override individual fields
DONE-07, Auditing: Who Did What, When, Where, How?
Audit Policy MetaSchema
_aud-audit-policy
_Audit-policy-guid
Control by
event id
includes
_Audit-policy-name (AK1.1)
_Audit-policy-description (IE1.1)
_Audit-data-security-level
_Audit-custom-detail-level
_Audit-policy-active (IE2.1)
_aud-event-policy
includes
Control by table / CUD
operation
_aud-file-policy
_Audit-policy-guid (FK)
_Event-id (FK) (IE1.1)
_Audit-policy-guid (FK)
_File-Name (IE1.1)
_Owner (IE1.2)
_Event-level
_Event-criteria
is controlled by
_aud-event
Multiple active policies
_Event-id
record creates on
record updates on
_Event-type (IE1.1)
_Event-name (IE1.2)
_Event-description (IE2.1)
record reads on
record deletes on
Internal & application
defined audit events
20 © 2005 Progress Software Corporation
_Audit-create-level
_Audit-create-criteria
_Audit-update-level
_Audit-update-criteria
_Audit-delete-level
_Audit-delete-criteria
_Audit-read-level
_Audit-read-criteria
_Create-event-id (FK) (IE2.1)
_Update-event-id (FK) (IE3.1)
_Delete-event-id (FK) (IE4.1)
_Read-event-id (FK) (IE5.1)
includes
_aud-field-policy
_Audit-policy-guid (FK)
_File-Name (FK) (IE1.1)
_Owner (FK) (IE1.2)
_Field-Name (IE1.3)
_Audit-create-level
_Audit-update-level
_Audit-delete-level
_Audit-read-level
_Audit-identifying-field
Override individual fields
DONE-07, Auditing: Who Did What, When, Where, How?
Audit Data MetaSchema
_client-session
_Client-name
_User-id (IE1.1)
_Authentication-date-time (IE2.1)
_Server-uuid
_Authentication-domain-type
_Authentication-domain-name
_Db-guid (FK) (IE3.1)
_Session-custom-detail
_Audit-data-security-level
_Data-seal
Client
Session
Configurable automated
audit data with optional
context & grouping
Policy
Data
Audit
Data
created
Application
Data
Record client session
information
_Client-session-uuid
_aud-audit-data
_Audit-data-guid
_Database-connection-id (IE1.1)
_Client-session-uuid (FK) (IE1.2)
_User-id (IE2.1)
_Audit-date-time (IE5.1)
_Audit-event-group (IE3.1)
_Db-guid (FK) (IE3.2)
_Transaction-id (IE3.3)
_Transaction-sequence (IE3.4)
_Event-id (FK) (IE4.1)
_Event-context (IE6.1)
_Application-context-id (IE7.1)
_Event-detail
_Audit-custom-detail
_Audit-data-security-level
_Data-seal
Audit
Data
consists of
_aud-audit-data-value
_Audit-data-guid (FK)
_Field-name (IE1.1)
_Continuation-sequence
Audit
Data
Values
_Data-type-code
_Old-string-value
_New-string-value
_Old-blob-value
_New-blob-value
_Old-clob-value
_New-clob-value
_Audit-data-security-level
_Data-seal
21 © 2005 Progress Software Corporation
Optional old/new
value recording
DONE-07, Auditing: Who Did What, When, Where, How?
Audit Data MetaSchema
_client-session
Record client session
information
_Client-session-uuid
_Client-name
_User-id (IE1.1)
_Authentication-date-time (IE2.1)
_Server-uuid
_Authentication-domain-type
_Authentication-domain-name
_Db-guid (FK) (IE3.1)
_Session-custom-detail
_Audit-data-security-level
_Data-seal
Configurable automated
audit data with optional
context & grouping
created
_aud-audit-data
_Audit-data-guid
_Database-connection-id (IE1.1)
_Client-session-uuid (FK) (IE1.2)
_User-id (IE2.1)
_Audit-date-time (IE5.1)
_Audit-event-group (IE3.1)
_Db-guid (FK) (IE3.2)
_Transaction-id (IE3.3)
_Transaction-sequence (IE3.4)
_Event-id (FK) (IE4.1)
_Event-context (IE6.1)
_Application-context-id (IE7.1)
_Event-detail
_Audit-custom-detail
_Audit-data-security-level
_Data-seal
consists of
_aud-audit-data-value
_Audit-data-guid (FK)
_Field-name (IE1.1)
_Continuation-sequence
_Data-type-code
_Old-string-value
_New-string-value
_Old-blob-value
_New-blob-value
_Old-clob-value
_New-clob-value
_Audit-data-security-level
_Data-seal
22 © 2005 Progress Software Corporation
Optional old/new
value recording
DONE-07, Auditing: Who Did What, When, Where, How?
Database events

Record level events
Database
What gets Audited?
Audit Event
Subsystem
– Create event
– Update event
– Delete event

No need for schema triggers
– controlled through file / field policy
23 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Internal events






Authentication (login)
Database connections
Schema changes
Audit policy administration
Security administration
Database utilities
Internal
What gets Audited?
Audit Event
Subsystem
– Start, stop, backup, restore, dump, load,
copy, etc.

Audit archiving
24 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Application events



Application context
Audit event groups
Application defined events (ID > 32000)
Application
What gets Audited?
Audit Event
Subsystem
– 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
25 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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
26 © 2005 Progress Software Corporation
Security Subsystem

DONE-07, Auditing: Who Did What, When, Where, How?

Trusted authentication systems / domains
– Assert verified identity of real
application user
– not dependent on _user records

No blank user access to audit data!
27 © 2005 Progress Software Corporation
Security Subsystem
Recording the Real User
DONE-07, Auditing: Who Did What, When, Where, How?
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
28 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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
29 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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
30 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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
31 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Auditing Demo…
32 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Agenda Slide






Why auditing?
Auditing in OpenEdge overview
A sneak preview…
Using auditing in OpenEdge™
Auditing best practices
Future thinking
33 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Auditing in OpenEdge™
Getting Started
Enabling auditing for internal events


Upgrade client / database to OpenEdge™ 10.1a
Enable auditing
Proutil dbname –C enableauditing area area1
[indexarea Area2] [deactivateidx]



Connect to database as the DBA
Set up database security key via data admin
Run APMT to load / enable shipped policies
34 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Auditing in OpenEdge
Getting 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
35 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Auditing in OpenEdge
Getting 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)
36 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Auditing in OpenEdge
Getting 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
37 © 2005 Progress Software Corporation
Audited
Audited
DONE-07, Auditing: Who Did What, When, Where, How?
Auditing in OpenEdge
Getting 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(auditevent-ctx[,event-detail[,user-data]])
…
id = AUDIT-CONTROL:LOG-AUDIT-EVENT (event-id,
event-context[, event-detail[, user-data]])
…
AUDIT-CONTROL:CLEAR-APPL-CONTEXT
38 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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)
39 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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
40 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
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!
41 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Don’t Miss These BOFs…
 Common Business Services Birds of a
Feather
Tue 6:00pm
 Auditing Birds of a Feather
Wed 8:00am
42 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Questions?
43 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
Thank you for
your time!
44 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?
45 © 2005 Progress Software Corporation
DONE-07, Auditing: Who Did What, When, Where, How?