Example: Application of Row-Level Security SELECT Constraints When User Lacks Required Privileges (MERGE Request) - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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)").