Teradata - User Management

This chapter discussed the various strategies of user management in Teradata.

Users

A user is created using CREATE USER command. In Teradata, a user is also similar to a database. They both can be assigned space and contain database objects except that the user is assigned a password.

Syntax

Following is the syntax for CREATE USER.

CREATE USER username 
AS  
[PERMANENT|PERM] = n BYTES 
PASSWORD = password 
TEMPORARY = n BYTES 
SPOOL = n BYTES;

While creating a user, the values for user name, Permanent space and Password is mandatory. Other fields are optional.

Example

Following is an example to create the user TD01.

CREATE USER TD01 
AS  
PERMANENT = 1000000 BYTES 
PASSWORD = ABC$124 
TEMPORARY = 1000000 BYTES 
SPOOL = 1000000 BYTES;

Accounts

While creating a new user, the user may be assigned to an account. ACCOUNT option in CREATE USER is used to assign the account. A user may be assigned to multiple accounts.

Syntax

Following is the syntax for CREATE USER with account option.

CREATE USER username 
PERM = n BYTES 
PASSWORD = password 
ACCOUNT = accountid

Example

The following example creates the user TD02 and assigns the account as IT and Admin.

CREATE USER TD02 
AS  
PERMANENT = 1000000 BYTES 
PASSWORD = abc$123 
TEMPORARY = 1000000 BYTES 
SPOOL = 1000000 BYTES 
ACCOUNT = (‘IT’,’Admin’);

The user can specify the account id while logging into Teradata system or after being logged into the system using SET SESSION command.

.LOGON username, passowrd,accountid 
OR 
SET SESSION ACCOUNT = accountid 

Grant Privileges

GRANT command is used to assign one or more privileges on the database objects to the user or database.

Syntax

Following is the syntax of the GRANT command.

GRANT privileges ON objectname TO username;

Privileges can be INSERT, SELECT, UPDATE, REFERENCES.

Example

Following is an example of GRANT statement.

GRANT SELECT,INSERT,UPDATE ON Employee TO TD01;

Revoke Privileges

REVOKE command removes the privileges from the users or databases. The REVOKE command can only remove explicit privileges.

Syntax

Following is the basic syntax for REVOKE command.

REVOKE [ALL|privileges] ON objectname FROM username;

Example

Following is an example of REVOKE command.

REVOKE INSERT,SELECT ON Employee FROM TD01;