17.05 - Example: Application of Row-Level Security SELECT and UPDATE Constraints When User Lacks Required Privileges (UPDATE Request) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

This example shows how the SELECT and UPDATE constraints are applied when a user that does not have the required privileges submits an UPDATE request in an attempt to update the classification level value for a row. The SELECT constraints filter out the rows that the user is not permitted to access and the UPDATE constraints restrict the user from executing the update operation on the target row.

The classification level value is stored in the classification_level column, one of the constraint columns. The other constraint column is classification_categories.

The statement used to create the table in this example is:

     CREATE TABLE rls_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

Following is the UPDATE statement:

      UPDATE rls_tbl SET col1=2 where col1=1;

The EXPLAIN shows the outcome of the SELECT and UPDATE constraints. A RETRIEVE step on RS.rls_tbl is performed by way of the primary index "RS.rls_tbl.col1 = 1" with a residual condition of ("((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T') AND ((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_tbl.categories ))='T')").

Next, there is a MERGE DELETE of the results to RS.rls_tbl with the updated rows constrained by (RS.rls_tbl.levels = SYSLIB.UPDATELEVEL (2, {LeftTable}.levels)), (RS.rls_tbl.categories = SYSLIB.UPDATECATEGORIES ('90000000'XB, {LeftTable}.categories)).

Then, there is a MERGE into RS.rls_tbl of the results.