DWHPro Insights

Teradata Roles: Simplifying Rights Management and Improving Performance

By Roland Wenzlofsky ·

Teradata Roles: Simplifying Rights Management and Improving Performance

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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam