The Developer Space

Developer's Cafe

  • Cloud
  • Database
  • Open Source
  • Programming
  • Web Dev
  • Mobile
  • Security
  • QuickRef
  • Home
  • Architecture
  • Restrict access to SQL Server Data using a Facade Database

Restrict access to SQL Server Data using a Facade Database

Shameel Ahmed - Architecture, CodeProject, Data Security, Database, Facade, SQL Server
July 25, 2019September 21, 2019 No Comments
2 0
Read Time6 Minute, 38 Second

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? External users might be downstream consumers of your data or a team under the same organization that needs access to your database for their apps/databases to work. What if the external users try to hack into your database and read data that they’re not supposed to, or even worse gain write access to it? What if they destroy/damage your data?

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 a 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-database Ownership Chaining work, the databases involved must have a common owner.

Server-Level vs. Database-Level

Cross-database Chaining can be enabled at the server-level or at the individual database-levels. Enabling it at the server-level makes Cross-database Ownership Chaining work across all databases on the server, regardless of individual settings of the database. If the requirement is to enable it only for a 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 one 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

Preparing the Primary DB

Let me illustrate this with an example. Create a database named CustomerDB. Then, 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') 

Creating the Facade DB

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

Creating Views

Create Views for each table in the Primary DB that you wish to grant access to the restricted user. You can also enable row-level access using a WHERE clause or other filtering keywords.

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

Your object explorer should look like this now:

FacadeDB ObjectExplorer
Object explorer

Creating Login and Users to Streamline Access

Create the restricted user Login and its 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 a “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

Turning on Database Ownership Chaining on Both the Databases

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

Test it out

Log in 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.

FacadeDB Select View
Restricted user output

Now, try querying the underlying table directly.

SELECT * FROM CustomerDB.dbo.Customers

You should see this error:

FacadeDB Select Underlying Table
Permission denied on underlying table
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 setup where a restricted user can query the FacadeDB and view the results, but they cannot query the underlying tables in the CustomerDB.

This article has been mirrored in DZone here

Share

Facebook
Twitter
LinkedIn
Email

Post navigation

DbStudio (beta) Launched
Check PostgreSQL Edition

Related Articles

Migrate your SQL Server Workloads to PostgreSQL: Quick Reference: Second Edition

Book: Migrate your SQL Server Workloads to PostgreSQL: Quick Reference – Second Edition

Shameel Ahmed
October 18, 2022October 22, 2022 1 Comment

How to fix Redis Error “ERR DEBUG command not allowed.”

Shameel Ahmed
July 4, 2022July 5, 2022 No Comments

Migrate MySQL table data to MongoDB collections using Python

Shameel Ahmed
June 18, 2021June 25, 2021 1 Comment

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

.NET Architecture Artificial Intelligence ASP.NET AWS Azure Books C# Career Cloud CodeProject Conversational Bots Database Data Security Facade IDEs Java Mobile MongoDB MySQL Open Source Patterns PostgreSQL Programming Python Redis Security SQL Server Tools Uncategorized Web Development Windows Phone

Recent Posts

  • Developer to Architect Series (Red Hat Enable Architect) January 16, 2023
  • Can ChatGPT replace Google Search? January 11, 2023
  • Learn Python with ChatGPT December 26, 2022
  • Book: Migrate your SQL Server Workloads to PostgreSQL: Quick Reference – Second Edition October 18, 2022
  • Increase Visual Studio Code Terminal Buffer Size July 14, 2022

Archives

  • January 2023 (2)
  • December 2022 (1)
  • October 2022 (1)
  • July 2022 (2)
  • February 2022 (1)
  • November 2021 (1)
  • July 2021 (1)
  • June 2021 (1)
  • September 2020 (1)
  • May 2020 (2)
  • April 2020 (1)
  • October 2019 (1)
  • September 2019 (4)
  • July 2019 (2)
  • May 2018 (1)
  • September 2017 (1)
  • April 2017 (1)
  • April 2014 (1)
  • August 2011 (1)
  • June 2009 (1)
Copyright 2022. The Developer Space | Theme: OMag by LilyTurf Themes
  • DbStudio
  • Re:Link
  • shameel.net
  • Privacy Policy
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are as essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
SAVE & ACCEPT