move-9: audit enable your application the easy way anthony d swindells engineering fellow

MOVE-9: Audit enable your Applicationthe Easy Way

Anthony D SwindellsEngineering Fellow

© 2006 Progress Software Corporation2MOVE-9 Audit Enable your Application the Easy Way

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


Guaranteednon-repudiable audit trail

Introducing OpenEdge 10.1A Auditing

Only audit what is necessary• Database CUD• Internal events• Database utilities• Application events

Relationally stored for reporting

Seamless access across the ABL and SQL

Who did What, When, Where and How?

End-Users

Database

PrivilegedUsers


From Schema-Trigger Based Auditing A

BL

Clie

nt

Audit PolicyTools

Application Code

Ap

pli

cati

on

D

ata

App DB

Audit EventManager(schema triggers)

Audit Data

Audit Data Manager

Audit Policy ManagerA

PI

Policy Data

Sec

uri

ty M

anag

er

SQ

L C

lien

t

Application Code R

epo

rtM

anag

erAudit

Report

Audit Data

Archive DB

ArchiveDaemon

Arc

hiv

eM

anag

er

OfflineAuditData


To Auditing in OpenEdge 10.1AA

BL

Clie

nt

Database Tools and Utilities

Open Tools

Audit Policy Tools (APMT)

Application Code

SQ

L C

lien

t

Application Code

Audit Data A

pp

lica

tio

n

Dat

a

Policy Data

App DB

Audit Data

Archive DB

Audit EventSubsystem

Dat

abas

e

Inte

rnal

Ap

pli

cati

on

Sec

uri

ty S

ub

syst

emAudit Data Subsystem

Audit Policy SubsystemA

PI

ArchiveDaemon A

rch

ivin

g S

ub

syst

em

Rep

ort

ing

Su

bsy

stem

AuditReport

OfflineAuditData


Agenda

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


Integrating Auditing into your Application

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

The Steps


Step 1: Before you Begin

Upgrade Databases / Clients to 10.1A Add Type II Storage Areas for Auditing

• prostrct add <db> addaudit.st

Enable Auditing (prepares for auditing)

Preparation

d "Audit_Data":20,32;512 . f 40960

d "Audit_Data":20,32;512 .

d "Audit_Index":21,1;64 . f 5120

d "Audit_Index":21,1;64 .

proutil <db> -C enableauditing area “Audit_Data” indexarea “Audit_Index” [deactivateidx]

Audit Data A

pp

lica

tio

n

Dat

a

Policy Data

App DB


Database Options and Audit Permissions

Sec

uri

ty S

ub

syst

em


Import Shipped Audit Policies / Add New

Use Audit Policy Maintenance

Nothing audited until policies defined / enabled

Audit Policy Subsystem


Step 2: Asserting the Trusted User Identity

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

First code! Load trusted authentication domains at startup

SECURITY-POLICY:LOAD-DOMAINS (dbalias).

ASSIGN gcDomainName = "InternalDomain":U

gcDomainType = “Internal":U

gcDomainKey = "InternalKey"

gcDomainDesc = "Internal Domain":U.

SECURITY-POLICY:REGISTER-DOMAIN (gcDomainName, gcDomainKey, gcDomainDesc, gcDomainType).

SECURITY-POLICY:LOCK-REGISTRATION.

Or manage completely via code:


Asserting the Trusted User Identity (who)

Create container object of authenticated credentials

Set current user for login session to created object

Modify session login code

lOk = SECURITY-POLICY:SET-CLIENT (ghCP).

CREATE CLIENT-PRINCIPAL ghCP.

ASSIGN ghCP:USER-ID = pcUser

ghCP:DOMAIN-NAME = gcDomainName

ghCP:SESSION-ID =

SUBSTRING(BASE64-ENCODE(GENERATE-UUID),1,22).

lOk = ghCP:SEAL(gcDomainKey).


Client

ProcessesContext

Data

Application Server

Login Credentials Create CLIENT-PRINCIPAL

EXPORT

Session-id

Session-idRetrieve

CLIENT-PRINCIPAL IMPORTReset User identity

Logout requestSession-id

Retrieve CLIENT-PRINCIPAL

hCp:LOGOUT

IMPORT

Application ServerShutdown

Purge

Asserting the Trusted User Identity (who)

Re-establishing identity

ContextSub-system


Re-asserting Identity from Context

Store in context using ghCP:SESSION-ID

/* Check if anything to do first */

IF VALID-HANDLE(ghCP) AND

ghCP:USER-ID = pcAssertUser THEN RETURN.

/* Re-assert identity – from context if possible */

DELETE OBJECT ghCP NO-ERROR.

CREATE CLIENT-PRINCIPAL ghCP NO-ERROR.

lOk = ghCP:IMPORT-PRINCIPAL(ctx.rawCP) NO-ERROR.

IF lOk AND (ghCP:USER-ID <> pcAssertUser OR

ghCP:LOGIN-STATE <> "LOGIN":U) THEN

