Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

Multi-tenant database in SQL Server

Posted on March 23, 2021January 14, 2023 By

Before talking about a multi-tenant database, let’s explain in a nutshell what a multi-tenant system is. Multitenancy is a software architecture that allows a single instance of software to be used by multiple organizations or groups of users.

Database side, one possible implementation is that you use a single database instance which will contain data of all the organizations your system serves.

To achieve this result, you could add to all databases’s tables a field containing the id of the tenant to which the row belongs. Then, in every single query you make against the database you have to consider this field to modify or extract only the data of the tenant of the user.

Particular attention must be paid to the last step: if you forget to include the tenant id in the WHERE condition of a query (for example and UPDATE) you could do serious damages, like mix data of different tenants with each other.

So it would be really cool to have a way to avoid having to apply that filter to all queries… This is where SQL Server row level security comes into play.

Row level security is a SQL Server feature introduced with the release of SQL Server 2016 and it can help us solve the problem described above. Let’s see an example…

Let’s take the table “Authors” of the database “Pubs” (search on Google or check this post to see how you can obtain a copy of this database). Here you can see the content of the “Authors” table:

Recordset sample 1

Add a “tenant_id” column of type INT, and assign to each author a tenant id. Now if you run the query again, you will see something similar:

Recordset sample 2

In order to use the row level security feature of SQL Server, we need to create an inline table valued function that will be used to evaluate if a row should be visible or not to the user. For our purpose we are going to create the following function:

CREATE FUNCTION [dbo].[TenantFilter] 
(
	@TenantID	INT
)
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN 
(
	SELECT 1 VAL WHERE @TenantID = CONVERT(INT, SESSION_CONTEXT(N'tenant_id'))
)

About the function above, please note the following:

  • the function must be created with the SCHEMABINDING option
  • the function must return a row if the visibility condition is true, no row otherwise; moreover it doesn’t matter the column number and the values of the columns you return
  • we are using the SESSION_CONTEXT function, in a moment we will see why

Now, to apply the function to the table “Authors” you have to use the following SQL statement:

CREATE SECURITY POLICY PolicyTenantFilter ADD FILTER PREDICATE dbo.TenantFilter(tenant_id) ON dbo.Authors WITH (STATE = ON)

Then execute the following command:

EXEC sp_set_session_context 'tenant_id', 1;

And finally run the query:

SELECT * FROM Authors
Recordset sample 3

As you can see in the image above, now you are able to see only records with tenant_id = 1, even if we have no filter condition in the SELECT statement!

Why the use of the function SESSION_CONTEXT? Because it’s an easy way to use some sort of “session variable” in the scope of a SQL Server connection. This implies, as a consequence, that you have to call the procedure “sp_set_session_context” every time you open a new connection from your client application, setting the correct tenant_id value.

A last thing to remember: the security policy created above, protects you from reading, updating and deleting records owned by a different tenant, but doesn’t block you to insert records with a different tenant id than yours.

To solve this problem you have to alter the security predicate adding a block predicate as follow:

ALTER SECURITY POLICY PolicyTenantFilter ADD BLOCK PREDICATE dbo.TenantFilter(tenant_id) ON dbo.Authors

Now if you try to insert a row with tenant_id = 4, you will get an error:

INSERT INTO Authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract, tenant_id)
 
VALUES('000-00-0001', 'Doug', 'Ross', '333-444-555', 'Oxford street 90', 'Oakland', 'CA', '95129', 1, 4)

Msg 33504, Level 16, State 1, Line 32
The attempted operation failed because the target object 'pubs.dbo.authors' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

In the example above you manually set the field tenant_id in the insert statement. It would be nice if the tenant_id could be set automatically, given that you called “sp_set_session_context” before.

You can do this setting a default constraint on the field tenant_id, like this way:

ALTER TABLE dbo.authors ADD CONSTRAINT DF_authors_tenant_id DEFAULT CONVERT(INT, SESSION_CONTEXT(N'tenant_id')) FOR tenant_id

With a default constraint configured this way you can run this insert statement without including the field tenant_id:

INSERT INTO Authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) 
VALUES('000-00-0001', 'Doug', 'Ross', '333-444-555', 'Oxford street 90', 'Oakland', 'CA', '95129', 1)

You can learn more about the use of security policies by consulting Microsoft’s docs online, but with this post you should have a basic understanding on how to work with them.

Sql Server 2016

Post navigation

Previous Post: Getting SQL Server sample databases
Next Post: Could not load file or assembly ‘DocumentFormat.OpenXml, Version=2.7.2.0

Related Posts

How to know who is blocking a query in Sql Server Sql Server
Delete all rows from all tables in a Sql Server database Sql Server
Get all referencing tables of a table in Sql Server Sql Server
Sql Server error: Cannot construct data type date, some of the arguments have values which are not valid. Sql Server
SQL Server LEN function return 0 passing a string with one space Sql Server
Sql Server add constraint WITH NOCHECK Sql Server

Search

Related posts

  • Sql Server error using a database with a CLR assembly
  • Delete all rows from all tables in a Sql Server database
  • Search a string in stored procedures, functions and…
  • Merge two or more historical tables with Sql Server…

Categories

  • .NET 6 (1)
  • .NET 7 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (7)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (20)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (4)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • VueJS (1)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2024 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark

Manage Cookie Consent
This site doesn’t collect user personal data and doesn’t install profiling or analytical cookies, either its own or from third parties. Read our privacy policy for more info.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage {vendor_count} vendors Read more about these purposes
View preferences
{title} {title} {title}