Chapter 19: Security and Authorization — Authentication, Privileges, Roles, Row-Level Security, and Audit

Learning Objectives

By the end of this chapter, you will be able to:

1. Explain DB2's authentication and authorization architecture, including the boundary between external authentication and internal authorization
2. Grant and revoke privileges at the database, schema, table, and column levels using precise SQL syntax
3. Implement role-based access control to simplify privilege management across large user populations
4. Configure row-level and column-level security using DB2's Row and Column Access Control (RCAC) features
5. Set up audit policies that satisfy PCI-DSS, HIPAA, SOX, and GDPR compliance requirements
6. Design the complete Meridian National Bank security model — from authentication through audit

Opening: The Cost of a Breach

In banking, a security breach doesn't just cost money — it costs trust. And trust, once lost, takes a decade to rebuild.

Consider what happened in 2019 when a major financial institution disclosed that a misconfigured access control rule exposed over 100 million customer records. The direct costs — regulatory fines, legal settlements, remediation — exceeded $300 million. But the indirect costs were worse: customer attrition, reputational damage, and an erosion of confidence that persisted for years. The root cause was not some exotic zero-day exploit. It was a privilege escalation through a misconfigured role, the kind of mistake that a disciplined security model would have caught before it reached production.

DB2 provides one of the most granular, auditable, and mature security frameworks of any database platform. On z/OS, it integrates with RACF — the same external security manager that protects the most sensitive government and financial systems on earth. On LUW, it supports operating system authentication, LDAP, Kerberos, and a flexible plugin architecture. Both platforms offer row-level security, column masking, trusted contexts, encryption, and audit policies that map directly to regulatory requirements.

But these features mean nothing if you do not understand them. A DB2 system with default security is a locked vault with the combination taped to the door. This chapter will teach you to design, implement, and verify a security model worthy of the data it protects.

We will build this understanding layer by layer — authentication first, then privileges, then roles, then fine-grained access control, then encryption, then audit — because security is not a feature you bolt on. It is an architecture you build from the ground up.

Let us begin at the outermost layer: proving who you are.

19.1 DB2 Security Architecture Overview

The Two Fundamental Questions

Every security system answers two questions:

1. Authentication: Who are you? (Identity verification)
2. Authorization: What are you allowed to do? (Permission enforcement)

DB2 separates these concerns cleanly. Authentication is handled outside of DB2 — by the operating system, an LDAP directory, a Kerberos realm, or an external security manager like RACF. Authorization is handled inside DB2 — through a layered system of privileges, authorities, roles, and fine-grained access controls stored in the DB2 catalog.

This separation is deliberate and important. It means DB2 never stores passwords. It never implements its own authentication protocol. It delegates identity verification to systems that specialize in it, and then makes authorization decisions based on the verified identity. This principle — "authenticate externally, authorize internally" — is one of the reasons DB2 has maintained its security reputation across four decades.

The Security Layers

Think of DB2 security as a series of concentric walls, each providing a different type of protection:

┌──────────────────────────────────────────────────┐
│  Layer 1: Network Security (SSL/TLS, firewalls)  │
│  ┌──────────────────────────────────────────────┐│
│  │  Layer 2: Authentication (RACF, OS, LDAP)    ││
│  │  ┌──────────────────────────────────────────┐││
│  │  │  Layer 3: Administrative Authorities      │││
│  │  │  ┌──────────────────────────────────────┐│││
│  │  │  │  Layer 4: Database/Schema Privileges  ││││
│  │  │  │  ┌──────────────────────────────────┐││││
│  │  │  │  │  Layer 5: Object Privileges       │││││
│  │  │  │  │  ┌──────────────────────────────┐│││││
│  │  │  │  │  │  Layer 6: RCAC (Row/Column)  ││││││
│  │  │  │  │  │  ┌────────────────────────┐  ││││││
│  │  │  │  │  │  │ Layer 7: Encryption    │  ││││││
│  │  │  │  │  │  └────────────────────────┘  ││││││
│  │  │  │  │  └──────────────────────────────┘│││││
│  │  │  │  └──────────────────────────────────┘││││
│  │  │  └──────────────────────────────────────┘│││
│  │  └──────────────────────────────────────────┘││
│  └──────────────────────────────────────────────┘│
└──────────────────────────────────────────────────┘

A request must pass through every layer to access data. Even if a user is authenticated (Layer 2) and has table-level SELECT privilege (Layer 5), a row permission (Layer 6) can still filter out rows they should not see, and column masking can redact sensitive values. This defense-in-depth approach means that a misconfiguration at one layer does not automatically compromise the entire system.

The DB2 Catalog as Security Registry

All authorization information in DB2 is stored in the system catalog tables. The most important ones for security include:

When DB2 receives a SQL statement, it checks these catalog tables to determine whether the requesting authorization ID has the necessary privileges. This check happens at both compile time (for static SQL) and execution time (for dynamic SQL), with caching for performance.

Key Concept: Authorization ID

Every connection to DB2 carries an authorization ID (auth ID) — a string that identifies the user or application. On z/OS, this is typically the RACF user ID (up to 8 characters). On LUW, it is the operating system user name (or LDAP/Kerberos principal). All privilege checks in DB2 are performed against this auth ID. Understanding what auth ID is in effect at any given moment is fundamental to understanding DB2 security.

19.2 [z/OS] Authentication with RACF, ACF2, and Top Secret

The External Security Manager Model

DB2 for z/OS does not authenticate users itself. Instead, it delegates authentication entirely to an External Security Manager (ESM). The three ESMs supported on z/OS are:

• RACF (Resource Access Control Facility) — IBM's own ESM, by far the most common in DB2 environments
• ACF2 (Access Control Facility) — from Broadcom (formerly CA Technologies)
• Top Secret — also from Broadcom

Of these, RACF is dominant in banking and government. We will focus our examples on RACF, but the concepts apply to all three ESMs.

How RACF Authentication Works for DB2

When a user connects to DB2 for z/OS, the authentication flow is:

1. User presents credentials — A TSO user ID and password, a CICS sign-on, or a DRDA connection with user ID and password.
2. z/OS passes credentials to RACF — The operating system calls RACF's authentication service (RACROUTE macro).
3. RACF validates identity — Against its own database of user profiles, group memberships, and password policies.
4. RACF returns a verified identity — A RACF user ID that becomes the DB2 authorization ID.
5. DB2 uses the auth ID for all privilege checks — DB2 never sees the password; it only sees the verified identity.

This means that DB2 security on z/OS is only as strong as the RACF configuration. A poorly configured RACF environment undermines everything DB2 does internally.

RACF Profiles for DB2

RACF protects DB2 resources through profiles in specific classes. The key RACF classes for DB2 are:

DSNR   — Controls who can connect to DB2 (plan authorization)
DSNADM — Controls administrative functions
MDSNSM — Controls DB2 subsystem-level operations

A RACF profile for DB2 plan execution looks like this:

RDEFINE DSNR DB2P.EXECUTE.PLANNAME UACC(NONE)
PERMIT DB2P.EXECUTE.PLANNAME CLASS(DSNR) ID(USRGRP1) ACCESS(READ)
SETROPTS RACLIST(DSNR) REFRESH

This defines a profile in the DSNR class that controls who can execute a specific DB2 plan. The UACC(NONE) sets the universal default to "no access," and the PERMIT command grants READ access (which allows execution) to a specific RACF group.

Connection Trust Levels

DB2 for z/OS recognizes different levels of trust depending on how the connection was established:

For DRDA (Distributed Relational Database Architecture) connections — the protocol used for remote access, including JDBC and ODBC — DB2 for z/OS supports multiple authentication modes configured at the DDF (Distributed Data Facility) level:

