MOVE-9: Audit enable your Application the Easy Way

1
MOVE-9 Audit enable your Applicationthe Easy Way
Anthony D Swindells
Engineering Fellow
2
Agenda

• OpenEdge Auditing Overview
• Integrating Auditing into your Application
• Coding for Performance
• Migrating your existing Audit Data

This presentation includes annotations with
additional complementary information
3
Introducing OpenEdge 10.1A Auditing
Who did What, When, Where and How?
End-Users

• Only audit what is necessary
• Database CUD
• Internal events
• Database utilities
• Application events
• Relationally stored for reporting
• Seamless access across the ABL and SQL

Application Security
OpenEdge Auditing
Database
Guaranteednon-repudiable audit trail
PrivilegedUsers
4
From Schema-Trigger Based Auditing

5
To Auditing in OpenEdge 10.1A


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

• OpenEdge Auditing Overview
• Integrating Auditing into your Application
• Coding for Performance
• Migrating your existing Audit Data

7
Integrating Auditing into your Application
The Steps

1. Before you Begin
2. Asserting the Trusted User Identity
3. Setting Application Context
4. Querying the Audit Data
5. Maintaining Audit Policy in your Application
6. Using Application Events for Read Auditing

8
Step 1 Before you Begin


App DB
Preparation
Policy Data

• Upgrade Databases / Clients to 10.1A
• Add Type II Storage Areas for Auditing
• prostrct add ltdbgt addaudit.st
• Enable Auditing (prepares for auditing)

Application Data
Audit Data
d "Audit_Data"20,32512 . f 40960 d
"Audit_Data"20,32512 . d "Audit_Index"21,164
. f 5120 d "Audit_Index"21,164 .
proutil ltdbgt -C enableauditing area Audit_Data
indexarea Audit_Index deactivateidx
9
Database Options and Audit Permissions
Security Subsystem
10
Import Shipped Audit Policies / Add New
Use Audit Policy Maintenance
Audit Policy Subsystem
Nothing audited until policies defined / enabled
11
Step 2 Asserting the Trusted User Identity
First code! Load trusted authentication domains
at startup

• Use Data Administration to define Trusted
  Authentication Systems and Domains and load via

SECURITY-POLICYLOAD-DOMAINS (dbalias).

• Or manage completely via code

ASSIGN gcDomainName "InternalDomain"U
gcDomainType Internal"U gcDomainKey
"InternalKey" gcDomainDesc "Internal
Domain"U. SECURITY-POLICYREGISTER-DOMAIN
(gcDomainName, gcDomainKey, gcDomainDesc,
gcDomainType). SECURITY-POLICYLOCK-REGISTRATION.
12
Asserting the Trusted User Identity (who)
Modify session login code

• Create container object of authenticated
  credentials
• Set current user for login session to created
  object

CREATE CLIENT-PRINCIPAL ghCP. ASSIGN ghCPUSER-ID
pcUser ghCPDOMAIN-NAME
gcDomainName ghCPSESSION-ID
SUBSTRING(BASE64-ENCODE(GENERATE-UUID),1,22). lO
k ghCPSEAL(gcDomainKey).
lOk SECURITY-POLICYSET-CLIENT (ghCP).
13
Asserting the Trusted User Identity (who)
Re-establishing identity
Client Processes
Application Server
Create CLIENT-PRINCIPAL
Retrieve CLIENT-PRINCIPAL
ContextData
Reset User identity
Retrieve CLIENT-PRINCIPAL
Context Sub-system
hCpLOGOUT
Application ServerShutdown
14
Re-asserting Identity from Context
Store in context using ghCPSESSION-ID
/ Check if anything to do first / IF
VALID-HANDLE(ghCP) AND ghCPUSER-ID
pcAssertUser THEN RETURN. / Re-assert identity
from context if possible / DELETE OBJECT ghCP
NO-ERROR. CREATE CLIENT-PRINCIPAL ghCP
NO-ERROR. lOk ghCPIMPORT-PRINCIPAL(ctx.rawCP)
NO-ERROR. IF lOk AND (ghCPUSER-ID ltgt
pcAssertUser OR ghCPLOGIN-STATE ltgt
"LOGIN"U) THEN DO / an invalid
client-principal was imported / END.
15
Pushing Identity back into Context
Store in context using ghCPSESSION-ID
IF NOT lOk THEN / invalid or new user / DO
ASSIGN ghCPUSER-ID pcUser
ghCPDOMAIN-NAME gcDomainName
ghCPSESSION-ID SUBSTRING(BASE64-ENCODE(
GENERATE-UUID),1,22). lOk
ghCPSEAL(gcDomainKey). ctx.rawCP
ghCPEXPORT-PRINCIPAL(). END. / Now reset to
current user identity / lOk
SECURITY-POLICYSET-CLIENT(ghCP).
16
Clean-up Logging out the User

