Case Study 2: Audit Trail Implementation for Regulatory Compliance

Background

Atlantic Shores Savings Bank (ASSB) is a community bank in Wilmington, Delaware, with $1.8 billion in assets and 95,000 deposit accounts. ASSB runs its core banking system on DB2 for z/OS 13, hosted on an IBM z15 mainframe.

In January 2024, ASSB's Office of the Comptroller of the Currency (OCC) examiner issued a Matter Requiring Attention (MRA) -- a formal finding that the bank's data change tracking was insufficient. The specific finding:

"The institution lacks a comprehensive, tamper-evident audit trail for customer and account data. Current change tracking relies on application-level logging, which does not capture changes made through direct database access, batch utilities, or ad-hoc administrative queries. This creates an unacceptable gap in the institution's ability to demonstrate compliance with BSA/AML record-keeping requirements."

ASSB had 90 days to present a remediation plan and 180 days to implement it.

The Problem in Detail

ASSB's existing "audit" consisted of:

1. Application logs: The Java middleware logged every transaction it processed. But changes made through SPUFI (the z/OS SQL interface), batch COBOL jobs, or DBA maintenance scripts were not logged.

2. DB2 log mining: The DB2 recovery log contains every change, but it is designed for recovery, not audit. Extracting human-readable change history from the log requires specialized tools (IBM Log Analysis Tool or third-party products) and is impractical for routine audit queries.

3. No before-images: The application logs recorded what was done but not what the previous values were. An auditor could see "Customer 12345's address was updated" but not what the old address was.

The OCC examiner's concern was legitimate. If a bank employee changed a customer's address to redirect statements (a known fraud vector), the current system could not reliably detect or reconstruct the change.

Design Requirements

ASSB's compliance team, working with the DBA team and an external auditor, defined these requirements:

1. Completeness: Every INSERT, UPDATE, and DELETE on designated tables must be recorded, regardless of the source (application, batch, ad-hoc, utility).

2. Before and after images: For updates, both the old and new values must be captured.

3. Attribution: Every change must record the DB2 authorization ID (who), the timestamp (when), and the application name (what program).

4. Tamper evidence: The audit trail itself must be protected from modification. Audit records, once written, cannot be updated or deleted through normal means.

5. Queryability: Auditors must be able to query the audit trail using standard SQL -- no specialized tools required.

6. Performance: Audit overhead must not exceed 15% of the baseline DML processing time for designated tables.

7. Retention: Audit records must be retained for 7 years per regulatory requirements.

Tables Requiring Audit Coverage

Architecture Decision: Triggers vs. Alternatives

Alternative 1: Change Data Capture (CDC)

DB2 for z/OS supports log-based CDC through IBM InfoSphere Data Replication. CDC reads the DB2 recovery log and publishes change records to a target (another DB2 table, Kafka, etc.).

Pros: Zero overhead on the DML path (reads the log asynchronously). Captures all changes including LOAD utility operations.

Cons: Asynchronous -- there is a delay between the change and the audit record (typically seconds, but could be minutes under load). Requires additional software licensing. More complex to set up and monitor.

Alternative 2: Temporal Tables

DB2 for z/OS 12+ supports system-period temporal tables, which automatically maintain a history table with before-images of every row change.

Pros: Built-in DB2 feature. Automatic before/after image capture. Query with FOR SYSTEM_TIME AS OF syntax.

Cons: Captures the entire row on every change (storage-intensive). Does not capture the application name or custom attribution fields. Cannot add custom logic (e.g., computing change deltas). ASSB is on DB2 13, which supports this, but the feature was not yet enabled in their subsystem.

Alternative 3: Database Triggers

AFTER triggers fire on every INSERT, UPDATE, and DELETE, capturing exactly the information needed in custom audit tables.

Pros: Synchronous -- the audit record is written within the same transaction as the change (atomicity guaranteed). Customizable -- capture exactly the fields needed. No additional software licensing. Works with all change sources (application, batch, ad-hoc).

Cons: Adds overhead to every DML statement. Does not capture LOAD utility changes (LOAD bypasses triggers). Requires careful design to meet performance targets.

