Understanding the Views scope (ii) - Ownership chaining

This is my second article about the Views and is a continuation from my previous one that you can find it here. I will only focus on the security part of the Views. In my first article I explained how you can secure data with Views limiting the scope of the returned information depending on the person who wants to access the data. In this article I will talk about the ownership chaining.

As we saw in my first article when we create a View for querying our tables we do not need to give permissions on those tables but the View only. And why is that? It's because SQL Server knows that we are the owner of those tables and so it uses the ownership chaining to bypass the necessary permissions checks. This behavior it's how the engine works and can't be changed.

It's possible to grant permission on a database object and allow the granted user to grant his permission on the object to others. In my experience this very rarely has to happen but since it is possible to do I decided to write this article to call some attention to when and if you need to do that.

Imagine that the Chief Financial Officer (CFO) of a company needs to have access to salary information of the company. We won't give him access to the main table but will create a View that selects all data from the salary's table:

CREATE VIEW AllSalaries AS
                      SELECT *
                      FROM Salary

Select all Open in new window

Then grant him the select permission on the View. But during his absence he needs to hand over his work to his deputy so we'll need to give him the option to grant himself the necessary permission to give access to his deputy, so the WITH GRANT OPTION will do the trick:

GRANT SELECT ON AllSalaries TO [MyDomain\CFO] WITH GRANT OPTION

Select all Open in new window

Can you see a security hole here?

If we are talking about a View that works with sensitive data the information can be spread to unwanted people. Of course we could give immediately the SELECT permission to the CFO and his deputy but imagine that the CFO wants to control when his deputy can access the data, or even if he has more than one deputy he wants to choose to whom he's going to give permission depending on his abscence period.

So, how to avoid this without saying NO to our CFO and ensure that his deputy won't give permissions on the View to more people?

DENY command is the answer since it precedes the GRANT permission. Of course if there are a lot of users in the company we would need to write a DENY command for each user. To avoid that work we can ask for creation of an AD group where these users will be added except the CFO and his deputy or deputies. Then the DENY command will be something like this:

DENY SELECT ON AllSalaries TO [MyDomain\DeniedUsers]

Select all Open in new window

Then even someone gives access to one of the users in the Denied group the AllSalaries view won't return an error but will raise the following error if that user try to access the View:

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

Select all Open in new window

You can go now and read the last article of this series.