query to find roles and access rights related

Database
Enthusiast

query to find roles and access rights related

Please help me in developing the following queries.

1.list of users who are having access rights directly given to them without using Roles
Answer:sel databasename, username,tablename,accessright from dbc.allrights where where accessright not in (sel accessright from dbc.allrolerights);
Please correct me,if the query is not correct.

2.list of users who are having Power User (All User Role) rights (GRANT/REVOKE etc)

3.list of roles which have been Granted/Revoked, who and which to whom.
Ans:i think this we will get it from DBC.ROLEMEMBERS table.please confirm

Thanks for your help
6 REPLIES

Re: query to find roles and access rights related

Do we have any specific query for the same.?

Thanks
Jitendra

Re: query to find roles and access rights related

SELECT A.ROLENAME,

A.GRANTEE,

A.GRANTOR,

    -- A.DefaultRole,

    -- A.WithAdmin,

    B.DATABASENAME,

    B.TABLENAME,

    --B.COLUMNNAME,

    B.GRANTORNAME,

B.AccessRight

FROM    DBC.ROLEMEMBERS A

JOIN    DBC.ALLROLERIGHTS B

ON A.ROLENAME = B.ROLENAME

WHERE Grantee='USER'

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

ORDER BY 2,1,6;

Enthusiast

Re: query to find roles and access rights related

when new user is created ,they get default access to DBC.tables ?. I do not want them to have DBC.tables access. Please suggest me

Teradata Employee

Re: query to find roles and access rights related

By default, some DBC views have SELECT access granted to PUBLIC. Some of these rights can be revoked, but the client drivers need some way to retrieve metadata in order to process queries and result sets properly.

There are views with X suffix (collectively called "X Views") that filter metadata based on the user's other access rights; SELECT access must not be revoked from those. And if you revoke SELECT access on any of the corresponding views without the X, then users will need to configure their client drivers to "Use X Views" instead. There is a significant performance impact to using X Views, which is why this is not the default behavior. Collecting statistics on DBC tables may reduce this impact (but will not eliminate it entirely).

Re: query to find roles and access rights related

i currently have 4200 users on the production system. i need to find which users only have select access to edw objects and classify them as analytic users, what would be the best way to achieve this task. some of my users have their own databases on which they have select, insert, create, delete. the only way i can truly classify an analytic user is a user which only has select, help, show on a view in edw. any help is greatly appreciated.

Re: query to find roles and access rights related

sel c.name as User_Name, b.databasename as Database_Name, a.accessright from dbc.accessrights a

join dbc.dbase b

on a.databaseid = b.databaseid

join dbc.UserDB c

on a.userid = c.id

where c.name =  'User_Name'

User Name list can be appended using in (,,,,,)

Try this and check if it will give you desired output.