Creating Profiles - Teradata Database

Teradata Database Administration

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

Creating Profiles

1 Open your favorite client software to construct your SQL request. For example:

CREATE PROFILE profile_name AS
  ACCOUNT = (‘account_str1’,’account_str2’)
  DEFAULT DATABASE = database_name,
  SPOOL = spool_space,
    (EXPIRE = n_days,
    MAXLOGONATTEMPTS = attempts,
    LOCKEDUSEREXPIRE = n_minutes), 
QUERY_BAND = 'Pair_name=pair_value;' [NOT] DEFAULT';

2 Include the parameters:





The unique name for the profile. Include such things as the user type or user department to identify its function. For example, GenUser_Profile or Finance_Profile. Consolidate user requirements where possible to avoid the unnecessary proliferation of profiles.


The database uses accounts for user job tracking and to prioritize requests.

Recommendation: Enter one or more account strings in accordance with the format shown in “Creating User Accounts” on page 106.

If you assign more than one account string to a profile, enclose each string in apostrophes, separate the strings with commas and enclose the complete specification in parentheses, for example:

(‘$M0+accl&S&D&H’,‘$L0+accr&S&D&H’, ‘$R0+acc51’)

Note: The content of an account string is limited to 128 characters.

Default Database

Optional. The database the user is most likely to access.

If no name is specified, the system uses the name specified in the user definition (CREATE USER statement).

Recommendation: Specify the default database at the user level, rather than in the profile, to allow for differences in user default database requirements.

Spool Space

This value limits the amount of space available for 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 spool space for a user from unused permanent space in the system.

Recommendation: Spool space requirements in a profile depend on the activities of member users. Begin by specifying spool space in bytes according to the following percentages of the total perm space allocation for the Spool_Reserve database.

  • General users: 5%
  • Update users: 10%
  • Batch users: 10%
  • Assistant administrative users: 10%
  • Database programmers: 10%
  • Periodically monitor usage patterns and modify spool space assignments when required using the MODIFY PROFILE statement.

    Spool space can also be specified as part of a CREATE USER statement or modified as part of a MODIFY USER statement.

    Note: Profile members that do not require permanent space must still be allocated a small amount of spool space in the profile, for example, 1 MB.

    Temporary Space

    This is required only when using global temporary tables.

    Recommendation: Initially omit a temporary space specification unless you have a predetermined temporary space requirement for this group of users. If necessary, you can add a temporary space specification later with a MODIFY PROFILE statement.


    Optional. The password control parameters allow you to apply different settings in the profile than are in the global default settings created in DBC.SysSecDefaults, as part of step 2 in “Setting Up the Database Administrator User” on page 30.

    Recommendation: Do not reset any password control parameter values in a profile unless the profile members cannot use the system defaults.

    Cost Profile

    Optional. The Optimizer cost profile name to be associated with the profile.


    Optional. Name of one or more row-level security constraints, each followed by a list of the hierarchical levels or non-hierarchical categories, valid for the constraint, which are being assigned to the profile_name.

    Query Band

    Optional. Creates a query band that is automatically set for the session at logon. For more details, see “About Defining Name-Value Pairs for Profile Query Bands” on page 231.

    3 Specify profile membership as follows:

  • Assign a profile to each user as part of “Working with Database Users.”
  • or

  • After creating users, use a MODIFY PROFILE statement to include member users.
  • 4 Close the client software or go on to Working with Database Users.

    Reference Information


    Information on...

    Is available in...

    syntax and options for the CREATE PROFILE statement

    SQL Data Definition Language Syntax and Examples

    using accounts to enforce session priorities

  • “Managing the Database Workload with Teradata Active System Management” on page 226
  • information on monitoring and reallocating space

    “Chapter 9 Managing Space: Operational DBAs” on page 169

    global temporary tables

    Database Design

    password control options

    Security Administration