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

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.