Hiding one or more columns

Leave a comment

November 12, 2015 by Kenneth Fisher

This isn’t something you have to do frequently, but sometimes you don’t want the users to have access to certain columns in a table. For example let’s say you have a salary column in your employee table that you don’t want everyone seeing.

There are two fairly simple options.

CREATE TABLE Employee (
	EmployeeId int NOT NULL IDENTITY (1,1),
	Name varchar(255),
	Address1 varchar(255),
	Address2 varchar(255),
	City varchar(255),
	State varchar(255),
	Zip varchar(50),
	Salary money
);

 

Column level security

Otherwise known as the hard way. Here you grant permissions to just the columns of the table that you want someone to have access to.

Open the permissions tab for the user and add the object you are interested in. Check Grant for the SELECT permission. Then hit the COLUMN PERMISSIONS button.

ColumPerms1

Select the column level permissions we are interested in. Note we did not check Grant for the Salary column.

ColumPerms2

Now instead of a check in the box there’s a square. This tells us that the permissions are not uniform across all of the columns.

ColumPerms3

In code:

GRANT SELECT ON [dbo].[Employee] ([EmployeeId]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Name]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Address1]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Address2]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([City]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([State]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Zip]) TO [Doctor];

So why is this a problem? Well here is a simple example. I log in as Doctor and I run the following query:

SELECT * FROM Employee

And I get this error:

Msg 230, Level 14, State 1, Line 13
The SELECT permission was denied on the column ‘Salary’ of the object ‘Employee’, database ‘Test’, schema ‘dbo’.

We can avoid the error if we just query the specific columns (which is what we should be doing anyway) but a lot of code is libel to break. I also want to point out that not only did this cause an error but it also let the user know that a Salary column even existed.

Which leaves us with:

Access through views

Otherwise known as the easy way.

We create a view that doesn’t include the column(s) we don’t want them to see.

-- Cleanup code (drop the table & re-create 
-- it to get rid of existing permissions)
IF OBJECT_ID('Employee') > 0 
	DROP TABLE Employee;
GO
CREATE TABLE Employee (
	EmployeeId int NOT NULL IDENTITY (1,1),
	Name varchar(255),
	Address1 varchar(255),
	Address2 varchar(255),
	City varchar(255),
	State varchar(255),
	Zip varchar(50),
	Salary money
);
GO
CREATE VIEW EmployeeList AS
SELECT 
	EmployeeId, Name, Address1, Address2,
	City, State, Zip
FROM Employee;

GRANT SELECT access to the view.

GRANT SELECT ON [dbo].[EmployeeList] TO [Doctor];

Now if Doctor tries to query against the Employee table they get a standard The SELECT permission was denied on the object ‘Employee’ error with no mention of any of the columns. But if they query the view EmployeeList they get the data we want them to have. And as a bonus both SELECT * or SELECT columnlist will work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: