Row permissions and column masks in DB2

Row access control (RAC) versus column access control

Row access control (RAC) attaches search conditions to a base table. When it is active, Db2 AND-s that combined condition into every SQL reference to the table, including through views. Users who “have SELECT” still only see rows their permissions allow. Write operations are checked the same way.

Column access control attaches one mask per column. The mask is a CASE expression. The query still returns the same number of rows (after row permissions). The column’s value in the final result may be the real value, a partial value, a category, or NULL.

MAC (mandatory access control) on z/OS is the older multilevel security model: a RACF SECLABEL column on the table. MLS and RAC are mutually exclusive on the same table. You can still activate column masks on a table that has a security label column. Pick RAC when the rule is “branch A staff see branch A customers.” Pick MLS when the rule is classification levels that RACF already understands.

The security administrator

SECADM creates, alters, drops, and comments on permissions and masks, and activates or deactivates RAC and column access control. SECADM does not get inherent SELECT on the table. That is deliberate: the person who writes the filter is not automatically the person who reads salaries.

If SEPARATE_SECURITY is YES, SYSADM and DBADM cannot activate RCAC. If it is NO, SYSADM can. CREATE PERMISSION / CREATE MASK additional object privileges (SELECT on referenced tables, EXECUTE on UDFs) are not required for SECADM when defining the rule—IBM documents that SECADM does not need those extra privileges just to reference objects inside the mask or permission.

CREATE PERMISSION, ALTER PERMISSION, DROP PERMISSION

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE PERMISSION BRANCH_STAFF ON CUSTOMER
  FOR ROWS WHERE
        VERIFY_GROUP_FOR_USER(SESSION_USER, 'STAFF') = 1
    AND BRANCH = (
          SELECT HOME_BRANCH
            FROM HR.EMP_PROFILE
           WHERE EMP_ID = SESSION_USER
        )
  ENFORCED FOR ALL ACCESS
  ENABLE;
 
CREATE PERMISSION BRANCH_MGR ON CUSTOMER
  FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER, 'MGR') = 1
  ENFORCED FOR ALL ACCESS
  ENABLE;
 
COMMIT;
 
ALTER TABLE CUSTOMER
  ACTIVATE ROW ACCESS CONTROL;

Clauses to memorize:

• FOR ROWS WHERE search-condition — same idea as a WHERE clause; it must be true to see or write the row
• ENFORCED FOR ALL ACCESS — SELECT, INSERT, UPDATE, MERGE, DELETE paths
• ENABLE or DISABLE — DISABLE is the CREATE default. A disabled permission does nothing even after RAC is activated

Multiple enabled permissions are connected with OR. In the example, a manager matches BRANCH_MGR (all rows) even if they are not in STAFF. A staff member matches only BRANCH_STAFF. There is no error when a row is hidden; it simply is not in the result.

1
2
3
ALTER PERMISSION BRANCH_STAFF DISABLE;
ALTER PERMISSION BRANCH_STAFF ENABLE;
DROP PERMISSION BRANCH_STAFF;

CREATE PERMISSION does not invalidate packages by itself. Activation of RAC, or enabling a permission while RAC is already on, does. Create the rules first, then activate, to avoid a storm of invalidations.

If you activate RAC with no permissions, Db2 installs a default permission that allows no SQL access. That fail-closed behavior surprises teams who expected “no rules means open.” Column access control is the opposite: activating masks with no masks defined does not hide columns; unmasked columns stay visible.

CREATE MASK, ALTER MASK, DROP MASK

One mask per column. The CASE result must match the column’s type, nullability, length, CCSID, and distinct type.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE MASK SSN_MASK ON EMPLOYEE
  FOR COLUMN SSN RETURN
    CASE
      WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'PAYROLL') = 1
        THEN SSN
      WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'MGR') = 1
        THEN 'XXX-XX-' || SUBSTR(SSN, 8, 4)
      ELSE NULL
    END
  ENABLE;
 
COMMIT;
 
ALTER TABLE EMPLOYEE
  ACTIVATE COLUMN ACCESS CONTROL;

ALTER MASK switches ENABLE/DISABLE. DROP MASK removes the object. You cannot put a mask on XML, LOB, FIELDPROC, period, history, archive, MQT, catalog, or temporary tables—the CREATE MASK list of excluded objects is long and IBM’s SQL Reference is the checklist.

Mask expressions: roles, authorization, session variables

Masking based on roles

