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.
About this task
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.
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.
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.