16.10 - Creating Users - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
  1. From the client program of your choice, log on to Teradata Database as user DBADMIN.
    This procedure creates users individually. To automate the creation of users by using BTEQ scripts, see Using BTEQ Scripts to Create Database Objects.
  2. Determine values for the following objects:
    Field or Control Explanation
    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.

    The username must conform to the rules for SQL object names.
    Owner 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.

    Password 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.

    Account Optional. You can set up 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.

    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.
    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.
    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. Note that you must specify a default role even if the user is a member of only one role.

    Default Journal Optional.

    Recommendation: Do not use for initial database implementation.

    Startup String
    Before Journal
    After Journal
    FALLBACK 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.

    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 default values for a user unless the default value is not applicable.

    All users must use the same collation value.
    Time Zone
    Default Date Form
    Default Character Set
    Run a CREATE USER statement, similar to the following:
    CREATE USER "GenUser1" FROM "DBADMIN"
    AS PERM = 0
    PASSWORD = "temp123"
    STARTUP = ''
    DEFAULT DATABASE = "Personnel_Views"
    NO FALLBACK
    NO BEFORE JOURNAL
    NO AFTER JOURNAL
    PROFILE=”GenUser_Profile”;

Reference Information

Step... Topic Resources for Further Information
3 Syntax and options for the CREATE USER statement SQL Data Definition Language Syntax and Examples
The creation and use of accounts Creating User Accounts