Case Study 02: Security Audit Response — Findings and Remediation

Background

Consolidated Federal Savings (CFS), a mid-size thrift institution regulated by the Office of the Comptroller of the Currency (OCC), operates its core banking platform on DB2 for z/OS. The mainframe hosts a DB2 data sharing group with two members (DB2A and DB2B) running on a Parallel Sysplex. The database manages approximately $18 billion in deposits across 1.2 million customer accounts.

During the annual OCC examination, the IT examiners conducted a targeted review of database security controls. The examination produced a formal Matter Requiring Attention (MRA) letter with nine findings, seven of which directly involved DB2 security configuration. CFS was given 120 days to remediate all findings and provide evidence of remediation.

This case study documents the seven DB2-related findings, the remediation actions taken, and the evidence package submitted to the OCC.

The Examination Findings

MRA-01: Inadequate Separation of Duties

Examiner's Finding: Three production DBAs hold both SYSADM-level RACF authority and direct DBADM privileges on all production databases. The same individuals can modify security configurations, access customer data, and alter audit settings. There is no independent verification of their actions.

Regulatory Basis: OCC Bulletin 2013-29 and FFIEC Information Security Handbook, which require separation of duties for privileged accounts.

Remediation:

CFS restructured its RACF group hierarchy and DB2 authority assignments:

BEFORE:
CFS-DBA-PROD  ->  SYSADM (RACF) + DBADM (DB2) + SECADM (DB2)
(Three individuals in this group)

AFTER:
CFS-DBA-OPS   ->  DBADM (DB2), SYSOPR (RACF)     [Operations DBAs]
CFS-SECURITY  ->  SECADM (DB2)                     [Security team]
CFS-DBA-EMER  ->  SYSADM (RACF, break-glass only)  [Emergency, dual approval]

The key changes:

1. SECADM was transferred from the DBA team to the Information Security department. Two security analysts — reporting to the CISO, not the CTO — now hold SECADM. They control RCAC, audit policies, trusted contexts, and role definitions.

2. SYSADM access was moved to a break-glass procedure. The RACF user IDs with installation SYSADM authority were placed under RACF password management with automatic password rotation. Access requires dual approval (DBA manager plus CISO), is logged in the change management system, and is automatically revoked after 4 hours.

3. DBADM remained with the operations DBA team, providing full capability for database object management, performance tuning, and production support — but without the ability to modify security configurations or audit settings.

-- Revoke SECADM from DBA auth IDs
REVOKE SECADM ON DATABASE FROM DBADM1;
REVOKE SECADM ON DATABASE FROM DBADM2;
REVOKE SECADM ON DATABASE FROM DBADM3;

-- Grant SECADM to the security team
GRANT SECADM ON DATABASE TO SECADM1;
GRANT SECADM ON DATABASE TO SECADM2;

Evidence Submitted: RACF group membership reports showing the new structure; DB2 catalog query results confirming SECADM holders are in the security team; break-glass procedure document with approval workflow; RACF audit log showing zero SYSADM accesses outside the break-glass process during the 30-day observation period.

MRA-02: Excessive PUBLIC Privileges

Examiner's Finding: Several production tables retained default PUBLIC privileges, including SELECT on customer account tables. Any authenticated user — including application support staff, batch operators, contractors, and interns — could query sensitive customer data directly.

Remediation:

The first step was to inventory the damage:

-- Identify all PUBLIC privileges on production tables
SELECT TABSCHEMA, TABNAME, SELECTAUTH, INSERTAUTH, UPDATEAUTH, DELETEAUTH
FROM SYSIBM.SYSTABAUTH
WHERE GRANTEE = 'PUBLIC'
  AND TABSCHEMA IN ('CFSCORE', 'CFSLOAN', 'CFSDEP')
  AND (SELECTAUTH = 'Y' OR INSERTAUTH = 'Y'
       OR UPDATEAUTH = 'Y' OR DELETEAUTH = 'Y');

