Home » Blog » How to use Column Level Permissions for Fine-Grained Permission Management

How to use Column Level Permissions for Fine-Grained Permission Management

2014-01-27 - General, Security

Introduction

The GRANT statement does not only allow you to grant permissions on an entire object. For tables and views you can also grant permissions for a single column or a subset of the columns.

Granting Column Level Permissions

To grant column level permissions you just need to specify the column list in the GRANT statement. The list can be specified together with the table or view that you are granting the permission on:

[sql] GRANT SELECT ON OBJECT::dbo.tst(id, col1) TO TestUser1;
[/sql]

This is pretty intuitive; we are granting a permission not on the complete table, but only on the columns specified.
However, the column list can also be specified in the section of the grant statement that specifies the privilege to be granted itself:

[sql] GRANT SELECT(id, col1) ON OBJECT::dbo.tst TO TestUser1;
[/sql]

As before, the keyword SELECT specifies the permission type. Directly behind the permission type instead of after the object name follows the comma separated list of columns in parenthesis. The rest of the GRANT statement is unchanged. Executing either statement grants the permission we expect:

The access to the specified columns has been granted successfully. Let's see what happens if TestUser1 tries to overstep the boundaries:

The error message returned is very expressive and tells us in detail which column we don't have access to. In fact, if you are trying to access multiple columns that you do not have access to, you will get a separate error message for each of them.

Column Level Privileges

There are three privileges that allow for column level granularity:

SELECT
UPDATE
REFERENCES

All other object privileges can only be used at the entity level. If you try to use the column syntax with one of them, SQL Server will return this error:

Additive Permissions

Like with any other permission, the use of the GRANT statement will never take permissions away. That is quite intuitive with entity-level permissions. However, with column level permissions it might seem a little odd at first.

Before we used this GRANT statement to give TestUser1 access to two of the three columns in the dbo.tst table:

[sql] GRANT SELECT(id, col1) ON OBJECT::dbo.tst TO TestUser1;
[/sql]

Now let's execute the same statement, but for the columns col1 and secret1:

[sql] GRANT SELECT(col1, secret1) ON OBJECT::dbo.tst TO TestUser1;
[/sql]

This does not mean as you might think that TestUser1 can now only access those two columns. Instead every additional GRANT just adds the columns that are new to the list of accessible columns. That means in this case that Testuser1 now has access to all three columns in the table:

Other GRANT usages

Over the last few days we discussed two advanced forms of the GRANT statement, both allowing delegation of permission management: GRANT … WITH GRANT OPTION and GRANT ... AS. Both are fully supported for column level permissions too.

Summary

There are three privileges that can be granted with column level granularity: SELECT, UPDATE and REFERENCES. To grant column level permissions the list of columns is specified in one of two places in the GRANT statement. All syntax forms of the GRANT statement support column level granularity.