Security Pitfalls – Column Level GRANT overrides Table Level DENY

Introduction

A few days ago we talked about Hierarchies of Securables. There we saw that a DENY on one level always wins over a GRANT on another level. Well, as they say, there is no rule without exception.

The Special Column Level Grant

As we have seen before, for example in my post about revoking of column permissions, column permissions are at times a little odd. In this post I would like to show you another one of those oddities. For that I first need a principal and a securable:

Before we get started however, let's recap what the expected behavior is. When a privilege is granted at the table level but denied at the schema level we expect, because we know that a DENY always wins, that access to the table will be denied. Let's confirm:

As expected, TestUser1 cannot SELECT from the table. Only after the DENY has been explicitly revoked can the table be accessed:

Now let's see what happens if we DENY access to the table, but GRANT it to a column:

The access attempt is successful. Even though access was denied at a different hierarchy level, and even though a DENY should always trump a GRANT, TestUser1 was able to SELECT from the table.

The documentation says about this: "A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility."

While in other cases such an inconsistency is usually marked deprecated, it seems Microsoft is reluctant to changing this behavior. So we have to live with the fact that a table level DENY cannot be used to override a column level GRANT.

However, a table level DENY and a column level GRANT is the only coupling that fails to obey the rules. For example a schema level DENY is stronger that a column level GRANT:

Summary

For reasons of backward compatibility, in SQL Server a column level GRANT is stronger than a table level DENY. This is a unique exception to the rule that a deny on any level of the hierarchy of securables always overrides a GRANT on another level.