This example show how the SELECT constraint predicates are applied to the source rows when a user that does not have the required OVERRIDE privileges attempts to run a MERGE request on a table that has the row-level security SELECT constraint. The SELECT constraint filters out the rows that the user submitting the MERGE request is not permitted to view. The SELECT constraint predicates are added to the MATCH condition.
An EXPLAIN statement is used to show the steps involved in the execution of the request and the outcome of the application of the constraint predicates.
The statements used to create the tables in this example are:
CREATE TABLE rls_src_tbl( col1 INT, col2 INT, classification_levels CONSTRAINT, classification_categories CONSTRAINT); CREATE TABLE rls_tgt_tbl( col1 INT, col2 INT, classification_levels CONSTRAINT, classification_categories CONSTRAINT);
The user’s sessions constraint values are:
Constraint1Name LEVELS Constraint1Value 2 Constraint3Name CATEGORIES Constraint3Value '90000000'xb
This EXPLAIN statement is used to show the steps involved in the execution of the MERGE request and the outcome of the application of the SELECT constraint predicates.
MERGE INTO rls_tgt_tbl USING rls_src_tbl ON (rls_tgt_tbl.col1=1) WHEN MATCHED THEN UPDATE SET col2=3 WHEN NOT MATCHED THEN INSERT (1,1,rls_src_tbl.levels,rls_src_tbl.categories);
An EXPLAIN shows a RETRIEVE step from RS.rls_src_tbl with a condition of ("((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_src_tbl.categories ))= 'T') AND ((SYSLIB.SELECTLEVEL (2, RS.rls_src_tbl.levels ))= 'T')").
The results are merged with matched updates and unmatched inserts into RS.rls_tgt_tbl with a condition of ("(((SYSLIB.SELECTCATEGORIES ('90000000'XB, {RightTable}.CATEGORIES)) = 'T') AND ((SYSLIB.SELECTLEVEL (2, {RightTable}.LEVELS )) = 'T')) AND (RS.rls_tgt_tbl.col1 = Field_4)").