jump to navigation

Looking into DENY and GRANT security permissions April 13, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , , ,
trackback

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!!!

Comments»

1. Dot Net Rules : Looking into DENY and GRANT security permissions - April 14, 2012

[…] developers are familiar with their use but there are some special cases I would like to point out. (read more) Share Posted: Σάββατο, 14 Απριλίου 2012 1:47 μμ από το μέλος […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: