16.10 - Creating a Database User - Teradata Database

Teradata Database Security Administration

Teradata Database
Release Number
Release Date
June 2017
Content Type
Publication ID
English (United States)

You can create permanent database users with a CREATE USER statement, for example:

When creating users and assigning user privileges, SQL statements routed through Unity apply to all connected Teradata Database systems.
CREATE USER  user_name  FROM  database_name  or  user_name  AS PASSWORD =  password  PERM =  permanent_space  SPOOL =  spool_space  TEMPORARY =  temporary_space  ACCOUNT = [‘a ccount_string1’..., ‘account_string2’...]
DEFAULT DATABASE =  database_namePROFILE =  profile_ name  DEFAULT ROLE =  role_name  OPTION = value,... ;


Syntax Element Explanation
CREATE USER user_name The name of an individual Teradata Database user.

Recommendation: Do not allow multiple users to share a username for accessing the database.

FROM database_name or user_name The name of the database or user that owns the space in which the user is created.

Recommendation: Create users in the space owned by the principal administrator, either DBADMIN or SECADMIN. For information on typical administrator configuration, see the topics beginning with Working with Administrative Users.

AS PASSWORD = password Required. 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 logon.

For information on password requirements, see Managing Database Passwords.

PERM = permanent_space Required. The space in bytes available to contain objects that the user creates or owns.
Permanent space comes from, and cannot exceed, the current PERM of the immediate owner of the user, which is normally the database or user specified in the FROM clause of the CREATE USER statement.

Recommendation: Administrators, database programmers, and other users that need to create and store objects in owned space require a perm space allocation.

Even if a user does not need perm space, a value of 0 is required.

For information about the assignment and use of perm space, see Database Administration.

If you allocate space to a new user, the user automatically receives certain ownership privileges on any objects that may inhabit the space.

For information on the privileges implicit in object ownership, see Ownership Privileges.

For a list of privileges automatically granted to newly created users, see Privilege Dictionary.

SPOOL = spool_space Optional. 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 specified in a CREATE USER statement.

Recommendation: 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 largest database the user accesses.

TEMPORARY = temporary_space Optional. Only objects such as global temporary tables and volatile tables require temporary space.

Recommendation: You should develop a strategy for use of the database objects that require temp space before you assign temp space to users. You can add a temp space specification later with a MODIFY USER or MODIFY PROFILE statement.

For additional information, see Database Administration.

ACCOUNT = [‘a ccount_string1 ’..., ‘ account_string2 ...] Optional. User account information that forms the basis of various user management options.

Recommendation: Specify accounts at the profile level.

For information on creating accounts, see Database Administration.

DEFAULT DATABASE = database_name Optional. The database that owns the space in which the Teradata Database stores or searches for new or target objects in an SQL query, unless a different database is specified in the query.

Recommendation: Specify the database most often accessed by the user.

Use the SET SESSION DATABASE statement to establish a new default database for the current session. Use the SELECT DATABASE statement to check the current session database.
PROFILE = profile_ name Optional. Specify the name of an existing profile to give the user membership in the profile.

When syntax elements appear in both the CREATE USER statement for a user, and in a profile of which the user is a member, the profile values take precedence.

A permanent user can be a member of only one profile.

Recommendation: Specify membership in a profile for each user.

DEFAULT ROLE = role_name Required if the user is a role member. Identifies the default role used for determining user privileges in the database for the current session.

You must specify a default role even if the user is a member of only one role. The role must exist in the database before you can be specify it in a CREATE USER statement.

Users that are members of more than one role can use the SET ROLE statement at the beginning of a session or transaction to change from the default role to another role.

Recommendation: Specify the role associated with the activities performed most frequently by the user.

Additional Options
FALLBACK Optional. Automatically creates a duplicate of each table stored in the user space, in addition to the duplicates already created by disk mirroring. The system reverts to the fallback tables in the event of a failure.

Recommendation: Do not specify FALLBACK at the user or database level. FALLBACK is recommended only for individual mission-critical tables or tables so large that their size prevents timely backup to tape or external disk.

For information on the use of FALLBACK, see Database Administration

Collation Optional. Database default values that apply to all users are already set as part of system installation and configuration.

Recommendation: Do not change the values for an individual user unless the default values are not applicable.

All users must use the same collation value.
Time Zone
Default Date Form
Default Character Set
For information on assigning row level security constraints to users, see Working with Constraint Assignments.