access_time 2 months ago languageEnglish
more_vert

Teradata Role Access on Databases

visibility 18 comment 0

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 2 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 621
thumb_up 0
access_time 8 months ago
visibility 35
thumb_up 0
access_time 5 months ago
visibility 14543
thumb_up 0
access_time 7 years ago