January 27, 2011 at 1:49 pm
Given the following:
DENY SELECT,UPDATE,INSERT,DELETE,EXECUTE,REFERENCES ON SCHEMA::dbo TO User1
GRANT SELECT ON dbo.Table1 TO User1;
GRANT SELECT ON dbo.Table2 TO User1;
Shouldn't User1 be able to perform a SELECT statement against Table1 or Table2 (but not anything else)?
When I open up this user in SSMS, it does show on the "Securables" tab the ability to select from these tables... but logging in as User1 and performing a select gets the error:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Table1', database 'MyDB', schema 'dbo'.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 27, 2011 at 2:09 pm
I'm quite interested to see what others come up with, as I can't seem to find what I'm looking for in the newer BOL pages... in the 2k Deny page it says the following...
If the DENY statement is used to prevent a user from gaining a permission and the user is later added to a group or role with the permission granted, the user does not gain access to the permission.
-Luke.
January 27, 2011 at 2:09 pm
Doesn't a DENY overrule the SELECT permission?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2011 at 2:15 pm
Koen (da-zero) (1/27/2011)
Doesn't a DENY overrule the SELECT permission?
I always thought so unless the select was granted at a more granular level...
I.E. I DENY Select to userA on table1, but Grant SELECT to usera on table1 column b.
Same thing happens with NTFS permissions. A more local (to the object) READ/Write/Modify etc will override an inherited DENY. when they are specified from the same level the DENY wins. I.E. USer1 is in Group A and Group B. Group A is denied access to folder B, while Group B has modify permission. The user is denied access to Folder B.
January 27, 2011 at 2:35 pm
I can only find the SQL Server 2000 page also, but I think what you want to do here is instead of:
GRANT SELECT
you should
REVOKE SELECT
If I'm reading correctly, the REVOKE will revoke the explicit DENY from your previous statement.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
January 27, 2011 at 2:37 pm
If permissions are denied at the schema level then the permission check algorithm will fail and access not granted.
http://msdn.microsoft.com/en-us/library/ms191291.aspx
Specifically:
Fail the permission check if the required permission is directly or implicitly denied to any of the identities in the security context for the objects in the permission space.
Though the explicit permission of the column overrides the permission of the object, I don't think that applies to the schema. The security context contains server, database, schema and object.
Also:
http://msdn.microsoft.com/en-us/library/ms191465.aspx
Also the deny table v. column grant is an inconsistency in the permissions hierarchy.
http://msdn.microsoft.com/en-us/library/ms188338.aspx
In short, it is correct to still deny you access.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 27, 2011 at 2:38 pm
SQLDCH (1/27/2011)
I can only find the SQL Server 2000 page also, but I think what you want to do here is instead of:GRANT SELECT
you should
REVOKE SELECT
If I'm reading correctly, the REVOKE will revoke the explicit DENY from your previous statement.
You would have to revoke DENY. The cascading effect of permissions from schema to table still take precedence though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 27, 2011 at 2:43 pm
umm, don't want to argue, but GRANT/DENY/REVOKE are all mutually exclusive. The permission that gets revoked is what was GRANTed or DENYed. It took me a few reads to digest this, though.
http://msdn.microsoft.com/en-us/library/ms187719(v=SQL.100).aspx
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
January 27, 2011 at 3:10 pm
SQLDCH (1/27/2011)
umm, don't want to argue, but GRANT/DENY/REVOKE are all mutually exclusive. The permission that gets revoked is what was GRANTed or DENYed. It took me a few reads to digest this, though.http://msdn.microsoft.com/en-us/library/ms187719(v=SQL.100).aspx
Think of REVOKE as deleting a previously applied GRANT or DENY.
If you GRANT or DENY, a row is added to sys.database_permissions.
If you REVOKE, that row is deleted.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 27, 2011 at 3:16 pm
So, what this all comes down to is that if I want to have a user that can only select from a few of the tables in a database, I need to
GRANT SELECT for those tables,
and
DENY SELECT,UPDATE,INSERT,DELETE,REFERENCES for all other tables.
And if I add new tables, I need to remember to run this DENY against it.
(I think you'll can see where I was trying to simplify life at....)
I'd like to thank all of you'll for jumping in here and answering my questions. I do appreciate it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 27, 2011 at 3:38 pm
Just to clarify, the way that I thought security worked was that if you specified a security setting at a higher level (either GRANT or DENY), that it would apply to everything underneath it unless there was something underneath it that specified something different.
What I wasn't thinking about is the whole aspect of how a user can belong to multiple groups, each with their own permissions. Once you think about it that way, having the top level DENY overrule a lower GRANT makes sense.
Which makes what I'm trying to do a PITA. 🙁
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 27, 2011 at 11:50 pm
WayneS (1/27/2011)
Which makes what I'm trying to do a PITA. 🙁
Pita?
http://upload.wikimedia.org/wikipedia/commons/5/5f/Pita_topped_with_artichoke_hummus_and_lamb.jpg
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 12:44 am
WayneS (1/27/2011)
Just to clarify, the way that I thought security worked was that if you specified a security setting at a higher level (either GRANT or DENY), that it would apply to everything underneath it unless there was something underneath it that specified something different.What I wasn't thinking about is the whole aspect of how a user can belong to multiple groups, each with their own permissions. Once you think about it that way, having the top level DENY overrule a lower GRANT makes sense.
Which makes what I'm trying to do a PITA. 🙁
Isn't this a situation where user defined db-roles may come in handy ?
don't grant the default db-roles, but create your own.
Grant these roles the auth they need and add members to the roles.
Security is always a mess. Be sure to have your check procedures in place.
(easy way to check since SQL2005: execute as login .... )
btw One shouldn't use user definend objects in the dbo schema any more. That's a bad habit that's been implemented when a replication bug has been discovered in SQL2000 !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2011 at 4:34 am
ALZDBA (1/28/2011)
WayneS (1/27/2011)
Just to clarify, the way that I thought security worked was that if you specified a security setting at a higher level (either GRANT or DENY), that it would apply to everything underneath it unless there was something underneath it that specified something different.What I wasn't thinking about is the whole aspect of how a user can belong to multiple groups, each with their own permissions. Once you think about it that way, having the top level DENY overrule a lower GRANT makes sense.
Isn't this a situation where user defined db-roles may come in handy ?
Yes and no. The user defined db-roles won't help him if he has a denied SELECT on the schema. DENY still overrides everything.
On the other hand, having a user defined db-role with GRANT SELECT on some tables and DENY SELECT on the others is a good way of managing security for a group without having to do this for each user who needs access. So long as that other DENY doesn't exist, that is.
WayneS (1/27/2011)
Which makes what I'm trying to do a PITA. 🙁
I echo the question. PITA???
ALZDBA (1/28/2011)
btw One shouldn't use user definend objects in the dbo schema any more. That's a bad habit that's been implemented when a replication bug has been discovered in SQL2000 !
Bad habit? What bug?
Maybe I'm misunderstanding you, but are you saying having multiple schemas is a bad idea? If so, I disagree with this statement. The new schema structure is much better than the old ownership structure and makes security much easier to deal with IMHO. There are a few issues (such as the use of sp_helptext) that take a bit of getting used to, but it's been a good experience over at my workplace, and very handy for segregating things properly.
January 28, 2011 at 4:59 am
Brandie Tarvin (1/28/2011)
...ALZDBA (1/28/2011)
btw One shouldn't use user definend objects in the dbo schema any more. That's a bad habit that's been implemented when a replication bug has been discovered in SQL2000 !Bad habit? What bug?
In some of the sql2000 procs for replication the level user wasn't implemented correct and so it stuggeled with non dbo owned objects.
I have no ref to the bug and even don't know if they fixed it.
We just noticed it back in the days and moved the object to dbo for the replication to work.
(mix of dbo and non dbo published tables)
Maybe I'm misunderstanding you, but are you saying having multiple schemas is a bad idea? If so, I disagree with this statement. The new schema structure is much better than the old ownership structure and makes security much easier to deal with IMHO. There are a few issues (such as the use of sp_helptext) that take a bit of getting used to, but it's been a good experience over at my workplace, and very handy for segregating things properly.
No. What I wanted to state is one should by default create a schema and not create objects on the dbo schema.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply