Dangers of Enabling Cross-Database Chaining

I'm about to deploy SQL Server 2000 Service Pack 3 (SP3) to a large number of servers in my enterprise. I noticed an option called cross-database chaining. What does this do?

Cross-database chaining is a new configuration option in SP3 that you can enable for individual databases by using the sp_dboption command

EXEC sp_dboption'privatedb', 'db chaining', 'ON'

You can also enable cross-database chaining server-wide by using the sp_configure command. Alternatively, you can use Enterprise Manager to enable this option for individual databases or at the server level.

Cross-database ownership chains are related to SQL Server ownership chains, except—as the name implies—they extend the idea of an ownership chain to objects in a different database. For background information about ownership chains, see the SQL Server Books Online (BOL) topic "Using Ownership Chains." Before SP3, SQL Server behaved as if cross-database chaining was on for all databases. By default, SP3 (and SP3a) turn cross-database chaining off.

Cross-database ownership chaining opens up several serious security holes and, except for specific circumstances, should be avoided. Here's an example of a security problem that cross-database ownership chains could create. Imagine you have two databases, PrivateDB and PublicDB. PrivateDB contains a table named PrivateTable, and PublicDB contains a table named PublicTable. A person who connected to SQL Server using the sa login has created each table; therefore, SQL Server marks each table as being owned by DBO within its respective database. No explicit permissions have been granted to either table; however, DBO can access both of them. A login called CrossTest also exists. The CrossTest login is a member of the db_owner role in PublicDB but is a member of only the public role in PrivateDB. In this situation, you expect SQL Server to deny CrossTest permission to access the PrivateDB.dbo.PrivateTable, and that's what happens if CrossTest issues a command such as

SELECT * FROM PrivateDB.dbo.PrivateTable

However, a security hole opens up if you enable cross-database ownership chaining for PrivateDB and PublicDB. The option tells SQL Server to check the login account, rather than a database-level username, when determining if an ownership chain is broken.

So, suppose you enable cross-database ownership chaining for each database. The CrossTest login could then log in to SQL Server and issue the following command in PublicDB:

CREATE VIEW dbo.MyBackDoorASSELECT * FROM PrivateDB.dbo.PrivateTable

Let's consider the ownership-chain implications. The login CrossTest still doesn't have permissions on the base table PrivateDB.dbo.PrivateTable. However, that login was able to create a view in PublicDB that references this forbidden object. The view and the supposedly private table are both owned by DBO, which maps back to the sa login in this case. Therefore, SQL Server doesn't see a break in the ownership chain when the view attempts to access the table. And because the ownership chain is unbroken, anyone who can access the view can also access PrivateTable in PrivateDB.

This example shows that anyone who is a member of the db_owner role for a given database can access any data in any database if cross-database chaining is enabled between the databases. Such broad access might be appropriate in narrowly defined circumstances, but the cross-database ownership chaining option can easily lead to the creation of unintended back doors into your databases.