17.00 - Example: Creating a Table With Row-Level Security Constraints - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

In this example, classification_level and classification_category are the names of previously defined constraints. The create text for the classification_level and classification_category constraint objects looks like the following.

     CREATE CONSTRAINT classification_level SMALLINT, NOT NULL,
     VALUES (top_secret:4, secret:3, confidential:2, unclassified:1), 
     INSERT SYSLIB.insert_level,
     UPDATE SYSLIB.update_level,
     DELETE SYSLIB.delete_level,
     SELECT SYSLIB.read_level ;
     CREATE CONSTRAINT classification_category BYTE(8), 
     VALUES (nato:1, united_states:2, canada:3, united_kingdom:4,
             france:5, norway:6, russia:7), 
     INSERT SYSLIB.insert_category,
     UPDATE SYSLIB.update_category,
     DELETE SYSLIB.delete_category,
     SELECT SYSLIB.read_category ;

Vantage implicitly adds column definitions for the row-level constraint columns classification_level and classification_category to the definition of table_1_rls_constraints when it creates the table. The function definitions specified in the constraint objects are executed as row-level security controls on SQL requests that access the rows of table_1_rls_constraints.

     CREATE TABLE table_1_rls_constraints (
       column_1 INTEGER,
       column_2 CHARACTER(30), 
       classification_level    CONSTRAINT, 
       classification_category CONSTRAINT)

The create text for the group_membership constraint object looks like this. This is a hierarchical constraint that defines four value name:value code pairs.

     VALUES (exec:100, manager:90, clerk:50, peon:25), 
     INSERT SYSLIB.ins_grp_member,
     SELECT SYSLIB.rd_grp_member ;

In this table definition, Vantage implicitly adds a constraint column named group_membership to the emp_record table when it creates that table. The group_membership column contains the data for the row-level constraint.

     CREATE TABLE emp_record (
       emp_name   VARCHAR(30),
       emp_number INTEGER, 
       salary     INTEGER, 
       group_membership CONSTRAINT)
     UNIQUE PRIMARY INDEX(emp_name);

After you create emp_table , you can submit a SELECT request that retrieves the data in the group_membership column by specifying it in the select list for the query. A SELECT request on table emp_record that includes group_membership in its select list might look something like this.

     SELECT emp_name, group_membership
     FROM emp_record
     WHERE group_membership=90;

For a user with the appropriate row-level security credentials, this request returns the emp_name and group_membership code for all managers. Note that you cannot specify a value name as the search condition. Instead, you must specify a value code. In this example, the value code 90 represents the value name manager.

For a user who does not have the row-level security credentials to read group_membership 90, this request returns 0 rows.

You can also retrieve this information from DBC.AsgdSecConstraints.