Understanding the Views scope (i) - Limiting the access to data

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Published:
Updated:
It can be said that a View is a virtual table defined by a query and that it also has columns and rows. There are several occasions to use a View, but in this article I will focus only in the security mechanism that is one of the principal reasons for using a View.

One of the purposes for creating a View is to allow users to access data without granting them permissions to the base tables. Imagine your company's Human Resource database. Every employee has access to his data, but how can  you ensure that each employee can only see information related to him?

For example, if you give read permission to the Salary table:
GRANT SELECT ON Salary TO [MyDomain\Employees]

Open in new window

MyDomain\Employees represents an AD group with all Employees users

Everyone now can see his own information by running a simple SELECT command:
SELECT *
FROM Salary
WHERE EmployeeID = USER_NAME()

Open in new window

Let's keep things simple so for this company the EmployeeID is the user's LogonID, so we can match the AD with the table primary key.

But they can also see any colleague's information by replacing the USER_NAME() with the corresponding colleague's ID... or even worse, they can see the salaries of all employees with a simple SELECT * FROM Salary command.

To avoid these situations we can create a View and give permissions only to that View instead of the Table. Below is the View definition:
CREATE VIEW EmployeeSalary AS
SELECT *
FROM Salary
WHERE EmployeeID = USER_NAME()

Open in new window


Now let's revoke the permissions that we gave to the Salary Table and give permission only for the View:
REVOKE SELECT ON Salary TO [MyDomain\Employees]
GO

GRANT SELECT ON EmployeeSalary TO [MyDomain\Employees]
GO

Open in new window


The user will have only access to the View and can't even see the Tables or other Views that may exists in the database.
View.JPGThat can be confirmed if the user tries to run a SELECT statement over the Salary Table. Will raise the following error:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'Salary', database 'EE_DB', schema 'dbo'.


Each employee now can only have access to the information that respects to him and none else.

If you liked this article, I wrote two more about Views:

  1. Understanding the Views scope (ii) - Ownership chaining
  2. Understanding the Views scope (iii) - The complete security layer
3
1,718 Views
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT

Comments (2)

Hi, Do you have any articles on how to connect MSSQL to an Active Directory to query?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Author

Commented:
Hi spen_lang. Thanks for the vote.
I didn't write nothing about querying AD but you can request an article in http://pages.experts-exchange.com/bountyBoard.html or search for a solution here in EE for your question (might someone answered that question before).
Cheers

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community