Teradata Role Access on Databases

visibility 75 comment 0 access_time 5m languageEnglish

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:


info Last modified by Raymond 5m copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Tags
More from Kontext
visibility 77
thumb_up 0
access_time 10m
visibility 78
thumb_up 0
access_time 10m
visibility 11
thumb_up 0
access_time 28d