• SERVER — Client sends user ID and password; DB2 (via RACF) authenticates
• CLIENT — DB2 trusts the client's authentication (dangerous — use only in fully trusted networks)
• SERVER_ENCRYPT — Like SERVER, but credentials are encrypted in transit
• KERBEROS — Kerberos ticket-based authentication (requires z/OS Integrated Security Services)

Meridian Bank z/OS Standard: All DRDA connections use SERVER_ENCRYPT at minimum. CLIENT authentication is prohibited by policy. Kerberos is used for application server connections within the trusted data center network.

RACF Group Structures for DB2

In practice, RACF groups map to DB2 access patterns. A well-designed RACF group structure for a banking environment might look like:

MERIDIAN                      (top-level group)
├── MER-DBA                   (database administrators)
│   ├── MER-DBA-PROD          (production DBAs)
│   └── MER-DBA-DEV           (development DBAs)
├── MER-APP                   (application IDs)
│   ├── MER-APP-ONLINE        (online transaction processing)
│   └── MER-APP-BATCH         (batch processing)
├── MER-OPS                   (operations staff)
└── MER-AUDIT                 (auditors — read-only)

Each group receives DB2 privileges through GRANT statements targeting the group name. When a new DBA joins the team, the security administrator adds their RACF ID to the MER-DBA-PROD group, and they inherit all associated DB2 privileges. No individual GRANT statements needed.

19.3 [LUW] Authentication

Authentication Types

DB2 for LUW supports several authentication types, configured at the instance level using the AUTHENTICATION database manager configuration parameter:

To view the current authentication type:

-- LUW: Check authentication configuration
db2 get dbm cfg | grep -i authentication

To change it:

-- LUW: Set authentication to SERVER_ENCRYPT
db2 update dbm cfg using AUTHENTICATION SERVER_ENCRYPT
db2stop
db2start

Note that changing the authentication type requires a database manager restart.

LDAP Authentication

For enterprise environments, LDAP authentication is typically configured through a GSS-API plugin. DB2 for LUW ships with an LDAP security plugin that can authenticate users against an LDAP directory (IBM Tivoli Directory Server, Microsoft Active Directory, OpenLDAP, etc.).

Configuration steps:

-- Step 1: Configure the LDAP plugin in db2 registry
db2set DB2AUTH=OSAUTHDB           (optional: OS auth for local + LDAP for remote)
db2set DB2_GRP_LOOKUP=LDAP        (groups come from LDAP, not OS)

-- Step 2: Configure LDAP server details in the plugin config
-- File: $INSTHOME/sqllib/cfg/IBMLDAPSecurity.ini
[LDAP]
LDAP_HOST = ldap.meridianbank.com
LDAP_PORT = 636
ENABLE_SSL = TRUE
USER_BASEDN = ou=employees,dc=meridianbank,dc=com
GROUP_BASEDN = ou=groups,dc=meridianbank,dc=com
USER_OBJECTCLASS = inetOrgPerson
USERID_ATTRIBUTE = uid

Kerberos Authentication

For environments with an existing Kerberos infrastructure (common in enterprises with Active Directory), DB2 for LUW can participate in Kerberos single sign-on:

-- LUW: Enable Kerberos authentication
db2 update dbm cfg using AUTHENTICATION KERBEROS
db2 update dbm cfg using SRVCON_GSSPLUGIN_LIST IBMkrb5
db2stop
db2start

With Kerberos, users authenticate once to the Kerberos Key Distribution Center (KDC) and receive a ticket-granting ticket. Subsequent DB2 connections use service tickets derived from this initial authentication — no password retransmission required.

Operating System Authentication

In the default SERVER or SERVER_ENCRYPT mode, DB2 for LUW validates credentials against the operating system:

• Linux/UNIX: User must exist in /etc/passwd (or NIS/LDAP via NSS), and the password is validated by PAM or shadow password file
• Windows: User must exist in the local Security Accounts Manager (SAM) or Active Directory, validated through Windows authentication APIs

This means that user management for DB2 on LUW starts at the OS level. Creating a DB2 user means creating an OS user first:

# Linux: Create an OS user for DB2 access
useradd -m -s /bin/bash meridian_teller01
passwd meridian_teller01

# The user can now connect to DB2 (after being granted CONNECT privilege)
db2 connect to MERIDIAN user meridian_teller01 using <password>

Important Distinction: On LUW, creating an OS user does not automatically grant any DB2 privileges. The user can attempt to authenticate, but until they receive at least CONNECT privilege on a database, they cannot do anything useful. On z/OS, the situation is analogous — a RACF user ID exists for authentication, but DB2 privileges must be explicitly granted.

19.4 Privileges — The Granular Permission Model

The Principle of Least Privilege

Before we discuss syntax, we must establish the principle that governs all security design: least privilege. Every user, application, and process should have exactly the privileges it needs to perform its function — no more, no less.

In banking, this is not optional. Regulators examine privilege assignments during audits. "We gave everyone DBADM because it was easier" is a finding that can result in fines, mandatory remediation, and consent orders. The granularity of DB2's privilege system exists precisely so that you can implement least privilege rigorously.

GRANT and REVOKE — The Fundamental Verbs

All privilege management in DB2 uses two SQL statements:

• GRANT — Gives a privilege to a user, group, or role
• REVOKE — Removes a privilege from a user, group, or role

The general syntax is:

GRANT <privilege-list> ON <object> TO <grantee> [WITH GRANT OPTION];
REVOKE <privilege-list> ON <object> FROM <grantee>;

Database-Level Privileges

Database-level privileges control broad operations within a database:

-- Grant CONNECT privilege (required for any database access)
GRANT CONNECT ON DATABASE TO USER meridian_app;

-- Grant ability to create tables (CREATETAB)
GRANT CREATETAB ON DATABASE TO ROLE app_developer;

-- Grant ability to create schemas (IMPLICIT_SCHEMA is usually default)
GRANT IMPLICIT_SCHEMA ON DATABASE TO USER dev_lead;

-- Grant ability to bind packages (important for static SQL)
GRANT BINDADD ON DATABASE TO ROLE app_deployer;

-- Grant ability to create external routines
GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO ROLE sys_developer;

-- LUW: Grant LOAD authority (required for LOAD utility)
GRANT LOAD ON DATABASE TO ROLE etl_operator;

The full list of database-level privileges on LUW includes: CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED_ROUTINE, CREATE_EXTERNAL_ROUTINE, IMPLICIT_SCHEMA, LOAD, QUIESCE_CONNECT, and EXTERNALROUTINE.

Schema-Level Privileges

Schemas provide a namespace for database objects. Schema-level privileges control who can create, alter, or drop objects within a schema:

-- Grant all schema privileges
GRANT ALTERIN, CREATEIN, DROPIN ON SCHEMA meridian TO ROLE app_developer;

-- Grant only the ability to create objects in the schema
GRANT CREATEIN ON SCHEMA meridian TO ROLE app_deployer;

-- Revoke DROP privilege (developers can create but not drop in production)
REVOKE DROPIN ON SCHEMA meridian FROM ROLE app_developer;

Table-Level Privileges

Table-level privileges are the most commonly used and provide fine-grained control over data access:

-- Grant SELECT (read) access
GRANT SELECT ON TABLE meridian.accounts TO ROLE teller;

-- Grant INSERT and UPDATE (write) access
GRANT INSERT, UPDATE ON TABLE meridian.transactions TO ROLE teller;

-- Grant DELETE access (rarely granted to application roles)
GRANT DELETE ON TABLE meridian.temp_staging TO ROLE etl_operator;

-- Grant ALL privileges (use sparingly)
GRANT ALL ON TABLE meridian.accounts TO ROLE branch_manager;

-- Grant with the ability to pass the privilege to others
GRANT SELECT ON TABLE meridian.accounts TO USER senior_dba WITH GRANT OPTION;

-- Grant REFERENCES (needed to create foreign keys referencing this table)
GRANT REFERENCES ON TABLE meridian.accounts TO ROLE app_developer;

