DENY vs. REVOKE – Part 3 – Hierarchies of Securables

Introduction

In this little series we are comparing the DENY statement with the REVOKE statement. Yesterday we looked at database and server roles and how the DENY statement can help fine-tune your role based security management.

Today I would like to look at a different kind of hierarchy, the hierarchy of objects or securables.

Object Hierarchy Example

In SQL Server every object is part of a schema that in turn lives in a database which resides on the SQL Server instance. This provides a natural hierarchy that we can use for permission management. The example today is going to look at this type of database object hierarchy. However, there are other securable hierarchies and the principle discussed today works for those too.

To try out permission management in the context of an object hierarchy, we are going to first create a schema and a table within that schema (and also our usual user):

Now we are going to GRANT SELECT on the table to TestUser1 and check that the table is now readable for this user:

So far we used just the GRANT statement in its simplest form, and as you can see, there were no surprises.

If you followed along over the last few days, the next test might seem obvious to you, but for completeness I am going to include it here anyway. In this test we are going to use REVOKE to revoke SELECT on the schema from TestUser1. However, as the permission was originally granted on the table and not on the schema, we expect the REVOKE to have no effect on TestUser1's ability to access the table.

As expected, the REVOKE did not do anything.

Now it is time to see if the DENY changes something:

It did. Because we denied access to the schema, TestUser1 was not able to select from the table anymore, even though we had explicitly granted access to that table before.

As with hierarchies of security principals, a DENY anywhere in a hierarchy of securables overrules any number of grants on other levels in that hierarchy.

The example above you are not likely to encounter in the real world. The more common use-case is to grant a principal access to a schema but exclude that one important table. To achieve that you would execute GRANT ... ON SCHEMA::... and then DENY ... ON OBJECT::... . Either way, it is not important if the DENY happened on a higher or lower level in the hierarchy than the GRANT. The DENY always overrules the GRANT.

Summary

Similar to hierarchies of security principals, a DENY anywhere in a hierarchy of securables like table, schema, database and server, overrides a GRANT in other places of that hierarchy.

DENY vs. REVOKE Series

This post is part of a five-part series comparing the DENY and the REVOKE statements.
Below is a list of links to the posts that are already available.