17.05 - Example: Selecting Rows From a Table With Row-Level Security Protection - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)

This example shows how you can specify a row-level security constraint column name in the select list or WHERE clause of a SELECT statement.

First, define the row-level security constraint. The create text for the group_membership constraint object looks like this.

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

In this table definition, the database implicitly adds a row-level security 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 security 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_record, you can use a SELECT statement that retrieves the specified data from the group_membership column by specifying the name of that column in the select list for the query.

Following is an example of a SELECT statement on table emp_record that includes group_membership in the select list.

     SELECT emp_name, group_membership
     FROM emp_record
     WHERE group_membership=90;

If you have the required security credentials, this query returns the emp_name and group_membership value name and value code for all managers. If you do not have the required credentials, no rows are returned. You cannot specify a value name as the search condition. You must specify a value code. In this example, the value code 90 represents the value name manager.

Suppose you then create this row-level security constraints and inventory table.

     CREATE CONSTRAINT classification_level SMALLINT, NOT NULL,
     VALUES (top_secret:4, secret:3, confidential:2, unclassified:1),
     INSERT  SYSLIB.InsertLevel,
     UPDATE SYSLIB.UpdateLevel,
     DELETE SYSLIB.DeleteLevel,
     SELECT SYSLIB.ReadLevel;

     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;

     CREATE TABLE inventory (
       column_1 INTEGER,
       column_2 INTEGER,
       column_3 VARCHAR(30),
       classification_level    CONSTRAINT,
       classification_category CONSTRAINT) 
     PRIMARY INDEX(column_1);

User joe then logs onto a database session. The create text for joe looks like this.

     CONSTRAINT = classfication_level (top_secret),
     CONSTRAINT = classification_category (united_states);

Because user joe is defined with the classification_level and classification_category row-level security constraints, he can execute this SELECT statement on inventory.

     SELECT *
     FROM inventory
     WHERE column_1 = 1212;

The result set looks similar to this, returning not only the data from the three columns explicitly defined for inventory, but also the data from the two row-level security columns.

column_1 column_2 column_3 classification_level classification_category
-------- -------- -------- -------------------- -----------------------
1212     90505    Widgets  3                    '4000000000000000'xb