Using INSERT ... SELECT with Tables That Have Row-Level Security - 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™

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