Profiles

A set of common parameters that can be applied to a group of users.

Now you have a question in your mind, what set of common parameters?

• These parameters include: Account id's, default database, spool space allocation, temporary space allocation and password attributes (expiration, minchar, etc.)
• Using profiles simplify user management, a change of a common parameter requires an update of a profile rather than updating each user
• Profiles are managed by
  • CREATE PROFILE, MODIFY PROFILE, DROP PROFILE, and SELECT PROFILE

Example: You have a group of 1000 users that are assigned same temporary space, same spool space, same account ID and same default database, if you have to change a single parameter for 1000 users is time consuming. This can be over come by creating a profile with the assigned parameters and assigning that profile to the users. This eases the user management

Implementing profiles:

• CREATE PROFILE: needed to create profiles
• DROP PROFILE: needed to modify and drop profiles

Initially only DBC has CREATE PROFILE and DROP PROFILE access rights. These can given to DBA's by:

GRANT CREATE PROFILE, DROP PROFILE TO DBA WITH GRANT OPTION;

In the following section we will see, how to create a PROFILE and assigning a user to a profile:

Create PROFILE statement:

CREATE PROFILE [profile_name] AS ACCONT = [account_id/null], DEFAULT DATABASE = [db_name/null], SPOOL = [bytes/null], TEMPORARY = [bytes/null], PASSWORD = [password];

CREATE PROFILE Emp AS ACCOUNT = '$H2$acct01&H', DEFAULT DATABASE = 'ABC', SPOOL = '20000000', TEMPORARY = '100000000'...;

Assigning PROFILE to the user: It can be assigned using CREATE USER and MODIFY USER

CREATE USER Emp01 AS..., PROFILE = Emp;

MODIFY USER Emp02 AS PROFILE = Emp;

To remove profile for a USER:

MODIFY USER Emp02 AS PROFILE=NULL;

Profile definitions are applied to all users, overriding specifications at system or user level. All members inherit changed profile parameters. The impact on current users is as follows:

• SPOOL and TEMP space are imposed immediately
• Password attributes take effect upon next logon
• Database and Account ID's are considered at next logon unless the user submits a SET SESSION ACCOUNT statement

Order of precedence of parameters:

• Specify database or account id's at session level
• Specified parameters in a Profile
• CREATE USER or MODIFY USER statements

Source: Teradata Manuals

Related Posts