Looking into DENY and GRANT security permissions April 13, 2012Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: DENY, GRANT, REVERT, REVOKE
In this post I would like to talk about DENY and GRANT security permissions. I will provide some examples that will demonstrate that DENY takes precedence over GRANT but there are some special cases.
We use DENY and GRANT T-Sql commands to give permissions on a securable (e.g table) to a principal (a custom user we have created).Most developers are familiar with their use but there are some special cases I would like to point out.
We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.
Launch SQL Server and in a new query window type the following
USE master GO CREATE DATABASE TestDB GO USE TestDB GO CREATE USER John WITHOUT LOGIN; GO CREATE TABLE Person (ID int, name VARCHAR(50), lastname VARCHAR(50)) GO INSERT INTO Person VALUES (1,'george','Harisson'), (2,'Jill','Diamond'), (3,'Jane','Littlewood') GO SELECT * FROM Person GRANT SELECT ON Person to PUBLIC DENY SELECT (lastname) ON Person TO john GO EXECUTE AS user='John' --this will fail SELECT * FROM Person GO --this will work SELECT ID, name FROM Person GO REVERT GO REVOKE SELECT (lastname) ON Person TO john
We create a sample database, a new user John and a new table, Person.Then we insert some values to the Person table.Then we deny (DENY) the SELECT operation for the lastname column of the Person table to user John.Then we change the user to John. Then we do a Select * from Person , which fails but the Select ID,name from Person operation succeeds for the user John.User John has no DENY permissions for the ID and name columns so the SELECT operation succeeds.Then we do a REVERT operation and REVOKE the SELECT lastname permission on table Person to user John.
Let’s have a look on a second example
In the same query window type
DENY SELECT ON Person TO john GRANT SELECT (name) ON Person TO john GO EXECUTE AS user='john' --this will fail SELECT * FROM Person --this will succeed SELECT name FROM Person REVERT USE master GO DROP DATABASE TestDB
We issue a DENY statement for a SELECT operation on the Person table for the John user.Then we issue a GRANT statement for a SELECT operation on the name column of the Person table.Then we change the user to John.Then we do a Select * from Person, which fails but the Select name from Person operation succeeds for the user John.So in this case we see that a GRANT statement takes precedence over DENY when it comes to column-level GRANT permissions.
Finally we issue a REVERT operation to revert to the dbo user and then we drop the TestDB database.
Hope it helps!!!