Don't break the chain!

Ownership Chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, for instance a view. It is a powerful technique used for controlling access to data enabling the creation of a ‘security layer' defined by Views and/or Stored Procedures such that authorized users can access a View or execute a Stored Procedure without being able to access the base tables directly. In this way, Ownership Chaining simplifies the security model greatly. This technique has been used in previous releases and is now also supported using schema object in SQL Server 2005.

The rule is that if all the database objects referenced in a View or Stored Procedure or Function are owned by the same user then SQL Server will only check permissions at the highest level. SQL Server assumes that because all the objects have the same owner, that the owner knows what they are doing when they set permissions on the top level object. This is an unbroken ownership chain and is a good thing. It also has the effect of creating a security layer because the underlying database objects are protected and are not accessible by the calling user. An easy way to guarantee an unbroken ownership chain is to have all objects in a database created by the same user, preferably the database owner. Conversely, if any of the objects are owned by other users, we have a broken ownership chain (not good) and SQL Server will be forced to check permissions at all levels and therefore access will be denied unless we set permissions at all these levels. This is not only difficult to administer but it also opens up access to lower level objects that we wanted to protect.

With the introduction of the Schema object in 2005, the owner is no longer apparent in the qualified name of the object, however ownership chaining still applies. If you own a Schema, you effectively own all the objects contained within it. Once again, as long as all the objects are owned by the same owner, we have an unbroken ownership chain and creating security layers is a piece of cake.

cheers

Brian

Recent posts:

Slam these SQL Injection Attacks!

SSMS 2008 Debug – very cool…

Books Online saves the day again - Configuring Filestream data