Home » Blog » Is Granting on All Columns Really the Same as Granting on the Table Itself?

Is Granting on All Columns Really the Same as Granting on the Table Itself?

2014-01-30 - General, Security, Security Misconceptions

Introduction

Granting a privilege on the entire table and granting that same privilege on all columns of that same table result in the same actions being executable by the grantee. So, at first glance it looks like as if the two actions lead to the same result. However there is one important difference.

Comparing All-Column-Grants with Table-Grants

To see what the difference is we need to first do some setup work:

[sql] CREATE TABLE dbo.tst1(id INT, col1 INT);
INSERT INTO dbo.tst1 VALUES(42, 1);

CREATE TABLE dbo.tst2(id INT, col1 INT);
INSERT INTO dbo.tst2 VALUES(42, 2);

GRANT SELECT ON dbo.tst1 TO TestUser1;
GRANT SELECT ON dbo.tst2(id, col1) TO TestUser1;
[/sql]

This creates two identical tables dbo.tst1 and dbo.tst2 and grants SELECT permission to TestUser1. For dbo.tst1 the permission is granted on the object level. For dbo.tst2 the permission is granted on all columns.

First let's see that the same permissions are now in effect:

TestUser1 can successfully select all columns from both tables.

See The Difference

So far both methods did yield the same result and TestUser1 has the same level of access to both tables. So where is the big difference?

The difference lies in how SQL Server treats new columns. Let's see that in action. First we need to add a new column to each of the two tables:

[sql] ALTER TABLE dbo.tst1 ADD new_column INT NOT NULL DEFAULT 13;
ALTER TABLE dbo.tst2 ADD new_column INT NOT NULL DEFAULT 13;
[/sql]

With that new column in place, let's run the same two select statements that we used above again:

While the SELECT against dbo.tst1 executes successfully, we are not as lucky with dbo.tbl2. For the latter an error is returned telling us that the new column is inaccessible.

Summary

If a privilege is granted on the table level, all new columns that are added to the table at a later time are automatically covered by that permission. However, if that same privilege was granted on all columns of the table, new columns are excluded from those permissions.

This can be used to control if access to new columns should be automatic or not. If you rather review each new column on a case by case basis, use column level permissions only. If you on the other hand like the idea of automatically extending permissions to new columns, use object level permissions instead.