The Decision

ASSB chose triggers as the primary mechanism, with two supplements:

1. Triggers for all online and batch DML operations (the vast majority of changes).
2. Operational controls for LOAD utility operations: any use of LOAD on audited tables requires a formal change ticket, and a post-LOAD reconciliation procedure compares row counts and checksums.
3. Audit table protection through GRANT restrictions: only the trigger's authorization ID can INSERT into audit tables. No user has UPDATE or DELETE authority on audit tables.

Implementation

Audit Table Design

After evaluating several approaches, the team chose a per-source-table audit model: one audit table for each audited table. This avoids the performance and query complexity problems of a single monolithic audit table.

-- Audit table for CUSTOMER
CREATE TABLE meridian_audit.customer_audit (
    audit_seq_id      BIGINT GENERATED ALWAYS AS IDENTITY
                      (START WITH 1, INCREMENT BY 1, NO CACHE),
    audit_timestamp   TIMESTAMP NOT NULL WITH DEFAULT,
    audit_action      CHAR(1) NOT NULL,  -- I, U, D
    audit_authid      VARCHAR(128) NOT NULL WITH DEFAULT,
    audit_applname    VARCHAR(32) NOT NULL WITH DEFAULT,
    audit_corrid      VARCHAR(128),
    -- Before-image columns (NULL for INSERT)
    old_customer_id   INTEGER,
    old_first_name    VARCHAR(50),
    old_last_name     VARCHAR(50),
    old_ssn           CHAR(11),
    old_email         VARCHAR(100),
    old_phone         VARCHAR(20),
    old_address_line1 VARCHAR(100),
    old_city          VARCHAR(50),
    old_state         CHAR(2),
    old_zip           CHAR(10),
    old_status        VARCHAR(20),
    -- After-image columns (NULL for DELETE)
    new_customer_id   INTEGER,
    new_first_name    VARCHAR(50),
    new_last_name     VARCHAR(50),
    new_ssn           CHAR(11),
    new_email         VARCHAR(100),
    new_phone         VARCHAR(20),
    new_address_line1 VARCHAR(100),
    new_city          VARCHAR(50),
    new_state         CHAR(2),
    new_zip           CHAR(10),
    new_status        VARCHAR(20)
)
IN AUDIT_DB.AUDIT_TS01;

Key design choices:

• BIGINT IDENTITY with NO CACHE: Guarantees monotonically increasing sequence numbers even across system restarts. NO CACHE prevents gaps after abnormal termination (important for audit completeness verification).

• Separate old_ and new_ columns: Full before and after images. For INSERT, old_ columns are NULL. For DELETE, new_ columns are NULL. For UPDATE, both are populated. This makes it trivial to see exactly what changed.

• Dedicated tablespace (AUDIT_TS01): The audit tables are physically separated from the operational tables. This allows independent backup schedules, dedicated buffer pools, and separate space management.

• No foreign keys: Audit tables intentionally have no foreign keys to operational tables. If a customer is deleted, the audit record must survive. Foreign keys would create unwanted dependencies.

Trigger Implementation

-- INSERT audit trigger for CUSTOMER
CREATE TRIGGER meridian_audit.trg_customer_aud_ins
AFTER INSERT ON meridian.customer
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO meridian_audit.customer_audit (
        audit_timestamp, audit_action, audit_authid, audit_applname,
        new_customer_id, new_first_name, new_last_name, new_ssn,
        new_email, new_phone, new_address_line1, new_city,
        new_state, new_zip, new_status
    ) VALUES (
        CURRENT TIMESTAMP, 'I', CURRENT SQLID, CURRENT CLIENT_APPLNAME,
        n.customer_id, n.first_name, n.last_name, n.ssn,
        n.email, n.phone, n.address_line1, n.city,
        n.state, n.zip, n.status
    );
END;

