Example: Creating Users With Row-Level Security Constraints - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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;