Example: Creating Users With Row-Level Security Constraints - Advanced SQL Engine - Teradata Database

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;