15.10 - CONSTRAINT - 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

Assign one or more row-level security constraints to the user_name.

There is a limit of 6 hierarchical constraints and 2 non-hierarchical constraints that can be assigned per user.

For more information about row-level security constraints, see CREATE CONSTRAINT.

row_level_security_constraint_column_name
Name of one or more row-level security constraints, each one followed by the list of the hierarchical levels or non-hierarchical categories, valid for the constraint, which are being assigned to the user_name.
  • Example hierarchical constraint assignment:
    CONSTRAINT = Classification_Level (Secret, Unclassified DEFAULT),
  • Example non-hierarchical constraint assignment:
    CONSTRAINT = Classification_Country (US, UK, GER)
level_name
Hierarchical levels, valid for the constraint, which are being assigned to the user_name.
DEFAULT
DEFAULT can be specified for 1 classification level from the specified list of level names for a hierarchical constraint. The specified level becomes the default value for the constraint when a session is established for the user.
If you do not specify DEFAULT for any of the listed classification levels, then the first level listed becomes the default for the user.
DEFAULT is not valid for non-hierarchical category constraints.
category_name
Non-hierarchical categories, valid for the constraint, which are being assigned to the user_name.
NULL

Example: Creating Users With Row-Level Security Constraints

The following SQL text creates users user_name, pls, and ArnAnderson, each with one or more row-level security constraints.

     CREATE USER user_name
 AS
     PERMANENT = 1e6, 
     PASSWORD=my_pwd
,
     CONSTRAINT = classfication_level (TopSecret),
     CONSTRAINT = classification_category (UnitedStates);
     CREATE USER pls
 AS
     PERMANENT = 1e6, 
     PASSWORD=secret
,
     CONSTRAINT = classification_level (Secret, Unclassified DEFAULT),
     CONSTRAINT = classification_category (UnitedStates);
     CREATE USER ArnAnderson
 AS
     PERMANENT = 1e6, 
     PASSWORD=hidden
,
     CONSTRAINT = classification_category (Norway);

The following set of CREATE USER requests creates a set of users are all created with the group_membership constraint, but each has a different value_name for that constraint.

User sally_jones is the only user who is defined as a personnel clerk.

     CREATE USER sally_jones
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Sal3446Jones
,
     CONSTRAINT = group_membership (clerk),
     DEFAULT ROLE=personnel_clerk;

User big_guy is an executive.

     CREATE USER big_guy
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Big9999Guy
,
     CONSTRAINT = group_membership (executive),
     DEFAULT ROLE=exec_role;

User al_manager is a manager.

     CREATE USER al_manager
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Al9999Manager
,
     CONSTRAINT = group_membership (manager),
     DEFAULT ROLE=mgr_role;

User tom_smith is an auditor and needs to be able to read the emprecords table. However, he should not have any other access to the table.

     CREATE USER tom_smith
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Tom1111Smith
,
     CONSTRAINT = group_membership (executive),
     DEFAULT ROLE=peon;

The following set of CREATE USER requests creates a set of users are all created with the group_membership constraint, but each has a different value_name for that constraint.

User sally_jones is the only user who is defined as a personnel clerk.

     CREATE USER sally_jones
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Sal3446Jones
,
     CONSTRAINT = group_membership (clerk),
     DEFAULT ROLE=personnel_clerk;

User big_guy is an executive.

     CREATE USER big_guy
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Big9999Guy
,
     CONSTRAINT = group_membership (executive),
     DEFAULT ROLE=exec_role;

User al_manager is a manager.

     CREATE USER al_manager
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Al9999Manager
,
     CONSTRAINT = group_membership (manager),
     DEFAULT ROLE=mgr_role;

User tom_smith is an auditor and needs to be able to read the emprecords table. However, he should not have any other access to the table.

     CREATE USER tom_smith
 AS 
     PERMANENT = 1E6, 
     PASSWORD=Tom1111Smith
,
     CONSTRAINT = group_membership (executive),
     DEFAULT ROLE=staff;