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!
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.
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.
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
Yes a DENY is not the reversal of a GRANT and vice-versa - that's what REVOKE does.
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.
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/
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.
11 People are following this question.
