<

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

x

Understanding the Views scope (ii) - Ownership chaining

Published on
9,246 Points
1,146 Views
1 Endorsement
Last Modified:
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

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

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]

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'.

Open in new window


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

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month