Example: Creating a Table With Row-Level Security Constraints - Teradata Database

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 ;

The system 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)
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, the system 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.