Using the DELETE statement with multilevel security

When a user with a valid security label deletes data from a table with row-level security, Db2 compares the security label of the user to that of the row.

About this task

Begin general-use programming interface information.The delete proceeds according to the following rules:
  • If the security label of the user and the security label of the row are equivalent, the row is deleted.
  • If the security label of the user dominates the security label of the row, the user's write-down privilege determines the result of the DELETE statement:
    • If the user has write-down privilege or write-down control is not enabled, the row is deleted.
    • If the user does not have write-down privilege and write-down control is enabled, the row is not deleted.
  • If the security label of the row dominates the security label of the user, the row is not deleted.

Example

Suppose that Alan has a security label of HIGH, that Beth has a security label of MEDIUM and write-down privilege defined in RACF®, and that Carlos has a security label of LOW. Write-down control is enabled.

Suppose that DSN8910.EMP contains the data that is shown in the following table and that the SECURITY column has been declared with the AS SECURITY LABEL clause.

Table 1. Sample data from DSN8910.EMP
EMPNO LASTNAME WORKDEPT SECURITY
000190 BROWN D11 HIGH
000200 JONES D11 MEDIUM
000210 LUTZ D11 LOW
Now, suppose that Alan, Beth, and Carlos each submit the following DELETE statement: 
DELETE FROM DSN8910.EMP
   WHERE DEPTNO='D11';

Because Alan has a security label that dominates the rows with security labels of MEDIUM and LOW, his write-down privilege determines whether these rows are deleted. Alan does not have write-down privilege, so the delete fails for these rows. Because Alan has a security label that is equivalent to the security label of the row with HIGH security, the delete on that row succeeds. The results of Alan's delete are shown in the following table:

Table 2. Sample data from DSN8910.EMP after Alan's delete
EMPNO EMPNAME DEPTNO SECURITY
000200 JONES D11 MEDIUM
000210 LUTZ D11 LOW

Because Beth has a security label that dominates the row with a security label of LOW, her write-down privilege determines whether this row is deleted. Beth has write-down privilege, so the delete succeeds for this row. Because Beth has a security label that is equivalent to the security label of the row with MEDIUM security, the delete succeeds for that row. Because the row with the security label of HIGH dominates Beth's security label, the delete fails for that row. The results of Beth's delete are shown in the following table:

Table 3. Sample data from DSN8910.EMP after Beth's delete
EMPNO EMPNAME DEPTNO SECURITY
000190 BROWN D11 HIGH

Because Carlos's security label is LOW, the delete fails for the rows with security labels of MEDIUM and HIGH. Because Carlos has a security label that is equivalent to the security label of the row with LOW security, the delete on that row succeeds. The results of Carlos's delete are shown in the following table:

Table 4. Sample data from DSN8910.EMP after Carlos's delete
EMPNO EMPNAME DEPTNO SECURITY
000190 BROWN D11 HIGH
000200 JONES D11 MEDIUM
Important: Do not omit the WHERE clause from DELETE statements. If you omit the WHERE clause from the DELETE statement, checking occurs for rows that have security labels. This checking behavior might have a negative impact on performance.
Considerations for SELECT...FROM...DELETE statements: If the user has write-down privilege or write-down control is not in effect, the security label of the user might not dominate the security label of the row. For statements that delete rows and select the deleted rows, the DELETE statement succeeds. However, the deleted row is not returned.End general-use programming interface information.