SQL Server: Exploring Cross-Database Ownership Chaining

  • Article

Introduction

Cross-database ownership chaining is a security feature of SQL server that allows users of databases access to other database objects hosted on the same SQL server instance, where users don’t granted access explicitly.

By default, cross-database ownership chaining is set to OFF at the server level and it is OFF on all databases except the master, msdb and tempdb databases.

Now, in this article, we will see how to use cross-database ownership chaining and explore its features.

Understanding Cross-Database Ownership Chaining

Where the phenomenon called cross-database ownership chaining takes place in situations is if we have a view in one database that joins together data from tables in multiple databases. Or a stored procedure that accesses objects in multiple databases while preventing direct access to the underlying tables or objects.

If both top-level objects like stored procedure, views and underlying objects or tables owned by a single owner with same security IDs (SIDs) then unbroken cross-database ownership chain will form. And then If we enable the cross-database ownership chain feature on those databases the result is SQL server stops checking permissions on underlying objects once it verifies that the user has permission on the top-level objects. The user doesn't require explicit permission to the underlying objects as it inherits same permission from top-level objects.

However, if all objects are not owned by the single owner, SQL server will check permissions of the user on both top-level and underlying objects.

Following example helps to illustrate this.

Assume we have 2 databases, called DB1, DB2 and both databases owned by a single owner with same security IDs.

We create a stored procedure in DB1 and its owned by dbo, that stored procedure refers to a table object in another database i.e. DB2 when return results. The table in database DB2 is also owned by dbo.

Create a login called “login1” and grant DBO permission on DB1 and do not grant any access on DB2 explicitly.

When the user attempts to execute the stored procedure, the following error is returned:

“*Msg 916, Level 14, State 1, Procedure PROC1, Line 4

The server principal "LOGIN1" is not able to access the database "DB2" under the current security context.”*

In this scenario, the solution is either enable the cross-database owner shipping on both databases or grant appropriate access to LOGIN1 account on DB2 objects explicitly to return the results.

Following T-SQL code helps to illustrate this.

--Create databases.
USE MASTER;
GO 
CREATE DATABASE  DB1
GO
CREATE DATABASE  DB2
 
--Map 'SA' as database owner for both databases.
USE DB1
GO
EXEC sp_changedbowner 'sa'
GO
USE DB2
GO
EXEC sp_changedbowner 'sa';
 
-- Create a table in DB2 for access from another database.
USE DB2
CREATE TABLE  TABLE1 (ID INT,NAME VARCHAR(20));
GO
INSERT INTO  TABLE1 VALUES(1,'SUMAN')
GO
 
--Create stored procedure in DB1 that access objects in another database.
USE DB1
CREATE PROC PROC1
AS
BEGIN
SELECT * FROM DB2.DBO.TABLE1
END
GO
--Create a Login
 
USE [master]
GO
CREATE LOGIN [LOGIN1] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
 
--Create a user in the DB1 who will access the stored procedure.
USE [DB1]
GO
CREATE USER [LOGIN1]  FOR  LOGIN [LOGIN1]
GO
ALTER ROLE [db_owner] ADD MEMBER [LOGIN1]
GO
 
-- Try to execute the stored procedure as LOGIN1, without Cross-Database Ownership Chaining.
 
USE DB1
GO
EXECUTE AS  LOGIN = 'LOGIN1';
GO
EXECUTE DBO.PROC1
GO
REVERT
GO

Prerequisites

Following conditions should satisfy before enabling the Cross-database Ownership Chaining.

  • The owner of both source and underlying databases should be the same with same security IDs (SIDs).
  • The owner of both source and underlying databases objects should be the same.
  • The user must have access-permissions on the source database or objects he directly accesses.
  • The server principal of the user in the source database that is running the stored procedure or view must have CONNECT access to the underlying database.

Enabling Cross-Database Ownership Chaining

Cross-database ownership chaining can be turned ON at either the server or the database level.

If we turned ON at the server level, cross-database chaining is enabled for all databases on that SQL Server instance, regardless of individual database settings and we can’t restrict it at the database level.

Enabling at the Server Level:

