June 22, 2011 at 6:46 am
created database role deny_read
added all users/groups to that role
Deny select on test_table to public
(all user are denied read perms on test_table as of now)
now,
revoke select on test_table to test_user ( removed the test_user from deny_read role)
[highlight=#ffff11]still the user can't select data from test_table.[/highlight]
please help me out whether iam on track 🙂
-gowtham m
June 22, 2011 at 7:06 am
MG Chowdary (6/22/2011)
created database role deny_readadded all users/groups to that role
Deny select on test_table to public
(all user are denied read perms on test_table as of now)
now,
revoke select on test_table to test_user ( removed the test_user from deny_read role)
[highlight=#ffff11]still the user can't select data from test_table.[/highlight]
please help me out whether iam on track 🙂
-gowtham m
I'm not sure what your question is here. Are you wondering why test_user can't SELECT from test_table? It's because test_user is a member of public, and you haven't REVOKEd the DENY from public.
John
June 25, 2011 at 1:56 am
MG Chowdary (6/22/2011)
now,revoke select on test_table to test_user ( removed the test_user from deny_read role)
Not true. This command does not remove the user from the role. It just revokes any explicit deny permission that the user might have on that table. The user is still member of the role, and is still denied select on the table.
You can read more on the permission checking algorithm here:
Deny permissions will almost always take precedence over a grant.
Exceptions:
- Grant on column-level will override deny on object level.
- You cannot grant, deny, or revoke permissions to sa, dbo, the entity owner, information_schema, sys, or yourself. (Cut and paste from BOL)
June 26, 2011 at 10:54 pm
I'm not sure what your question is here. Are you wondering why test_user can't SELECT from test_table? It's because test_user is a member of public, and you haven't REVOKEd the DENY from public.
John
Thanks for ur reply,
Then whats the workaround in that kinda situation where a particular user need to be given select perms and rest are denied
i denied select for public because the default public role has read perms on all objects so to avoid new users from selecting data i denied select on public.
(hope i conveyed what i need plz let me know if need any details)
-gowtham m
July 4, 2011 at 1:59 am
MG Chowdary (6/26/2011)
i denied select for public because the default public role has read perms on all objects so to avoid new users from selecting data i denied select on public.
The public role doesn't have any permissions on user-created (as opposed to system) objects by default. If the permissions are there, it's because you granted them. Try revoking all GRANTs from public on user objects and then just granting access to users (or, better still, roles) that need it.
John
July 6, 2011 at 11:51 pm
If you want deny user from select the table
you should
Deny select on test_table to test_user
"revoke select on test_table to test_user"
only remove the select permission of this user (test_user) from table
if this user is belong to certain group, let say test_group
and test_group have "SELECT" permission on test_table,
test_user still able to select the data from the table
If you deny select on test_table to test_user
it will override any permission grant(through group) to this user
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply