The Ownership Chaining Quandary or How to Circumvent an Explicit DENY

Introduction

In The Ownership Chain we looked at how SQL Server skips permission checking, based on the ownership chain of the securables involved. In that same post I said that ownership chaining is a good thing in most circumstances. However, as with most powerful technologies, there is the potential for abuse.

In this post, I would like to show you one example of how ownership chaining can be misused to gain unauthorized access to a securable.

Example of Unauthorized access using Ownership Chaining

Let us look at the example of the Junior DBA. Junior is supposed to do most of the maintenance tasks in our database. That includes the execution of upgrade scripts and hot fixes from time to time. However, management decided that Junior should not be able to see or change data in the table that holds the salary information for all employees. How would you implement that?

First, we need a JuniorDba user:

The database schema looks like this:

Well, there are other objects in the database and probably also other columns in that salary table, but for our example this suffices.

Now, the requirement was for Junior to be able to execute maintenance tasks as well as install upgrades and hot fixes. That means, we should grant CONTROL on the database. We also want to prevent that Junior snoops on the salary, so we should deny SELECT on that table.

In real life you would also deny other privileges on this table, but for this example denying SELECT is enough to demonstrate the problem.

Now Junior should not be able to select from the salary table anymore. Let us confirm:

Great, that works. However, Junior is clever and immediately identifies the rather huge gap. Junior can create a procedure like this one:

By default, procedures and tables are owned by the owner of their schema. If the HumanResources schema was created by a sysadmin or a member of the db_owner database role, it is owned by dbo, the same as the dbo schema. That means, that now both the procedure and the table have the same owner and therefore ownership chaining is in effect:

With that simple procedure, Junior was able to get a complete picture of the company's salary situation. The CONTROL privilege on the database combined with ownership chaining allowed Junior to circumvent an explicit DENY. This type of problem is not unique to the CONTROL permission. Other combinations of privileges can lead to the same result.

To prevent this kind of attack, a lot of moving parts have to be taken into account. You could just change the owner of that table to a separate database principal. However, now other procedures cannot rely on ownership chaining anymore either. That means that the legitimate users now need direct access to that table, a situation you probably want to avoid too. You could try to separate the entire salary related functionality into its own schema and block that schema off from Junior. However, then Junior might not be able to execute upgrade scripts anymore.

If this quandary needs to be solved within the database, you might have to revert to signing all modules (that need to access the salary table) with a certificate. A procedure can be scripted with its signature. That means that Junior could still install a procedure that has been signed by a more senior team member. However, Junior would not be able to alter that procedure for adverse intents, as that would invalidate the signature.

Module signing is a very clean solution; however, it comes with a high maintenance overhead. A simpler solution would be to encrypt the salary information within the application. That way it would not matter if Junior could see the stored (and encrypted) information.

Summary

While Ownership Chaining is a good thing in most situations, it can be misused to gain unauthorized access to a securable in other situations. This can create a quandary that is not easy to solve.