-- Grant ALTER (needed to add columns, create indexes, etc.)
GRANT ALTER ON TABLE meridian.accounts TO ROLE app_developer;

-- Grant INDEX (needed to create indexes on the table)
GRANT INDEX ON TABLE meridian.accounts TO ROLE app_developer;

The table-level privileges in DB2 are: SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES, and CONTROL (which implies all the others plus the ability to grant them).

Column-Level Privileges

When table-level privileges are too broad, DB2 supports column-level UPDATE and REFERENCES privileges:

-- Allow tellers to update only the balance and status columns
GRANT UPDATE (balance, account_status)
    ON TABLE meridian.accounts
    TO ROLE teller;

-- Allow foreign key references only to the account_id column
GRANT REFERENCES (account_id)
    ON TABLE meridian.accounts
    TO ROLE app_developer;

Column-level SELECT is not directly supported through GRANT (use views or column masking for that), but column-level UPDATE is essential for least-privilege enforcement. A teller who can update an account balance should not be able to update the account holder's Social Security number.

View-Based Security

Before RCAC was introduced, views were the primary mechanism for restricting which rows and columns a user could see:

-- Create a view that shows only the teller's assigned branch
CREATE VIEW meridian.v_branch_accounts AS
SELECT account_id, customer_name, account_type, balance
FROM meridian.accounts
WHERE branch_id = SESSION_USER_BRANCH()
WITH CHECK OPTION;

-- Grant SELECT on the view, not the base table
GRANT SELECT ON meridian.v_branch_accounts TO ROLE teller;

Views still serve an important security role, but RCAC (Section 19.7) provides a more robust mechanism that cannot be bypassed through direct table access.

WITH GRANT OPTION

The WITH GRANT OPTION clause allows a grantee to pass the privilege to others:

GRANT SELECT ON TABLE meridian.accounts
    TO USER dba_lead WITH GRANT OPTION;
-- dba_lead can now grant SELECT on this table to others

Use this sparingly. Every WITH GRANT OPTION creates a privilege chain that is harder to audit and harder to revoke cleanly. When you revoke a privilege that was granted with WITH GRANT OPTION, DB2's behavior depends on the platform:

• LUW: Revoking from the grantor also revokes from anyone they granted to (cascading revoke)
• z/OS: Similar cascading behavior, but REVOKE ... BY ALL may be needed for completeness

REVOKE — Removing Privileges

-- Revoke specific privilege
REVOKE INSERT ON TABLE meridian.accounts FROM ROLE teller;

-- Revoke all privileges on a table
REVOKE ALL ON TABLE meridian.accounts FROM USER former_employee;

-- Revoke database-level privilege
REVOKE CONNECT ON DATABASE FROM USER former_employee;

19.5 Roles — Simplifying Privilege Management

The Problem Roles Solve

Without roles, privilege management in a large organization becomes a nightmare. Consider Meridian National Bank with 500 tellers, 50 branch managers, and 20 DBAs. Without roles, granting access to a new table requires 570 individual GRANT statements. When a teller is promoted to branch manager, you must revoke dozens of teller privileges and grant dozens of manager privileges. This does not scale.

Roles solve this by creating named collections of privileges:

ROLE: teller
├── SELECT ON meridian.accounts
├── SELECT ON meridian.customers
├── INSERT ON meridian.transactions
├── UPDATE (balance, account_status) ON meridian.accounts
└── EXECUTE ON meridian.process_deposit

ROLE: branch_manager
├── (all teller privileges via role membership)
├── SELECT ON meridian.branch_performance
├── UPDATE ON meridian.branch_config
└── EXECUTE ON meridian.approve_loan

When a new teller joins, one statement handles it: GRANT ROLE teller TO USER new_teller. When they are promoted, two statements: REVOKE ROLE teller FROM USER promoted_user; GRANT ROLE branch_manager TO USER promoted_user;

Creating and Managing Roles

-- Create roles
CREATE ROLE teller;
CREATE ROLE branch_manager;
CREATE ROLE auditor;
CREATE ROLE dba_operations;

-- Grant privileges to roles
GRANT SELECT ON TABLE meridian.accounts TO ROLE teller;
GRANT INSERT ON TABLE meridian.transactions TO ROLE teller;
GRANT EXECUTE ON PROCEDURE meridian.process_deposit TO ROLE teller;

-- Grant a role to a user
GRANT ROLE teller TO USER john_smith;

-- Grant a role to another role (role hierarchy)
GRANT ROLE teller TO ROLE branch_manager;
-- branch_manager now inherits all teller privileges

-- Grant a role to a group (LUW)
GRANT ROLE teller TO GROUP teller_group;

-- Check who holds a role
SELECT GRANTEE, GRANTEETYPE, ADMIN
FROM SYSCAT.ROLEAUTH
WHERE ROLENAME = 'TELLER';

Role Hierarchies

Roles can be granted to other roles, creating hierarchies:

-- Base roles
CREATE ROLE bank_employee;
CREATE ROLE teller;
CREATE ROLE senior_teller;
CREATE ROLE branch_manager;
CREATE ROLE regional_manager;

-- Build hierarchy
GRANT ROLE bank_employee TO ROLE teller;
GRANT ROLE teller TO ROLE senior_teller;
GRANT ROLE senior_teller TO ROLE branch_manager;
GRANT ROLE branch_manager TO ROLE regional_manager;

-- Privileges at each level accumulate upward
GRANT CONNECT ON DATABASE TO ROLE bank_employee;
GRANT SELECT ON TABLE meridian.customers TO ROLE bank_employee;
GRANT INSERT ON TABLE meridian.transactions TO ROLE teller;
GRANT EXECUTE ON PROCEDURE meridian.override_limit TO ROLE senior_teller;
GRANT SELECT ON TABLE meridian.branch_performance TO ROLE branch_manager;
GRANT SELECT ON TABLE meridian.region_performance TO ROLE regional_manager;

A regional_manager now inherits privileges from all levels below: bank_employee, teller, senior_teller, and branch_manager.

DBADM vs. SECADM vs. SQLADM

DB2 defines several built-in administrative authorities that function like super-roles. Understanding their differences is critical:

The separation of DBADM and SECADM is a deliberate separation of duties. A DBA can manage database objects and data but cannot modify security policies. A SECADM can manage security policies but cannot access the data those policies protect. This prevents any single individual from both controlling access and accessing data — a fundamental principle of financial regulation.

-- LUW: Grant SECADM authority
GRANT SECADM ON DATABASE TO USER security_admin;

-- LUW: Grant DBADM authority
GRANT DBADM ON DATABASE TO USER senior_dba;

-- z/OS: Administrative authorities are granted through RACF
-- or through the GRANT statement with installation SYSADM authority
GRANT DBADM ON DATABASE TO auth_id;

Meridian Bank Policy: No single individual holds both DBADM and SECADM. Production DBADM is held by the operations DBA team via role membership. SECADM is held by the information security officer. This separation is audited quarterly.

19.6 Administrative Authorities

The Authority Hierarchy

DB2 defines a hierarchy of administrative authorities that provide system-wide powers beyond what individual privileges offer. The hierarchy differs between z/OS and LUW, but the principle is the same: higher authorities encompass the capabilities of lower ones.

z/OS Authority Hierarchy

Installation SYSADM          (highest — set in DSNZPARM)
└── Installation SYSOPR
└── SYSADM (database level)
    └── DBADM
        └── DBCTRL
            └── DBMAINT

On z/OS, the installation SYSADM is defined by the SYSTEM ADMIN 1 and SYSTEM ADMIN 2 fields in the DSNZPARM (subsystem parameter module). This auth ID has unrestricted access to everything in the DB2 subsystem. It is typically set to a RACF group ID that is tightly controlled:

