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


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

Published on
9,218 Points
1 Endorsement
Last Modified:
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
(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))

GRANT SELECT ON EmployeesData TO [MyDomain\Employees]

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
FROM EmployeesData
WHERE DepartmentName = 'IT'

-- Who are the people working in IT department
FROM EmployeesData
WHERE DepartmentName = 'IT'

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
FROM EmployessData E
INNER JOIN AllSalaries S ON (E.EmployeeID = S.EmployeeID))

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.


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.

Join & Write a Comment

Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month