Using the SELECT statement with multilevel security

When a user with a valid security label selects data from one or more tables with row-level security enabled, Db2 compares the security label of the user to the security label of each row.

About this task

Begin general-use programming interface information.The results from the comparison of the security label of the user to the security label of each row are returned according to the following rules:

  • If the security label of the user dominates the security label of the row, Db2 returns the row.
  • If the security label of the user does not dominate the security label of the row, Db2 does not return the data from that row, and Db2 does not generate an error report.

Example

Suppose that Alan has a security label of HIGH, Beth has a security label of MEDIUM, and Carlos has a security label of LOW. 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
000010 HAAS A00 LOW
000190 BROWN D11 HIGH
000200 JONES D11 MEDIUM
000210 LUTZ D11 LOW
000330 LEE E21 MEDIUM

Now, suppose that Alan, Beth, and Carlos each submit the following SELECT statement: 

SELECT LASTNAME
  FROM EMP
  ORDER BY LASTNAME;

Because Alan has the security label HIGH, he receives the following result: 

BROWN
HAAS
JONES
LEE
LUTZ

Because Beth has the security label MEDIUM, she receives the following result: 

HAAS
JONES
LEE
LUTZ

Beth does not see BROWN in her result set because the row with that information has a security label of HIGH.

Because Carlos has the security label LOW, he receives the following result: 

HAAS
LUTZ

Carlos does not see BROWN, JONES, or LEE in his result set because the rows with that information have security labels that dominate Carlos's security label. Although Beth and Carlos do not receive the full result set for the query, Db2 does not return an error code to Beth or Carlos.End general-use programming interface information.