Understanding Cross Database Ownership Chaining in SQL Server
Written By: K. Brian Kelley -- 6/29/2009
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
Problem
I have a grasp on ownership chaining, but I'm wondering what cross-database ownership chaining is and how it works? How is the owner determined across databases if ownership is based on database users?
Solution
Cross database ownership chaining is an extension of ownership chaining, except it does cross the database boundary. If you're not familiar with ownership chaining, you should probably start with this earlier tip Ownership chaining in SQL Server security feature or security risk. An example where cross database ownership chaining might occur is if you have a view in one database which references a table in another database. The view in the first database refers to a table in the second database. If we were talking objects within the same database, if both the table and view were owned by the same user, an ownership chain would form where an end user would only need access to the view. With cross database ownership chaining, that same thing is possible, except across databases.
Cross database ownership chaining can be turned on at either the server or the database level. If cross database ownership chaining is on at the server level, it is on for all database on that server, regardless of what the individual database settings are. By default, cross database ownership chaining is turned off at the server level and it is off on all databases except the following:
These three system databases require cross-database ownership chaining to be turned on. Outside of these three, the general rules, due to security implications, are:
- Cross database ownership chaining should not be turned on at the server level.
- It should only be turned on for databases which require it.
You can determine if it is on at the server level with the following query (SQL Server 2005/2008). If the value is 0, it is off at the server level. A value of 1 indicates it is on.
SELECT [name], value
FROM [sys].configurations
WHERE [name] = 'cross db ownership chaining'; |
This also applies for the is_db_chaining_on column in sys.databases. We can query sys.databases to see what databases cross database ownership chaining is turned on explicitly:
SELECT [name] AS [Database], [is_db_chaining_on]
FROM [sys].databases
ORDER BY [name]; |
For databases which are activated for cross database ownership chaining, an ownership chain is permitted to cross the database layer. The way the ownership chain is determined is similar to an ownership chain within a database. The exception is that the owner of each object is ultimately mapped to a login, if that is possible (it must be for a cross database ownership chain to form).
In SQL Server 2005 and above, it is possible to create a database user that does not map to a login. To determine these mappings, the following query shows the ultimate owner at the login level for stored procedures and user tables:
SELECT
so.[name] AS [Object]
, sch.[name] AS [Schema]
, USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [OwnerUserName]
, sp.NAME AS [OwnerLoginName]
, so.type_desc AS [ObjectType]
FROM sys.objects so
JOIN sys.schemas sch
ON so.[schema_id] = sch.[schema_id]
JOIN [sys].database_principals dp
ON dp.[principal_id] = COALESCE(so.[principal_id], sch.[principal_id])
LEFT JOIN [master].[sys].[server_principals] sp
ON dp.sid = sp.sid
WHERE so.[type] IN ('U', 'P'); |
Therefore, if you have an object in one database which refers to an object in a second database, both databases are configured for database ownership chaining (or it is configured at the server level), and both objects have the same owner, then a cross-database ownership chain will form. Just as with a normal ownership chain, security is checked on the first object, but not on the second. There is one catch, however, which causes it to be different from a normal ownership chain. The login querying the first object must also have access into the second database. This could be through the guest user as it is with master, msdb, or tempdb. But if the login does not have the ability to connect to that second database, the query will fail.
The following table shows the various options (assuming both databases are configured for cross database ownership chaining):
| Access to 1st DB |
Access to 2nd DB |
Guest User Enabled on 2nd DB? |
Cross Database Ownership Forms? |
| Yes |
No |
No |
No |
| Yes |
No |
Yes |
Yes |
| Yes |
Yes |
No |
Yes |
| Yes |
Yes |
Yes |
Yes |
If a cross database ownership cannot form, then if one object refers to an object in a different database, the login must map to a user in each database which has the appropriate rights on the objects.
To help further illustrate this here is an example. We will setup two databases and create a new user and give rights until the setup is successful. This should be done with an account that has sysadmin rights.
Note: The create database statements below are in their simplest form. If you have do not have default locations configured you may need to add other parameters.
USE master
GO
CREATE DATABASE Chain1
GO
CREATE DATABASE Chain2
GO
USE Chain2
GO
CREATE TABLE dbo.ChainTest
(name VARCHAR(20))
GO
INSERT INTO ChainTest
VALUES('MSSQLTips')
GO
USE Chain1
GO
CREATE PROC spChainTest
AS
SELECT * FROM Chain2.dbo.ChainTest
GO
USE master
GO
CREATE LOGIN ChainTest WITH PASSWORD = 'ABC123!!!', DEFAULT_DATABASE = Chain1
GO
USE Chain1
GO
CREATE USER ChainTest
GO
GRANT EXEC ON spChainTest TO ChainTest
GO
|
Now if we login with the new login "ChainTest" and execute stored procedure spChainTest you will get this error.
Msg 916, Level 14, State 1, Procedure spChainTest, Line 3
The server principal "ChainTest" is not able to access the database "Chain2" under the current security context. |
Now make this change using the sysadmin connection This will enable the guest account and also turn on database chaining.
USE Chain2
GO
GRANT CONNECT TO GUEST;
GO
ALTER DATABASE Chain2 SET DB_CHAINING ON
GO |
Now if we login with the new login "ChainTest" and execute stored procedure spChainTest you will get this error. We now have permission to the database, but not to the object.
Msg 229, Level 14, State 5, Procedure spChainTest, Line 3
The SELECT permission was denied on the object 'ChainTest', database 'Chain2', schema 'dbo'. |
So the last step is to give SELECT rights to the guest account, by running the following command using the sysadmin connection. Note: in the system databases guest has rights via the public role.
GRANT SELECT ON dbo.ChainTest TO guest
GO |
Now if we login with the new login "ChainTest" and execute stored procedure spChainTest you will get this result which is the one row that we inserted into the table.
Next Steps
- Understanding the security features in SQL Server is vital to ensure your data stays intact and your confidential data is not seen by people that should not have access. Take the time to learn more about server and database security.
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
