Teradata User Management

The Teradata Database Administrators are responsible for managing the users in the database. They will create the Teradata account for each user with limited space and access to the database objects.

Users

A user is created using the 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.

We must explicitly grant the CREATE USER or USER privilege to any user that requires it, except user DBC, which has these privileges by default. The creator of a user automatically receives the DROP USER privilege on the user.

Syntax

Following is the syntax for CREATE USER.

• CREATE USER user_name: The name of an individual Teradata Database user. We cannot allow multiple users to share a username for accessing the database.
• PERM: The space in bytes available to contain objects that the user creates or owns. Permanent space cannot exceed the current PERM of the user's immediate owner, which is usually the database or user specified in the FROM clause of the CREATE USER statement.
• PASSWORD = password: A temporary password. If the system is set up according to the recommendations in creating the security administrator user, the user is prompted to change the temporary password to a private password at first login.
• TEMPORARY: Only objects such as global temporary tables and volatile tables require temporary space. We should develop a strategy for using the database objects that need temp space before assigning temp space to users. We can add a temp space specification later with a MODIFY USER or MODIFY PROFILE statement.
• SPOOL: Defines the maximum amount of space available to the user for intermediate query results, volatile tables, or formatted answer sets to queries. The system borrows spool space from unused system perm space, such as the spool reserve in creating the spool space reserve.
  The spool space specified in a profile takes precedence over spool space defined in a CREATE USER statement.
  Specify spool space as part of a profile unless the user requires unique spool considerations.
  The spool space allocation should be approximately 20% of the perm space available to the most extensive database the user accesses.

NOTE: While creating a user, the values for user name, Permanent space, and Password is mandatory. But the other fields are optional.

Example

Following is an example to create user T001.

Accounts

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

• The user, but the profile contains more than one account. The default is the first account in the string. The user can submit a SET SESSION ACCOUNT statement to set the default for the current session to one of the other accounts.
• A user and the profile contains only one account. The default is the account defined in the profile.
• A user, but the profile does not include an account. The default is the account identifier of the immediate owner of the user.
• Not assigned to the user, the default is the account identifier of the user's immediate owner.

NOTE

1. If a user definition specifies an account but not a profile, the default account is the account string in the user definition.
2. If you assign a profile to a user, and the profile specifies an account string, the profile account is in effect for AMPUsage and DBQL for that user. However, the system uses the value found in the DBC.DatabaseV view for space accounting.

Syntax

Following is the syntax for CREATE USER with the account option.

Example

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

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

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.

NOTE
Privileges can be INSERT, SELECT, UPDATE, REFERENCES.

Example

Following is an example of the GRANT statement.

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 the REVOKE command.

Example

Below is an example of the REVOKE command.