Chapter 19 Quiz: Security and Authorization

Test your understanding of Chapter 19. Attempt all questions from memory before revealing the answers. The act of retrieval — even when you get it wrong — strengthens learning more than re-reading.

Scoring Guide: - 22-25 correct: Excellent — you have strong command of DB2 security concepts - 17-21 correct: Good — review the sections where you missed questions - 12-16 correct: Fair — re-read the chapter with the quiz questions in mind - Below 12: Security is too important to be fuzzy on. Re-read the chapter carefully, then retake this quiz in 48 hours

Multiple Choice Questions

Question 1

Which of the following best describes DB2's approach to authentication?

A) DB2 stores user passwords in the system catalog and validates them internally B) DB2 delegates authentication to an external system (OS, LDAP, RACF) and handles authorization internally C) DB2 handles both authentication and authorization through GRANT/REVOKE statements D) DB2 uses certificate-based authentication exclusively

Answer **B) DB2 delegates authentication to an external system (OS, LDAP, RACF) and handles authorization internally** This is DB2's fundamental security principle: "authenticate externally, authorize internally." DB2 never stores passwords. It relies on operating system authentication, LDAP, Kerberos, RACF (z/OS), or GSS-API plugins for identity verification, then uses its internal catalog (SYSCAT.DBAUTH, SYSCAT.TABAUTH, etc.) for authorization decisions.

Question 2

On DB2 for z/OS, what is the primary external security manager used for authentication?

A) LDAP B) Kerberos C) RACF D) PAM

Answer **C) RACF** RACF (Resource Access Control Facility) is IBM's external security manager for z/OS. While ACF2 and Top Secret are alternatives, RACF is by far the most common in DB2 for z/OS environments, especially in banking and government. RACF validates user credentials through the RACROUTE macro and returns a verified identity that becomes the DB2 authorization ID.

Question 3

Which LUW authentication type should NEVER be used in a production environment?

A) SERVER B) SERVER_ENCRYPT C) CLIENT D) KERBEROS

Answer **C) CLIENT** CLIENT authentication trusts the client system to verify the user's identity. DB2 performs no authentication of its own. This is acceptable only in development/test environments on fully isolated networks. In production, it means anyone who can reach the DB2 port can connect as any user without providing a password — a catastrophic security gap.

Question 4

What is the maximum length of a RACF user ID used as a DB2 authorization ID on z/OS?

A) 8 characters B) 16 characters C) 30 characters D) 128 characters

Answer **A) 8 characters** RACF user IDs are limited to 8 characters, and since the DB2 for z/OS authorization ID is derived from the RACF user ID, it inherits this limitation. This constraint has significant implications for naming conventions and is one of the notable differences between z/OS and LUW, where auth IDs can be longer.

Question 5

Which privilege is required for a user to connect to a DB2 database?

A) SELECT B) CONNECT C) BINDADD D) CREATETAB

Answer **B) CONNECT** CONNECT is the most fundamental database-level privilege. Without it, a user cannot establish a connection to the database, even if they have been authenticated at the operating system or RACF level. It is typically the first privilege granted to any user or role.

Question 6

What does the WITH GRANT OPTION clause do when used in a GRANT statement?

A) Grants the privilege immediately without requiring a commit B) Allows the grantee to grant the same privilege to other users C) Makes the grant permanent and immune to REVOKE D) Applies the grant to all tables in the schema

Answer **B) Allows the grantee to grant the same privilege to other users** WITH GRANT OPTION creates a privilege chain: the grantee can pass the privilege to other users, who may in turn pass it further (if they also received WITH GRANT OPTION). This is powerful but risky — it makes privilege lineage harder to audit and can lead to uncontrolled privilege propagation. On LUW, revoking from the original grantee cascades to all downstream grants.

Question 7

Which authority is specifically designed for security administration and CANNOT access data directly?

A) DBADM B) SYSADM C) SECADM D) DATAACCESS

Answer **C) SECADM** SECADM (Security Administrator) can manage security objects — roles, RCAC permissions and masks, audit policies, LBAC components, and trusted contexts — but cannot SELECT, INSERT, UPDATE, or DELETE data from tables. This separation of duties ensures that the person who defines access control policies cannot also access the data protected by those policies.

Question 8

What happens when you activate row access control on a table without first creating any row permissions?

A) All users retain their existing access B) Only SYSADM can access the data C) Nobody can access any rows, including DBADM D) The activation command fails with an error

Answer **C) Nobody can access any rows, including DBADM** When row access control is activated, DB2 implicitly denies access to all rows unless a permission explicitly grants it. This is a deny-by-default model. If no permissions exist, no one — not even DBADM — can see any data. Only SECADM can create the permissions needed to restore access. This is the most dangerous mistake during RCAC deployment and why you must always create permissions before activation.

Question 9

In the following RCAC column mask, what will a user with the TELLER role see for the SSN column?

CREATE MASK mask_ssn ON customers FOR COLUMN ssn
RETURN CASE
    WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'AUDITOR') = 1 THEN ssn
    WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'TELLER') = 1 THEN 'XXX-XX-' || RIGHT(ssn, 4)
    ELSE 'XXX-XX-XXXX'