-- z/OS: DSNZPARM settings (set during installation)
SYSTEM ADMIN 1 = MERIDBA1
SYSTEM ADMIN 2 = MERIDBA2
SYSTEM OPERATOR 1 = MERIOPR1
SYSTEM OPERATOR 2 = MERIOPR2

LUW Authority Hierarchy

SYSADM          (highest — instance owner's group)
├── SYSCTRL     (can create/drop databases, no data access)
├── SYSMAINT    (can backup/restore, no data access)
└── SYSMON      (monitoring only)

Within a database:
SECADM    (security administration — independent authority)
DBADM     (full database administration and data access)
SQLADM    (SQL tuning — EXPLAIN, but no data modification)
WLMADM    (workload management)
ACCESSCTRL (privilege management)
DATAACCESS (data access)

On LUW, SYSADM authority is granted implicitly to members of the operating system group specified by the SYSADM_GROUP database manager configuration parameter:

-- LUW: Check current SYSADM group
db2 get dbm cfg | grep SYSADM_GROUP

-- LUW: Change SYSADM group
db2 update dbm cfg using SYSADM_GROUP db2adm

Understanding SYSADM vs. SECADM

This distinction deserves emphasis because it confuses many administrators:

SYSADM (LUW) or Installation SYSADM (z/OS): - Can do almost anything in the instance/subsystem - Cannot manage RCAC, audit policies, LBAC, or trusted contexts on LUW (these require SECADM) - On z/OS, installation SYSADM historically had unrestricted authority, but modern versions have added SECADM for separation of duties

SECADM: - Specifically designed for security-focused tasks - Can CREATE/ALTER/DROP security objects (roles, trusted contexts, audit policies, RCAC permissions and masks, LBAC components) - Cannot access data directly — cannot SELECT from tables, cannot INSERT/UPDATE/DELETE - Represents the security administrator function in a separation-of-duties model

This separation means that even an all-powerful SYSADM cannot silently modify row-level security policies or disable audit logging. Only SECADM can do that, and that person is typically in a different reporting chain (information security, not IT operations).

19.7 Row and Column Access Control (RCAC)

Why RCAC Exists

Traditional privilege-based security (GRANT/REVOKE) operates at the object level: you can access a table or you cannot. But in many real-world scenarios, you need finer granularity:

• A teller should see accounts only for their assigned branch
• A customer service representative should see the last four digits of a Social Security number but not the full number
• An auditor should see all transactions but not the names associated with accounts under investigation

Views can address some of these requirements, but they have limitations: if a user has direct table access (for application reasons), a view-based restriction can be bypassed. RCAC solves this by enforcing access control rules directly on the base table, regardless of how it is accessed.

RCAC has two components:

1. Row Permissions — Control which rows a user can see (row-level security)
2. Column Masks — Control what values a user sees in specific columns (column-level masking)

Row Permissions

A row permission is a boolean condition that filters rows for specific users. Only rows satisfying the condition are visible to (or modifiable by) the user.

-- Step 1: Create a row permission
-- Tellers see only accounts from their assigned branch
CREATE PERMISSION meridian.perm_branch_accounts
    ON meridian.accounts
    FOR ROWS WHERE
        branch_id = (
            SELECT branch_id
            FROM meridian.employee_assignments
            WHERE employee_id = SESSION_USER
        )
        OR
        VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
        OR
        VERIFY_ROLE_FOR_USER(SESSION_USER, 'DBA_OPERATIONS') = 1
    ENFORCED FOR ALL ACCESS
    ENABLE;

-- Step 2: Activate RCAC on the table
ALTER TABLE meridian.accounts ACTIVATE ROW ACCESS CONTROL;

Once activated, every query against meridian.accounts — whether SELECT, UPDATE, DELETE, or even INSERT — passes through this permission filter. A teller at Branch 101 literally cannot see, modify, or even learn of the existence of accounts at Branch 102.

The ENFORCED FOR ALL ACCESS clause means the permission applies to all types of data access. The ENABLE clause activates the permission immediately.

Critical Detail: When you activate row access control on a table, DB2 implicitly denies all access to all rows unless at least one permission grants access. If you activate RCAC without creating any permissions, nobody can see any data — including DBADM. Only SECADM can create and manage RCAC permissions.

Column Masks

Column masks transform the values of specific columns based on who is querying. Unlike row permissions (which filter rows), column masks return every row but redact specific values:

-- Mask Social Security numbers: show only last 4 digits to tellers
CREATE MASK meridian.mask_ssn
    ON meridian.customers
    FOR COLUMN ssn
    RETURN
        CASE
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
                THEN ssn
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'BRANCH_MANAGER') = 1
                THEN 'XXX-XX-' || RIGHT(ssn, 4)
            ELSE 'XXX-XX-XXXX'
        END
    ENABLE;

-- Mask account balances: exact balances only for authorized roles
CREATE MASK meridian.mask_balance
    ON meridian.accounts
    FOR COLUMN balance
    RETURN
        CASE
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'TELLER') = 1
                THEN balance
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
                THEN balance
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'BRANCH_MANAGER') = 1
                THEN balance
            ELSE DECIMAL(0.00, 15, 2)
        END
    ENABLE;

-- Activate column access control on the table
ALTER TABLE meridian.customers ACTIVATE COLUMN ACCESS CONTROL;
ALTER TABLE meridian.accounts ACTIVATE COLUMN ACCESS CONTROL;

Column masks are evaluated after row permissions. So a teller first sees only their branch's accounts (row permission), and then within those accounts, SSNs are masked (column mask). The layering is:

1. Row permissions filter which rows are visible
2. Column masks transform values in visible rows
3. The result is what the user sees

Label-Based Access Control (LBAC) — z/OS

DB2 for z/OS also supports Label-Based Access Control, a more formal security model based on security labels. LBAC is modeled after military-grade classification systems (CONFIDENTIAL, SECRET, TOP SECRET) and is available on both z/OS and LUW, though it is most commonly used on z/OS:

-- Create a security label component
CREATE SECURITY LABEL COMPONENT classification
    ARRAY ['TOP_SECRET', 'SECRET', 'CONFIDENTIAL', 'UNCLASSIFIED'];

-- Create a security policy
CREATE SECURITY POLICY bank_data_policy
    COMPONENTS classification
    WITH DB2LBACRULES;

-- Apply the policy to a table
ALTER TABLE meridian.sensitive_reports
    ADD SECURITY POLICY bank_data_policy;

-- Add a security label column to the table
ALTER TABLE meridian.sensitive_reports
    ADD COLUMN sec_label DB2SECURITYLABEL;

-- Grant a security label to a user
CREATE SECURITY LABEL bank_data_policy.secret_level
    COMPONENT classification 'SECRET';

GRANT SECURITY LABEL bank_data_policy.secret_level
    TO USER intelligence_analyst FOR ALL ACCESS;

With LBAC, a user with SECRET clearance can see rows labeled CONFIDENTIAL and UNCLASSIFIED but not rows labeled TOP_SECRET. The enforcement is automatic and cannot be bypassed.

RCAC Best Practices

1. Always create at least one permissive rule before activating RCAC. Activating row access control with no permissions locks everyone out.

2. Include administrative escape hatches. Your permissions should allow SECADM or designated administrative roles to access all data for maintenance purposes.

3. Test exhaustively before production deployment. RCAC errors can silently filter data, making queries return incorrect results without raising errors.

4. Document every permission and mask. RCAC rules are stored in the catalog (SYSCAT.CONTROLS on LUW, SYSIBM.SYSCONTROLS on z/OS), but human-readable documentation is essential for audit.

5. Use VERIFY_ROLE_FOR_USER or VERIFY_GROUP_FOR_USER functions rather than hardcoding auth IDs. This keeps RCAC rules role-based, not user-based.

19.8 Trusted Contexts and Secure Connections

The Three-Tier Security Challenge

Modern applications typically use a three-tier architecture: client, application server, database. The application server connects to DB2 using a service account (e.g., meridian_app), but the actual end user is a teller, branch manager, or customer using a web interface.

Without trusted contexts, DB2 sees every request as coming from meridian_app. This means:

• RCAC rules cannot distinguish between tellers and managers
• Audit logs show meridian_app for every operation, making individual accountability impossible
• The service account must have all privileges needed by every end user — violating least privilege

Trusted contexts solve this by allowing the application server to switch the DB2 authorization ID to match the actual end user, without re-authenticating.

Creating a Trusted Context

-- Create a trusted context for the application server
CREATE TRUSTED CONTEXT meridian_online_app
    BASED UPON CONNECTION USING SYSTEM AUTHID meridian_app
    ATTRIBUTES (ADDRESS '10.0.1.50',     -- app server IP
                ENCRYPTION 'HIGH')        -- require encryption
    DEFAULT ROLE teller
    ENABLE;

-- Allow specific users to switch into the trusted context
ALTER TRUSTED CONTEXT meridian_online_app
    ADD USE FOR john_smith ROLE teller,
    ADD USE FOR jane_doe ROLE branch_manager,
    ADD USE FOR PUBLIC ROLE bank_employee WITHOUT AUTHENTICATION;

When the application server connects from IP 10.0.1.50 using the meridian_app auth ID, DB2 recognizes it as a trusted context. The application can then switch the auth ID to john_smith without re-authenticating, and DB2 applies john_smith's privileges and the teller role.

How Role Switching Works

Within a trusted context, the application uses the SET SESSION AUTHORIZATION statement (or the sqleseti API) to switch users:

-- Application switches DB2 session to the actual end user
SET SESSION AUTHORIZATION = 'JOHN_SMITH';

-- Now all SQL executes as JOHN_SMITH with the teller role
SELECT * FROM meridian.accounts;
-- RCAC filters to John's branch, column masks apply for teller role

-- Switch to a different user
SET SESSION AUTHORIZATION = 'JANE_DOE';
-- Now operating as JANE_DOE with branch_manager role

The audit trail now shows the actual end-user auth ID, RCAC rules apply per user, and the application service account's privileges are not used for data access.

Trusted Context Security Attributes

Trusted contexts can enforce multiple security attributes:

CREATE TRUSTED CONTEXT meridian_batch
    BASED UPON CONNECTION USING SYSTEM AUTHID meridian_batch_svc
    ATTRIBUTES (
        ADDRESS '10.0.2.0/24',          -- subnet range
        ENCRYPTION 'HIGH',              -- require encryption
        SERVERNAME 'BATCH_SERVER_01'     -- specific server identity
    )
    WITH USE FOR
        batch_operator_1 ROLE etl_operator,
        batch_operator_2 ROLE etl_operator
    NO DEFAULT ROLE
    ENABLE;

If any attribute does not match — wrong IP address, insufficient encryption, unrecognized server — the trusted context is not activated, and the connection proceeds as a normal (untrusted) connection with only the service account's base privileges.

Meridian Bank Standard: All three-tier applications must use trusted contexts. Direct database connections from application servers without trusted context are flagged as security violations.

19.9 Audit Policies

Why Audit Matters

Privilege management tells DB2 what to allow. Audit tells DB2 what to record. In regulated industries, audit is not optional — it is the evidence trail that proves to regulators, auditors, and courts that security controls are in place and functioning.

DB2 provides comprehensive audit capabilities that can record:

• Who accessed what data, when, and from where
• Which SQL statements were executed, by whom
• Authentication successes and failures
• Privilege grants and revokes
• Administrative actions
• Security policy changes

Audit Categories

DB2 defines several audit categories (events to capture):

Creating Audit Policies (LUW)

-- Create an audit policy for sensitive tables
CREATE AUDIT POLICY sensitive_data_policy
    CATEGORIES
        EXECUTE STATUS BOTH,          -- log all executions, success and failure
        CHECKING STATUS BOTH,         -- log all authorization checks
        CONTEXT STATUS BOTH           -- log connection context
    ERROR TYPE AUDIT;                 -- fail the operation if audit write fails

-- Apply the policy to specific tables
AUDIT TABLE meridian.accounts USING POLICY sensitive_data_policy;
AUDIT TABLE meridian.customers USING POLICY sensitive_data_policy;
AUDIT TABLE meridian.transactions USING POLICY sensitive_data_policy;

-- Create a policy for security events across the database
CREATE AUDIT POLICY security_events_policy
    CATEGORIES
        VALIDATE STATUS BOTH,         -- all authentication attempts
        SECMAINT STATUS BOTH,         -- all GRANT/REVOKE operations
        SYSADMIN STATUS BOTH,         -- all admin operations
        AUDIT STATUS BOTH             -- changes to audit config itself
    ERROR TYPE AUDIT;

-- Apply at the database level
AUDIT DATABASE USING POLICY security_events_policy;

-- Create a policy for a specific user (high-privilege user monitoring)
CREATE AUDIT POLICY dba_monitoring_policy
    CATEGORIES
        EXECUTE WITH DATA STATUS BOTH,  -- capture actual data values accessed
        CONTEXT STATUS BOTH
    ERROR TYPE AUDIT;

AUDIT ROLE dba_operations USING POLICY dba_monitoring_policy;

The ERROR TYPE AUDIT clause is critical for compliance: it means that if DB2 cannot write the audit record (disk full, I/O error, etc.), the operation itself fails. This guarantees that no unaudited access occurs — a requirement under several compliance frameworks.

Extracting and Archiving Audit Data (LUW)

On LUW, audit data is written to audit log files and must be extracted using the db2audit tool:

# Configure audit at the instance level
db2audit configure datapath /db2audit/data archivepath /db2audit/archive

# Start auditing
db2audit start

# Extract audit data to delimited files for analysis
db2audit extract delasc delimiter ! to /db2audit/reports

# Archive audit logs
db2audit archive database MERIDIAN

# Extract from archive
db2audit extract delasc from files /db2audit/archive/db2audit.db.MERIDIAN.log.*

The extracted files can be loaded into analysis tables for reporting:

-- Create a table to hold extracted audit records
CREATE TABLE meridian_audit.execute_log (
    timestamp       TIMESTAMP,
    category        CHAR(8),
    authid          VARCHAR(128),
    application_id  VARCHAR(128),
    statement_text  CLOB(1M),
    status          INTEGER,
    object_schema   VARCHAR(128),
    object_name     VARCHAR(128)
);

-- Load extracted audit data
LOAD FROM /db2audit/reports/execute.del OF DEL
    INSERT INTO meridian_audit.execute_log;

z/OS: SMF Records for Audit

On z/OS, DB2 audit data is written to SMF (System Management Facility) records — the standard z/OS auditing infrastructure. DB2 uses several SMF record types:

To enable DB2 audit tracing on z/OS:

-START TRACE(AUDIT) CLASS(1,2,3) DEST(SMF)

SMF records are typically processed by enterprise audit tools (IBM Guardium, BMC Audit for DB2, or custom DFSORT/ICETOOL extracts). The advantage of SMF integration is that DB2 audit records flow into the same infrastructure used for all z/OS auditing — providing a unified view across the entire mainframe environment.

//* z/OS JCL to extract DB2 audit records from SMF
//EXTRACT EXEC PGM=IFASMFDP
//SYSPRINT DD SYSOUT=*
//DUMPIN   DD DSN=SYS1.MAN1,DISP=SHR
//DUMPOUT  DD DSN=DB2.AUDIT.EXTRACT,DISP=(NEW,CATLG),
//            SPACE=(CYL,(50,10),RLSE)
//SYSIN    DD *
  INDD(DUMPIN,OPTIONS(DUMP))
  OUTDD(DUMPOUT,TYPE(142))