This query returned 47 tables with PUBLIC SELECT, including CUSTOMERS, ACCOUNTS, TRANSACTIONS, and LOAN_APPLICATIONS. Before revoking, the team traced all dependent objects:

-- Find all packages and plans that depend on PUBLIC access
SELECT BNAME, BTYPE, DNAME, DTYPE
FROM SYSIBM.SYSPACKDEP
WHERE BSCHEMA IN ('CFSCORE', 'CFSLOAN', 'CFSDEP');

With dependency mapping complete, the team executed the revocations and created explicit grants:

-- Revoke all PUBLIC privileges on sensitive tables
REVOKE ALL ON TABLE CFSCORE.CUSTOMERS FROM PUBLIC;
REVOKE ALL ON TABLE CFSCORE.ACCOUNTS FROM PUBLIC;
REVOKE ALL ON TABLE CFSCORE.TRANSACTIONS FROM PUBLIC;
REVOKE ALL ON TABLE CFSLOAN.LOAN_APPLICATIONS FROM PUBLIC;
REVOKE ALL ON TABLE CFSDEP.DEPOSIT_ACCOUNTS FROM PUBLIC;
-- (42 additional REVOKE statements for other tables)

-- Grant explicit access to the roles that need it
GRANT SELECT ON TABLE CFSCORE.CUSTOMERS TO ROLE CICS_ONLINE_APP;
GRANT SELECT ON TABLE CFSCORE.ACCOUNTS TO ROLE CICS_ONLINE_APP;
GRANT SELECT, INSERT ON TABLE CFSCORE.TRANSACTIONS TO ROLE CICS_ONLINE_APP;
GRANT SELECT ON TABLE CFSCORE.CUSTOMERS TO ROLE BATCH_REPORTING;

Evidence Submitted: Before-and-after catalog query results showing zero PUBLIC privileges on production tables; regression test results for all CICS transactions and batch jobs; no application failures during the two-week observation period.

MRA-03: No Audit Trail for Privileged Access

Examiner's Finding: DB2 audit tracing was configured at CLASS(1) only, which captured SQL statement accounting data but not authorization events or privileged operations. There was no record of GRANT/REVOKE operations, DBA data access, or failed authorization attempts.

Remediation:

Audit tracing was expanded to cover all security-relevant events:

-- Start comprehensive audit tracing
-START TRACE(AUDIT) CLASS(1,2,3) DEST(SMF)
-- CLASS 1: Authorization failures
-- CLASS 2: GRANT/REVOKE operations
-- CLASS 3: Audit trace records for audited tables

Additionally, sensitive tables were marked for audit:

-- Mark sensitive tables for full audit
ALTER TABLE CFSCORE.CUSTOMERS AUDIT ALL;
ALTER TABLE CFSCORE.ACCOUNTS AUDIT ALL;
ALTER TABLE CFSCORE.TRANSACTIONS AUDIT ALL;
ALTER TABLE CFSLOAN.LOAN_APPLICATIONS AUDIT ALL;

The SMF 142 records generated by these settings are processed nightly by IBM Security Guardium, which generates automated reports and alerts for:

• All GRANT/REVOKE operations
• All DBA access to customer data tables
• All failed authorization attempts (potential breach indicators)
• Any data access outside normal business hours
• Any single auth ID accessing more than 1,000 customer records in a single session

Evidence Submitted: SMF record extracts showing captured events across all three classes; Guardium dashboard configuration showing alert rules; sample alerts generated during testing; 30-day trend report of authorization failures.

MRA-04: Missing Row-Level Access Controls

Examiner's Finding: Branch employees could view account data for any branch, not just their assigned branch. A teller in the New York branch could query customer data for the San Francisco branch by simply modifying a CICS screen's branch code parameter.

Remediation:

CFS implemented RCAC row permissions on the core tables:

