Chapter 19 Exercises: Security and Authorization

These exercises reinforce and extend the concepts from Chapter 19. They range from foundational recall to advanced design challenges. Security is a domain where getting the details wrong has real consequences — treat even the "beginner" exercises seriously.

Difficulty Ratings: - Beginner — Recall and comprehension. If you read the chapter, you can answer these. - Intermediate — Application and analysis. Requires thinking beyond what was explicitly stated. - Advanced — Synthesis and evaluation. Requires research, judgment, or integration of multiple concepts.

Part A: Authentication and Authorization Fundamentals

Exercise 19.1 — Authentication vs. Authorization (Beginner)

Explain the difference between authentication and authorization in your own words. For each of the following scenarios, identify whether it is an authentication issue, an authorization issue, or both:

a) A user cannot connect to the database because their password has expired. b) A user can connect to the database but receives SQL0551N when querying a table. c) A user's LDAP account is locked after five failed login attempts. d) A DBA can create tables but cannot create audit policies. e) A JDBC application connects using a service account but needs to execute SQL as individual end users.

Exercise 19.2 — Security Layer Identification (Beginner)

For each DB2 security feature below, identify which security layer it belongs to (Network, Authentication, Administrative Authority, Database Privilege, Object Privilege, RCAC, or Encryption):

1. SSL/TLS configuration
2. GRANT SELECT ON TABLE
3. RACF profile in DSNR class
4. CREATE PERMISSION ... FOR ROWS WHERE
5. SYSADM_GROUP configuration
6. CREATE MASK ... FOR COLUMN
7. Native database encryption
8. GRANT CONNECT ON DATABASE
9. LDAP security plugin
10. Trusted context

Exercise 19.3 — Catalog Table Exploration (Beginner)

Write SQL queries against the DB2 catalog to answer each question (provide both LUW and z/OS catalog table names where applicable):

a) List all users who have SELECT privilege on the meridian.accounts table. b) List all roles defined in the database and their members. c) List all RCAC permissions and masks currently active. d) Find all tables that have audit policies applied. e) Identify all users with DBADM authority.

Part B: Privileges and GRANT/REVOKE

Exercise 19.4 — Privilege Design (Intermediate)

Meridian National Bank is adding a new job function: Loan Processor. Loan processors need to:

• Read customer information (customers table)
• Read and create loan applications (loan_applications table)
• Update the status field (only) on loan applications
• Execute the meridian.calculate_interest_rate function
• Read account balances (accounts table) but not account holder names
• Never delete any data from any table

Write the complete set of SQL statements to: 1. Create a loan_processor role 2. Grant all necessary privileges 3. Assign the role to a user named lp_garcia 4. Verify the grants by querying the catalog

Explain why you chose table-level vs. column-level privileges for each grant.

Exercise 19.5 — WITH GRANT OPTION Analysis (Intermediate)

Consider the following sequence of SQL statements:

-- Executed by SECADM
GRANT SELECT ON TABLE meridian.transactions TO USER alice WITH GRANT OPTION;

-- Executed by alice
GRANT SELECT ON TABLE meridian.transactions TO USER bob WITH GRANT OPTION;

-- Executed by bob
GRANT SELECT ON TABLE meridian.transactions TO USER carol;

-- Executed by SECADM
REVOKE SELECT ON TABLE meridian.transactions FROM USER alice;

a) After the REVOKE, can bob still SELECT from meridian.transactions? Why or why not? b) Can carol still SELECT from meridian.transactions? Why or why not? c) How does the cascading behavior differ between DB2 for z/OS and DB2 for LUW? d) What catalog table(s) would you query to trace the GRANT chain before the REVOKE? e) Why is WITH GRANT OPTION considered a security risk? What alternative approach would you recommend?

Exercise 19.6 — Privilege Audit Script (Intermediate)

Write a comprehensive SQL script that generates a "privilege report" for the meridian schema. The report should show:

1. All table-level privileges grouped by grantee
2. All column-level privileges grouped by table and grantee
3. All schema-level privileges
4. All roles and their privilege assignments
5. Any users who hold CONTROL privilege (which is a security concern)
6. Any privileges granted WITH GRANT OPTION

Format the output so that it could be presented to an auditor.

Exercise 19.7 — Privilege Revocation Impact Analysis (Advanced)

You have been asked to revoke all privileges from a user (former_dba) who has left the organization. Before executing the revocation:

a) Write queries to identify all objects owned by former_dba. b) Write queries to identify all objects that depend on privileges held by former_dba (views, packages, MQTs). c) Explain the potential impact of revoking privileges that were granted WITH GRANT OPTION. d) Write a step-by-step remediation plan that transfers ownership and privileges before revoking access. e) What catalog tables do you need to check on z/OS vs. LUW?

Part C: Roles and Administrative Authorities

Exercise 19.8 — Role Hierarchy Design (Intermediate)

Design a role hierarchy for a hospital database that supports these job functions:

• Receptionist: Can view patient demographics, schedule appointments
• Nurse: All receptionist capabilities plus view/update vital signs, view medications
• Doctor: All nurse capabilities plus prescribe medications, view/update diagnoses, order tests
• Specialist: All doctor capabilities plus access to restricted research data
• Administrator: View appointment schedules and billing, no access to medical records
• Auditor: Read-only access to everything including audit logs

Draw the role hierarchy diagram. Write the CREATE ROLE and GRANT ROLE statements. Explain how this hierarchy satisfies the HIPAA minimum necessary standard.

Exercise 19.9 — Separation of Duties (Intermediate)

Explain why DB2 separates DBADM and SECADM authorities. For each scenario below, identify which authority is required and explain why:

a) Creating a new table in the meridian schema b) Creating a row-level security permission c) Granting SELECT on a table to a role d) Creating an audit policy e) Restoring a database from backup f) Creating a trusted context g) Running RUNSTATS on a table h) Modifying an RCAC column mask

Write a verification query that checks whether any single user holds both DBADM and SECADM in your database.

Exercise 19.10 — SYSADM vs. DBADM (Advanced)

A colleague argues: "Just give everyone SYSADM — it's easier, and we can track what they do with audit logs."

Write a detailed rebuttal (at least 300 words) that addresses: a) Why audit alone is insufficient as a security control b) Specific risks of SYSADM over-provisioning c) How SYSADM can bypass or compromise audit mechanisms d) Regulatory implications (PCI-DSS, SOX, HIPAA) e) The correct alternative approach with specific DB2 features

Part D: Row and Column Access Control (RCAC)

Exercise 19.11 — Row Permission Design (Intermediate)

Meridian National Bank is expanding to support private banking for high-net-worth customers. Private banking accounts should be visible only to:

• The assigned private banker
• The head of private banking
• Compliance officers
• External auditors

Regular tellers and branch managers should not see private banking accounts at all, even if the account belongs to their branch.

Write the RCAC row permission that enforces this rule. Consider: - How to identify private banking accounts (assume a service_tier column with value 'PRIVATE') - How to identify the assigned private banker (assume a private_banker_id column) - How to handle the transition when a private banking account is converted from a regular account - Error handling: what happens if the RCAC rule has a bug?

Exercise 19.12 — Column Mask Design (Intermediate)

Design column masks for the following columns in Meridian's customers table:

Write the CREATE MASK statements for each column. Explain how the CASE expressions should be ordered and why the order matters.

Exercise 19.13 — RCAC Activation Sequence (Advanced)

You are activating RCAC on a production table that currently has 500 active users. Write a detailed deployment plan that includes:

a) Pre-deployment verification steps (what to check before activation) b) The correct order of operations (permissions, masks, activation) c) A rollback plan if RCAC causes unexpected behavior d) Testing approach to verify that each role sees the correct data e) Communication plan for affected users f) Post-deployment monitoring queries

What is the most dangerous mistake you could make during RCAC activation, and how do you prevent it?

