mssqltips logo

Filtering Columns in SQL Server Using Views

By:   |   Updated: 2010-09-21   |   Comments   |   Related: More > Security

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?

Solution

One of the easiest ways to do this is through the use of views, however, there are two requirements for this:

  1. The users do not already have the ability to select data from the table.
  2. A view can be created where an ownership chain can be established with the table.

If you're not familiar with ownership chaining, you can read more about it in this previous tip.

If the users don't already have access to the table, we can grant SELECT permissions against the view and ensure the view only has the columns we want the users to query. For instance, consider the following sample table:

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);

Now let's say that we have a certain subset of users who should never see the salary amount and social security number for employees. We can create a view which only includes the columns which we want to display. For instance:

CREATE VIEW dbo.FilteredEmployee
AS
SELECT EmployeeID, FirstName, MiddleName, SurName
FROM dbo.Employee;

A SELECT * from this view shows the columns which are available:

we can grant SELECT permissions against the view and ensure the view only has the columns we want the users to query

If we try to force a SELECT against columns which aren't included in the view, we'll get an error. For instance, this SELECT statement will fail:

SELECT EmployeeID, Salary
FROM dbo.FilteredEmployee;

Therefore, if we can use a view, we can successfully filter the data and grant the users access to the view, we can present just the data we want the users to have access to.

Next Steps
  • Views can also be used used for INSERTs, UPDATEs and DELETEs, but it would be better to have users use stored procedures for data maniuplation
  • Once the view is created you would just GRANT select rights to the user or the database role that you setup. This can be done using the GRANT command or using SQL Server Management Studio.


Last Updated: 2010-09-21


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools