Random Blog Challenge: Grant (Fritchey) Permissions to Revoke Permissions.

Challenge Accepted:

Grant Fritchey ( b | t ), in his infinite wisdom, has created a challenge; a random blog post challenge all to do with the featured image at the top of this post:

Actually, from his blog post, his actual reason was…

for no particular reason at all or maybe because I had too much food at lunch or just because Jason Hall gave me the idea

Now to me, that is a cheeky response, which is fitting because this seems like a cheeky gif… as in it is saying “oh hey!” in response to someone asking “Who did this?!”.

So taking this into account, I’ve decided to document some cheeky behaviour with permissions in SQL Server.

Setting Ourselves Up for Cheekiness:

Let us create a super secret table, with a super secret column containing a super secret value that only we should be able to update.

USE Pantheon;
GO

CREATE TABLE dbo.SuperSecretContent
(
ID int IDENTITY(1, 1)
,SecretColumn varchar(50)
);
GO

INSERT INTO dbo.SuperSecretContent
(SecretColumn)
SELECT RIGHT(NEWID(), 50);
GO

Yeah…I know, my super secret value is a NEWID()…I’m not sorry.

Enter Stage Left:

We also have a random LOGIN and USER that we didn’t create but we somehow have the create scripts for…

USE [master];
GO
CREATE LOGIN [PhysicianWhom] WITH PASSWORD = 'A_Rapid_Hedgehogs_Wrench';
GO

USE [Pantheon];
GO
CREATE USER [PhysicianWhom] FROM LOGIN PhysicianWhom;
GO

Fade To:

One day we check out our super secret value and we see:

SELECT SecretColumn
FROM dbo.SuperSecretContent;
GO

Dammit! He must have access to db_datawriter so he can update all user tables. I’ll do a quick check just to make sure!

SELECT Users.[name] AS UserName
, DBRoles.[name] AS RoleName
FROM sys.database_principals AS Users
INNER JOIN sys.database_role_members AS RoleMembers
ON Users.principal_id = RoleMembers.member_principal_id
INNER JOIN sys.database_principals AS DBRoles
ON DBRoles.principal_id = RoleMembers.role_principal_id
WHERE Users.[name] = N'PhysicianWhom';
GO

Ha! I know how you did it now. You’re not going to be in that database role for long! If he needs update access to other tables then we can grant UPDATE permissions on those tables individually.

Let’s remove him and then refresh our table.

-- Role
ALTER ROLE db_datawriter DROP MEMBER PhysicianWhom;
GO

TRUNCATE TABLE dbo.SuperSecretContent;
GO

INSERT INTO dbo.SuperSecretContent
(SecretColumn)
SELECT RIGHT(NEWID(), 50);
GO

Happily Ever After?

Time passes and we check our value again:

Arrgh!!! How? Wait, do you have UPDATE permissions on my table?

SELECT Users.[name] AS UserName
, Perms.[permission_name] AS PermissionGranted
, Perms.class_desc AS PermissionOn
, Obj.[name] AS ObjectName
, Perms.state_desc AS PermissionState
FROM sys.database_principals AS Users
INNER JOIN sys.database_permissions AS Perms
ON Perms.grantee_principal_id = Users.principal_id
INNER JOIN sys.objects AS Obj
ON Obj.[object_id] = Perms.major_id
WHERE Users.[name] = N'PhysicianWhom';
GO

This is no one’s fault but my own. Removing them from the db_datawriter role was a shotgun approach, I need to be more precise. So I’m going to DENY him update access to my table and then the problem will be over.

DENY UPDATE ON dbo.SuperSecretContent TO PhysicianWhom;
GO

TRUNCATE TABLE dbo.SuperSecretContent;
GO

INSERT INTO dbo.SuperSecretContent
 (SecretColumn)
SELECT RIGHT(NEWID(), 50);
GO

Paranoia Kicks In:

He can’t have updated it…right?

I mean, he doesn’t have db_datawriter permission; I’ve denied him update permission on my table. I’m safe!…right?

[It’s at this point I went and got a cup of coffee..]

But I denied him! I denied him!

No! He will not win! I am determined…but lost? In my desperation, I scour Books Online and chance upon this little nugget from BOL…

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.

…and a small ray of light shines through.

I make a quick modification to my permissions script…

SELECT Users.[name] AS UserName
, Perms.[permission_name] AS PermissionGranted
, Perms.class_desc AS PermissionOn
, Obj.[name] AS ObjectName
, Perms.state_desc AS PermissionState
, ( SELECT [name]
 FROM sys.columns AS c
 WHERE c.[object_id] = Obj.[object_id]
 AND c.column_id = Perms.minor_id) AS ColumnPermission
FROM sys.database_principals AS Users
INNER JOIN sys.database_permissions AS Perms
ON Perms.grantee_principal_id = Users.principal_id
INNER JOIN sys.objects AS Obj
ON Obj.[object_id] = Perms.major_id
WHERE Users.[name] = N'PhysicianWhom';
GO

Righting A Terrible Wrong

How do I fix this?

A deny is not going to work; we’ve already tried that.

Adding him to db_denydatawriter is not going to work. What happens if he needs to update other tables in our database?

But if we GRANT him access on our entire table then that will overwrite our column level GRANT. Then we can DENY him on our table and all is write…I mean right with the world!

GRANT UPDATE ON OBJECT::dbo.SuperSecretContent TO [PhysicianWhom]
DENY UPDATE ON dbo.SuperSecretContent TO PhysicianWhom;
GO

EXECUTE AS LOGIN = 'PhysicianWhom';

SELECT USER_NAME() AS WhoooAreYou;

UPDATE dbo.SuperSecretContent
SET SecretColumn = 'Are you listening to me?'
WHERE 1 = 1;
GO

SELECT SecretColumn AS UpdatedTo
FROM dbo.SuperSecretContent;
GO

REVERT;
GO

A Little Parting Gift:

A little present for all you “deprecated means nothing” people out there.

I’m just going to highlight a section here of relevance:

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.

I aimed to get this blog post out yesterday but when I ran this on…

Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )

…then my table-level DENY DID take precedence over my column-level GRANT.

When did they introduce that? Was this mentioned and I just missed it? Seriously give it a go and see for yourself!

A Little Parting Gif: