PROFILES IN TERADATA
In this article you will get to know the PROFILES IN TERADATA
You can create profiles to define resource parameters for groups of users with similar needs instead of defining them for each individual user. Then list the profile in a CREATE or MODIFY USER statement to establish membership in a profile for each user.
Profiles can be based on the primary user types and the other factors that group users according to database resource requirements.
- Use profiles to specify parameters such as:
- Permanent and spool space allocations
- User account(s)
- Password control parameters
- Default database assignment
- Query band
Note: The value for a profile attribute supersedes and replaces any corresponding values specified in global defaults, or in a CREATE USER request.
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';
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.
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:
Note: The content of an account string is limited to 128 characters.
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.
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.
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.
Recommendation: Do not reset any password control parameter values in a profile unless the profile members cannot use the system defaults.
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.
Optional. Creates a query band that is automatically set for the session at logon.
To assign profile to user, use below query
MODIFY USER USERNAME AS PROFILE=PROFILENAME;
DROP statements are recorded in the Data Dictionary. However, the result of a DROP PROFILE, DROP DATABASE, or DROP ROLE statement is not cascaded to the user rows in DBC.Dbase, so the corresponding default setting for each affected user is not reset to NULL. When an affected user next logs on, the system does not return an error or warning.
If you drop a default profile, the system uses the following by default:
ACCOUNT, SPOOL, TEMP, and DEFAULT DATABASE specifications in the CREATE USER or latest MODIFY USER statement
Password attributes defined at the system level in DBC.SecurityDefaultsV.
Note: If you re-create a profile with the same name after it was dropped, users defined for that profilename are assigned the profile parameters at the next logon. The effect of a profile re-creation is not immediate.