Creating Users - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Creating Users

1 Start Teradata Administrator and log on as user DBADMIN.

Note: This procedure creates users individually. To automate the creation of users by using BTEQ scripts, see “Using BTEQ Scripts to Create Database Objects” on page 86.

2 From the main screen, click the Preview Mode button to allow future display of generated SQL.

3 Do the following to create a user:

a Click Tools>Create>User.

b In the Create User dialog, specify the values for the required fields and controls.


Field or Control


User Name

Required. The name of an individual Teradata Database user.

Each database user should be uniquely identified for accurate logging. Do not allow users to share a username for accessing the database.

Note: The username must conform to the rules for SQL object names.


Required. The name of the database or user that owns the space in which the user is created.

Recommendation: DBADMIN. All users should be created in space owned by the database administrator.


Required. A temporary password. The user is prompted to change the temporary password to a permanent, private password at first logon.

Permanent Space

May be required. Space in bytes that can be used to contain objects the user creates or owns. Permanent space is required for those users that create and store space-consuming objects.

Recommendation: Base the permanent space specification on user needs.

  • General users: Not required.
  • Update users: Required if users create and store objects.
  • Batch users: Required if users create and store objects.
  • Assistant administrators: Required if users create and store objects.
  • Database programmers: Required to create and store objects.
  • Spool Space

    Optional. Specifies the limit to the amount of space in bytes available for temporary process constructs such as intermediate query results or formatted answer sets to queries and volatile tables. Spool space for a user is shared among the queries that user is executing concurrently.

    The system borrows user spool space from any unused permanent space found on the system.

    The spool space specification 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 special spool considerations.

    If you do enter a value for an individual user, specify approximately 20% of the perm space available to the largest database the user will query.

    Temporary Space

    Not applicable. Required only when using global temporary tables.

    Specifying temporary space requires that you develop a strategy for use of the advanced features that need such space. If necessary, you can add a temporary space specification later with a MODIFY USER or MODIFY PROFILE statement.


    Optional. You can set up user account information to form the basis of various user management options.

    Recommendation: Do not specify accounts at the user level, unless the user requires unique system priorities. Instead name a profile in the Profile parameter that contains the needed account(s). For information, see “Creating User Profiles” on page 108.

    Note: If a user is a member of a profile that specifies one or more accounts, the profile account assignment supersedes and invalidates all account assignments specified in the user definition.

    Default Database

    Optional. Identifies 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.

    Note: Users can use the DATABASE statement to establish a new default database for the current session.

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

  • General users: Specify a read-only Views database. If restrictions are necessary, use role privileges to allow access to particular views.
  • Update users: Specify either a read-only Views or an updatable Views database, depending on where the user normally works.
  • Batch users: Specify the Tables_Database, where batch operations take place.
  • Assistant administrators: Specify DBADMIN.
  • Database programmers: Specify the name of the development database.
  • Profile Name

    Optional. Gives the user membership in the named profile.

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

    A user can be a member of only one profile.

    Recommendation: Specify profile membership for all users. Select from the drop-down list and highlight the needed profile.

    Default Role

    Identifies the default role used for determining user privileges in the database for the current session. This is usually the role associated with the activities performed most frequently by the user.

    Note: The user can employ the SET ROLE statement at the beginning of a session or transaction to change from the default role to another role. The SET ROLE ALL statement allows access to all roles of which the user is a member.

    Recommendation: You may specify a default role later after you have created the user and the roles. See “Defining the Default Role for a User” on page 122. Note that you must specify a default role even if the user is a member of only one role.

    Default Journal


    Recommendation: Do not use for initial database implementation.

    Startup String

    Before Journal

    After Journal


    Optional. Specification of FALLBACK 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: Specify FALLBACK only for individual mission-critical tables or tables so large that their size prevents timely backup to tape or external disk.


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

    Recommendation: Do not change the default values for a user unless the default value is not applicable.

    Note: All users must use the same collation value.

    Time Zone

    Default Date Form

    Default Character Set

    c Click the Create button. The Query Window displays the generated CREATE USER statement, similar to the following:

    AS PERM = 0
    PASSWORD = "temp123"
    STARTUP = ''
    DEFAULT DATABASE = "Personnel_Views"

    d In the Query Window, click the Run Query button . A Query Complete message appears in the bottom left corner of the main window when the query executes.

    e Close the Query Window and the Create User dialog box.

    4 Log off Teradata Administrator or go on to “Using Roles to Manage User Privileges” on page 115.

    Reference Information


    For Step...

    Information on...

    Is available in...


    syntax and options for the CREATE USER statement

    SQL Data Definition Language Syntax and Examples

    the creation and use of accounts

    “Creating User Accounts” on page 106