Example: Creating a Table with Row-Level Security Constraints - Teradata Vantage

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 creating the table. The function definitions specified in the constraint objects run 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)
     UNIQUE PRIMARY INDEX(col1);

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.

     CREATE CONSTRAINT group_membership SMALLINT, NOT NULL,  
     VALUES (exec:100, manager:90, clerk:50, peon:25), 
     INSERT SYSLIB.ins_grp_member,
     SELECT SYSLIB.rd_grp_member ;

In this table definition, Vantage adds a constraint column named group_membership to the emp_record table when creating 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 use SELECT to retrieve the data in the group_membership column by specifying group_membership in the select list for the query, as in the following SELECT request:

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