The three things all sound like some mechanisms designed for "managing and administering" the database. But how? And what's the difference?
Teradata Account is mainly designed for system accounting purpose. It can help analyze the system performance and usage, plan the capacity, and also for charge-back billing.
One user can have multiple accounts, for different workload and priority purposes. And vice versa, one account can be applied to many users, so that they share the same accounting properties.
When creating a user, one or more account IDs can be specified. Account IDs are used to track system CPU, I/O usage or space usage.
When the user logs on, a valid account ID can be specified in the logon string, otherwise the first account ID from the CREATE/MODIFY USER statement will become the default. All logons require an account ID. If no account ID is specified in CREATE/MODIFY USER statement, it defaults to the account ID of the immediate owner's database.
Account IDs may begin with $L, $M, $H, or $R, meaning priorities low, medium, high and rush respectively. They are different performance groups. General guideline on performance groups:
- $L batch jobs
- $M complex ad-hoc queries
- $H tactical or OLTP queries
- $R very critical queries
Account IDs can be changed without logging off. This is used to change the session's priority or "nicing a query". Bottom line, the new priority cannot exceed the priority originally defined by the performance group for the account.
ASE (Account String Expansion): Substitution variables can be used in the account ID portion of a user's logon string. ASE is a mechanism to provide more detailed utilization report and user accounting data. Valid ASE variables are:
- &L logon timestamp
- &D date
- &T time
- &H hour
- &I logon host/session number/request number
- &S session number
DBC.AccountInfo and DBC.AMPUsage are related system accounting views.
— Create an Account ID ( the first account ID is the default one)
CREATE USER Wei FROM SYSDBA AS PERM = 1E9, SPOOL = 2E9, PASSWORD = 123, FALLBACK, ACCOUNT = ( '$M_medium_&S&D&H', '$H0+Urgent&S&D&H', 'L_load_&S&D&H');
— Log on using one of the Account IDs
— Change the account ID during the session
SET SESSION ACCOUNT='$H0+Urgent&S&D&H' FOR REQUEST/SESSION;
A profile is a set of common user parameters that can be applied to a group of users. The parameters include:
- Account IDs
- Default database
- Spool space allocation
- Temporary space allocation
- Password attributes
A group of users with the same profile specified operate a common set of parameters. Common practice is to create different profiles for different kinds of user groups, based on system parameters that group members share, and then assign the profiles to users. Parameters settings in profiles always take precedence over those specified in CREATE/MODIFY USER statements.
A change of a common parameter requires an update of a profile instead of each individual user affected by the change. Imagine a requirement to change the password policy for 10,000 users without profile — a nightmare.
If a profile is changed, the impact to its user group can be: 1) immediate, 2) in response to a SET SESSION statement, or 3) upon next logon, depending on the parameters:
- SPOOL and TEMP space allocation are imposed immediately
- Password attributes take effect upon next logon
- Account IDs and default database are considered next logon, unless the user submits a SET SESSION ACCOUNT statement which specifies the newly updated account ID
If a parameter in a profile is not set or is set to NULL, then the user's setting will be applied.
DBC.ProfileInfo is the related view for profile.
— Create a Profile
CREATE PROFILE Employee_P AS ACCOUNT = 'L_load_&S&D&H', DEFAULT DATABASE = EmployeeDB, SPOOL = 1E9, TEMPORARY = 1E7, PASSWORD ATTRIBUTES = (EXPIRE = 90, MAXLOGONATTEMPTS = 3);
— Assign the Profile to a user
CREATE/MODIFY USER Emp01 AS PERM=0, PASSWORD=123, PROFILE=Employee_P;
— Remove a profile from a user
MODIFY USER Emp01 AS PROFILE=NULL;
A role is simply a collection of access rights. It can be viewed as a "pseudo-user" with priviledges on a number of database objects. Any user granted a role can take on the identity of the pseudo-user and access all of the objects it has rights to. Common practice is to create different roles for different job functions and responsibilities, grant specific privileges on database objects to these roles, and then grant these roles to selected users.
Roles can only be nested one level. Thus a role that has a role member cannot also be a member of another role.
DBC.RoleInfo and DBC.RoleMembers are related views for role.
— Create a new Role
CREATE ROLE Role_A;
— Grant some privileges to this Role
GRANT SELECT, EXECUTE ON View1 TO Role_A;
— Assign this role to a user
GRANT Role_A TO User1;
— Revoke a role from a user
REVOKE Role_A FROM User1;
— The user can change its role
SET ROLE Role_B;
— Or void the current role
SET ROLE NONE;
— Or have all valid roles (for that user) to be active
SET ROLE ALL;
In summary, Account simplifies the accounting task, Profiles simplifies user management and Roles simplifies access rights management. Also, profiles and roles share a same characteristic — the concept of ownership and ownership hierarchy is not applicable to them. Account, profile and role can all be specified in the CREATE/MODIFY USER statements.