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

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
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)").