The Architect Zone

The Ultimate Reference for Architects

Restrict access to SQL Server Data using a Facade Database

How many times have you felt the need to grant restricted access to your SQL Server databases to external users and felt unsafe about doing it? What if the external users try to hack into your database and gains write access to it? What if they destroy/damage your database?

This post describes a method to create a Facade Database to provide restricted access to specific tables in your databases to specific users without granting direct access to any of the underlying databases/tables. SQL Server provides a feature called Cross Database Ownership chaining that can help us achieve this. The examples provided in this article have been developed and tested on an SQL Server 2008 R2 Server. This feature is supported in older versions of SQL Server too, but we’ll limit the discussion to the following versions.

  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014

 

Ownership Chaining                                                 

When a script accesses multiple database objects sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server traverses the links in a chain, it evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately. These differences have important implications for managing access and security.

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. If both objects have the same owner, permissions on the referenced object are not evaluated.

Cross Database Ownership Chaining

SQL Server can be configured to allow ownership chaining between specific databases or across all databases inside a single Server of SQL Server. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required. To make Cross DB Ownership Chaining‎ work, the databases involved must have a same owner.

Server-Level vs Database-Level

Cross Database Chaining can be enabled at the Server level or at the individual Database level. Enabling it at the Server level makes Cross Database Ownership Chaining work across all databases on the Server irrespective of the individual setting of the Database.  If the requirement is to enable it only for few databases, then you should enable it at the database level.

Server-Level Cross Database Ownership Chaining

To enable Server level Cross Database Ownership Chaining, use the following T-SQL statements.

EXECUTE sp_configure 'show advanced', 1
GO
RECONFIGURE
GO

EXECUTE sp_configure 'cross db ownership chaining', 1
GO
RECONFIGURE
GO

To check if it is enabled already, use this query:

SELECT [name], value  
FROM [sys].configurations 
WHERE [name] = 'cross db ownership chaining';

A value of 1 indicates that it is already enabled.

Database-Level Cross Database Ownership Chaining

To enable Database level Cross Database Ownership Chaining, use the following T-SQL statements.

ALTER DATABASE myDatabase SET DB_CHAINING ON
GO

To check if it already enabled at the individual database level, run:

SELECT name, is_db_chaining_on FROM sys.databases
GO


Steps to create a Facade DB

Prepare the Primary DB

Let me illustrate with an example. Create a database named CustomerDB

Create a table named Customers and insert some test data

CREATE TABLE [dbo].[Customers](
	[CustomerId] [int] IDENTITY(1,1) NOT NULL,
	[CustomerName] [varchar](50) NOT NULL,
	[Address] [varchar](500) NOT NULL,
	[City] [varchar](50) NOT NULL,
	[Country] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Customers ([CustomerId], [CustomerName], [Address], [City], [Country]) VALUES (1, 'Michael Douglas', 'LA Home', 'Los Angeles', 'US')
INSERT INTO Customers ([CustomerId], [CustomerName], [Address], [City], [Country]) VALUES (2, 'Al Pacino', 'NY Home', 'New York', 'US')
INSERT INTO Customers ([CustomerId], [CustomerName], [Address], [City], [Country]) VALUES (3, 'James Cameroon', 'NJ Home', 'New Jersey', 'US')

Step 1: Create the Facade DB

Create a database named FacadeDB (or any other name for that matter).

Step 2: Create the Views

Create Views for each table in the Primary DB that you wish to grant access to the restricted user.

CREATE VIEW [dbo].[CustomerView] AS SELECT * FROM CustomerDB.dbo.Customers

 

Your object explorer should look like this now:

 

Step 3: Create the Login and Users

Create the restricted user Login and it's associated Users in the databases. The user must be added to the Primary Database as 'public', otherwise ownership chaining will not work. The user must have at least 'db_datareader' role on the Facade Database.

CREATE LOGIN [FacadeUser] WITH PASSWORD=N'facadeuser', DEFAULT_DATABASE=[FacadeDB], 
			DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [CustomerDB]
GO
CREATE USER [FacadeUser] FOR LOGIN [FacadeUser] WITH DEFAULT_SCHEMA=[dbo]
GO


USE [FacadeDB]
GO
CREATE USER [FacadeUser] FOR LOGIN [FacadeUser] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_datareader', N'FacadeUser'
GO

 

Step 4: Turn on Database Ownership Chaining on both the Databases

ALTER DATABASE CustomerDB SET DB_CHAINING ON
GO
ALTER DATABASE FacadeDB SET DB_CHAINING ON
GO

 

Step 5: Try It Now

Login to the server as the restricted user (FacadeUser) and execute the following commands.

SELECT * FROM CustomerView

You should be able to see the rows of the underlying table.

Now try querying the underlying table directly.

SELECT * FROM CustomerDB.dbo.Customers

You should see this error:

The SELECT permission was denied on the object 'Customers', database 'CustomerDB', schema 'dbo'.

Conclusion

If you followed the above steps, you should have a working set up where a restricted user can query the FacadeDB and view the results but he/she cannot query in the underlying tables in the CustomerDB.

 

Comments are closed