What are Teradata roles?
Roles group privileges for administration. Membership alone does not mean all privileges are active: current/default role, SET ROLE and supported nesting determine the effective role path.
What are the advantages of roles in Teradata compared to granting individual rights to users?
The assignment of rights is simplified.
An example: You define one role per department in your company. If a user changes the department, you don’t have to remove every right and assign all rights individually to the new department. You change the role of the user.
Do roles also offer performance benefits?
Roles can reduce repeated grant metadata and simplify administration, but a performance improvement is not guaranteed. Measure the actual authorization workload before making a performance claim.
What is the syntax for creating a role?
CREATE ROLE DWHPRO_ADMIN;
How are rights assigned or revoked to a role?
For example, you can add read access:
GRANT SELECT ON Customer TO DWHPRO_ADMIN;
You can revoke read access like this:
REVOKE SELECT ON Customer FROM DWHPRO_ADMIN;
Can roles be nested?
Granting RoleA TO RoleB makes RoleA privileges available through RoleB under supported activation/nesting rules. Verify current nesting restrictions; no membership activation or privilege-change test was performed.
How can I grant a user the right to create or delete roles?
GRANT ROLE TO DWHPRO WITH GRANT OPTION;
This right can only be given to users, no databases or roles!
If a user has been assigned several roles, which one is active?
The active role can be selected with the following statement:
SET ROLE RoleA;
Should all roles be active simultaneously? This is the syntax:
SET ROLE ALL;
A default role that is used when a user logs on can be defined when creating/changing the user:
CREATE/MODIFY USER dwhpro … DEFAULT ROLE = DWHPRO_ADMIN;
Where can I see all existing roles?
In view DBC.RoleInfoV
Where can I see all existing role members?
In view DBC.RoleMembersV
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book