-- UPDATE audit trigger for CUSTOMER
CREATE TRIGGER meridian_audit.trg_customer_aud_upd
AFTER UPDATE ON meridian.customer
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO meridian_audit.customer_audit (
        audit_timestamp, audit_action, audit_authid, audit_applname,
        old_customer_id, old_first_name, old_last_name, old_ssn,
        old_email, old_phone, old_address_line1, old_city,
        old_state, old_zip, old_status,
        new_customer_id, new_first_name, new_last_name, new_ssn,
        new_email, new_phone, new_address_line1, new_city,
        new_state, new_zip, new_status
    ) VALUES (
        CURRENT TIMESTAMP, 'U', CURRENT SQLID, CURRENT CLIENT_APPLNAME,
        o.customer_id, o.first_name, o.last_name, o.ssn,
        o.email, o.phone, o.address_line1, o.city,
        o.state, o.zip, o.status,
        n.customer_id, n.first_name, n.last_name, n.ssn,
        n.email, n.phone, n.address_line1, n.city,
        n.state, n.zip, n.status
    );
END;

-- DELETE audit trigger for CUSTOMER
CREATE TRIGGER meridian_audit.trg_customer_aud_del
AFTER DELETE ON meridian.customer
REFERENCING OLD AS o
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO meridian_audit.customer_audit (
        audit_timestamp, audit_action, audit_authid, audit_applname,
        old_customer_id, old_first_name, old_last_name, old_ssn,
        old_email, old_phone, old_address_line1, old_city,
        old_state, old_zip, old_status
    ) VALUES (
        CURRENT TIMESTAMP, 'D', CURRENT SQLID, CURRENT CLIENT_APPLNAME,
        o.customer_id, o.first_name, o.last_name, o.ssn,
        o.email, o.phone, o.address_line1, o.city,
        o.state, o.zip, o.status
    );
END;

This pattern was replicated for all 6 audited tables, yielding 18 triggers total (3 per table).

Audit Query Views

For auditor convenience, the team created views that present the audit data in human-readable formats:

-- View: Customer change history with human-readable descriptions
CREATE VIEW meridian_audit.v_customer_changes AS
    SELECT
        audit_seq_id,
        audit_timestamp,
        CASE audit_action
            WHEN 'I' THEN 'Created'
            WHEN 'U' THEN 'Modified'
            WHEN 'D' THEN 'Deleted'
        END AS action_description,
        audit_authid AS changed_by,
        audit_applname AS application,
        COALESCE(new_customer_id, old_customer_id) AS customer_id,
        COALESCE(new_first_name, old_first_name) || ' ' ||
        COALESCE(new_last_name, old_last_name) AS customer_name,
        -- Show what changed for updates
        CASE WHEN audit_action = 'U' AND old_email <> new_email
             THEN 'Email: ' || COALESCE(old_email, '(null)') ||
                  ' -> ' || COALESCE(new_email, '(null)')
             ELSE '' END AS email_change,
        CASE WHEN audit_action = 'U' AND old_address_line1 <> new_address_line1
             THEN 'Address: ' || COALESCE(old_address_line1, '(null)') ||
                  ' -> ' || COALESCE(new_address_line1, '(null)')
             ELSE '' END AS address_change,
        CASE WHEN audit_action = 'U' AND old_status <> new_status
             THEN 'Status: ' || old_status || ' -> ' || new_status
             ELSE '' END AS status_change
    FROM meridian_audit.customer_audit
    ORDER BY audit_seq_id DESC;

Audit Table Protection

The tamper-evidence requirement was implemented through authorization:

-- Only the trigger's authorization can insert into audit tables
GRANT INSERT ON meridian_audit.customer_audit TO TRIGGER_AUTH;

-- Auditors can read but not modify
GRANT SELECT ON meridian_audit.customer_audit TO ROLE AUDITOR;
GRANT SELECT ON meridian_audit.v_customer_changes TO ROLE AUDITOR;

-- Nobody can UPDATE or DELETE audit records
-- (No GRANT for UPDATE or DELETE is issued to any user or role)

Even a DBA with SYSADM authority would need to explicitly grant themselves UPDATE/DELETE authority -- an action that is itself logged by DB2's security audit facility (AUDIT trace).

Retention Management

