Using INSERT … SELECT With Tables That Have Row-Level Security - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

You can use INSERT … SELECT requests with tables that have row-level security if all the tables have exactly the same row-level security constraints.

Vantage does not execute the security policy UDF for row-level security constraints on the target table.
  • If the session executing the request does not have the appropriate OVERRIDE privilege to use the DML statement on the target table, the database takes the values for all row-level security constraint columns from the source table.
  • If the session has the appropriate OVERRIDE privilege, the database takes the constraint values from the source table unless they are provided as part of the INSERT … SELECT request.

Example: Application of Row-Level Security INSERT Constraint Functions for Single Statement INSERT Requests

This example shows how the INSERT constraint functions are applied for a single-statement INSERT request on a table that has the row-level security INSERT constraint.

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 functions.

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

This EXPLAIN statement is used to show the steps involved in the execution of the INSERT request and the outcome of the application of the INSERT constraint functions.

       EXPLAIN INSERT rls_tbl(1,1,,);

The system returns this EXPLAIN text.

 *** Help information returned. 6 rows.
 *** Total elapsed time was 1 second.
Explanation
------------------------------------------------------------------------ 1) First, we do an INSERT into RS.rls_tbl constrained by (
     RS.rls_tbl.levels = SYSLIB.INSERTLEVEL (2)), (
     RS.rls_tbl.categories = SYSLIB.INSERTCATEGORIES ('90000000'XB)).
     The estimated time for this step is 0.07 seconds.
  -> No rows are returned to the user as the result of statement 1.
     The total estimated time is 0.07 seconds.

Example: Row-Level Security INSERT and SELECT Constraints When User Lacks Required Privileges (INSERT...SELECT Request)

This example shows how the INSERT and SELECT constraints are applied when a user that does not have the required OVERRIDE privileges attempts to execute an INSERT...SELECT request on a table that has the row-level security INSERT and SELECT constraints.

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

Following is the INSERT statement:

       INSERT rls_tgt_tbl SELECT * FROM rls_src_tbl;

An EXPLAIN shows the INSERT and SELECT constraints applied during RETRIEVE step from rls_src_tbl with a condition of ("((SYSLIB.SELECTCATEGORIES ( '90000000'XB, RS.rls_src_tbl.categories ))= 'T') AND ((SYSLIB.SELECTLEVEL (2, rls_src_tbl.levels ))= 'T')").