• Log out at true end of session
• Only do a logout when user really changes
• Do not logout with each Application Server
  roundtrip!

IF VALID-HANDLE(ghCP) THEN DO IF
ghCPLOGIN-STATE "LOGIN"U THEN
ghcpLOGOUT() NO-ERROR. / also delete
context using ghCPSESSION-ID / DELETE
OBJECT ghCP NO-ERROR. ghCP ?. END.
17
Step 3 Setting Audit Context and Scope
Reporting on when, where and why?
Audit-event-record Audit-event-record Audit-even
t-record Audit-event-record Audit-event-record
Audit-event-record Audit-event-record Audit-eve
nt-record Audit-event-record
Audit Event Group
Client Login Session
Application Context
Database Transaction
18
Application Context and Audit Event Groups
Example usage
DEFINE VARIABLE ctxID AS CHARACTER. DEFINE
VARIABLE grpID AS CHARACTER. ctxID
AUDIT-CONTROLSET-APPL-CONTEXT (PROGRAM-NAME(1)
Create Order", cOrderData,cExtraStuff). grp
ID AUDIT-CONTROLBEGIN-EVENT-GROUP (PROGRAM-NAM
E(1) Create Order Line", cLineData,cExtraStu
ff). AUDIT-CONTROLEND-EVENT-GROUP. AUDIT-CONTRO
LCLEAR-APPL-CONTEXT.
Indexed
Indexed
19
Step 4 Querying Audit Transactional Data

• Only record what you need to report
• Use structured event names
• _sys.tbl.create
• _sys.tbl.trig.update
• Use reporting database
• Avoids SHARE-LOCK
• Stringed values always in American format
• SESSIONDATE-FORMAT "mdy
• SESSIONNUMERIC-FORMAT "American"
• Sample ProDataSet query code available on PSDN

Client Session Information
Audit Transaction Data
Modified Values Per field
20
What information is recorded?
Who did it?
When did it happen?
What event caused it?
What was the event on?
What was going on at the time?
Any other relevant info?
21
Audit Transactional Data Meta-Schema
Recursive Join
22
Locating Specific Audit Data
Event context field _aud-audit-data._event-context
DEFINE VARIABLE cKey AS CHARACTER NO-UNDO. ASSIGN
cKey "PUB.orderline" CHR(6)
STRING(SPORTS.orderline.ordernum) CHR(7)
STRING(SPORTS.orderline.linenum). IF
CAN-FIND(FIRST SPORTS._aud-audit-data NO-LOCK
WHERE SPORTS._aud-audit-data._event-context
cKey) THEN MESSAGE "Audit data exists for "
cKey.
By default uses Primary Key Fields
ltownergt.lttablegtCHR(6)ltid-fld-valgtCHR(7)ltid-fld-va
lgt..
CHR(8) is used to delimit array elements
23
Recording Field Values
Selectable via table / field policy

• Streamed (default)
• Modified values stored in _Event-detail field of
  the primary _aud-audit-data record
• Minimizes performance impact
• Limited by max record length auto overflows
• Arbitrary field order / content

ltfld-namgt CHR(6) ltdata-typgt CHR(6)
ltold-valgt CHR(6) ltnew-valgt CHR(7)

• CHR(8) is used to delimit array elements
• One Record per Field
• Query for specific field value changes

24
Step 5 Maintaining Audit Policy in Application
Calling the APMT API

• Published API is low level and exposes data as a
  ProDataSet
• See OpenEdge Development Programming Interfaces
• Rather use new sample Audit Manager
• auditing/audmngrclntp.p
• auditing/audmngrservp.p

25
Enabling an Audit Policy using the Sample Manager
DEFINE VARIABLE ghAuditManager AS HANDLE
NO-UNDO. DEFINE VARIABLE cError AS
CHARACTER NO-UNDO. DEFINE VARIABLE cPolicy
AS CHARACTER NO-UNDO. / enable policy that
tracks menu item selection / ASSIGN cPolicy
"MenuRun"U. RUN auditing/audmngrservp.p
PERSISTENT SET ghAuditManager. RUN
enableAuditPolicyName IN ghAuditManager (INPUT
MYDB"U, INPUT cPolicy, OUTPUT
cError). IF cError ltgt ""U THEN MESSAGE "Audit
policy QUOTER(cPolicy) " failed to
enable." SKIP cError.
26
Step 6 Using Application Defined Audit Events
AUDIT-CONTROLLOG-AUDIT-EVENT method

• Must defined event
• _event-id gt 32000
• For non-database operations
• Also good for complex table/field data
• Can be used for controlled read auditing
• Event context _Event-context describes what was
  audited and is indexed
• Propagated to all database connections
• Recorded where event enabled

27
Application Event Examples
/ 32800 Run Menu Option / AppID
AUDIT-CONTROLLOG-AUDIT-EVENT (32800,
cMenuCode, cDetail, cMore). / READ auditing
32003 Customer Enquiry / AppID
AUDIT-CONTROLLOG-AUDIT-EVENT (32003,
STRING(Customer.CustNum), cCustomerDetail,
cMore).
Indexed
Indexed
28
Agenda

• OpenEdge Auditing Overview
• Integrating Auditing into your Application
• Coding for Performance
• Migrating your existing Audit Data

29
Coding for Performance

• Tune performance through Audit Policy
• Only SET-CLIENT on Appserver when identity really
  changes
• Faster to import CLIENT-PRINCIPAL from context
  than re-create and re-seal
• Careful how ASSIGN indexed fields do in single
  statement
• Carefully control record and transaction scope
• Every database update causes an audit event
• Consider reporting / query requirements

30
Agenda

• OpenEdge Auditing Overview
• Integrating Auditing into your Application
• Coding for Performance
• Migrating your existing Audit Data

31
Migrating your existing Audit Data

• Upgrade database to 10.1A
• Enable auditing
• Load audit policy
• Set up audit permissions to define an audit
  archiver
• Assert identity to audit archiver using
  SET-CLIENT or SET-DB-CLIENT
• Allows manual creation of audit data

Migrate audit data into _aud-audit-data _aud-audit-data-value (optional detail)
Careful to set -zn SESSIONDATE-FORMAT "mdy SESSIONNUMERIC-FORMAT "American"
See example auditing/migrateaudit.p
32
In Summary

• Application changes are not required to use
  OpenEdge Auditing
• Assuming use of _User or SETUSERID()
• Make OpenEdge Auditing a seamless part of your
  application
• Maximize the benefits of OpenEdge Auditing by
  changing your application
• OpenEdge Auditing is more than just database
  auditing

Worth upgrading to OpenEdge 10.1Ajust for this
feature alone
33
Relevant Exchange Sessions

• DB-4 Who does What and When regarding Auditing?
• DEV-17 Effective Design and Deployment of
  OpenEdge Audit Policies
• MOVE-14 Migrating Your Authentication System to
  OpenEdge 10.1A and Beyond

34
Education / Documentation References

• Education
• What's New OpenEdge 10.1 Auditing
• Documentation
• http//documentation.progress.com/output/OpenEdge1
  01a/wwhelp/wwhimpl/js/html/wwhelp.htm
• All code samples shown have been posted to PSDN
• http//www.psdn.com/library/index.jspa

35
Questions?
36
Thank you foryour time
37
(No Transcript)