Teradata Roles: Simplifying Rights Management and Improving Performance

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

The assignment of rights is simplified. u003cbru003eu003cbru003eAn 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.

Yes, 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 only one assignment is needed from user to role per user. This results in less congestion on the DBC tables.

u003cstrongu003eCREATE ROLEu003c/strongu003e DWHPRO_ADMIN;

u003cbr/u003eFor example, you can add read access:u003cbr/u003eGRANT SELECT ON Customer TO DWHPRO_ADMIN;u003cbr/u003eYou can revoke read access like this:u003cbr/u003e REVOKE SELECT ON Customer FROM DWHPRO_ADMIN;

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.u003cbru003eu003cbru003eBut: Nested roles negatively impact performance as permissions have to be collected from the DBC tables more complicatedly!

GRANT ROLE TO DWHPRO WITH GRANT OPTION;u003cbru003eu003cbru003eThis right can only be given to users, no databases or roles!

The active role can be selected with the following statement:u003cbru003eu003cbru003eSET ROLE RoleA;u003cbru003eu003cbru003eShould all roles be active simultaneously? This is the syntax:u003cbru003eu003cbru003eSET ROLE ALL;u003cbru003eu003cbru003eA default role that is used when a user logs on can be defined when creating/changing the user:u003cbru003eu003cbru003eCREATE/MODIFY USER dwhpro … DEFAULT ROLE = DWHPRO_ADMIN;

In table DBC.RoleInfoV

In table DBC.RoleMembersV