Using the MERGE statement with multilevel security

MERGE is an SQL statement that combines the conditional INSERT and UPDATE operations on a target table. Data that is not already present in the target table is inserted with the INSERT part of the MERGE statement. Data that is already present in the target table is updated with the UPDATE part of the MERGE statement.

Results

Begin general-use programming interface information.Because the MERGE statement consists of the INSERT and UPDATE operations, the multilevel security rules for the INSERT operation apply to the INSERT part of the MERGE statement and the multilevel security rules for the UPDATE operation apply to the UPDATE part of the MERGE statement.

INSERT rules for MERGE statements
For the INSERT part of the MERGE statement, when a user with a valid security label inserts data into a table with row-level security enabled, the security label of the row is determined according to the following rules:
  • If the user has write-down privilege or if the write-down control is not enabled, the user can set the security label for the row to any valid security label. If the user does not specify a value for the security label, the security label of the row becomes the same as the security label of the user.
  • If the user does not have write-down privilege and if the write-down control is enabled, the security label of the row becomes the same as the security label of the user.
UPDATE rules for MERGE statements
For the UPDATE part of the MERGE statement, when a user with a valid security label updates a table with row-level security enabled, Db2 compares the security label of the user to the security label of the row. The update 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 updated and the value of the security label is determined by whether the user has write-down privilege:
    • If the user has write-down privilege or if the write-down control is not enabled, the user can set the security label of the row to any valid security label.
    • If the user does not have write-down privilege and if the write-down control is enabled, the security label of the row is set to the value of the security label of the user.
  • If the security label of the user dominates the security label of the row, the result of the UPDATE operation is determined by whether the user has write-down privilege:
    • If the user has write-down privilege or if the write-down control is not enabled, the row is updated and the user can set the security label of the row to any valid security label.
    • If the user does not have write-down privilege and if the write-down control is enabled, the row is not updated.
  • If the security label of the row dominates the security label of the user, the row is not updated.
SELECT...FROM...MERGE statement considerations
If the user has write-down privilege or if the 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 merge rows and select the merged rows, the MERGE statement succeeds. However, the merged row is not returned.
End general-use programming interface information.