DENY vs. REVOKE – Part 5 – Durability

Introduction

When durability is mentioned in the context of a relational database management system it usually refers to one of the ACID properties. However, in this post it means something different.

Over the last few days we have seen a few cases where a DENY had a different effect than a REVOKE. All had to do with hierarchies. But there is something more fundamental that is different between the two. It is how SQL Server handles the request internally.

Permission Durability

When a GRANT in executed, SQL Server creates a new entry in its internal permissions list. That entry there won't get removed until it gets either revoked or overwritten.

When a REVOKE is executed, SQL Server finds the matching entry in the internal permissions list and removes it. That's why it works only on permissions that have been granted exactly the same way the REVOKE statement is written. If it was granted on a different securable, a different principal or with a different privilege, SQL Server will not be able to find the entry at the time the REVOKE is executed and therefore won't remove it.

The DENY statement is different. We have seen examples of that over the last few days. It can affect prior grants without the need to have an exact match. Under the covers that is achieved not by altering the existing granted permissions, but instead by recording the DENY in the same permissions list.

Every time SQL Server needs to find out if a user (or login) has the permission to execute a given statement, it looks at that list and gathers all relevant denies. If it finds a matching one, the statement is terminated right away with a "permission denied" error. Remember, matching might mean that the deny was made against a different level of one of the security related hierarchies.

Only if no matching deny was found, the grants are checked. If a matching grant is found is statement is executed. Otherwise the same "permission denied" error is returned.

Durable DENY Example

The easiest way to see that a DENY is indeed durable, is to execute it first, before any GRANT on that securable hierarchy was executed. To see that in action let's create a new table in a new schema and also create or usual user, TestUser1:

Now, before anything else let's execute a DENY SELECT on the schema. Even if that is directly followed by a GRANT SELECT one the table, access will still be denied:

Now, the durability goes only so far. If you execute a DENY and then a matching GRANT for the same permission (on the same securable for the same privilege to the same principal), the DENY gets removed from the permissions list and replaced with the GRANT. That is the reason why the example in the first part of this series about DENY vs. REVOKE which executed alternating GRANT and DENY statements showed the behavior it did.

Summary

Durability in the context of permissions, particular the DENY statement, means that a denied permission actually gets an entry in SQL Server's internal permissions list. Therefore it does not matter if the DENY was executed before or after the GRANT if they happened on different hierarchy levels. However, they do overwrite each other if executed for the same permission.