-- Row permission: branch-scoped access for operational roles
CREATE PERMISSION CFSCORE.PERM_ACCOUNTS_BRANCH
    ON CFSCORE.ACCOUNTS
    FOR ROWS WHERE
        BRANCH_CODE IN (
            SELECT BRANCH_CODE
            FROM CFSCORE.EMPLOYEE_BRANCH_ASSIGN
            WHERE EMPLOYEE_RACF_ID = SESSION_USER
              AND STATUS = 'A'
        )
        OR VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
        OR VERIFY_ROLE_FOR_USER(SESSION_USER, 'COMPLIANCE') = 1
        OR VERIFY_ROLE_FOR_USER(SESSION_USER, 'DBA_OPS') = 1
    ENFORCED FOR ALL ACCESS
    ENABLE;

ALTER TABLE CFSCORE.ACCOUNTS ACTIVATE ROW ACCESS CONTROL;

Similar permissions were created for the CUSTOMERS, TRANSACTIONS, and LOAN_APPLICATIONS tables. The CICS application was modified to pass the actual user's RACF ID through the DB2 attachment facility rather than using a shared transaction auth ID.

Evidence Submitted: Catalog queries showing active RCAC on all core tables; test results demonstrating branch isolation (screenshots of identical CICS queries returning different results for tellers at different branches); Guardium report confirming zero cross-branch access after remediation go-live.

MRA-05: Unmasked Personally Identifiable Information

Examiner's Finding: Social Security numbers, dates of birth, and full account numbers were displayed in full to all users with SELECT access, regardless of job function. Tellers processing routine deposits could see full SSNs — information they do not need for their job function.

Remediation:

-- Column mask for SSN
CREATE MASK CFSCORE.MASK_CUSTOMER_SSN
    ON CFSCORE.CUSTOMERS
    FOR COLUMN SSN
    RETURN
        CASE
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
                THEN SSN
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'COMPLIANCE') = 1
                THEN SSN
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'CICS_TELLER') = 1
                THEN 'XXX-XX-' || SUBSTR(SSN, 8, 4)
            ELSE 'XXX-XX-XXXX'
        END
    ENABLE;

-- Column mask for date of birth
CREATE MASK CFSCORE.MASK_CUSTOMER_DOB
    ON CFSCORE.CUSTOMERS
    FOR COLUMN DATE_OF_BIRTH
    RETURN
        CASE
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
                THEN DATE_OF_BIRTH
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'COMPLIANCE') = 1
                THEN DATE_OF_BIRTH
            ELSE DATE('0001-01-01')
        END
    ENABLE;

ALTER TABLE CFSCORE.CUSTOMERS ACTIVATE COLUMN ACCESS CONTROL;

MRA-06: Unencrypted Remote Connections

Examiner's Finding: DRDA connections from the distributed application servers to the DB2 for z/OS subsystem used SERVER authentication without encryption. Network monitoring confirmed that user credentials and query data were transmitted in cleartext across the internal network.

Remediation:

The DDF authentication was changed to SERVER_ENCRYPT, and AT-TLS was configured to encrypt all DRDA traffic:

-- z/OS: Update DDF authentication in DSNZPARM
AUTH=SERVER_ENCRYPT

-- AT-TLS policy for DB2 DDF port
TTLSRule        CFS_DB2_DDF
{
    LocalPortRange  446
    Direction       Inbound
    TTLSGroupAction CFS_DB2_Group
    {
        TTLSEnabled  On
    }
    TTLSEnvironmentAction CFS_DB2_Env
    {
        HandshakeRole  ServerWithClientAuth
        TTLSKeyringParmsRef CFS_Keyring
        {
            Keyring  CFS/DB2RING
        }
        TTLSCipherParmsRef CFS_Ciphers
        {
            V3CipherSuites TLS_RSA_WITH_AES_256_CBC_SHA256
            V3CipherSuites TLS_RSA_WITH_AES_128_CBC_SHA256
        }
    }
}

All JDBC connection strings on the distributed application servers were updated to use SSL:

jdbc:db2://mainframe.cfs.com:446/CFSDB:sslConnection=true;
    sslTrustStoreLocation=/opt/cfs/certs/db2trust.jks;
    sslTrustStorePassword=****;

