Understanding the Views scope (iii) - The complete security layer

This is the third and last article about the security mechanism of the Views. If you didn't read the other two articles they are here:

1. Understanding the Views scope (i) - Limiting the access to data
2. Understanding the Views scope (ii) - Ownership chaining

In this last article I will mix the above articles and add some more stuffs that missed in those articles.

In my first article I showed how to use a view to limit the access to data by returning only the rows that each user should see. Of course it is also possible to filter by columns and should be the right thing to do since a simple SELECT * would may return more data than the necessary and may also cause more network traffic.
Imagine that in a company's Intranet there's a page with all Employees and the data is provided by the Human Resources department. For sure no one wants that their more private information to be available for the rest of the company then fields like address, birthday, civil status and children's won't be published. A good solution is to create the View with the limited data and give access to all employees so they can see the information in the webpage:

CREATE VIEW EmployeesData
                      AS
                      (SELECT E.EmployeeID, D.DepartmentName, E.EmployeeLastName, E.EmployeeFirstName, C.CategoryName
                      FROM EmployeeSalary E
                      	INNER JOIN Department D ON (E.DepartmentID = D.DepartmentID)
                      	INNER JOIN Category C ON (E.CategoryID = C.CategoryID))
                      GO
                      
                      GRANT SELECT ON EmployeesData TO [MyDomain\Employees]
                      GO

Select all Open in new window

Do not forget that users doesn't need to have permissions on any of the above tables because of the ownership chaining I referred in my second article.
You might realize that I added more tables and INNER JOIN clauses. That is to show that we can have complex Views and it is a good way to encapsulate and reuse the code. For example if someone from IT department wants to know how many people are working in their department or who are their department colleagues, they can use the View:

-- How many people are working in IT department
                      SELECT COUNT(*)
                      FROM EmployeesData
                      WHERE DepartmentName = 'IT'
                      
                      -- Who are the people working in IT department
                      SELECT *
                      FROM EmployeesData
                      WHERE DepartmentName = 'IT'

Select all Open in new window

Continuing with our company's Intranet we want to provide a web page with employee's salary receipt so each employee can only see their own information. With the View created in my first article we can easily join with this one created here and we will have all the necessary information:

CREATE VIEW EmployeeSalary AS
                      (SELECT *
                      FROM EmployessData E
                      INNER JOIN AllSalaries S ON (E.EmployeeID = S.EmployeeID))

Select all Open in new window

Don't forget that AllSalaries's View it's already limiting the data by the EmployeeID so each employee will only see his relevant information.
Also be careful when using nested Views. You can lose control of them with more complex queries and will be harder to debug apart of a possible negative impact in the performance. This is only a simple example to show that it's possible to create nested Views. Be wise when using them.

As you could see there's a lot to talk about Views and I mainly focused on the security.
I hope you enjoyed the articles. It was fun writing them.

Cheers.