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

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]

Select all 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()

Select all 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()

Select all 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

Select all 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.
That can be confirmed if the user tries to run a SELECT statement over the Salary Table. Will raise the following error:




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