Chapter 7 - Profiles
“It's always been, and always will be the same in the world: the horse does the work, and the coachman is tipped.”
Profiles
It is easy to get mixed up between a Profile and a Role. Think of it as “Profiles are for People” and “Roles are for Rights”.
Profiles define system attributes for users (people). By assigning a profile to a group of users, you can rest assured that all group members will operate under a common set of attributes.
Create a different profile for each user group, based on system attributes that members share.
If you do set the value of a parameter in the profile the settings override the settings for the user in a CREATE USER or MODIFY USER statement.
If you do not set the value of a parameter, the system uses the setting defined for the user in a CREATE USER or MODIFY USER statement.
Profile parameters include:
- Account IDs
- Default database
- Spool and Temp space (but not Perm)
- Password attributes
Imagine a thousand users assigned the same amount of spool and temp space, the same default database, and the same account ID. With them all under the same profile, you can make one profile change and all thousand users receive the change. Imagine the nightmare of having to make a change to all thousand users if a profile was not used.
Getting Started for Profile Creation
To create a profile, one needs to be given CREATE and DROP Profile Rights. Only DBC is given these rights initially. User DBC can GRANT others this right.
When the system first arrives, only DBC can create and drop profiles. DBC can, however, grant others this access right. In the above examples, DBC has granted both SysDBA and TeraTom the ability to create and drop profiles. Notice that TeraTom has been given the ability to grant others this right because of the WITH GRANT OPTION.
Creating A Profile and a User
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9,
TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
CREATE USER Sales01 AS
PERM = 0,
PASSWORD = Salesabc,
PROFILE = Sales_Profile;
We have just created a profile. We have given them three account options, but the first account listed is the default. Each user assigned this profile will have a default database as Sales_Views and each gets 1,000,000,000 Bytes of spool space and 500,000,000 Bytes of temp space. We have also set quite a few password restrictions.
We have also created a user and assigned them to our newly created profile.
Password Security
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9,
TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
Teradata password security features allow you to:
• Expire passwords after a specific number of days.
• Define the amount of time to elapse before a password is allowed to be reused.
• Control both the minimum and maximum length of a password.
• Disallow specific digits or special characters in a password.
• Limit the number of erroneous logon attempts before the system locks the user out.
• Automatically unlock users after a specific period of time.
You can make the password logon as simple or complex as you desire.
Password Security Meanings
ExpirePassword - Number of days to elapse before password expiration. A zero (0) indicates that passwords do not expire and the default is 0.
PasswordMinChar - Min number of characters in a valid password string and default is 1.
PasswordMaxChar - Max number of characters in a valid password string and default is 30.
PasswordDigits - Indicates if digits are allowed in the password (Y, N, or R ) and the default is Y.The letter R indicates that one or more digits are required.
PasswordSpecChar - Indicates if special characters are allowed in the password (Y or N) and the default is Y. The options of A to P and R provide for more secure passwords.
PasswordRestrictedWords - Indicates whether or not a password is subject to content restrictions (Y or N) and the default is N.
PasswordRestrictWords - Indicates whether or not a password is subject to content restrictions (Y or N) and the default is N.
MaxLogonAttempts - Indicates the number of erroneous logons allowed before locking user. Zero (0) indicates that user is never locked and the default is 0 while the max is 32,767.
LockedUserExpire - Indicates the minutes to elapse before a locked user is unlocked. Zero (0) indicates immediate unlock while -1 = locked indefinitely. The default is 0 and max is 32,767.
PasswordReuse - Indicates the days to elapse before a password can be reused. Zero (0) indicates immediate reuse. The default is 0 and the number of days is 32,767.
Creating A Profile and then Modifying a User
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9,
TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
MODIFY USER Sales99 AS
PROFILE = Sales_Profile ;
Once a profile is created, you can easily create users and assign them to the profile. You can also modify existing users and assign them the profile also. That is exactly what we have done in our above example.
Quiz – What are the Profile Values?
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9,
TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
CREATE USER Sales02 AS
PERM = 0,
PASSWORD = Salesxyz,
PROFILE = Sales_Profile,
SPOOL = 2E6,
ACCOUNT = '$R_Sales&L' ;
How much spool does Sales02 get? __________
If Sales02 logs into Teradata, what is the default account that will be used? __________
Answer to Quiz – What are the Profile Values?
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9,
TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
CREATE USER Sales02 AS
PERM = 0,
PASSWORD = Salesxyz,
PROFILE = Sales_Profile,
SPOOL = 2E6,
ACCOUNT = '$R_Sales&L' ;
Since these values are listed in the profile the profile values take precedence?
How much spool does Sales02 get? 1E9
If Sales02 logs into Teradata, what is the default account that is used? '$L_Sales&L'
Quiz – What are the Profile Values After Null?
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9, TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
CREATE USER Sales02 AS
PERM = 0,
PASSWORD = Salesxyz,
PROFILE = Sales_Profile,
SPOOL = 2E6,
ACCOUNT= '$R_Sales&L' ;
MODIFY USER Sales 02 AS PROFILE = NULL;
After the Modify User command above is run:
- How much spool does Sales02 get? __________
- What is the account for the current session? __________
- What is the account for a new session? ____________
Answer to Quiz – What Are the Profile Values After Null?
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9, TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
CREATE USER Sales02 AS
PERM = 0,
PASSWORD = Salesxyz,
PROFILE = Sales_Profile,
SPOOL = 2E6,
ACCOUNT = '$R_Sales&L' ;
MODIFY USER Sales02 AS PROFILE = NULL;
After the Modify User command above is run:
- How much spool does Sales02 get? 2E6
- What is the account for the current session? '$L_Sales&L'
- What is the account for a new session? '$R_Sales&L'
The DBC.ProfilesVX View
| SELECT | ProfileName ,DefaultAccount AS “Def Acct” ,DefaultDB ,SpoolSpace (FORMAT 'z,zzz,zz9,999') ,TempSpace (FORMAT 'zzz, zz9, 999') |
| FROM | DBC.ProfileInfoV |
ProfileNameI , ProfileName , ProfileId , DefaultAccount , DefaultDataBase , SpoolSpace , TempSpace , ExpirePassword , PasswordMinChar , PasswordMaxChar , PasswordDigits , PasswordSpecChar , PasswordRestrictWords , MaxLogonAttempts , LockedUserExpire , PasswordReuse , Version , CommentString , CreateUID , CreateTimeStamp , LastAlterUID , LastAlterTimeStamp , CostProfName
Columns in this view
This view shows information about profiles, but only for the current user's profile. Any DBC view that has a VX at the end of it means it is a newer view (V), and it is restricted (X) to show information that only concerns the current user.
The DBC.ProfilesV View
| SELECT | ProfileName ,DefaultAccount AS “Def Acct” ,DefaultDB ,SpoolSpace (FORMAT 'z,zzz,zz9,999') ,TempSpace (FORMAT 'zzz,zz9,999') |
| FROM | DBC.ProfileInfoV |
ProfileNameI , ProfileName , ProfileId , DefaultAccount , DefaultDataBase , SpoolSpace , TempSpace , ExpirePassword , PasswordMinChar , PasswordMaxChar , PasswordDigits , PasswordSpecChar , PasswordRestrictWords , MaxLogonAttempts , LockedUserExpire , PasswordReuse , Version , CommentString , CreateUID , CreateTimeStamp , LastAlterUID , LastAlterTimeStamp , CostProfName
Columns in this view
This view shows information about all profiles in the system.
The DBC.AccountInfoVX View
SELECT * FROM DBC.AccountInfoVX;
| UserName | AccountName | UserOrProfile |
| Sales_Profile | $L_Sales&L | Profile |
| Sales01 | $L_Sales&L | User |
This view shows information about accounts in the system, but it is restricted to the current user and the information that affects only them.
ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
SELECT Profile;
Check to see if you have a profile
SELECT * FROM
DBC.ProfileInfoVX;
Show all information about your profiles
SELECT * FROM
DBC.RoleMembersVX;
Check to see all the role names, the grantor and the date it was granted
SELECT RoleName, DatabaseName, TableName, ColumnName, AccessRight FROM DBC.UserRoleRightsV ORDER BY 1;
Check to see all the columns above for any roles in the system
SELECT COUNT(*)
FROM DBC.RoleInfoVX;
Check to see all the roles that you personally have created
The ProfileInfoVX, RoleMembers, UserRoleRights and RoleInfoVX views show you what you need to know about Roles and Profiles. Remember that Roles are for “Rights” and Profiles are for “People”.
Teradata Administrator Can CREATE Profiles (1 of 2)
You can use Teradata Administrator to create Profiles. Go to Teradata Administrator and to the Tools menu and select Profiles. Above, you can see the General screen.
Teradata Administrator Can CREATE Profiles (2 of 2)
You can use Teradata Administrator to create Profiles. Go to Teradata Administrator and to the Tools menu, and select Profiles. Above you can see the Password screen.
Dropping a Profile
CREATE PROFILE Sales_Profile AS
ACCOUNT = ('$L_Sales&L', '$M_Sales&L', '$H_Sales&L'),
DEFAULT DATABASE = Sales_Views,
SPOOL = 1E9,
TEMPORARY = 500E6,
PASSWORD = (EXPIRE = 120, MINCHAR = 7, MAXLOGONATTEMPTS = 4,
LOCKEDUSEREXPIRE = 60, REUSE = 180,
DIGITS = 'R', RESTRICTWORDS = 'Y', SPECCHAR = 'P'),
DROP PROFILE Sales_Profile ;
It might take a lot of words to create a profile, but it only takes three to drop one.
The Effects of Dropping a Profile
If a profile is dropped, users with that profile keep that profile.
When a profile with the same name is recreated, the new profile takes effect on the next login.
There are three major effects on sessions currently logged in when their profile is dropped:
- Spool and temporary space settings immediately change for the user's settings.
- Account and database settings change to the user's settings the next time the user performs a login, or if the user explicitly changes the settings themselves.
- Users may only change to an account in the list of account IDs available to them.
DROP PROFILE Sales_Profile ;
We were tested earlier with questions concerning whether a profile takes precedence over the same parameters given (when a user is created), and the profile always takes precedence. But, when a profile is dropped, then the parameters given to the user explicitly during the Create User statement often then take over. The rules above should help clear this up.