Ownership Chains
When multiple database objects access each other sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server traverses the links in a chain, SQL Server evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately. These differences have important implications for managing security.
Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view. Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.
How Permissions Are Checked in a Chain
When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.
Example of Ownership Chaining
In the following illustration, the July2003 view is owned by Mary. She has granted to Alex permissions on the view. He has no other permissions on database objects in this instance. What occurs when Alex selects the view?
.gif "Diagram of ownership chaining")
Alex executes SELECT * on the July2003 view. SQL Server checks permissions on the view and confirms that Alex has permission to select on it.
The July2003 view requires information from the SalesXZ view. SQL Server checks the ownership of the SalesXZ view. Because this view has the same owner (Mary) as the view that calls it, permissions on SalesXZ are not checked. The required information is returned.
The SalesXZ view requires information from the InvoicesXZ view. SQL Server checks the ownership of the InvoicesXZ view. Because this view has the same owner as the previous object, permissions on InvoicesXZ are not checked. The required information is returned. To this point, all items in the sequence have had one owner (Mary). This is known as an unbroken ownership chain.
The InvoicesXZ view requires information from the AcctAgeXZ view. SQL Server checks the ownership of the AcctAgeXZ view. Because the owner of this view differs from the owner of the previous object (Sam, not Mary), full information about permissions on this view is retrieved. If the AcctAgeXZ view has permissions that enable access by Alex, information will be returned.
The AcctAgeXZ view requires information from the ExpenseXZ table. SQL Server checks the ownership of the ExpenseXZ table. Because the owner of this table differs from the owner of the previous object (Joe, not Sam), full information about permissions on this table is retrieved. If the ExpenseXZ table has permissions that enable access by Alex, information is returned.
When the July2003 view tries to retrieve information from the ProjectionsXZ table, the server first checks to see whether cross-database chaining is enabled between Database 1 and Database 2. If cross-database chaining is enabled, the server will check the ownership of the ProjectionsXZ table. Because this table has the same owner as the calling view (Mary), permissions on this table are not checked. The requested information is returned.
Cross-Database Ownership Chaining
SQL Server can be configured to allow ownership chaining between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required.
Potential Threats
Ownership chaining is very useful in managing permissions on a database, but it does assume that object owners anticipate the full consequences of every decision to grant permission on a securable. In the previous illustration, Mary owns most of the underlying objects of the July 2003 view. Because Mary has the right to make objects that she owns accessible to any other user, SQL Server behaves as though whenever Mary grants access to the first view in a chain, she has made a conscious decision to share the views and table it references. In real life, this might not be a valid assumption. Production databases are far more complex than the one in the illustration, and the permissions that regulate access to them rarely map perfectly to the administrative structures of the organizations that use them.
You should understand that members of highly privileged database roles can use cross-database ownership chaining to access objects in databases external to their own. For example, if cross-database ownership chaining is enabled between database A and database B, a member of the db_owner fixed database role of either database can spoof her way into the other database. The process is simple: Diane (a member of db_owner in database A) creates user Stuart in database A. Stuart already exists as a user in database B. Diane then creates an object (owned by Stuart) in database A that calls any object owned by Stuart in database B. Because the calling and called objects have a common owner, permissions on the object in database B will not be checked when Diane accesses it through the object she has created.