A stored procedure handles the 7-year retention requirement:

CREATE PROCEDURE meridian_audit.archive_old_audits (
    IN  p_retention_years INTEGER,
    OUT p_records_archived BIGINT,
    OUT p_status VARCHAR(200)
)
LANGUAGE SQL
BEGIN
    DECLARE v_cutoff TIMESTAMP;
    SET v_cutoff = CURRENT TIMESTAMP - p_retention_years YEARS;

    -- Archive to a partitioned archive table (or offload to tape)
    INSERT INTO meridian_audit.customer_audit_archive
    SELECT * FROM meridian_audit.customer_audit
    WHERE audit_timestamp < v_cutoff;

    GET DIAGNOSTICS p_records_archived = ROW_COUNT;

    DELETE FROM meridian_audit.customer_audit
    WHERE audit_timestamp < v_cutoff;

    SET p_status = 'Archived ' || CHAR(p_records_archived)
                  || ' records older than ' || CHAR(v_cutoff);
    COMMIT;
END;

Performance Results

The team's performance testing on a production-equivalent z/OS LPAR measured the following:

The overhead exceeded the 15% target. The team optimized:

1. Dedicated buffer pool: Audit tables were assigned to a separate buffer pool (BP3) sized for write-heavy workloads, reducing physical I/O during audit inserts.

2. Index optimization: The audit tables were initially created with 3 indexes. Removing 2 indexes (keeping only the identity column primary key) reduced INSERT overhead. Query-supporting indexes were added to the audit tables as separate objects and built during off-peak hours.

3. Tablespace page size: Changed from 4K to 16K pages for audit tablespaces, reducing the number of page allocations during high-volume inserts.

After optimization:

All within the 15% budget.

Regulatory Outcome

ASSB presented the remediation plan at the 90-day mark. The OCC examiner approved it with minor recommendations. At the 180-day mark, the system was fully deployed. The follow-up examination resulted in the MRA being closed with the comment:

"The institution has implemented a comprehensive, database-level audit trail that captures all changes to designated tables regardless of access method. The before-and-after image design provides full reconstruction capability. Access controls on the audit tables provide appropriate tamper evidence. The institution's remediation is satisfactory."

Lessons Learned

1. Triggers are the right tool for synchronous, tamper-evident audit trails. CDC and temporal tables are valuable alternatives, but neither provides the same combination of synchronous recording, custom attribution, and protection within the same transaction boundary.

2. Performance optimization is mandatory, not optional. The naive trigger implementation exceeded the overhead budget by 2x. Without dedicated buffer pools, index optimization, and page size tuning, the project would have failed its performance requirement.

3. Audit table design affects query performance for years. The decision to use per-table audit tables (rather than one monolithic audit table) paid off immediately when auditors started running ad-hoc queries. A single audit table for all 6 source tables would have been significantly harder to query and index.

4. LOAD utility bypass is a real gap. The team initially overlooked that DB2's LOAD utility does not fire triggers. Operational controls (change tickets, post-LOAD reconciliation) fill the gap, but they are procedural, not technical. This is a limitation of the trigger-based approach.

5. Retention management must be designed from day one. The TRANSACTION audit table grows by approximately 120,000 rows per day -- 44 million rows per year. Without partition-based archival or regular maintenance, the table would become unmanageable within months.

Discussion Questions

1. ASSB chose per-table audit tables over a single unified audit table. What are the advantages of a unified approach? Under what circumstances would you recommend it?

2. The audit triggers capture CURRENT CLIENT_APPLNAME for attribution. What happens if an application does not set its client application name? How would you mitigate this?

3. If ASSB upgraded to use temporal tables for automatic history tracking, would they still need triggers? What would triggers provide that temporal tables do not?

4. Design a monitoring solution that alerts when audit trigger overhead exceeds the 15% threshold. What metrics would you collect, and how often?

5. An auditor requests a query: "Show me all changes to customer addresses in the last 90 days where the change was made by someone other than the customer." Write the SQL against the audit view and identify what additional data you would need.

Return to Chapter 12 | Continue to Key Takeaways