Security Pitfalls – Revoking Column Permissions

Introduction

Yesterday I wrote that the REVOKE statement can only be used to revoke the exact permission that was granted before. There is however one notable exception to this and it has to do with column permissions. But before we investigate that exception further, let's look at the standard behavior.

Mismatching REVOKE Example

For this example we are going to first GRANT the SELECT privilege on the dbo schema to TestUser1. As we have seen in Using SQL Server Schema Permissions to Simplify Permission Management, this will give the user SELECT access to all tables in that schema. If we now REVOKE the SELECT privilege on a particular table, SQL Server won't find a matching (identical) permission to revoke and the statement returns without causing any effect. In particular, TestUser1 still has read access to that very table:

This is the documented and therefore expected behavior of the REVOKE statement. It can only remove the exact permission that was granted before. Now let's look at a column permission example.

Revoking a "Permission Non-Granta"

If we GRANT SELECT on the id column of dbo.tst1 like this:

And if we then REVOKE the SELECT privilege on the table itself like this:

We expect TestUser1 to still have SELECT access to the id column as the two permissions involved did not match. To the contrary however, an access attempt by TestUser1 fails:

So, if you REVOKE access on a table, any prior column level grants of the same privilege on the same table (to the same database principal) are also revoked.

That begs the question whether this oddity works the same way in the other direction. To check, we are going to GRANT SELECT access on the table itself and then REVOKE that same access from one of the two columns:

And, as you can see, while TestUser1 was still able to access the id column, access to the column col1 was denied. To make sense of this special behavior, think of table level granting or revoking as being executed as a series of column level actions. While, as we have seen before, granting on a table and granting on all columns of that table is not the same, in this situation it helps to assume it is.

Under the covers, to make this special behavior work, SQL Server is utilizing a mechanism that is not used anywhere else in the permission management logic. So while this looks like an oversight, we can assume that this is indeed intentional. I will write about the details of how this is accomplished in a later post.

Summary

The REVOKE statement removes only permissions that have been granted exactly alike before. However, there is one exception. Column and table permissions interact in a different way; in fact they behave as if a table level grant or deny is always broken down into a series of column level grants or denies respectively.