All Forums Database
syam 7 posts Joined 09/07
26 Feb 2009
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 RolesAnswer: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 confirmThanks for your help

jiterma5 1 post Joined 01/12
25 Jan 2012

Do we have any specific query for the same.?


Jitendra Sharma

09 Sep 2014




    -- A.DefaultRole,

    -- A.WithAdmin,









WHERE Grantee='USER'

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

ORDER BY 2,1,6;

18 Apr 2016

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

Fred 1096 posts Joined 08/04
19 Apr 2016

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).

alirana78 1 post Joined 03/16
21 Apr 2016

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.

ali rana

Keshav_Antal 2 posts Joined 08/14
27 May 2016

sel 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 =
where =  'User_Name'
User Name list can be appended using in (,,,,,)
Try this and check if it will give you desired output.

Keshav Singh Antal

You must sign in to leave a comment.