RLS Samples

This project contains demos and helper scripts for implementing Row-Level Security (RLS) on SQL Server (2016+) and Azure SQL Database.

Getting started demos

RLS-Hospital-Builtin-Demo.sql
This demo shows how to use RLS to control access to rows based on SQL Server's built-in security system (users and roles). Use this approach when each user has their own SQL login.

RLS-Projects-MidTier-Demo.sql
This demo shows how to use RLS for middle-tier applications, where all database queries are done via a shared SQL login or service account. This is a common pattern for multi-tenant web apps.

Advanced scenarios

RLS-Performance-Common-Patterns.sql
This demo demonstrates three common patterns for implementing RLS predicates. In addition, this demo shows how RLS has performance comparable to what you'd get with view-based workarounds for row-level filtering.
Details: Row-Level Security: Performance and common patterns

RLS-Middle-Tier-Apps-Demo-using_disjunctions.sql
This demo shows how to optimize RLS query plans that have a disjunction (logical OR). This is common when you allow certain "superusers" to access all rows.
Details: Row-Level Security for Middle-Tier Apps -- Using Disjunctions in the Predicate

RLS-Query-Store.sql
This demo shows how to optimize the performance of RLS predicates by A/B testing different configurations, and using the Query Store to measure the performance impact.
Details: Optimizing RLS performance with the Query Store

RLS-Auto-Enable.sql
A stored procedure that automatically generates a security policy for all tables in your database.
Details: Apply Row-Level Security to all tables

RlsTrigger.sql
A helper trigger that can automatically add a filter predicate on any newly created tables that include, for instance, a "TenantId" column.
Details: Apply Row-Level Security automatically to newly created tables

Retired demos, left here for reference...

RLS-Middle-Tier-Apps-Demo.sql
NOTE: This demo has been superseded by the RLS-Projects-MidTier-Demo above. Use it instead! This demo creates a simple multi-tenant database for a middle-tier application, using Row-Level Security to filter rows based on a Tenant ID.
Details: Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security

RLS-Blocking-Inserts.sql
NOTE: This workaround is now obsolete due to the introduction of block predicates. We've left this here for reference, but you should really use block predicates instead. This demo shows how to implement "insert-blocking" functionality using check constraints, as an additional safeguard against mistakes in your app code.
Details: Row-Level Security: Blocking unauthorized INSERTs