<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
9,016 Points
1,216 Views
3 Endorsements
Last Modified:
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
2 Comments

Expert Comment

by:spen_lang
Hi, Do you have any articles on how to connect MSSQL to an Active Directory to query?
0
LVL 55

Author Comment

by:Vitor Montalvão
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
0

Featured Post

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month