Query to find user access and roles in Teradata

Teradata developers have to face the scenarios where they need to check whether their user id (or someone else in their team) has access to particular database or table in Teradata. For this trivial question, it’s not wise to disturb the admin/DBA team if you can check it yourself by querying on just 2 DBC tables.

To find user access we need to query on DBC.ROLEMEMBERS and DBC.ALLROLERIGHTS. Both are system tables and your user id (through which you login in Teradata) must have access to query on these tables. You can easily check that by doing –

• SELECT * FROM DBC.ROLEMEMBERS
• SELECT * FROM DBC.ALLROLERIGHTS

If you have access then run below query to get all required information about specific user given in the where clause.

SELECT

A.ROLENAME,

A.GRANTEE AS USER_ID,

A.GRANTOR AS ADMIN_ID,

B.DATABASENAME,

B.TABLENAME,

B.GRANTORNAME,

B.ACCESSRIGHT

FROM    DBC.ROLEMEMBERS A

JOIN    DBC.ALLROLERIGHTS B

ON  A.ROLENAME = B.ROLENAME

WHERE  GRANTEE=’give_user_id_here‘

GROUP BY 1,2,3,4,5,6,7

ORDER BY 2,1,6;

Feel free to share your thoughts in the comment section.