Backward Compatibility for Cross-Database Ownership Chaining
New Information - SQL Server 2000 SP3.
SQL Server 2000 SP3 provides new options for configuring cross-database ownership chaining. The following information will help you determine whether to apply the default SP3 cross-database ownership chaining behavior during Setup, or override the changes and retain pre-SP3 cross-database ownership chaining behavior. In addition, this topic provides information about configuring support for cross-database ownership chaining after installation.
All database objects have owners. When an object--such as a stored procedure, view, or user-defined function--references other objects and the calling and the called objects are owned by the same user, an ownership chain is established. SQL Server uses the ownership chain to determine how to check permissions.
When the same user owns the source object (the view, stored procedure, or user-defined function) and all target objects (underlying tables, views, or other objects), the ownership chain is said to be unbroken. When the ownership chain is unbroken, SQL Server checks permissions on the source object but not on the target objects.
Ownership chaining simplifies security management by allowing users to grant permissions on views, stored procedures, and user-defined functions instead of on individual objects in the database. For example, a user who owns several tables can create a view that includes data from the tables. The user can then grant permissions on the view instead of on the individual tables.
Cross-Database Ownership Chaining
Cross-database ownership chaining occurs when a source object depends on objects in other databases.
A cross-database ownership chain works in the same way as ownership chaining within a database, except that an unbroken ownership chain is based on all the object owners being mapped to the same login account.
Therefore, in a cross-database ownership chain, if the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.
For example, if two databases are owned by the same login account, the dbo users in these databases are mapped to the same login account. If cross-database ownership chaining is enabled for these databases, source objects in the dbo schema can access target objects in the dbo schema of both databases.
Risks Associated with Cross-Database Ownership Chaining
Ownership chaining within a database is a useful application design technique; however, Microsoft does not recommend cross-database ownership chaining because of security risks. These risks are due to the actions that highly-privileged users can perform:
- Database owners and members of the db_ddladmin or db_owners database roles can create objects owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases.
- Users with the CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases from newly created or attached databases.
Even though Microsoft recommends turning off cross-database ownership chaining for maximum security, there are some environments where you can fully trust your highly-privileged users; for applications in those environments, you can enable cross-database ownership chaining at the database or instance level.
Setup Dialog Box Options
Use the following information to determine whether to select or clear the Enable cross-database ownership chaining for all databases check box:
- When the box is cleared, SQL Server 2000 SP3 Setup applies a change that, by default, turns off cross-database ownership chaining for all user databases. After installation, you can turn on cross-database ownership chaining for individual user databases using the db_option system stored procedure. This cross-database security enhancement enables you to configure which databases can be accessed from within other databases.
- If you select the check box, you are overriding the security enhancement and choosing to allow cross-database ownership chaining for all databases. Selecting the check box therefore exposes your system to the security risks described above.
Regardless of which option you choose during Setup, you can later modify server and database support for cross-database ownership chaining.
Configuring Cross-Database Ownership Chaining After Installation
If, after running Setup, you need to change the cross-database ownership chaining configuration, use the new options in the sp_configure and sp_dboption stored procedures:
- Configure cross-database ownership chaining support for the instance of SQL Server with the new Cross DB Ownership Chaining option of sp_configure. When this option is set to 0, you can control cross-database ownership chaining at the database level using sp_dboption. When this option is set to 1, you cannot restrict cross-database ownership chaining, which is the pre-SP3 behavior.
If you change this option, include the RECONFIGURE option to reconfigure the instance without having to restart it. For example, use the following command to allow cross-database ownership chaining in all databases:
EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE
- Configure cross-database ownership chaining at the database level with the new db chaining option of sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or target database. When this option is set to true, the database can participate in a cross-database ownership chain. By default, this option is false for all user databases after you apply SP3.
The following command turns on cross-database ownership chaining for the Northwind database:
EXEC sp_dboption 'Northwind', 'db chaining', 'true'
You cannot turn off cross-database ownership chaining for the master, tempdb, and msdb databases. You cannot turn on cross-database ownership chaining for the model database, which is used as a template for user databases.
The effects of sp_dboption are manifested only when the sp_configure Cross DB Ownership Chaining option is set to 0.