END ENABLE;

A) The full SSN value B) 'XXX-XX-' followed by the last 4 digits C) 'XXX-XX-XXXX' D) NULL

Answer **B) 'XXX-XX-' followed by the last 4 digits** The CASE expression evaluates in order. If the user has the TELLER role, the second WHEN clause matches and returns the partially masked SSN. The AUDITOR check comes first, so if a user somehow has both roles, they would see the full SSN (AUDITOR takes precedence). The ELSE clause applies to everyone without AUDITOR or TELLER roles.

Question 10

What is the purpose of a trusted context in DB2?

A) To encrypt data stored in trusted tablespaces B) To allow an application server to switch the DB2 auth ID to the actual end user without re-authenticating C) To bypass RCAC rules for trusted applications D) To grant automatic DBADM to trusted IP addresses

Answer **B) To allow an application server to switch the DB2 auth ID to the actual end user without re-authenticating** Trusted contexts solve the three-tier authentication problem. An application server connects using a service account, but the trusted context allows it to switch the DB2 session auth ID to match the actual end user. This enables per-user RCAC enforcement, per-user audit trails, and per-user privilege checking — all without requiring each end user to maintain a separate DB2 connection.

Question 11

Which audit category records authentication successes and failures?

A) EXECUTE B) CHECKING C) VALIDATE D) CONTEXT

Answer **C) VALIDATE** VALIDATE records all authentication attempts, both successful and failed. This is critical for detecting brute-force attacks, compromised credentials, and unauthorized access attempts. CHECKING records authorization checks (privilege verification), EXECUTE records SQL execution, and CONTEXT records connection and operation context information.

Question 12

What does ERROR TYPE AUDIT mean in a CREATE AUDIT POLICY statement?

A) Audit records are written to the error log B) Only error conditions are audited C) If DB2 cannot write the audit record, the audited operation fails D) Audit policy creation errors are logged

Answer **C) If DB2 cannot write the audit record, the audited operation fails** ERROR TYPE AUDIT is a critical compliance feature. It guarantees that no unaudited access can occur. If the audit subsystem fails (disk full, I/O error, etc.), DB2 will fail the operation rather than allow it to proceed without being recorded. This is required by several compliance frameworks (PCI-DSS, SOX) to ensure the integrity of the audit trail.

Question 13

On z/OS, DB2 audit records are written to which system facility?

A) Syslog B) SMF (System Management Facility) C) VSAM audit dataset D) DB2 diagnostic log

Answer **B) SMF (System Management Facility)** DB2 for z/OS writes audit trace records to SMF, the standard z/OS auditing infrastructure. DB2 uses SMF record types 101, 102, and 142 for various audit categories. This integration means DB2 audit records flow into the same enterprise audit infrastructure used by all z/OS subsystems, enabling unified compliance reporting.

Question 14

Which of the following is NOT a valid DB2 for LUW administrative authority?

A) SYSADM B) SECADM C) SYSOPER D) SQLADM

Answer **C) SYSOPER** SYSOPER is an Oracle administrative authority, not a DB2 one. The valid DB2 for LUW administrative authorities include: SYSADM, SYSCTRL, SYSMAINT, SYSMON (at the instance level) and DBADM, SECADM, SQLADM, WLMADM, ACCESSCTRL, DATAACCESS (at the database level).

Question 15

What is the recommended minimum authentication type for DB2 for LUW production environments?

A) CLIENT B) SERVER C) SERVER_ENCRYPT D) DATA_ENCRYPT

Answer **C) SERVER_ENCRYPT** SERVER_ENCRYPT ensures that credentials (user ID and password) are encrypted during transmission, preventing network sniffing attacks. Plain SERVER transmits credentials in cleartext or weakly hashed form. DATA_ENCRYPT provides even stronger protection by encrypting all data in transit, but SERVER_ENCRYPT is the minimum acceptable standard for production. CLIENT authentication is never acceptable in production.

Question 16

In a DB2 role hierarchy where ROLE_A is granted to ROLE_B, and ROLE_B is granted to USER_X, which statement is true?

A) USER_X has only the privileges of ROLE_B B) USER_X has the privileges of both ROLE_A and ROLE_B C) USER_X must explicitly request ROLE_A privileges at connection time D) Role hierarchies are not supported in DB2

Answer **B) USER_X has the privileges of both ROLE_A and ROLE_B** When a role is granted to another role, it creates a hierarchy where the receiving role inherits all privileges of the granted role. Since USER_X holds ROLE_B, and ROLE_B includes ROLE_A, USER_X effectively has the combined privileges of both roles. This is how DB2 supports organizational hierarchies — a branch_manager role that includes a teller role inherits all teller privileges.

Question 17

Which function is used within RCAC permissions and masks to check if a user holds a specific role?

A) HAS_ROLE() B) CHECK_ROLE() C) VERIFY_ROLE_FOR_USER() D) USER_IN_ROLE()

