A couple of days back I wrote the blog on Msg 230 and thought it was one of the simplest implementations of access control on the system. To continue with the code in this blog, please make sure you read the same first before getting into the examples here.
In that blog, I wrote a statement where I said the DENY takes precedence over GRANT. Post this, my good friend Vinod Kumar had pinged me to say that this might not be the case always. Now that statement got me thinking harder and I was quick to ask, prove me wrong? More than a challenge, it was a great opportunity to learn some of these finer details that lets us learn and understand SQL Server better. I have always been of the opinion that the best way to learn something is by sharing. So here is the repro to the exception where the DENY at a higher level is overridden by a GRANT at a granular level.
Similar to our previous post, let us go ahead and create our samples database, create a user inside this database, our secure table with some data.
CREATE DATABASE PermissionsDB
GO
USE PermissionsDB
GO
CREATE USER Pinal WITHOUT LOGIN;
GO
CREATE TABLE SecureTbl (ID INT, Name VARCHAR(50), SSN VARCHAR(20))
GO
INSERT INTO SecureTbl VALUES (1, 'Pinal', '111-22-3333')
GO
If we try to access the table using the login of “Pinal” at this point in time without any explicit permission given to the user. We will get the following error:
Msg 229, Level 14, State 5, Line 38 The SELECT permission was denied on the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.
Now that we have our secure table ready, let us go ahead and DENY explicit rights on the table to user ‘Pinal’. Next, we will go ahead and GRANT at a specific column level – in the command below. In this example, we have gone ahead to give rights on the column “ID” alone.
-- Does a GRANT at column-level override a DENY at the object level
DENY SELECT ON SecureTbl TO Pinal
GRANT SELECT (ID) ON SecureTbl TO Pinal
GO
Let us next change the user context to “Pinal” and execute the Select command.
EXECUTE AS USER='Pinal'
SELECT * FROM SecureTbl
SELECT ID FROM SecureTbl
REVERT
Though we have DENY permissions at the Object level (table in this instance), the GRANT seems to be working on the specific column level though.
Msg 230, Level 14, State 1, Line 38
The SELECT permission was denied on the column 'Name' of the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.
Msg 230, Level 14, State 1, Line 38
The SELECT permission was denied on the column 'SSN' of the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.If you want to GRANT access to multiple columns, we can do it using the command as shown below.
GRANT SELECT (ID, Name) ON SecureTbl TO Pinal
GO
I am always of the opinion that it is better to DENY across the board and then give specific access sometimes. If you might notice, when compared to the previous article – we have NOT GRANTED SELECT at the global level for all here. We were explicit in the DENY logic for instance.
-- Explicitly grant at object level
GRANT SELECT ON SecureTbl TO PUBLIC
Let us make sure there are no unnecessary databases in our server. Here is the cleanup script.
-- Clean up
USE MASTER
GO
DROP DATABASE PermissionsDB
I hope you also learnt something new today. This is a great way to learn the fine prints when it comes to working with databases and permissions. Please let me know if you have ever used this technique in your environments and what was the actual scenario?
Reference: Pinal Dave (https://blog.sqlauthority.com)
