Teradata DBA Queries - Using Roles and manage Privileges in Teradata - Examples and Scripts

/* FOR MAINTENANCE OF ROLES AND PRIVILEGES */

SELECT * FROM DBC.AllRights WHERE TABLENAME = 'ORDERS';

We need to create Roles and assign privileges to the roles, rather than individual roles.

The below query will give us the list of access rights for a particular user (DBC in this case) on a particular database (retail in the example below).

 select DB.databasename

 , tvm1.tvmname as tvmname

 , user1.databasename as usrname

 ,AR.* 

 from dbc.accessrights as AR

 inner join dbc.dbase as DB

 on AR.databaseid = DB.databaseid

 inner join DBC.TVM as tvm1

 on AR.tvmid = tvm1.tvmid

  inner join dbc.dbase as user1

 on AR.userid = user1.databaseid

 where user1.databasename = 'dbc'

 and DB.databasename = 'retail';

If we keep assigning privileges to individual users, then this table will keep growing. In addition, maintenance will be a overhead.

Individual privileges are assigned as seen in the example below:

GRANT SELECT ON RETAIL.ORDERS TO TRAINING_1;

As per Teradata:

Using Roles simplifies the management of user privileges with roles. 

Roles help improve performance by reducing the number of rows added to and deleted from DBC.AccessRights. 

To use roles, assign privileges to a specific role and then grant the role to users. The users can then access all the objects on which their assigned role and nested roles have privileges.

The above can be achieved using the below list of commands:

CREATE ROLE REPORT_USER;

GRANT SELECT ON RETAIL.ORDERS TO REPORT_USER;

SELECT * FROM DBC.USERS;

CREATE USER TRAINING_1

FROM RETAIL

AS PASSWORD = "TRAINING_1"

, PERMANENT = 40000

, DEFAULT ROLE = REPORT_USER;

DROP USER TRAINING_2;

CREATE USER TRAINING_2

FROM TRAINING_1

AS PASSWORD = "TRAINING_2"

, PERMANENT = 40000

, DEFAULT ROLE = REPORT_USER

, PROFILE = HIGH_SPOOL_USER;

CREATE PROFILE HIGH_SPOOL_USER

AS SPOOL = 10000;

SELECT * FROM DBC.ROLES;

SELECT * FROM DBC.RoleInfo;

Roles and Privileges in Teradata

To manage privileges using roles:

• Create different roles for the different job functions and responsibilities. Roles work best when based on job functions or responsibilities. Some users may belong to more than one role.
• Decide which privileges to which objects a role should have.
• Grant specific privileges on database objects to the roles.
• Grant the role to users or other roles. However, note that roles can only be nested one level.
• Assign default roles to users. Assigning a default role to a user allows the user to automatically access all the objects to which the role has been granted privileges. It also gives the user additional privileges to all the objects its nested roles also owns

Join us in our classroom for more discussion on Teradata.