/*

Audit Policy Best Practices

1. Audit all authentication attempts (VALIDATE) — both success and failure. Failed logins may indicate an attack.

2. Audit all privilege changes (SECMAINT) — every GRANT and REVOKE should be recorded permanently.

3. Audit sensitive table access with EXECUTE — for tables containing PII, PHI, or financial data, record who accessed what.

4. Use EXECUTE WITH DATA cautiously — capturing actual data values in audit logs creates a second copy of sensitive data. Ensure audit logs are protected with the same rigor as the database itself.

5. Use ERROR TYPE AUDIT for compliance-critical policies — this guarantees no unaudited access.

6. Archive and protect audit logs — audit logs must be immutable and retained per regulatory requirements (typically 7 years for financial data, 6 years for HIPAA).

7. Monitor audit log volume — EXECUTE-level auditing generates enormous volumes. Size your audit infrastructure accordingly.

19.10 Encryption — Data at Rest and In Transit

The Three States of Data

Data exists in three states, each requiring different encryption approaches:

1. In transit — Moving between client and server over the network
2. At rest — Stored on disk (database files, backup files, log files)
3. In use — Being processed in memory

DB2 addresses the first two comprehensively.

Encryption In Transit: SSL/TLS

Both DB2 for z/OS and DB2 for LUW support SSL/TLS encryption for network communications:

LUW SSL Configuration

# Step 1: Create a key database (using GSKit)
gsk8capicmd_64 -keydb -create \
    -db /home/db2inst1/sqllib/security/keystore/db2server.kdb \
    -pw "strong_password" \
    -type cms -stash

# Step 2: Import or create a server certificate
gsk8capicmd_64 -cert -create \
    -db /home/db2inst1/sqllib/security/keystore/db2server.kdb \
    -stashed \
    -label "db2server_cert" \
    -dn "CN=db2server.meridianbank.com,O=Meridian Bank,C=US" \
    -size 2048

# Step 3: Configure DB2 to use SSL
db2 update dbm cfg using SSL_SVR_KEYDB /home/db2inst1/sqllib/security/keystore/db2server.kdb
db2 update dbm cfg using SSL_SVR_STASH /home/db2inst1/sqllib/security/keystore/db2server.sth
db2 update dbm cfg using SSL_SVR_LABEL db2server_cert
db2 update dbm cfg using SSL_SVCENAME 50001

# Step 4: Restart DB2
db2stop
db2start

z/OS SSL Configuration

On z/OS, SSL/TLS is configured through AT-TLS (Application Transparent Transport Layer Security), which provides encryption at the TCP/IP stack level without application changes:

/* z/OS Policy Agent AT-TLS configuration */
TTLSRule                DB2_DDF_Secure
{
    LocalPortRange     446
    Direction          Inbound
    TTLSGroupAction    DB2_DDF_Group
    {
        TTLSEnabled    On
        Trace          2
    }
    TTLSEnvironmentAction DB2_DDF_Env
    {
        HandshakeRole  Server
        TTLSKeyringParmsRef DB2_Keyring
        {
            Keyring    MERIDIAN/DB2RING
        }
        TTLSCipherParmsRef DB2_Ciphers
        {
            V3CipherSuites TLS_RSA_WITH_AES_256_CBC_SHA256
            V3CipherSuites TLS_RSA_WITH_AES_128_CBC_SHA256
        }
    }
}

Encryption At Rest

LUW: Native Encryption (Db2 11.1+)

DB2 for LUW supports native encryption at the database level, which encrypts all data, indexes, LOBs, logs, and temporary files:

-- Create an encrypted database
CREATE DATABASE MERIDIAN
    ENCRYPT;

-- Or encrypt an existing database (requires backup/restore cycle)
-- Step 1: Backup
BACKUP DATABASE MERIDIAN TO /backups

-- Step 2: Restore with encryption
RESTORE DATABASE MERIDIAN FROM /backups
    ENCRYPT;

The encryption key is stored in a keystore file protected by a master key. For enterprise key management, DB2 integrates with:

• IBM Security Guardium Key Lifecycle Manager (GKLM)
• PKCS#11 hardware security modules (HSMs)
• Third-party key management systems via KMIP

-- LUW: Configure centralized key management
db2 update dbm cfg using KEYSTORE_TYPE PKCS12
db2 update dbm cfg using KEYSTORE_LOCATION /opt/ibm/db2keystore/db2keys.p12

z/OS: Encryption with Hardware Crypto

DB2 for z/OS leverages the mainframe's hardware cryptographic processors (Crypto Express adapters) for encryption with minimal performance impact:

-- z/OS: Create an encrypted tablespace
CREATE TABLESPACE ACCTDATA
    IN MERIDIAN
    USING STOGROUP MERSG01
    ERASE YES
    COMPRESS YES
    DSSIZE 16G
    SEGSIZE 64
    BUFFERPOOL BP32K1;

-- Apply z/OS DFSMS data set encryption
-- (Configured via RACF CSFKEYS class and SMS data class)

z/OS data set encryption is managed through RACF and SMS (Storage Management Subsystem), providing transparent encryption at the storage level. DB2 data sets, including table spaces, index spaces, and log data sets, can be encrypted without any application changes.

Column-Level Encryption

For encrypting individual columns (e.g., Social Security numbers, credit card numbers), DB2 provides encryption functions:

-- Encrypt a value before storing
INSERT INTO meridian.customers (customer_id, ssn_encrypted)
VALUES (1001, ENCRYPT('123-45-6789', 'encryption_password'));

-- Decrypt when retrieving
SELECT customer_id, DECRYPT_CHAR(ssn_encrypted, 'encryption_password')
FROM meridian.customers
WHERE customer_id = 1001;

However, column-level encryption using SQL functions has significant drawbacks: the encryption password must be managed by the application, encrypted columns cannot be indexed for searching, and key management is manual. For most use cases, native database encryption (at-rest encryption) combined with RCAC column masking provides better security with less complexity.

Meridian Bank Standard: All production databases use native at-rest encryption. SSL/TLS is required for all connections. Column-level encryption is used only for specific PCI-DSS requirements where cardholder data must be encrypted at the column level independent of database-level encryption.

19.11 Compliance Patterns

PCI-DSS: Payment Card Industry Data Security Standard

PCI-DSS defines requirements for protecting cardholder data. Here is how DB2 features map to key PCI-DSS requirements:

Implementation pattern:

-- PCI-DSS: Create a role for payment processing
CREATE ROLE pci_payment_processor;
GRANT SELECT ON TABLE meridian.card_transactions TO ROLE pci_payment_processor;
GRANT INSERT ON TABLE meridian.card_transactions TO ROLE pci_payment_processor;

-- PCI-DSS: Mask the Primary Account Number (PAN)
CREATE MASK meridian.mask_card_pan
    ON meridian.card_transactions
    FOR COLUMN card_number
    RETURN
        CASE
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'PCI_PAYMENT_PROCESSOR') = 1
                THEN card_number   -- full PAN only for authorized processors
            ELSE
                'XXXX-XXXX-XXXX-' || RIGHT(card_number, 4)
        END
    ENABLE;

-- PCI-DSS: Audit all access to cardholder data
CREATE AUDIT POLICY pci_audit_policy
    CATEGORIES
        EXECUTE WITH DATA STATUS BOTH,
        CHECKING STATUS BOTH,
        CONTEXT STATUS BOTH
    ERROR TYPE AUDIT;

AUDIT TABLE meridian.card_transactions USING POLICY pci_audit_policy;

HIPAA: Health Insurance Portability and Accountability Act

HIPAA's Security Rule requires protections for electronic Protected Health Information (ePHI). DB2 mappings:

SOX: Sarbanes-Oxley Act

SOX Section 404 requires internal controls over financial reporting. For DB2, this means:

