Teradata Administrator gives you some very handy features. One I find very useful is the “Rights” function when you right-click on a table or view, which will show you all users that can access a single table or view. Or you can right-click on a Database and use “Right on DB/User” to list all users that have access to the Database.
But, what if you want to know who can access all tables and views in a Database individually?
I’ve taken the SQL from the “Rights” function and modified it to list all access rights on individual objects in the database, so this will show you what access has been granted individually to all tables, views, macros, and procedures within that database specified as “DatabaseName=’database'”:
SELECT UserName AS Grantee,TableName,ColumnName,AccessRight,GrantAuthority,
SELECT RoleName AS Grantee,TableName,ColumnName,AccessRight,'R',
--Modify the 'database' value to the name of the database you wish to view
WHERE DatabaseName = 'database'
-- AND TableName='TableName'
-- AND Grantee='Username"
ORDER BY Grantee, TableName
Note you can modify this further (note the commented-out lines) for different purposes, such as by Table or User.
If you want to see the SQL executed by functions within Teradata Administrator when you execute any function, click Window –> SQL History, or press F12.
Created from Teradata Administrator 184.108.40.206