DO: /* an invalid client-principal was imported */

END.


Pushing Identity back into Context

Store in context using ghCP:SESSION-ID

IF NOT lOk THEN /* invalid or new user */

DO:

ASSIGN ghCP:USER-ID = pcUser

ghCP:DOMAIN-NAME = gcDomainName

ghCP:SESSION-ID =

SUBSTRING(BASE64-ENCODE(GENERATE-UUID),1,22).

lOk = ghCP:SEAL(gcDomainKey).

ctx.rawCP = ghCP:EXPORT-PRINCIPAL().

END.

/* Now reset to current user identity */

lOk = SECURITY-POLICY:SET-CLIENT(ghCP).


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 ghCP:LOGIN-STATE = "LOGIN":U THEN ghcp:LOGOUT() NO-ERROR.

/* also delete context using ghCP:SESSION-ID */

DELETE OBJECT ghCP NO-ERROR.

ghCP = ?.

END.


Step 3: Setting Audit Context and Scope

Audit-event-record…

Audit-event-recordAudit-event-record

…Audit-event-record

…Audit-event-recordAudit-event-record

…Audit-event-recordAudit-event-record

…Audit-event-record

…

DatabaseTransaction

…

AuditEvent Group

…

Application Context

…

Client Login

Session…

Reporting on when, where and why?


Application Context and Audit Event Groups

Example usage

DEFINE VARIABLE ctxID AS CHARACTER.

DEFINE VARIABLE grpID AS CHARACTER.

ctxID = AUDIT-CONTROL:SET-APPL-CONTEXT

(PROGRAM-NAME(1) + “:Create Order",

cOrderData,cExtraStuff).

…

grpID = AUDIT-CONTROL:BEGIN-EVENT-GROUP

(PROGRAM-NAME(1) + “:Create Order Line",

cLineData,cExtraStuff).

…

AUDIT-CONTROL:END-EVENT-GROUP.

AUDIT-CONTROL:CLEAR-APPL-CONTEXT.

Indexed

Indexed


is the group for

supplies context to

consists of

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 (FK) (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 (FK) (IE7.1)_Event-detail_Audit-custom-detail_Audit-data-security-level_Data-seal

_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

_client-session

_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

Step 4: Querying Audit Transactional Data

Client

Session

Information

Audit

Transaction

DataModified

Values

Per field

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

• SESSION:DATE-FORMAT = "mdy“

• SESSION:NUMERIC-FORMAT = "American"

Sample ProDataSet query code available on PSDN

AuditReport


What information is recorded?

supplies context to

is the group for

_aud-audit-data

_Audit-data-guid


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?


is the group for

supplies context to

consists of

created

_aud-audit-data

_Audit-data-guid


_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

_client-session

_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

Audit Transactional Data Meta-Schema

Recursive Join

(FK) Foreign Key

(IEx.y) Inversion Entry (non-unique)

x = Index Number

y = Field Order in Index

LEGEND


Locating Specific Audit Data

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.

Event context field _aud-audit-data._event-context

<owner>.<table>CHR(6)<id-fld-val>[CHR(7)<id-fld-val>.. ]

CHR(8) is used to delimit array elements

By default uses Primary

Key Fields


Recording Field Values

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

Selectable via table / field policy

<fld-nam> + CHR(6) + <data-typ> + CHR(6) + [<old-val> +] CHR(6) + <new-val> + CHR(7)

• CHR(8) is used to delimit array elements

One Record per Field• Query for specific field value changes


Step 5: Maintaining Audit Policy in Application

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

Calling the APMT API


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 <> "":U THEN MESSAGE "Audit policy: “

QUOTER(cPolicy) " failed to enable." SKIP cError.


Step 6: Using Application Defined Audit Events

Must defined event• _event-id >= 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

AUDIT-CONTROL:LOG-AUDIT-EVENT method


Application Event Examples

…

/* 32800 = Run Menu Option */

AppID = AUDIT-CONTROL:LOG-AUDIT-EVENT

(32800, cMenuCode,

cDetail, cMore).

…

/* READ auditing 32003 = Customer Enquiry */

AppID = AUDIT-CONTROL:LOG-AUDIT-EVENT

(32003, STRING(Customer.CustNum),

cCustomerDetail, cMore).

…

Indexed

Indexed


Agenda



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


Agenda



See example: auditing/migrateaudit.p

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-znSESSION:DATE-FORMAT = "mdy“SESSION:NUMERIC-FORMAT = "American"


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


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


Education / Documentation References

Education • What's New OpenEdge 10.1: Auditing

Documentation• http://documentation.progress.com/output/

OpenEdge101a/wwhelp/wwhimpl/js/html/wwhelp.htm

All code samples shown have been posted to PSDN• http://www.psdn.com/library/index.jspa


Questions?


Thank you foryour time

move-9: audit enable your application the easy way anthony d swindells engineering fellow

Documents