Creating User DBADMIN - Teradata Database

Teradata Database Administration

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

Creating User DBADMIN

1 From an administrative client, log on to Teradata Administrator as user DBC.

2 Create the database administrator as follows:

a From the menu, click Tools>Create>User.

b Specify values for the following fields and controls:

Note: Do not enter values at this time for fields not listed.




User Name

Required. The name of the database administrator user.

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


The owner of user DBADMIN, which is DBC.


Required. The temporary password for DBADMIN.

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

Note: 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 Security Administration.

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 example in step c on page 33.

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

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

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 in step c on page 33.

Recommendation: Spool space specification is not normally needed. Spool is already allocated in the DBADM profile.

Default Database

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

Recommendation: Specify DBADMIN.


Required. The name of the profile in which the user has membership. A user can be a member of only one profile.

Recommendation: Enter DBADM, the profile created for user DBADMIN in Creating the DBADM Profile.


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.

Recommendation: Uncheck Fallback. FALLBACK is only recommended for individual mission-critical tables or tables so large that their size prevents timely backup to tape or external disk.

Note: You can adjust the space assignments if required, using the Modify User GUI.

c Click Create.

The Query Window pops up and displays the generated CREATE USER statement, for example:

AS PERM = 2e5 * (hashamp() + 1);
PASSWORD = "temp"
SPOOL = spool_space

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.

Note: Some options appear in the SQL that are not specified in the procedure. These are system defaults and should remain as they are. For example Before Journal and After Journal are checked No by default.

d From the Query window, click the Run Query button .

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

3 Keep the Teradata Administrator utility open for use in the “Granting Database Privileges to User DBADMIN” on page 35, or close the utility by clicking File>Exit.

Note: 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.

Reference Information

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


For step...

Information on...

Is available in...


logon to Teradata Administrator

Teradata Administrator User Guide


user types and characteristics

Chapter 5: “Creating Users and Granting Privileges,” in Security Administration

syntax and options for the CREATE USER statement

SQL Data Definition Language Syntax and Examples

guidelines for managing of space

Chapter 9: “Managing Space: Operational DBAs.”

the default password control values and how to change them

Chapter 6: “Managing Database Passwords,” in Security Administration