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_1to 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_1directly.
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 AllRoleRightsVview 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: