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;
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';
GO
SELECT * 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';
GO
SELECT * FROM dbo.Employee;
GO
REVERT;
GO
The errors you should see:
Msg 230, Level 14, State 1, Line 2
The SELECT permission was denied on the column 'SSN' of the
object 'Employee", database 'MSSQLTips', schema 'dbo'.
Msg 230, Level 14, State 1, Line 2
The 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';
GO
SELECT 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.
I hope you all have enjoyed reading this article.
Comments are welcome....
