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