«

Mar 22

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.

 

1 comment

  1. Foo Hoo

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>