Answer **C) VERIFY_ROLE_FOR_USER()** VERIFY_ROLE_FOR_USER(auth_id, role_name) returns 1 if the specified auth ID holds the specified role, and 0 otherwise. This function is essential for writing role-based RCAC rules. The companion function VERIFY_GROUP_FOR_USER() performs the same check for groups. Using these functions keeps RCAC rules role-based rather than hardcoding individual user names.

Question 18

What is the RACF class used to control who can connect to (execute plans against) a DB2 subsystem on z/OS?

A) FACILITY B) DSNR C) DB2SEC D) SURROGAT

Answer **B) DSNR** DSNR is the RACF class that controls DB2 plan execution authorization on z/OS. Profiles in the DSNR class follow the pattern `ssid.EXECUTE.planname`, where ssid is the DB2 subsystem identifier. The DSNADM class controls administrative functions, and facility classes control other z/OS-level operations.

Question 19

When configuring SSL/TLS for DB2 for LUW, which tool is used to create the key database?

A) openssl B) keytool C) gsk8capicmd_64 D) db2certmgr

Answer **C) gsk8capicmd_64** DB2 for LUW uses IBM's Global Security Kit (GSKit) for SSL/TLS operations. The gsk8capicmd_64 command-line tool creates and manages key databases (.kdb files), generates certificate requests, imports certificates, and manages trust chains. While openssl and keytool can create certificates, the DB2 GSKit key database format requires the GSKit tools.

Question 20

Which RCAC feature would you use to ensure that a teller can see account records but the SSN column always shows 'XXX-XX-XXXX'?

A) Row permission B) Column mask C) Security label D) Trusted context

Answer **B) Column mask** Column masks transform the values returned for specific columns based on the querying user's identity or role. Unlike row permissions (which filter entire rows), column masks return all accessible rows but redact or transform the values in specific columns. A column mask on the SSN column can return 'XXX-XX-XXXX' for tellers while showing the full value for auditors.

Question 21

For PCI-DSS compliance, which audit category would you use to capture the actual SQL statements executed against cardholder data tables?

A) VALIDATE B) CHECKING C) EXECUTE D) SECMAINT

Answer **C) EXECUTE** The EXECUTE audit category records SQL statement execution, optionally including the actual SQL text and data values (EXECUTE WITH DATA). For PCI-DSS requirement 10.2, which mandates logging all access to cardholder data, EXECUTE-level auditing on tables containing PANs and other cardholder data provides the required evidence trail.

Question 22

What does native database encryption in DB2 for LUW (Db2 11.1+) encrypt?

A) Only user tables B) Only columns explicitly marked for encryption C) All data, indexes, LOBs, logs, and temporary files D) Only data in transit

Answer **C) All data, indexes, LOBs, logs, and temporary files** Native database encryption in DB2 for LUW provides comprehensive at-rest encryption. It encrypts everything stored on disk: table data, index data, LOB data, transaction logs, and temporary table spaces. This ensures that physical access to the storage media (stolen disk, improper disposal) does not expose data. The encryption is transparent to applications — no SQL changes required.

Question 23

In the Meridian Bank security model, why is the auditor role granted SELECT on all tables but no INSERT, UPDATE, or DELETE?

A) To reduce licensing costs B) To enforce read-only access that satisfies audit independence requirements C) Because auditors use a different database instance D) To improve query performance

Answer **B) To enforce read-only access that satisfies audit independence requirements** Audit independence requires that auditors can examine data and controls without the ability to modify what they are examining. If an auditor could modify data, they could conceal findings or create false evidence. The read-only constraint ensures that audit observations are based on unmodified data, satisfying both regulatory requirements and the principle of audit independence.

Question 24

On z/OS, how is the installation SYSADM authority configured?

A) Through a GRANT statement executed by any DBADM B) Through the SYSTEM ADMIN fields in DSNZPARM C) Through a RACF PERMIT command D) It is automatically assigned to the DB2 instance owner

Answer **B) Through the SYSTEM ADMIN fields in DSNZPARM** The installation SYSADM is the highest authority in a DB2 for z/OS subsystem. It is defined by the SYSTEM ADMIN 1 and SYSTEM ADMIN 2 fields in the DSNZPARM (subsystem parameter module), set during DB2 installation or modified through the DB2 installation panel. This is a system-level setting, not a SQL-level grant, reflecting the mainframe's security architecture where critical authorizations are managed at the system parameter level.

Question 25

Which of the following correctly describes the relationship between RCAC row permissions and column masks?

A) Row permissions are applied first, then column masks transform values in the visible rows B) Column masks are applied first, then row permissions filter based on masked values C) They are applied simultaneously in a single step D) You can use either row permissions or column masks on a table, but not both

Answer **A) Row permissions are applied first, then column masks transform values in the visible rows** The evaluation order is: row permissions filter which rows are accessible, and then column masks transform the column values in those accessible rows. This means row permission predicates see the actual (unmasked) column values, ensuring correct filtering. Column masks then transform the output so that the querying user sees only what they are authorized to see. Both can be used on the same table simultaneously.

Return to Chapter 19 | Continue to Key Takeaways