Home > Uncategorized > SQL-Saturday #116 – Question of the day: When does a GRANT overrides a DENY in SQL Server?

SQL-Saturday #116 – Question of the day: When does a GRANT overrides a DENY in SQL Server?

SQL_SAT

 

SQLSaturday #116 – Bangalore

WoW!! What a great day it was to learn and connect with SQL guru’s and like minded folks who carry same passion for SQL server as I do. It’s quite an honor to meet and listen @blakhani, @pinaldave, @banerjeeamit, @vinodk_sql, @kashyapa , Rick @Joes2Pros……You guys are truly inspirational

Fellow attendees -  For those of you on Twitter, follow #sqlpass and make sure to check out the #sqlsat116 and #sqlsaturday hashtags to stay up to date

As tweeted by @vinodk_sqlRace to First 10 blog post on #SQLSat116. If you attended the event, we want to hear from you. Drop us a nudge :) …”

Here’s the first one to start with –

Fellow attendees – During the session on Security Pitfalls, Vinod gave us this interesting question to reply/tweet later.

Question: When GRANT overrides DENY in #sql server? Was asked during a session today in #sqlsaturday #sqlsat116

Answer: An explicit GRANT on a TABLE::COLUMN takes precedence on DENY TABLE #sqlsat116 #sqlsaturday

And here’s a simple test I did to get me answer

We needs 3 users/login (one being S.A.) 

–Here:

–UserOne is SA on SQL Instance

–UserTwo has got READ/WRITE privileges on TEST database

–UserThree is DBO (database_owner) for TESTDB

–Step1. Create a sample table say ‘iden_test’ with 2 cols (id,name)

USE [test]

GO

CREATE TABLE [dbo].[iden_test](

     [id] [int] IDENTITY(1,1) NOT NULL,

     [name] [varchar](10) NULL

)

–Step2. Run below T-SQL to DENY READ (SELECT) to UserTwo

DENY SELECT on OBJECT::dbo.iden_test TO UserTwo

GO

–Step 3. Try running below query and this should fail with permissions issues

SELECT id FROM dbo.iden_test

Go

Error: Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object ‘iden_test’, database ‘test’, schema ‘dbo’.

–Step 4. Login to server with UserThree (SA user) and run below T-SQL to GRANT

GRANT SELECT ON OBJECT::dbo.iden_test(id) TO UserTwo

Go

 –Step 5. Login again as UserTwo and run the SELECT (per Step 3) again

SELECT id FROM dbo.iden_test

Go

I can get the results for the specific column now.

Conclusion – A Grant on Table (Column) overrides the DENY on same object.

Varun as iVarund

About these ads
  1. January 13, 2012 at 10:05 am

    Varun, If i gave Deny on some object and again giving grant on pecuilar coloumn and if the user is able to access that coloumn then it’s breaking the Deny Rule that is already applied. Is this a feature of SQL Server OR kind of BUG?

  2. January 13, 2012 at 10:06 am

    January 13, 2012 at 10:05 am | #1 Reply | Quote Varun, If i gave Deny on some object and again giving grant on pecuilar coloumn and if the user is able to access that coloumn then it’s breaking the Deny Rule that is already applied. Is this a feature of SQL Server OR kind of BUG?

    • January 13, 2012 at 12:48 pm

      Hello Gaurav
      Good observation, when a DENY takes precedence over GRANT then why this behavior?
      At first glance I don’t know the answer (modest), and then I tried doing some search on BOL (mother of SQL documentation) and found below explanation

      “A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release
      So, an exceptional case.
      -Varun

  1. January 11, 2012 at 4:57 pm
  2. January 13, 2012 at 3:26 am

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: