All Forums Database
Karam 75 posts Joined 07/09
20 Nov 2009
question about explicit access rights

Hi All

What I want oto know
Iam intrested to know all the explicit access rights on a given table covering individual and role privileges.
Say for eg - UserA and RoleA have insert rights on Table1.
Then what should I query to get the resultset showing UserA and RoleA with their access.

What I know
1.Using DBC.ALLRIGHTS and DBC.ALLROLERIGHTS , by providing a username , we can get all the explicit rights granted to user either directly or via role.
2.Specifying tablename in WHERE clause of DBC.ALLRIGHTS , the resultset gives us the user name and its rights having automatic access.

Above question is similar but the focus is explicit rights on an an object covering individual users and roles.

Jimm 298 posts Joined 09/07
20 Nov 2009

Use DBC.RoleMembers

Select RMM.Grantee As UserName
, RMM.RoleName
, ARR.AccessRight
, ARR.Databasename
, ARR.Tablename

From DBC.RoleMembers RMM
Join DBC.AllRoleRights ARR
ON RMM.RoleName = ARR.RoleName
Where ARR.Databasename = '?DBName'
And ARR.Tablename = '?TblName'
Union
Select ALR.Username
, Null
, ALR.Accessright
, ALR.Databasename
, ALT.Tablename
Where ARR.Databasename = '?DBName'
And ARR.Tablename = '?TblName'
Order By 4,5,1,3,2
;

Karam 75 posts Joined 07/09
20 Nov 2009

Thanks Jimm..
But query following UNION is missing table names.. Can you check that..

Jimm 298 posts Joined 09/07
21 Nov 2009

Apologies - it was late!

Select RMM.Grantee As UserName
, RMM.RoleName
, ARR.AccessRight
, ARR.Databasename
, ARR.Tablename

From DBC.RoleMembers RMM
Join DBC.AllRoleRights ARR
ON RMM.RoleName = ARR.RoleName
Where ARR.Databasename = '?DBName'
And ARR.Tablename = '?TblName'
Union
Select ALR.Username
, Null (Char(30))
, ALR.Accessright
, ALR.Databasename
, ALR.Tablename
From DBC.AllRights ALR
Where ALR.Databasename = '?DBName'
And ALR.Tablename = '?TblName'
Order By 4,5,1,3,2
;

Karam 75 posts Joined 07/09
01 Dec 2009

thanks again Jimm...your script is good when we want to know a user's explicit rights either directly or via role where as my question is - how to know which user/role is having explicit rights on a particular table? Any insight here?

Tread831 1 post Joined 11/12
19 Nov 2012

Using the script above we could do something like this :     
     Select RMM.Grantee
          , RMM.RoleName
          , ARR.AccessRight
          , ARR.Databasename
          , ARR.Tablename
          From DBC.RoleMembers RMM
          Join DBC.AllRoleRights ARR
          ON RMM.RoleName = ARR.RoleName
          Where ARR.Databasename = '<dbname here>'
          And (ARR.Tablename = 'All' or ARR.Tablename = '<tablename here>')
          AND RMM.Grantee = '<userID here'
----------------- This catches the "ALL" case as well as the actual table name  but may have a drawback as it allows for duplicates --------------------------------------
and to remove duplicates try this
Select distinct(RMM.RoleName), RMM.Grantee , ARR.AccessRight From DBC.RoleMembers RMMJoin DBC.AllRoleRights ARR
ON RMM.RoleName = ARR.RoleName
Where ARR.Databasename = '<DBname here>'
And (ARR.Tablename = 'All' or ARR.Tablename = '<TABLE NAME HERE>')
AND RMM.Grantee = '<USER ID HERE>'
 
- I know this is way late but it helps to share this info for the next person who needs it. Work smarter!  Google it!

You must sign in to leave a comment.