If your LBAC credentials do not allow you to read a row, then it is as if that row does not exist for you so there is no way for you to update that row. For rows that you are able to read, you must also be able to write to the row in order to update it.
When you try to update a row, your LBAC credentials for writing are compared to the security label protecting the row. If write access is blocked, the update fails and an error is returned. If write access is not blocked, then the update continues.
The update that is performed is done the same way as an update to a
non-protected row except for the treatment of the column that has a data type of DB2SECURITYLABEL. If you do not explicitly set the value of that column, it is automatically set to the security label that you hold for write access. If you do not have a security label for write access, an error is returned and the statement fails.
If the update explicitly sets the column that has a data type of
DB2SECURITYLABEL, then your LBAC credentials are checked again. If the update you are trying to perform would create a row that your current LBAC credentials would not allow you to write to, then what happens depends on the security policy that is protecting the table. If the security policy has the RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option, then the update fails and an error is returned. If the security policy does not have the RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option or if it instead has the OVERRIDE NOT AUTHORIZED WRITE SECURITY LABEL option, then the security label you provide is ignored and if you hold a security label for write access, it is used instead. If you do not hold a security label for write access, an error is returned.
Example:
Assume that table T1 is protected by a security policy named P1 and has a column named LABEL that has a data type of DB2SECURITYLABEL.
T1, including its data, looks like this:
Table 24. Table T1
EMPNO LASTNAME DEPTNO LABEL
1 Rjaibi 11 L1
2 Miller 11 L2
3 Bird 11 L3
Assume that user Jenni has LBAC credentials that allow her to read and write data protected by the security labels L0 and L1 but not data protected by any other security labels. The security label she holds for both read and write is L0. The details of her full credentials and of what elements are in the labels are not important for this example.
Jenni issues this SQL statement:
SELECT * FROM T1
Jenni sees only one row in the table:
Table 25. Jenni's SELECT Query Result
EMPNO LASTNAME DEPTNO LABEL
1 Rjaibi 11 L1
The rows protected by labels L2 and L3 are not included in the result set because Jenni's LBAC credentials do not allow her to read those rows. For Jenni it is as if those rows do not exist.
Jenni issues these SQL statements:
UPDATE T1 SET DEPTNO = 44 WHERE DEPTNO = 11;
SELECT * FROM T1;
The result set returned by the query looks like this:
Table 26. Jenni's UPDATE & SELECT Query Result
EMPNO LASTNAME DEPTNO LABEL
1 Rjaibi 44 L0
The actual data in the table looks like this:
Table 27. Table T1
EMPNO LASTNAME DEPTNO LABEL
1 Rjaibi 44 L0
2 Miller 11 L2
3 Bird 11 L3
The statement executed without error but affected only the first row. The second and third rows are not readable by Jenni so they are not selected for update by the statement even though they meet the condition in the WHERE clause.
Notice that the value of the LABEL column in the updated row has changed even though that column was not explicitly set in the UPDATE statement. The column was set to the security label that Jenni held for writing.
Now Jenni is granted LBAC credentials that allow her to read data protected by any security label. Her LBAC credentials for writing do not change. She is still only able to write to data protected by L0 and L1.
Jenni again issues this SQL statement:
UPDATE T1 SET DEPTNO = 44 WHERE DEPTNO = 11
This time the update fails because of the second and third rows. Jenni is able to read those rows, so they are selected for update by the statement. She is not, however, able to write to them because they are protected by security labels L2 and L3. The update does not occur and an error is returned.
Jenni now issues this SQL statement:
UPDATE T1
SET DEPTNO = 55, LABEL = SECLABEL_BY_NAME( ’P1’, ’L2’ ) WHERE LASTNAME = "Rjaibi"
The SECLABEL_BY_NAME function in the statement returns the security label named L2. Jenni is trying to explicitly set the security label protecting the first row.
Jenni's LBAC credentials allow her to read the first row, so it is selected for update.
Her LBAC credentials allow her to write to rows protected by the security label L0 so she is allowed to update the row. Her LBAC credentials would not, however, allow her to write to a row protected by the security label L2, so she is not allowed to set the column LABEL to that value. The statement fails and an error is
returned. No columns in the row are updated.
Jenni now issues this SQL statement:
UPDATE T1 SET LABEL = SECLABEL_BY_NAME( ’P1’, ’L1’ ) WHERE LASTNAME = "Rjaibi"
The statement succeeds because she would be able to write to a row protected by the security label L1.
T1 now looks like this:
Table 28. Table T1
EMPNO LASTNAME DEPTNO LABEL
1 Rjaibi 44 L1
2 Miller 11 L2
3 Bird 11 L3