DENY vs. REVOKE – Part 4 – Hierarchies of Privileges

Introduction

Over the last few days we have looked at the difference between DENY and REVOKE, particularly in the context of hierarchies. So far we looked at hierarchies of security principals and hierarchies of securables. There is one more hierarchy that I would like to cover: The hierarchy of permissions.

Permission Hierarchy Example

If you look up a specific privilege in Books Online, for example under Object Permissions, you will see next to each specific privilege another privilege that implies the former. Most object privileges are only implied by the CONTROL object privilege, but there are others.

As with the other hierarchies that we looked at, this hierarchy can be used to fine tune your permission management. Let's look at a simple example. First we need to create a user and a table:

Now we GRANT CONTROL on that table to our user:

As you can see, the CONTROL privilege implies the SELECT privilege.

Following the same pattern that we have used with the other two hierarchies, let's try to REVOKE SELECT on that table form TestUser1:

We had never granted SELECT to TestUser1 so we expected the REVOKE to not have any effect on the existing access permissions. The above confirms that we assumed correctly.
Now let's see if we can use DENY to remove the SELECT privilege:

That worked; the DENY SELECT successfully prevented read access on the table in spite of the previously granted CONTROL privilege.

As with all other hierarchies we looked at before, a DENY anywhere in the hierarchy overrides any GRANT on a different level.

Just to clarify however, the DENY SELECT does only affect attempts to read data. Other privileges that are implied by CONTROL are not affected. So, TestUser1 is for example still able to execute an INSERT statement against that table:

While a DENY always overrides a GRANT if executed on different levels of a security hierarchy, the DENY will only affect operations that require either the specific privilege that was denied or any privilege that is implied by it.

Summary

Permission level hierarchies build another security related hierarchy. As with other hierarchies we looked at, a DENY on any level of this type of hierarchy will override any number of grants on other levels.

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.