Row-level security

In one line: row-level security (RLS) adds a predicate to every query on a table so each tenant sees only its own rows. The filter does exactly that and no more. It doesn’t stop writes, it trusts whoever sets the context, and it leaks through constraints and identity values.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/row-level-security on GitHub.

What you’ll see

  • “A tenant can see another tenant’s rows” after code on the connection changed SESSION_CONTEXT.
  • “A tenant wrote rows into another tenant”, or made its own rows disappear from its view.
  • “The admin report comes back empty.” The predicate applies to dbo and sysadmin too, so a report with no tenant set sees 0 rows, with no error.
  • Duplicate-key errors that name another tenant’s value.

Why it happens

  • A filter predicate filters reads only. In the demo, a tenant inserted an order for tenant 2 and moved one of its own orders there, and both succeeded.
  • The predicate trusts SESSION_CONTEXT, and any code on the connection can set it. That includes SQL injection and user-written report queries. The tenant switched itself from 3 to 1 and saw 90,000 rows.
  • Constraints check every row in the table, visible or not. A unique order number across tenants answered “does SO-000500 exist?” in its error message. A shared identity tells each tenant how many orders everyone takes.

What to do

CREATE SECURITY POLICY Security.TenantPolicy
    ADD FILTER PREDICATE Security.fn_Tenant(TenantID) ON Sales.Orders,
    ADD BLOCK  PREDICATE Security.fn_Tenant(TenantID) ON Sales.Orders AFTER INSERT,
    ADD BLOCK  PREDICATE Security.fn_Tenant(TenantID) ON Sales.Orders AFTER UPDATE
    WITH (STATE = ON);

EXEC sys.sp_set_session_context @key = N'TenantID', @value = 3, @read_only = 1;
  • Add BLOCK predicates for writes. They then fail with error 33504.
  • Set the tenant WITH @read_only = 1 when the connection opens, and again each time a pooled connection is reused. Changing it then fails with 15664.
  • Scope unique keys to the tenant: UNIQUE (TenantID, OrderNumber).
  • Keep the predicate a plain equality on an indexed column, and CAST SESSION_CONTEXT to the column’s type. In the demo that read 8 pages. Without the index it was 3,047. With an OR IS_MEMBER('db_owner') bypass, or without the CAST, it was 291.
  • Run admin jobs tenant by tenant, or accept the scan an admin bypass costs.

What doesn’t help

  • A filter predicate alone. It never checks what’s written.
  • Comparing SESSION_CONTEXT without a CAST. Besides the scan, a tenant ID set as the string '3' matched nothing: the tenant saw 0 rows and no error.

Check your server

-- Security policies in this database: which tables they cover, and whether writes are blocked.
SELECT p.name AS policy_name,
       p.is_enabled,
       OBJECT_SCHEMA_NAME(pr.target_object_id) + '.' + OBJECT_NAME(pr.target_object_id) AS target_table,
       pr.predicate_type_desc,
       pr.operation_desc,
       pr.predicate_definition
FROM sys.security_policies AS p
JOIN sys.security_predicates AS pr ON pr.object_id = p.object_id
ORDER BY p.name, target_table, pr.predicate_type_desc;

A table with a FILTER row and no BLOCK rows accepts writes for any tenant.

See it happen

The row-level-security demo runs a three-tenant table the common way: one application user, with the tenant in SESSION_CONTEXT. It shows writes past the filter, a spoofed context, leaks through keys and identities, and what each predicate shape costs.

Related: data type traps · implicit conversions · non-SARGable predicates

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights