Using Ownership Chains
New Information - SQL Server 2000 SP3.
Views, stored procedures, and user-defined functions provide a secondary method of granting permissions to users. They provide users with access to underlying items in the database, possibly without permissions granted on specific objects and statements.
Views can depend on other views or tables. Procedures and user-defined functions can depend on other procedures, functions, views, or tables. These dependencies can be thought of as an ownership chain. Ownership chains apply only to object permissions (SELECT, INSERT, UPDATE, DELETE, and EXECUTE).
Often, the owner of a source object (the view, stored procedure, or user-defined function) also owns all target objects (the objects it depends on). When one user owns the source object and all target objects, the ownership chain is said to be unbroken. If different users own the target objects, the ownership chain is broken. SQL Server relies on the state of the ownership chain to determine when to check permissions.
When an ownership chain is unbroken, SQL Server checks permission on the source object only. This enables a user to grant permissions on views, stored procedures, and user-defined functions instead of on each database object. This can greatly simplify permissions management.
If the ownership chain is broken, SQL Server checks permissions on each branch of the chain owned by a different user. Only those statements where the user has the necessary permissions will be executed, and the remaining statements will get an "Insufficient Permissions" error. In this way, SQL Server allows object owners to retain control over permissions.
Note When temporary objects are created within a stored procedure, they are owned by the procedure owner and not by the user currently executing the procedure.
If one user owns the source object and all target objects, the user can grant permissions on the source object. For example, Mary has created a view called auview1 that depends on the authors table, which she also owns. If Mary grants Sue permission to use auview1, Sue can see data in the view that is from the authors table, event without permissions on the table.
If a user creates a view or stored procedure that depends on objects owned by another user, SQL Server checks permissions on objects owned by other users. For example, Joe creates a procedure called procedure1, which depends on procedure2 (also owned by Joe), and procedure3 (owned by Sue). These procedures in turn depend on other tables and views owned by Joe and Sue. The following graphic shows the ownership chains.
Joe grants Mary permission to use procedure1. SQL Server checks the permissions on procedure1, procedure3, view2, table2, and table3 to check that Mary is allowed to use them.
Cross-Database Ownership Chaining
Cross-database ownership chaining occurs when a source object depends on objects in other databases.
Cross-database ownership chaining works in the same way as ownership chaining within a database. The only distinction is that an unbroken ownership chain is based on all objects being owned by the same login account, not the same database user name. This is because one login account can have different user names in different databases.
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.
Security Considerations for 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 possible 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 your highly-privileged users with data in other 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 cross-database ownership chaining is not recommended, some applications might require cross-database ownership chaining. Additionally, there are some environments where you can fully trust your highly-privileged users. For this reason, cross-database ownership chaining is configurable.
Configuring Cross-Database Ownership Chaining
Because of the security implications of cross-database ownership chaining, SQL Server 2000 SP3 allows you to turn cross-database ownership chaining on or off for each database. For more information, see Setting Configuration Options.