fbpx

Teradata roles and their proper use

By DWH Pro Admin

February 20, 2020


What are Teradata roles?

Roles serve to simplify rights management. A user who is assigned a role automatically has the same rights as the role.

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 single right and assign all rights to the new department individually. You just change the role of the user.

Do roles also offer performance benefits?

Definitely, because without the use of roles, all rights for each user must be stored in the DBC tables. The required rights are assigned to the role once, and then only one single assignment from user to role per user is needed. This results in less congestion on the DBC tables.

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 rolls be nested?

Roles can, in turn, be assigned to roles. In this way, the role inherits all rights of the superordinate role. Nesting can only take place on one level.

But: Nested roles have a negative performance impact as permissions have to be collected from the DBC tables in a more complicated way!

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, not to databases or roles!

If a user has 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 which 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 table DBC.RoleInfoV

Where can I see all existing role members?

In table DBC.RoleMembersV

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>