Exercise 19.14 — LBAC vs. RCAC Comparison (Advanced)

Compare Label-Based Access Control (LBAC) with Row and Column Access Control (RCAC). For each criterion, identify which approach is more appropriate:

a) A government agency with formal classification levels (UNCLASSIFIED through TOP SECRET) b) A bank with branch-level data segregation c) A healthcare system with role-based access to patient records d) A multi-tenant SaaS application with customer-level data isolation e) A system requiring both row filtering and column masking

Under what circumstances would you use both LBAC and RCAC on the same table?

Part E: Trusted Contexts and Encryption

Exercise 19.15 — Trusted Context Design (Intermediate)

Meridian Bank's online banking application runs on three application servers (10.0.5.10, 10.0.5.11, 10.0.5.12) behind a load balancer. Design a trusted context configuration that:

a) Accepts connections from any of the three servers b) Requires encrypted connections c) Allows user switching for authenticated bank customers d) Assigns the online_customer role by default e) Allows bank employees accessing through the same app to receive their employee role instead

Write the complete CREATE TRUSTED CONTEXT and ALTER TRUSTED CONTEXT statements.

Exercise 19.16 — Encryption Decision Matrix (Intermediate)

For each of the following data elements in Meridian's database, recommend the appropriate encryption approach (none, column-level, tablespace-level, database-level, or combination) and justify your choice:

a) Customer Social Security numbers b) Account balances c) Transaction descriptions d) Internal branch performance metrics e) Credit card PANs (Primary Account Numbers) f) Audit log records g) Backup files

Exercise 19.17 — SSL/TLS Configuration (Intermediate)

Write the complete step-by-step commands to configure SSL/TLS for a DB2 for LUW instance, including:

a) Creating the key database b) Generating a certificate signing request (CSR) c) Importing a CA-signed certificate d) Configuring DB2 to use the certificate e) Testing the connection with SSL f) Verifying that non-SSL connections are rejected (if required by policy)

Part F: Audit and Compliance

Exercise 19.18 — Audit Policy Design (Intermediate)

Design audit policies for the following compliance requirements. For each, specify the audit categories, status types, error handling, and scope:

a) PCI-DSS: All access to tables containing cardholder data must be logged b) HIPAA: All failed authentication attempts and all access to patient records must be logged c) SOX: All changes to financial tables (INSERT, UPDATE, DELETE) and all DDL changes must be logged d) Internal policy: All actions by DBA accounts must be logged with full statement text and data values

Write the CREATE AUDIT POLICY and AUDIT statements for each.

Exercise 19.19 — Audit Log Analysis (Advanced)

Given the following audit log entries (simplified), identify the security incident and describe the remediation steps:

TIMESTAMP            AUTH_ID         CATEGORY   STATUS  OBJECT              STATEMENT
2025-03-15 02:14:01  BATCH_SVC       VALIDATE   0       N/A                 CONNECT
2025-03-15 02:14:03  BATCH_SVC       EXECUTE    0       MERIDIAN.CUSTOMERS  SELECT * FROM MERIDIAN.CUSTOMERS
2025-03-15 02:14:05  BATCH_SVC       EXECUTE    0       MERIDIAN.CUSTOMERS  SELECT * FROM MERIDIAN.CUSTOMERS WHERE SSN LIKE '___-__-____'
2025-03-15 02:14:07  BATCH_SVC       EXECUTE    -551    MERIDIAN.SALARIES   SELECT * FROM MERIDIAN.SALARIES
2025-03-15 02:14:08  BATCH_SVC       EXECUTE    -551    MERIDIAN.SALARIES   SELECT * FROM MERIDIAN.SALARIES
2025-03-15 02:14:09  BATCH_SVC       EXECUTE    -551    MERIDIAN.SALARIES   SELECT * FROM MERIDIAN.SALARIES
2025-03-15 02:14:10  BATCH_SVC       EXECUTE    0       MERIDIAN.CUSTOMERS  SELECT SSN, CUSTOMER_NAME, ANNUAL_INCOME FROM MERIDIAN.CUSTOMERS
2025-03-15 02:15:00  BATCH_SVC       VALIDATE   0       N/A                 DISCONNECT

a) What suspicious patterns do you observe? b) What is the likely nature of the incident? c) What immediate steps should be taken? d) What long-term remediation steps are needed? e) What additional audit policies would have provided more information?

Exercise 19.20 — Compliance Gap Analysis (Advanced)

Your organization's external auditors have identified the following findings during a PCI-DSS assessment of the Meridian DB2 environment. For each finding, describe the DB2-specific remediation:

1. Finding: Shared database accounts are used by multiple application servers.
2. Finding: Audit logs do not capture the actual SQL statement text for queries on cardholder data tables.
3. Finding: A DBA account has both administrative and security administrative privileges.
4. Finding: No row-level access controls exist; any user with SELECT on a table can see all rows.
5. Finding: Audit logs are stored on the same filesystem as the database.
6. Finding: Database connections from the application tier are not encrypted.

Part G: Integrated Design Challenges

Exercise 19.21 — Security Model for a New Application (Advanced)

Meridian National Bank is launching a mobile banking application. Design the complete security model:

a) Authentication approach (how do mobile users authenticate to DB2?) b) Trusted context configuration for the mobile API servers c) Role definitions for mobile banking users (different from in-branch users) d) RCAC rules (customers should see only their own accounts) e) Column masking (what should mobile users NOT see?) f) Audit policies (what to capture for mobile banking activity) g) Encryption requirements (mobile traffic is over the public internet)

Write all SQL statements and explain each design decision.

Exercise 19.22 — Disaster Recovery and Security (Advanced)

Consider the security implications of disaster recovery:

a) When you restore a DB2 database from backup to a DR site, what security artifacts are preserved and what must be recreated? b) How do RACF profiles on z/OS factor into DR planning? c) If the DR site uses different OS users (LUW), how does this affect DB2 security? d) Are encryption keys preserved in backups? What happens if the keystore is lost? e) How do audit log archives factor into DR?

Exercise 19.23 — Security Performance Impact (Advanced)

RCAC, audit policies, and encryption all have performance implications. Design a test plan to measure the impact of each security feature:

a) What baseline measurements should you capture before enabling security features? b) How would you measure the impact of RCAC row permissions on query performance? c) How would you measure the impact of EXECUTE-level auditing on transaction throughput? d) What DB2 monitoring tools and metrics would you use? e) If security features degrade performance by 15%, what optimizations would you consider?

Exercise 19.24 — Zero-Trust Architecture (Advanced)

"Zero trust" means every access request is verified regardless of source. Design a zero-trust DB2 security architecture for Meridian that:

a) Never trusts any connection without verification (no CLIENT authentication) b) Enforces individual accountability even through shared application layers c) Limits blast radius of any compromised credential d) Provides continuous verification (not just at connection time) e) Assumes the network is compromised

Map each requirement to specific DB2 features and configuration settings.

Exercise 19.25 — Cross-Chapter Integration (Advanced)

A security breach has been detected at Meridian National Bank. An unauthorized party gained access to customer records through a compromised application service account. Using concepts from Chapters 4, 12, 17, and 19:

a) Chapter 4 (Data Types): How does the choice of data types for SSN and account numbers affect the breach exposure? What if SSNs were stored as integers vs. encrypted strings? b) Chapter 12 (Indexes): If the attacker ran a query like SELECT * FROM customers WHERE ssn = '123-45-6789', would an index on SSN help or hurt from a security perspective? (Consider index-only access and audit.) c) Chapter 17 (Backup/Recovery): The breach occurred over three days before detection. The database has been backed up twice during that period. How do you recover to a known-good state while preserving evidence? d) Chapter 19 (Security): What specific security controls would have prevented this breach? Implement them.

Return to Chapter 19 | Continue to Quiz