| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2004-06-14 : 20:57:12
|
| According to BOL, DENY should have precedence over GRANT. But here goes:UserA is in public and RoleA. Public is granted all permissions. RoleA is denied update on TableA. However, UserA can update TableA.What's wrong here?Sarah Berger MCSD |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-14 : 21:23:53
|
| GrantA granted permission removes the denied or revoked permission at the level granted (user, group, or role). The same permission denied at another level such as group or role containing the user still applies. However, although the same permission revoked at another level still applies, it does not prevent the user from accessing the object. For example, if John is already explicitly denied access to customer, has his access to sales, revoked, and then is explicitly granted access to customer, he now can access customer because the deny is removed. The revoke permission for sales joined with the granted permission for John gives John a granted permission overall.(books online)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 21:27:47
|
| >> To selectively prevent employees from seeing CorporateSecrets, create a Nonsecure role, and add the individual employees who should not see the table. When you deny permission to view CorporateSecrets to Nonsecure, this access is removed from all members of Nonsecure, while the rest of the employees in the company are not affected.Are you sure the user isn't dbo?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-06-14 : 21:38:22
|
| The user is not dbo, nor in db_owner role.Derrick, your post is regarding REVOKE, I think. Revoke removes a previously granted or denied permission. My question is regarding DENY, why it isn't overriding the GRANT.Sarah Berger MCSD |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 21:41:14
|
| derrickleggettThat would only apply to permission had been granted to RoleA so removing the deny.A revoke would be overidden by the public permission by the deny should override the grant.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-06-14 : 21:55:28
|
| OK, got it. Sorry for bothering.UserA is a system administrator, and is the owner of a different database. When I looked at the roles for the current database, UserA was only in public and RoleA, but when I looked in EM under Security/Logins, I saw that UserA is a system administrator, albeit not dbo.Sarah Berger MCSD |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-14 : 21:55:55
|
| If the User or public has been granted "after" the deny, then I believe the user would have permissions. Feel free to correct me of course.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 22:15:47
|
| >> Feel free to correct me of course.At that price I will.Any user who is a member of a role which has denied permissions on an object will not be able to access the object no matter what is granted to other roles to which he is a member. That's the difference between deny and revoke.A grant to the same role will remove the deny but a grant to another role won't affect it.DisclaimerHaven't tried it but that's my understanding of how it should work.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-14 : 23:56:56
|
| Couldn't resist. I don't see how your results are right Sarah.--I create the following scenario. After each permissions statement, I tried to run: select * from djl_test_table.sp_addlogin 'test','tester','Northwind'sp_adduser 'test','test','testrole'sp_addrolemember 'testrole','test'sp_addrole 'testrole'--At this point, test cannot select from djl_test_table which has no select permissions. (tested)GRANT SELECT ON djl_test_table TO public--Now it works.DENY SELECT ON djl_test_table TO public--Now it doesn't.GRANT SELECT ON djl_test_table TO test--Now it doesn't.GRANT SELECT ON djl_test_table TO testrole--Now it doesn't.GRANT SELECT ON djl_test_table TO public--Now it works.DENY SELECT ON djl_test_table TO test--Now it doesn't.GRANT SELECT ON djl_test_table TO public--Now it doesn't.GRANT SELECT ON djl_test_table TO testrole--Now it doesn't.GRANT SELECT ON djl_test_table TO testMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-06-15 : 13:25:08
|
And the last one should be quote:
GRANT SELECT ON djl_test_table TO test--Now it works
What about my results don't you understand?Everything you posted is in line with what Nigel and I have posted. (Except maybe that UserA is able to access data they are denied to, although UserA is in public, it seems being a system administrator overrides that)Sarah Berger MCSD |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-06-15 : 15:41:52
|
| revokes and denies do jack squat to a user with sysadmin server role. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-16 : 01:35:21
|
| What about my results don't you understand?Nothing. I wasn't replying to you. :) I just want to run the test for fun. The sysadmin is god...that's why nobody should have it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-06-16 : 08:13:08
|
quote:
Originally posted by derrickleggett
What about my results don't you understand?Nothing. I wasn't replying to you. :) I just want to run the test for fun. The sysadmin is god...that's why nobody should have it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
Funny Thats what they call me sometimes.  I cannot Post what they call me other times......... JimUsers <> Logic |
|
|
|
|
Another permissions question