question

jhowe avatar image
jhowe asked

sql security

hi guys just a quick overview i cannot seem to find a brief overview of the following for SQL server 2008 R2. What is the difference between GRANT WITH GRANT DENY I am a member of the sysadmin role so i have access to everything. If i want to give a new user permissions to a table that is not in a server role (like sysadmin) do i HAVE to specifically give GRANT access or DENY if i don't want them to have access? I don't get it because if i don't give GRANT then surely they are already denied? Doesn't make sense to me. Either give them access or not, 1 or 0 i don't understand the need for GRANT and DENY. If i don't GRANT or DENY are they DENIED by default? Hope someone can clear this up!
sql-server-2008sql-server-2008-r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
GRANT specifically allocates permissions to access an object WITH GRANT further allows those you've granted permissions to grant permissions to others. You probably don't want to do this. DENY specifically forbids access to an object. This is not the same as REVOKE - which removes any previous GRANT. If you haven't specifically GRANTed access to an individual, then the system will look at any roles and user groups of which the user is a member to get the permissions appropriate to that user.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
Ok what about this example. I have a business analyst that needs access to a customer table, there is a secure column i.e. card number that i want to deny access. I give his windows group DENY on the card number column. I want him to have access to all other columns. Does that mean i have to give to tick GRANT for all other columns or does he have access to all other columns by default because i have not ticked DENY? Do you see what i'm getting at i don't understand why there is GRANT AND DENY. Surely it should be either ACCESS 1 or 0.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
and to add to @ThomasRushton's great answer - a DENY takes precedence over other permissions. So yes you are right, if you haven't granted access, then the user does not have access, but if you DENY, and then later on GRANT, without revoking the DENY, they will still be denied. Don't think of the security setting as 1 or 0, it's more like -1, 0, 1
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Yes a DENY is not the reversal of a GRANT and vice-versa - that's what REVOKE does.
4 Likes 4 ·
jhowe avatar image jhowe commented ·
i see so a DENY is like an extra layer of security. a GRANT will still not work unless you REVOKE the DENY. is that correct?
0 Likes 0 ·
jhowe avatar image jhowe commented ·
Thanks very much for clearing that up!
0 Likes 0 ·
jhowe avatar image jhowe commented ·
so just to clarify... to GRANT access to a table that has a deny select i need to run the following : REVOKE SELECT ON dbo.Customer TO [DOMAIN\\User] GRANT SELECT ON dbo.Customer TO [DOMAIN\\User]
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
To add to ThomasRushton's and Kev's excellent answers, the server does not even check the permissions for someone that is in the sysadmin fixed role or, for the database, the dbo role. This means that while Deny takes precedence over grant, a sysadmin will not be affected by the deny.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
There are two options. The most conceptually direct option is to use column level permissions. There is a good article on this at: http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/ Personally, I think column level permissions add unnecessary complexity in practice and recommend that no one ever use them. A less conceptually direct, but generally simpler, option is to create a view and then only grant permissions on the view. There's an article for that at: http://www.mssqltips.com/sqlservertip/2125/filtering-columns-in-sql-server-using-views/
2 Likes 2 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Can you elaborate on the problems? It should be make sure he has the permissions granted and make sure he isn't inheriting a Deny from somewhere. Also make sure there isn't a trigger that is doing something funny to block him.
1 Like 1 ·
jhowe avatar image jhowe commented ·
Hi Tim, yea thanks for that i kind of knew that already. The user that i'm trying to give access to this table seems to still be having trouble. Even though i've run those statements above. Is there anything else i need to do?
0 Likes 0 ·
jhowe avatar image jhowe commented ·
hi Tim what would i do in the situation where for one table i want a user to have access to select only half of the columns?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.