How to Create Profiles | Teradata Vantage - Creating Profiles - Advanced SQL Engine - Teradata Database

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,
     PASSWORD =
       (EXPIRE = n_days,
       MAXLOGONATTEMPTS = attempts,
       LOCKEDUSEREXPIRE = n_minutes),
   QUERY_BAND = 'Pair_name=pair_value;' [NOT] DEFAULT';

2. Include the following parameters:

   Parameter	Description
   Name	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.
   Account	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. 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: ('$M00FINB&D&H','$L00accO&D&H', '$R00MKTR&D&H') 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	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% To specify spool space, you can also use an expression that returns a numeric value, for example: CREATE PROFILE Bennie AS SPOOL ='2e6' BYTES; 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. 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	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.
   Password	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. Recommendation: Do not reset any password control parameter values in a profile unless the profile members cannot use the system defaults.
   Cost Profile	Optional. An Optimizer cost profile associated with the profile. Optimizer cost profiles are not intended for use on production systems. The Cost Profile parameter is for use only under the direction of Teradata Support Center personnel.
   Constraint	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 [NOT] DEFAULT	Optional. Creates a query band that is automatically set for the session at logon. The DEFAULT option allows users to set different values for a session or transaction query band than the values in the profile query band. The NOT DEFAULT option prevents a user from changing the query band values for a session or transaction. For more details, see About Defining Name-Value Pairs for Profile Query Bands.
   Ignore Query_Band Values	Optional. Defines the values that are discarded if they appear in a SET QUERY_BAND request. This option prevents a user with this profile from setting a query band with certain values. For more details, see About Defining Name-Value Pairs for Profile Query Bands.

3. Specify profile membership by doing one of the following:
   • Assign a profile to each user as part of Working with Database Users.
   • 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