Filtering SQL Server Columns Using Column Level Permissions

標籤：

Problem

I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately so that not everyone can select the data? In a previous tip, Filtering Columns in SQL Server Using Views we looked at using Views. In this tip we cover how this can be done with column-level permissions.

Solution

One of the easiest ways to do this is through the use of views. However, if that isn‘t possible, there is another way: column-level permissions. They are a little harder to see and require a bit more diligence to keep track of, but they work just fine. So what‘s the difference between column level permissions and, say, table level permissions?

First, let‘s set up a table to use:

CREATE TABLE dbo.Employee (EmployeeID INT IDENTITY(1,1),FirstName VARCHAR(20) NOT NULL,MiddleName VARCHAR(20) NULL,SurName VARCHAR(20) NOT NULL,SSN CHAR(9) NOT NULL,Salary INT NOT NULL,CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID));

And we‘ll go ahead and load it up with a couple of entries for a proof of concept:

INSERT INTO dbo.Employee (FirstName, MiddleName, SurName, SSN, Salary)VALUES (‘John‘, ‘Mark‘, ‘Doe‘, ‘111223333‘, 50000);INSERT INTO dbo.Employee (FirstName, MiddleName, SurName, SSN, Salary)VALUES (‘Jane‘, ‘Eyre‘, ‘Doe‘, ‘222334444‘, 65000);

Let‘s go ahead and set up two users and two roles for this demonstration:

CREATE ROLE HR_Employee;GO CREATE ROLE HR_Intern;GO CREATE USER SalaryPerson WITHOUT LOGIN;GO EXEC sp_addrolemember @membername = ‘SalaryPerson‘, @rolename = ‘HR_Employee‘;GO CREATE USER SummerIntern WITHOUT LOGIN;GO EXEC sp_addrolemember @membername = ‘SummerIntern‘, @rolename = ‘HR_Intern‘;GO 

This sets up two levels of users: HR Employees (role HR_Employee, of which SalaryPerson is one) and HR Interns (role HR_Intern, played by SummerIntern). Now, when we normally grant permissions, we do so against the whole object or schema. For instance, this grants SELECT permission against the dbo.Employee table to HR_Employee role members:

GRANT SELECT ON dbo.Employee TO HR_Employee;

Now, we don‘t want interns to have this level of permissions. We only want them to have access to specific columns. There‘s a way to do this. Immediately after the table name, we can specify the columns we want to grant permission to (or DENY, if we needed to do that) within a set of parentheses, like so:

GRANT SELECT ON dbo.Employee (EmployeeID, FirstName, MiddleName, SurName) TO HR_Intern;

Now, if you prefer the GUI, you can do and see the same thing in SQL Server Management Studio, it just takes a little closer eye. Note the difference between Figure 1 (a checkbox, signifying complete permissions against the table) and Figure 2 (a green square, indicating that there are some permissions, but we‘ll have to take a closer look).

(Figure 1)

(Figure 2)

If we click on the Select row, the button for Column Permissions activates. Clicking on that shows that we do indeed have permissions at the column level. Note there is no checkbox beside SSN nor Salary (Figure 3).

(Figure 3)

Therefore, the HR_Intern role cannot query these columns. They can find out that they are there, but they can‘t retrieve data. If you want to see these permissions in action, execute the following snippets. This should work just fine, because HR_Employees can SELECT against the whole table:

EXECUTE AS USER = ‘SalaryPerson‘;GOSELECT * FROM dbo.Employee;GO REVERT;GO

This will fail with a couple of access denied errors, listing the columns the user cannot access:

EXECUTE AS USER = ‘SummerIntern‘;GOSELECT * FROM dbo.Employee;GO REVERT;GO

The errors you should see:

Msg 230, Level 14, State 1, Line 2The SELECT permission was denied on the column ‘SSN‘ of the object ‘Employee", database ‘MSSQLTips‘, schema ‘dbo‘.Msg 230, Level 14, State 1, Line 2The SELECT permission was denied on the column ‘Salary‘ of the object ‘Employee", database ‘MSSQLTips‘, schema ‘dbo‘.

This will work, because the columns in the query are accessible to HR_Intern:

EXECUTE AS USER = ‘SummerIntern‘;GOSELECT EmployeeID, FirstName, SurName FROM dbo.Employee;GO REVERT;GO

And that‘s how to restrict using column permissions. Incidentally, you can do the same for DENY. Therefore, if a group of users already have access to columns they shouldn‘t, and you can‘t rework security in this manner, you could use DENY if you had to, like so:

DENY SELECT ON dbo.Employee (SSN, Salary) TO HR_Intern;

Since DENY trumps any other permissions, this will effectively block access to those columns. This should be used as a last resort, obviously, because the use of DENY is not intuitive. And DENY at the column level is another step removed from what we‘re used to when looking at permissions.

Filtering SQL Server Columns Using Column Level Permissions