16.20 - CONSTRAINT - Teradata Database - Teradata Vantage NewSQL Engine

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;