Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

Sharded Multi-Tenant Database using SQL Server Row-Level Security

Rate me:
Please Sign up or sign in to vote.
4.98/5 (17 votes)
24 Nov 2021CPOL11 min read 18.9K   13  
An overview of how to design a scalable Multi-Tenant Database using Row-Level Security Predicate Functions and DDL triggers to enforce that all shared tables are always restricted according to the tenant.
This article shows concrete example code of a database-level scalable architecture for Multi-Tenant SaaS using sharded SQL Databases u. It shows the creation of the Catalog database, the creation of a shared multitenant database, how to design tables with the TenantId column (and how to design the clustered index for best performance), how to use row-level security predicate functions to automatically filter the records based on the SQL user, and how to create a DDL trigger to enforce that the shared tables will always have the security predicate applied to them, ensuring that a developer mistake will never expose tenant data to other tenants.

Views

Daily Counts

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
I'm passionate about building things since I was born and passionate about computers and programming since I was a kid. I taught myself programming when I was 12 by reading some BASIC programming books and never stopped since then, having played and worked with dozens of technologies.

I have more than 20 years of work experience, having played both technical and management roles, and having worked for companies in many different sectors, including Financial Institutions, Healthcare, and Startups.

I have a Bachelor's degree in Computer Science from University of São Paulo, and an MBA degree from Fundação Getúlio Vargas, both top-ranked universities in Latam. Born and raised in Brazil, lived in Chile for a while, now living in Florida (Fort Lauderdale Area).

If you like my articles please check and follow me on https://github.com/Drizin

Comments and Discussions