-- SOX: Audit all changes to financial tables
CREATE AUDIT POLICY sox_financial_policy
    CATEGORIES
        EXECUTE STATUS BOTH,
        OBJMAINT STATUS BOTH,     -- track schema changes
        SECMAINT STATUS BOTH,     -- track privilege changes
        SYSADMIN STATUS BOTH      -- track admin operations
    ERROR TYPE AUDIT;

AUDIT TABLE meridian.general_ledger USING POLICY sox_financial_policy;
AUDIT TABLE meridian.accounts USING POLICY sox_financial_policy;
AUDIT TABLE meridian.transactions USING POLICY sox_financial_policy;

-- SOX: Enforce separation of duties
-- Ensure no one holds both DBADM and SECADM
-- (organizational policy, verified by audit query)
SELECT a.GRANTEE, 'DBADM' AS authority
FROM SYSCAT.DBAUTH a WHERE a.DBADMAUTH = 'Y'
INTERSECT
SELECT b.GRANTEE, 'DBADM'
FROM SYSCAT.DBAUTH b WHERE b.SECURITYADMAUTH = 'Y';
-- This query should return ZERO rows

GDPR: General Data Protection Regulation

GDPR requires the ability to mask, anonymize, and delete personal data. DB2's RCAC column masks directly support data minimization:

-- GDPR: Mask personal data for analytics users
CREATE MASK meridian.mask_customer_name
    ON meridian.customers
    FOR COLUMN customer_name
    RETURN
        CASE
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'CUSTOMER_SERVICE') = 1
                THEN customer_name
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'ANALYTICS') = 1
                THEN 'ANONYMIZED_' || CAST(customer_id AS VARCHAR(20))
            ELSE '***RESTRICTED***'
        END
    ENABLE;

-- GDPR: Right to erasure — delete customer data
-- (Requires careful FK handling; typically a stored procedure)
CREATE PROCEDURE meridian.gdpr_erase_customer(IN p_customer_id INTEGER)
LANGUAGE SQL
BEGIN
    DELETE FROM meridian.customer_communications
        WHERE customer_id = p_customer_id;
    DELETE FROM meridian.customer_preferences
        WHERE customer_id = p_customer_id;
    UPDATE meridian.transactions
        SET customer_name = 'ERASED', notes = 'GDPR erasure'
        WHERE customer_id = p_customer_id;
    -- Retain transaction records for regulatory requirements
    -- but anonymize personal identifiers
    DELETE FROM meridian.customers
        WHERE customer_id = p_customer_id;
END;

19.12 The Meridian National Bank Security Model

Overview

Now we bring everything together. The Meridian National Bank security model demonstrates how authentication, authorization, RCAC, trusted contexts, encryption, and audit work as a unified system. This is not a toy example — it is a production-grade security architecture for a banking database.

Role Definitions

-- ==============================================
-- MERIDIAN NATIONAL BANK: ROLE HIERARCHY
-- ==============================================

-- Base role: all bank employees
CREATE ROLE bank_employee;

-- Operational roles
CREATE ROLE teller;
CREATE ROLE senior_teller;
CREATE ROLE branch_manager;
CREATE ROLE regional_manager;

-- Specialized roles
CREATE ROLE loan_officer;
CREATE ROLE customer_service;

-- Oversight roles
CREATE ROLE auditor;
CREATE ROLE compliance_officer;

-- Technical roles
CREATE ROLE dba_operations;
CREATE ROLE security_admin;
CREATE ROLE app_developer;
CREATE ROLE etl_operator;

-- Role hierarchy
GRANT ROLE bank_employee TO ROLE teller;
GRANT ROLE teller TO ROLE senior_teller;
GRANT ROLE senior_teller TO ROLE branch_manager;
GRANT ROLE branch_manager TO ROLE regional_manager;
GRANT ROLE bank_employee TO ROLE loan_officer;
GRANT ROLE bank_employee TO ROLE customer_service;

Privilege Assignments

-- Bank employee: basic connectivity and read access
GRANT CONNECT ON DATABASE TO ROLE bank_employee;
GRANT SELECT ON TABLE meridian.customers TO ROLE bank_employee;
GRANT SELECT ON TABLE meridian.accounts TO ROLE bank_employee;

-- Teller: transaction processing
GRANT INSERT ON TABLE meridian.transactions TO ROLE teller;
GRANT UPDATE (balance, last_activity_date)
    ON TABLE meridian.accounts TO ROLE teller;
GRANT EXECUTE ON PROCEDURE meridian.process_deposit TO ROLE teller;
GRANT EXECUTE ON PROCEDURE meridian.process_withdrawal TO ROLE teller;

-- Branch manager: reporting and overrides
GRANT SELECT ON TABLE meridian.branch_performance TO ROLE branch_manager;
GRANT EXECUTE ON PROCEDURE meridian.override_transaction_limit TO ROLE branch_manager;
GRANT EXECUTE ON PROCEDURE meridian.approve_account_opening TO ROLE branch_manager;

-- Auditor: read everything, modify nothing
GRANT SELECT ON TABLE meridian.accounts TO ROLE auditor;
GRANT SELECT ON TABLE meridian.customers TO ROLE auditor;
GRANT SELECT ON TABLE meridian.transactions TO ROLE auditor;
GRANT SELECT ON TABLE meridian.audit_log TO ROLE auditor;
-- Auditor gets NO INSERT, UPDATE, or DELETE on any table

-- DBA operations: administrative tasks, no business data access by default
GRANT DBADM ON DATABASE TO ROLE dba_operations;

Row-Level Security: Branch Scoping

The most important RCAC rule for Meridian: tellers and branch managers see only their branch's data.

-- Row permission: branch-scoped access for operational roles
CREATE PERMISSION meridian.perm_accounts_branch
    ON meridian.accounts
    FOR ROWS WHERE
        -- Tellers and managers see their branch
        branch_id IN (
            SELECT branch_id
            FROM meridian.employee_assignments
            WHERE employee_id = SESSION_USER
              AND assignment_status = 'ACTIVE'
        )
        -- Regional managers see all branches in their region
        OR branch_id IN (
            SELECT b.branch_id
            FROM meridian.branches b
            JOIN meridian.region_assignments ra
                ON b.region_id = ra.region_id
            WHERE ra.employee_id = SESSION_USER
              AND ra.assignment_status = 'ACTIVE'
        )
        -- Auditors and compliance see everything
        OR VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
        OR VERIFY_ROLE_FOR_USER(SESSION_USER, 'COMPLIANCE_OFFICER') = 1
        -- DBAs see everything (for maintenance)
        OR VERIFY_ROLE_FOR_USER(SESSION_USER, 'DBA_OPERATIONS') = 1
    ENFORCED FOR ALL ACCESS
    ENABLE;

ALTER TABLE meridian.accounts ACTIVATE ROW ACCESS CONTROL;

Column Masking: Protecting Sensitive Fields

-- Mask SSN based on role
CREATE MASK meridian.mask_customer_ssn
    ON meridian.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_OFFICER') = 1
                THEN ssn
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'BRANCH_MANAGER') = 1
                THEN 'XXX-XX-' || RIGHT(ssn, 4)
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'TELLER') = 1
                THEN 'XXX-XX-' || RIGHT(ssn, 4)
            ELSE 'XXX-XX-XXXX'
        END
    ENABLE;

-- Mask account numbers for non-operational roles
CREATE MASK meridian.mask_account_number
    ON meridian.accounts
    FOR COLUMN account_number
    RETURN
        CASE
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'TELLER') = 1
                THEN account_number
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'BRANCH_MANAGER') = 1
                THEN account_number
            WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1
                THEN account_number
            ELSE '****' || RIGHT(account_number, 4)
        END
    ENABLE;

ALTER TABLE meridian.customers ACTIVATE COLUMN ACCESS CONTROL;
ALTER TABLE meridian.accounts ACTIVATE COLUMN ACCESS CONTROL;

