question about explicit access rights

Database
Enthusiast

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.
5 REPLIES
Enthusiast

Re: question about explicit access rights

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
;

Enthusiast

Re: question about explicit access rights

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

Re: question about explicit access rights

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
;
Enthusiast

Re: question about explicit access rights

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?

Re: question about explicit access rights

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!