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

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.

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

     CREATE USER joe AS
     PERMANENT=1e6,
     PASSWORD=Joe1234,
     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