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;