Trusted Context: Online Banking Application

-- Trusted context for the online banking application server
CREATE TRUSTED CONTEXT meridian_online_banking
    BASED UPON CONNECTION USING SYSTEM AUTHID meridian_web_svc
    ATTRIBUTES (
        ADDRESS '10.0.5.0/24',
        ENCRYPTION 'HIGH'
    )
    DEFAULT ROLE bank_employee
    ENABLE;

-- Trusted context for the batch processing system
CREATE TRUSTED CONTEXT meridian_batch_processing
    BASED UPON CONNECTION USING SYSTEM AUTHID meridian_batch_svc
    ATTRIBUTES (
        ADDRESS '10.0.6.10',
        ENCRYPTION 'HIGH'
    )
    DEFAULT ROLE etl_operator
    ENABLE;

Comprehensive Audit Policy

-- Audit all access to customer and account data
CREATE AUDIT POLICY meridian_data_access_policy
    CATEGORIES
        EXECUTE STATUS BOTH,
        CHECKING STATUS BOTH,
        CONTEXT STATUS BOTH
    ERROR TYPE AUDIT;

AUDIT TABLE meridian.accounts USING POLICY meridian_data_access_policy;
AUDIT TABLE meridian.customers USING POLICY meridian_data_access_policy;
AUDIT TABLE meridian.transactions USING POLICY meridian_data_access_policy;

-- Audit all security changes
CREATE AUDIT POLICY meridian_security_policy
    CATEGORIES
        SECMAINT STATUS BOTH,
        SYSADMIN STATUS BOTH,
        AUDIT STATUS BOTH,
        VALIDATE STATUS BOTH
    ERROR TYPE AUDIT;

AUDIT DATABASE USING POLICY meridian_security_policy;

-- Enhanced monitoring for DBA activity
CREATE AUDIT POLICY meridian_dba_policy
    CATEGORIES
        EXECUTE WITH DATA STATUS BOTH,
        CONTEXT STATUS BOTH,
        OBJMAINT STATUS BOTH
    ERROR TYPE AUDIT;

AUDIT ROLE dba_operations USING POLICY meridian_dba_policy;

Security Verification Queries

After implementing the security model, run these verification queries to confirm correct configuration:

-- Verify no user holds both DBADM and SECADM
SELECT GRANTEE FROM SYSCAT.DBAUTH
WHERE DBADMAUTH = 'Y'
INTERSECT
SELECT GRANTEE FROM SYSCAT.DBAUTH
WHERE SECURITYADMAUTH = 'Y';
-- Expected: empty result set

-- List all roles and their members
SELECT r.ROLENAME, ra.GRANTEE, ra.GRANTEETYPE
FROM SYSCAT.ROLES r
JOIN SYSCAT.ROLEAUTH ra ON r.ROLENAME = ra.ROLENAME
ORDER BY r.ROLENAME, ra.GRANTEE;

-- List all RCAC permissions
SELECT CONTROLNAME, TABSCHEMA, TABNAME, CONTROLTYPE, ENABLE
FROM SYSCAT.CONTROLS
ORDER BY TABSCHEMA, TABNAME;

-- List all audit policies and their targets
SELECT AUDITPOLICYNAME, OBJECTTYPE, OBJECTSCHEMA, OBJECTNAME
FROM SYSCAT.AUDITUSE
ORDER BY AUDITPOLICYNAME;

-- Check for users with excessive privileges
SELECT GRANTEE, GRANTEETYPE,
    CONTROLAUTH, ALTERAUTH, DELETEAUTH,
    INSERTAUTH, SELECTAUTH, UPDATEAUTH
FROM SYSCAT.TABAUTH
WHERE TABSCHEMA = 'MERIDIAN'
ORDER BY GRANTEE;

Security Model Summary

The complete Meridian security architecture follows defense in depth:

Spaced Review: Chapters 4, 12, and 17

These questions revisit material from earlier chapters to strengthen long-term retention. Answer from memory before checking.

From Chapter 4: Data Types and Table Design

1. What is the difference between CHAR(10) and VARCHAR(10) in terms of storage? CHAR(10) always stores exactly 10 bytes, padding shorter values with spaces. VARCHAR(10) stores only the actual characters plus a 2-byte length prefix. For columns where values vary in length, VARCHAR saves significant storage. For fixed-length codes (state abbreviations, currency codes), CHAR is more efficient.

2. Why does Meridian Bank use DECIMAL(15,2) instead of FLOAT for monetary amounts? FLOAT uses binary floating-point representation, which introduces rounding errors in decimal calculations. DECIMAL(15,2) stores exact decimal values. In banking, a rounding error of even one cent across millions of transactions creates reconciliation failures and regulatory findings.

3. What constraint would you use to ensure that an account balance never goes negative? A CHECK constraint: ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);. This enforces the rule at the database level regardless of which application performs the update.

From Chapter 12: Indexes and Query Performance

4. When would you create a composite index on (branch_id, account_type) versus two separate indexes? A composite index is preferred when queries frequently filter on both columns together (WHERE branch_id = 101 AND account_type = 'CHECKING'), because the index can satisfy the entire predicate in a single lookup. Separate indexes require index ANDing, which is less efficient. However, if queries also frequently filter on account_type alone (without branch_id), a separate index on account_type may be needed — a composite index is useful only when the leading columns are specified.

5. How does RCAC interact with indexes? When RCAC row permissions filter rows, DB2 applies the permission predicate to the query. The optimizer can use indexes to efficiently evaluate these predicates. If a row permission filters by branch_id, an index on branch_id improves performance for RCAC-filtered queries. Design your indexes with RCAC predicates in mind.

From Chapter 17: Backup, Recovery, and High Availability

6. If a security breach is detected at 2:00 PM, how does the recovery strategy change? Point-in-time recovery becomes relevant. You can restore a backup and roll forward logs to a point before the breach occurred, but this means losing all legitimate transactions after that point. A better approach: identify the specific unauthorized changes from audit logs and reverse them with compensating transactions, preserving all legitimate work. This is why comprehensive audit logging is not optional — it is your forensic evidence.

7. Are audit logs included in database backups? Why does this matter? On LUW, audit logs are separate from database backups. They must be archived independently. If you restore a database from backup but lose the audit logs, you have no record of what happened during the lost period — which is a compliance violation. Ensure audit log archival is part of your backup strategy.

Summary

Security is not a feature — it is an architecture. In this chapter, you have learned:

• DB2 separates authentication (external) from authorization (internal), a design that delegates identity verification to specialized systems while keeping access control within the database engine.
• Privileges follow the principle of least privilege: GRANT only what is needed, at the most specific level possible (column before table, table before schema, schema before database).
• Roles simplify privilege management by creating named collections of privileges that map to job functions, with role hierarchies that mirror organizational structures.
• RCAC provides row-level and column-level security that cannot be bypassed through direct table access — essential for multi-tenant data, branch-scoped access, and PII protection.
• Trusted contexts bridge the gap between three-tier architectures and individual accountability, enabling per-user audit trails and RCAC enforcement even when all connections come through a shared service account.
• Audit policies provide the evidence trail that compliance demands: who accessed what, when, and from where.
• Encryption protects data at rest and in transit, with DB2 supporting native database encryption, SSL/TLS, and integration with enterprise key management systems.

The Meridian National Bank security model demonstrates how all these layers work together: roles define what each job function can do, RCAC limits what data each person can see, trusted contexts ensure individual accountability through shared application servers, and audit policies record everything for regulatory compliance.

In the next chapter, we will turn to performance monitoring and tuning — because a secure database that cannot meet its performance SLAs is a secure database that will be bypassed by frustrated developers. Security and performance must coexist, and we will show you how.

Return to Chapter 19 | Continue to Exercises | Quiz | Key Takeaways