USING ORACLE TO CREATE AUDIT TRAILS FOR PART

USING ORACLE TO CREATE AUDIT TRAILS FOR PART 11 COMPLIANCE Dr Peter Smith Project Director WYETH RESEARCH IS 9/17/2020 1

AGENDA • REVIEW OF PART 11 • AUDITING FUNCTIONALITY • WRAPPER FUNCTIONALITY 9/17/2020 2

21 CFR PART 11 SUMMARY • FDA Regulation as of April, 1997 on Electronic Records and Electronic Signatures – Intent to ensure Electronic Records have the validity of Paper Records • Part 11 is a sound regulation – we want to use electronic records • Short regulation – major impact • Revised Guidelines as of Feb. 2003 – Emphasis on Risk Assessment – New guidelines provide a sensible relief from extreme interpretations – But the law has not changed! 9/17/2020 3

EXPERIENCE SHOWS • Manual systems cannot be validated. • Data integrity requires administrative controls and redundant checking and detailed reviews. • If it is not documented, it was not done. • If you cannot prove it was done by a trained analyst using the approved procedure it was not done right. 9/17/2020 4

REQUIREMENTS OF PART 11 • Electronic Records are secure – Auditing of who, when, what, why any changes to the record are made • Electronic signatures are authenticated – The person making the entry is who they say they are 9/17/2020 5

THE AUDIT TRAIL • To assure that any record cannot be changed without recording – – Who makes the change When the change was made What the new value is and what the old one was Why the change was made • It’s a Good Lab. Practice requirement, not directly Part 11 – 21 CFR 58. 130 (e) 9/17/2020 6

HOW TO IMPLEMENT AN AUDIT TRAIL IN ORACLE? • Standard Oracle auditing – Serves different purpose • Oracle 9 i provides new fine-grained auditing => audit policies => Selective Audit product – Not Part 11 specific however • Any good Oracle programmer can implement via Triggers and good design – The details make the case 9/17/2020 7

AT WYETH • We wanted a Part 11 Audit tool which – Is certified by our Part 11 PMO – Is of General utility • We have written a globally-available, generic set of scripts that create the audit tables and business table triggers – Will provide the flexibility to match different database designs – Can be applied Retrospectively 9/17/2020 8

TWO MAIN MODELS FOR A GENERIC AUDIT TRAIL (GAT) • Column based, recording each change to a “cell” (column) – E. g. change in three cells => three records in the audit database • Row-based, recording each record as it is changed – E. g. change in three cells in one source table row => one record in the audit database 9/17/2020 9

EXAMPLE OF COLUMNBASED GAT Source tables Old + New values RECORD 1 Trigger on RECORD 2 INSERT UPDATE DELETE RECORD 3 AUDIT table with audit columns e. g. time stamp UPSERTS? 9/17/2020 10

EXAMPLE OF ROW-BASED GAT Source table New value RECORD 1 Old RECORD 1 New RECORD 1 Trigger on INSERT UPDATE DELETE 9/17/2020 SHADOW table with audit columns e. g. time stamp 11

Empdept example Primary key change => automatically triggers both I and D 9/17/2020 12

No reason for insert necessary AUDIT TABLE SHOWS INSERTIONS Primary key change => automatically triggers both I and D 9/17/2020 13

Making changes Primary key change => automatically triggers both I and D 9/17/2020 14

Primary key change => automatically triggers both I and D 9/17/2020 15

THREE VARIATIONS OF ROW-BASED GAT • Source table includes audit fields – High overhead – Referential integrity issues – But good for snapshots/history • Shadow Table includes all U, I, D changes – Comprehensive auditing – Need report or view to show history • Shadow Table only records U, D – Audit table divided between source and shadow – Used when there are many inserts, few changes 9/17/2020 16

STRENGTHS • Row based – Easy to track record history – Concise audit tables – Each schema has its own audit tables – Best for point-in-time queries (e. g. research results) • But hard to know what changed • Column Based – One audit table for several schemas – Easily identifies which cell has changed • But hard to reconstruct record – – We wrote a reporting procedure to address this Best for value-sensitive data (e. g. bank balances) • Both approaches can be used at the same time 9/17/2020 17

REMOTE USERS • How to utilize Oracle security for pooled user accounts? • Necessary to exploit mid-tier architectures 9/17/2020 18

MIDDLEWARE BADBOY DATABASE Source TABLE Joe. S SET REMOTE USER ( Joe. S) Audit Package UPDATE Log into mid-tier Mid-tier security Optional Mid-tier authentication against Oracle Table security Joe. S + GMT +… TRIGGERS Shadow Audit TABLE 9/17/2020 19

GMT • In order to track the sequence of changes, need one time standard => GMT • We developed a Java module which computes GMT – From the (Unix) System time-zone – Plus conversion offset to GMT • Oracle 9 i includes this 9/17/2020 20

WRAPPER SOLUTIONS • How to secure data collected analyzed before entry to Oracle? – EXCEL is widely used – E. g. Instrument =>EXCEL=>Oracle • Industry response is via Wrapper solutions – Intercept the data before it reaches EXCEL, audit all transactions thereafter. 9/17/2020 21

EXAMPLE Supervisor/control/admin EXCEL Database => results + Audit trail 9/17/2020 22

SEVERAL VENDORS • For data: – Nugenesis, Velquest, Automsoft, Stelex, SSI-Cyberlab, Cincom • It’s not what you do, it’s how you do it – Usability, non-intrusiveness 9/17/2020 23

If We Do Wrappers Right: • Paperless - Paper notebooks and data sheets replaced by electronic data. • Authenticated Analysts - All electronic data from authenticated analysts using validated sources through validated connections. • Part 11 Compliant - Electronic records stored in secure 21 CFR Part 11 compliant repository. • Automated Verification - Verify that trained analysts, calibrated instruments, current analytical test methods and approved supplies perform the work. • Dynamic Linking - Data and metadata dynamically linked in fully relational form to permit rapid review of results. • Rapid Reporting - Compliance flags highlight specific data requiring review prior to approval. (Thanks to Velquest) 9/17/2020 24

Thanks • To my excellent colleagues at Wyeth: – Tad Harrison Row-based GAT – Dave Vander. Brooke – Jack Tierney – Jay Miller Column-based GAT – Nina Occhiolini DBAs – Percy Ries-Burks • Walter Romanski of Oracle technical staff smithp@wyeth. com 9/17/2020 25