Evidence Submitted: DDF configuration showing AUTH=SERVER_ENCRYPT; AT-TLS policy configuration document; network packet capture showing encrypted traffic (protocol headers only — no plaintext data visible); JDBC connection configuration review for all application servers.

MRA-07: No Password Policy Enforcement for Service Accounts

Examiner's Finding: DB2 batch and application service accounts (eight total) used static passwords that had not been changed in over two years. Investigation revealed that several passwords were shared among team members via a spreadsheet stored on a shared network drive.

Remediation:

1. All eight service account passwords were immediately changed.
2. Service accounts were enrolled in CyberArk (privileged access management) for automatic password rotation every 90 days.
3. The shared password spreadsheet was deleted from the network drive and from all backup copies. An incident report was filed with the CISO.
4. RACF password rules were updated to enforce minimum length (16 characters), complexity requirements, and history depth.

-- RACF: Update password rules for service accounts
ALTUSER CFSBATCH PASSWORD(newpassword) NOEXPIRED
ALTUSER CFSCICS1 PASSWORD(newpassword) NOEXPIRED
-- Passwords are now managed by CyberArk
-- NOEXPIRED prevents RACF from expiring the password independently
-- CyberArk handles rotation on its 90-day schedule

Evidence Submitted: CyberArk enrollment confirmation for all eight service accounts; RACF password policy configuration showing new complexity requirements; written attestation that the shared spreadsheet was deleted; incident report for the password exposure with corrective action plan.

Remediation Timeline

Total elapsed time: 17 weeks (within the 120-day requirement).

Follow-Up Examination Results

The OCC conducted a follow-up examination six months after the remediation deadline. All seven DB2-related findings were closed. The examiner's report noted:

"CFS has implemented a comprehensive database security framework that meets the requirements of the FFIEC Information Security Handbook. The separation of DBADM and SECADM authorities, combined with row-level access control, column masking, and a robust audit trail, represents an effective control environment for the protection of non-public personal information. The break-glass procedure for SYSADM access with dual approval is particularly well-designed and should be considered a model for peer institutions."

CFS subsequently adopted the remediated security model as their standard configuration template for all new DB2 subsystems and incorporated the verification queries into their quarterly internal audit program.

Key Takeaways for Practitioners

1. Regulatory findings are specific and evidence-based. OCC examiners checked catalog tables, RACF group reports, and network configurations directly. Vague claims of security are insufficient — remediation evidence must be equally specific and verifiable through queries and configuration dumps.

2. PUBLIC privileges are a silent, accumulating risk. Over years of development, testing, and production migrations, PUBLIC grants accumulate and are rarely reviewed. A quarterly PUBLIC privilege audit should be part of every organization's standard operating procedures.

3. Separation of duties is a non-negotiable requirement in banking. The examiner did not debate whether DBADM and SECADM should be separate; they checked the catalog and found they were not. The finding was automatic and immediate. There is no argument that will convince a regulator that combining these authorities is acceptable.

4. RCAC retrofitting requires extensive application testing. Modifying the CICS attachment facility to propagate individual RACF IDs (instead of a shared transaction auth ID) was the most time-consuming part of the remediation. New applications should be designed for RCAC from the beginning.

5. Audit without analysis is compliance theater. Turning on SMF CLASS(1,2,3) tracing without a tool to process, alert on, and report from the data provides little actual security value. IBM Guardium, Splunk, or equivalent tooling transforms raw audit data into actionable intelligence.

6. Password management for service accounts is a persistent vulnerability. Shared passwords in spreadsheets are more common than any organization wants to admit. Privileged access management tools (CyberArk, HashiCorp Vault, Thycotic) solve this systematically. The cost of the tool is trivial compared to the cost of a regulatory finding.

7. Evidence preservation matters as much as remediation. CFS maintained before-and-after snapshots of every catalog query, every RACF report, and every configuration change. This evidence package was as important as the remediation itself — without it, the examiner had no way to verify that the changes were real and complete.

Return to Chapter 19 | Continue to Key Takeaways