15.10 - level_name - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Name of a hierarchical level, valid for the constraint_name, that is to replace the default level.

The specified level_name must be currently assigned to the user. Otherwise, Teradata Database returns an error to the requestor.

Constraint Definitions for the Examples

The following constraint and user definitions are used for the examples that follow.

The following SQL text creates a hierarchical constraint named classification_level that enforces all four of the possible statement actions for a constraint.

     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;

The following SQL text creates a non-hierarchical constraint named classification_category that enforces all four of the possible statement actions for a constraint.

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

     CREATE USER user_name AS
     PERMANENT = 1e6, 
     PASSWORD=my_pwd,
     CONSTRAINT = classfication_level (top_secret),
     CONSTRAINT = classification_category (united_states);

Creates user pls.

     CREATE USER pls AS
     PERMANENT = 1e6, 
     PASSWORD=secret,
     CONSTRAINT = classification_level (secret, unclassified DEFAULT),
     CONSTRAINT = classification_category (united_states);

The following SQL text creates user arn_anderson.

     CREATE USER arn_anderson AS
     PERMANENT = 1e6, 
     PASSWORD=Arn2222ANDERSON,
     CONSTRAINT = classification_category (norway);

Example: Changing the Security Level and Category for a Session

The user_name logs on. The resulting session has a label consisting of an unclassified level and a nato category. The first request executed for the session changes the label to a top_secret level and the category to a combination of united_states and nato.

     SET SESSION CONSTRAINT = classification_level (top_secret), 
     CONSTRAINT = classification_category  (nato, united_states);

Assume that later on, the session initiated by user_name wanted to read one of the three rows from inventory, so the user submits the following SELECT request.

     SELECT * 
     FROM inventory 
     WHERE col_1 = 1212;

The returned result set would be as follows.

Col_1	 Col_2	 Col_3   		Classification_Level  	Classification_Category
=====  ===== =====   ====================  =======================
1212	 90505	 Widgets	 3			                     '0100000000000000'XB

Example: Changing the Row-Level Security Level for a Session

User pls logs on. The resulting session has a label consisting of an unclassified level and a nato category. As soon as the session is established, pls changes the level to secret.

     SET SESSION CONSTRAINT = classification_level (secret);

After the SET SESSION CONSTRAINT request executes, the session has a label of secret and nato.

Suppose that the session initiated by pls is used to insert 3 rows into the table named inventory. The INSERT requests used to insert the rows into inventory are as follows.

     INSERT INTO inventory VALUES (1212, 90505,'Widgets',,);
     INSERT INTO inventory VALUES (12122, 90504,'Buggy Whips',,);
     INSERT INTO inventory VALUES (12126, 90501,'Whip Sockets',,);

The last two positional values are generated by the INSERT constraint UDFs, not by the session.

The column values for the rows after these INSERT requests complete are as follows.

inventory        
col_1 col_2 col_3 classification_level classification_category
1212 90505 Widgets 3 ‘0100000000000000'XB
12122 90504 Buggy Whips 3 ‘0100000000000000'XB
12126 90501 Whip Sockets 3 ‘0100000000000000'XB