Creating the Database Administrator User - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™
  1. From an administrative client, log on as user DBC.
  2. Create the database administrator as follows:
    Field Description
    User Name Required. The name of the database administrator user.

    All administrative procedures in this publication use the name DBADMIN for the principal administrator.

    Owner The owner of user DBADMIN, which is DBC.
    Permanent Space Required. The space in bytes that contains all objects that user DBADMIN creates or owns.

    Perm space can also be specified with a constant expression resulting in a numeric value in bytes. This allows you to specify perm space per AMP, which is useful when porting applications from a smaller test system to a production system. See the next example.

    Because user DBADMIN will create and own nearly all databases and tables, assign it the majority of space on the system.

    Recommendation: 60-65% of available system (DBC) space.

    Password Required. The temporary password for DBADMIN.

    Recommendation: Any simple password will work that follows the default system password controls and site policy. Each user is prompted to change the temporary password to a permanent, private password at first logon.

    Be sure to review the default password control values to determine whether they conform to your site security requirements. For information on password control options, see Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.
    Spool Space Optional. The spool space specification is only a limit to the amount of space available for intermediate query results or formatted answer sets to queries and volatile tables. The system borrows spool space for a user from unused permanent space anywhere in the system.

    Spool space can also be specified with a constant expression resulting in a numeric value in bytes. This allows you to specify spool space per AMP, which is useful when porting applications from a smaller test system to a production system. See the example below.

    Recommendation: Spool space specification is not normally needed. Spool is already allocated in AdminProfile.

    Default Database

    Optional. The user or database containing the space in which Advanced SQL Engine stores or searches for new or target objects unless a different database is specified in the transaction SQL.

    Recommendation: Specify DBADMIN.

    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, to which the system can revert in the event of a failure.
    You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
    Profile Required. The name of the profile in which the user has membership. A user can be a member of only one profile.

    Recommendation: Enter AdminProfile, the profile created for user DBADMIN in Creating the Database Administrator Profile.

For example:

CREATE USER "DBADMIN" FROM "DBC"
AS PERM = 2e5 * (hashamp() + 1)
PASSWORD =  "temp" 
SPOOL =  spool_space 
PROFILE = AdminProfile
STARTUP = ''
NO BEFORE JOURNAL
NO AFTER JOURNAL;

The hashamp() function is a database built-in function that returns the highest AMP number in the system configuration. Because AMP numbers start from zero, (hashamp() + 1) returns the total number of AMPs in the system. The example expression: 2e5 * (hashamp() + 1) means allocate 200,000 bytes on each AMP for perm or spool space for the user or database.

After you create the administrative user DBADMIN, do not log on as user DBC to perform subsequent administrative activities except during activities that only user DBC can perform.

Keep the client application open for use in the Granting Database Privileges to User DBADMIN.

Reference Information

Reference topics are arranged according to the first step in which the topic appears.

Step Topic Resource for Further Information
2 User types and characteristics “Creating Users and Granting Privileges,” in Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100
Syntax and options for the CREATE USER statement Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144
Guidelines for managing of space Managing Space: Operational DBAs
The default password control values and how to change them “Managing Database Passwords,” in Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100