The following statement turns ON cross-database ownership chaining at the server level.



      --Setting the cross-database ownership chaining option to 1 enables it and setting it back to 0 disables it.  
   
      USE master;  
  GO
  EXECUTE     sp_configure  'show advanced', 1;
  RECONFIGURE;
  EXECUTE     sp_configure  'cross db ownership chaining', 1;
  RECONFIGURE;

Alternatively, go to object explorer of management studio>>> Right-click on the server instance>>> Select Properties >>>Point to Security page >>> Check cross-database ownership chaining option>>>Click OK to enable the cross-database ownership chaining at the server level.

Enabling at the Database Level: ** **

Use following ALTER DATABASE statement t turns ON cross-database ownership chaining for specific databases. 

-- Setting the DB_CHAINING option to ON enables it and setting it to OFF disables it.
ALTER DATABASE  <Your DatabaseName> SET DB_CHAINING ON;

Observations:

  • If we detach then reattach a database or restore a database that has cross-database ownership chaining enabled, we'll have to re-enable it after re-attaching or restoring the database.
  • By default, we all are using cross-database ownership chaining future on all our SQL Servers. We surely already used a function or table of the master\temp-database. If we look at the security-configuration of the master-\temp database we will see that we are mostly not explicitly granted access. That’s because cross-database ownership chaining is applied in that operations.

Checking Status of Cross-Database Ownership Chaining

Following statements will helps us to find out if cross-database ownership chaining is enabled or disabled.

--To find out the status of cross-database ownership chaining at instance level. If the value is 0, indicates it is OFF and if the value is 1, indicates it is ON at the instance level.
 
SELECT [Name], [value_in_use] 
FROM [sys]. configurations 
WHERE [name] = 'cross db ownership chaining';
 
--To find out if cross-database ownership chaining is configured on which DATABASES on current server instance. If the value is 0, indicates it is OFF and if the value is 1, indicates it is ON at the database level.
 
SELECT [name] AS  [Database], [is_db_chaining_on] 
FROM [sys]. databases
ORDER BY  [name];

Limitations

Cross-database ownership chaining had below limitations.

  • Cross-Database ownership chaining allows only to object permissions, operations such as SELECT, UPDATE, DELETE, INSERT and EXECUTE but for any other operation beyond this like DDL statements or statements like TRUNCATE TABLE, etc. SQL server will always be checking permissions on underlying objects too, that means the user must have granted the permissions needed on underlying objects explicitly.
  • Cross-database ownership chaining does not work in cases where dynamically created SQL statements are executed in stored procedures. On statements that run dynamically through EXECUTE or sp_executesql, SQL Server checks permissions in the security context of the user who executed the procedure instead of the procedure owner. As a result, Dynamic SQL breaking the ownership-chain and users must have permissions on all objects that are directly referenced by dynamically executed statements.

Security Threats

Microsoft endorses that enable cross-database ownership chaining only where we can fully trust highly-privileged users because of it disclosures us to the following security risks:

  • In the context of a single database, the database owner and members of the db_ddladmin and db_owners database roles can create objects owned by other users. These new objects can use objects in other databases that have the same owners, that means new objects had the potential to cross the security boundary of a database and access to objects in the other databases.
  • Users with CREATE DATABASE permission can create new databases and attach existing databases to an instance of SQL server. If cross-database ownership chaining is enabled, these users can access objects in other databases that have the same owners.

Alternative

A far better option for handling these scenarios is module signing (i.e. using certificates to sign executable modules). Module signing, introduced in SQL Server 2005, has the following benefits:

  1. It is not limited to DML, SELECT, and EXECUTE permissions
  2. It works with Dynamic SQL
  3. It does not have the security threats noted above
  4. It allows for very granular control over permissions for individual objects instead of granting permission for all objects of the same owner
  5. It does not require that the database owners be the same between databases
  6. It does not require that the object owners be the same between objects 
  7. It does not require that the user executing the top-level module have any access to the other databases

Module signing, however, cannot be applied to views or inline table-valued functions.

Summary

We would need to carefully analyze where do we want to configure it. And, it should be enabled only in environments where we can fully trust highly-privileged users. It can be configured during setup for all databases, or selectively for specific databases using the Transact-SQL commands sp_configure and ALTER DATABASE and It’s not recommended to enable at the server level. Instead, enable it only for the databases where you really need it.

References

See also