Teradata - User Management
This chapter discussed the various strategies of user management in Teradata.
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.
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.
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;
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.
Following is the syntax for CREATE USER with account option.
CREATE USER username PERM = n BYTES PASSWORD = password ACCOUNT = accountid
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 command is used to assign one or more privileges on the database objects to the user or database.
Following is the syntax of the GRANT command.
GRANT privileges ON objectname TO username;
Privileges can be INSERT, SELECT, UPDATE, REFERENCES.
Following is an example of GRANT statement.
GRANT SELECT,INSERT,UPDATE ON Employee TO TD01;
REVOKE command removes the privileges from the users or databases. The REVOKE command can only remove explicit privileges.
Following is the basic syntax for REVOKE command.
REVOKE [ALL|privileges] ON objectname FROM username;
Following is an example of REVOKE command.
REVOKE INSERT,SELECT ON Employee FROM TD01;