Example: Application of Row-Level Security SELECT Constraints When User Lacks Required Privileges (MERGE Request)
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 execute 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)").