How to check all access rights for a Teradata user ?

Often we want to troubleshoot the access issues for a colleague or a end user. There is an easy way to do this without having to rely on the DBAs

In Teradata, all explicit rights granted by the system and the security admin are managed by using "roles". Role membership can be determined within the DBC.RoleMembers view. Access rights for a given role can be identified in the DBC.AllRoleRights.

The following query combines these two views and provides a unified view of all the accesses the user has

SELECT A.ROLENAME as Role_Name,
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=’user_id_here‘
ORDER BY 2,1,6;

nVector

posted on 26 May 18