Inside a trusted connection, test the Db2 role with VERIFY_ROLE_FOR_USER. That is the right function when access is “only while wearing the TELLER role,” not “in RACF group TELLER.”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE MASK INCOME_MASK ON CUSTOMER
  FOR COLUMN INCOME RETURN
    CASE
      WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'MGR') = 1
        THEN INCOME
      WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'STAFF') = 1
        THEN CASE
               WHEN INCOME >= 200000 THEN 4
               WHEN INCOME BETWEEN 100000 AND 199999 THEN 3
               WHEN INCOME BETWEEN 50000 AND 99999 THEN 2
               ELSE 1
             END
      ELSE NULL
    END
  ENABLE;

Masking based on authorization

SESSION_USER is the primary authorization ID. Compare it to a column (row-owner pattern), look it up in a mapping table, or use VERIFY_GROUP_FOR_USER for RACF groups / secondary IDs. CURRENT SQLID is a different register; do not assume it equals SESSION_USER.

Masking based on session variables

Built-in GETVARIABLE reads session variables (including ones your application sets). A connection pool can SET a tenant id or business unit, and the mask or permission can filter on that value without creating a Db2 role per tenant. Keep the variable name stable; permissions and masks on the same table must share the same environment (CURRENT SCHEMA, PATH, encoding) recorded in SYSIBM.SYSENVIRONMENT.

1
2
3
4
CREATE PERMISSION TENANT_ROWS ON BILLING.INVOICE
  FOR ROWS WHERE TENANT_ID = GETVARIABLE('CLIENT_TENANT')
  ENFORCED FOR ALL ACCESS
  ENABLE;

Masking and functions

A UDF used in a permission or mask must be SECURED. Defining a secure object requires the CREATE_SECURE_OBJECT privilege (SECADM-controlled when SEPARATE_SECURITY is YES). Built-ins such as VERIFY_GROUP_FOR_USER and SUBSTR are fine. Functions that are not deterministic, have external action, or MODIFIES SQL DATA are not allowed in mask CASE expressions. If the mask CASE references another RCAC table, that inner table’s access control is not cascaded into the mask evaluation—design mapping tables with that in mind.

Triggers on RCAC tables must also be secure. Unsecured triggers are a common bind-time surprise after you activate control.

Masking and aggregation

Row permissions run first, so SUM and COUNT never see forbidden rows. Column masks are applied so that they do not rewrite WHERE, GROUP BY, or HAVING using the masked value for grouping logic: grouping and ordering use original values. The values you see in the select list can still be masked.

Two traps:

• SELECT DISTINCT and COUNT(DISTINCT col) may not match the uniqueness you see after masking (many SSNs become XXX-XX-1234)
• If the masked column is inside an expression, the mask is applied before the expression, so SUM(INCOME) can sum the 1–4 category numbers instead of dollars if INCOME is masked to a category

On INSERT/UPDATE/MERGE, Db2 uses original column values to compute new values, then checks that the mask would return the column to itself. A mask that always returns 'XXX-XX-0000' will reject updates to that column.

Masking and views

You do not CREATE PERMISSION ON a view. Activate RCAC on the base table. Every view, join view, and tool that SQL-accesses that table inherits the filter and masks. That is why RCAC replaced a generation of “security views” that applications had to remember to use. QMF, DSNTEP2, and the payroll COBOL program all see the same rules.

Row permissions and joins

Each table in a join is filtered by its own permissions before the join predicate. If CUSTOMER hides branch B rows from you, an inner join to ORDERS looks as if those customers have no orders. An outer join can null-pad masked columns; Db2 forces a null column to mask as null so you cannot smuggle a value out through IS NULL tricks in the mask CASE.

Application designers must accept that the same SQL text returns different result sets for different users without an error. Reports that “lost” rows are often working as designed.

Explain It Like I'm Five

The table is a notebook. GRANT SELECT is permission to open the notebook. A row permission is a sticker on each page: “only the kids in classroom A may look at this page.” If your sticker does not match, the page is invisible—you are not told it exists. A column mask is a folding flap over one word on the page. Teachers see the word. Kids see “****.” The school security officer (SECADM) writes stickers and flaps but does not automatically get to read the diary. Opening the notebook in a different cover (a view) does not remove the stickers.

Exercises

1. Write a permission that lets SESSION_USER see only rows where OWNER_ID = SESSION_USER, and a second permission that lets role AUDITOR see all rows. Why OR matters here?
2. Write a mask on PHONE that shows the full number to group HELPDesk and otherwise returns the last four digits.
3. Predict SELECT SUM(INCOME) FROM CUSTOMER if INCOME is masked to categories 1–4 for STAFF.
4. Explain what SQL you would see after ACTIVATE ROW ACCESS CONTROL with no CREATE PERMISSION.
5. Can you put both a SECLABEL column (MLS) and row permissions on HOSPITAL.PATIENT? What about a column mask on DIAGNOSIS?