Teradata Role Access on Databases

Teradata provides many DBC views that can be used for different purposes. In article, I've shared Useful DBC (Data Base Computer) System Views in Teradata. This page provides some examples about role permission related views.

Role views

There are two role permission related views: dbc.allrolerightsv and dbc.rolemembersv. The former returns all the role permissions while the latter returns role members.

Let's use the following DDLs to create some roles.

CREATE ROLE TEST_ROLE_1;
CREATE ROLE TEST_ROLE_1_1;
GRANT SELECT ON TestDb to TEST_ROLE_1_1;
GRANT TEST_ROLE_1_1 TO TEST_ROLE_1;

The first two statements create two roles. Then SELECT access is granted to the second role on database TestDb. The last statement grants role TEST_ROLE_1_1 to TEST_ROLE_1. 

Check role rights

The following query check role permissions:

select * from dbc.allrolerightsv
where RoleName in ('TEST_ROLE_1','TEST_ROLE_1_1');

The result looks like the following screenshot:

Note: there is not permission granted on TEST_ROLE_1 directly. 

Check role members

Run the following query to check role members:

select * from dbc.rolemembersv
where rolename='TEST_ROLE_1_1'

Result:

Check role rights

Since role permissions can be inherited, we need to use both tables to find role TEST_ROLE_1's rights/permissions. Directly query the AllRoleRightsV view will not list all the inherited permissions.

The following is a sample query:

select rr.* from dbc.allrolerightsv rr
inner join dbc.rolemembersv rm
on rr.RoleName = rm.RoleName
WHERE rm.grantee = 'TEST_ROLE_1'
UNION 
select rr.* from dbc.allrolerightsv rr
WHERE rr.RoleName = 'TEST_ROLE_1';

The output: