Row-level security (RLS) restricts access to rows based on the caller’s identity or execution context, with the logic held in the database rather than the application. It’s built from an inline table-valued function (the predicate function) bound to tables by a security policy. It’s available in SQL Server, Azure SQL Database, Azure SQL Managed Instance, SQL database in Fabric, and Fabric Warehouse and SQL analytics endpoint. Figure 7.2 traces a read and an insert through the policy built in this section.
Filter versus block predicates
| Predicate | Applies to | Effect |
|---|---|---|
| Filter | Reads by SELECT, UPDATE, DELETE |
Rows silently disappear; the app isn’t told |
Block AFTER INSERT |
New rows | Insert fails if the new row violates the predicate |
Block AFTER UPDATE |
Updated rows | Prevents updating a row to a value that violates the predicate |
Block BEFORE UPDATE |
Existing rows | Prevents updating rows that currently violate it |
Block BEFORE DELETE |
Existing rows | Prevents deleting rows that violate it |
A filter predicate alone still lets users insert rows they then can’t see, and update a visible row so that it becomes filtered; add block predicates to stop that. The optimiser doesn’t check an AFTER UPDATE block predicate when the columns it uses weren’t changed. Block predicates apply to BULK INSERT too. The RLS page states that Microsoft Fabric and Azure Synapse Analytics support filter predicates only; that note sits beside the Warehouse example, while the Fabric SQL database feature list simply marks RLS as supported, so test block predicates there before relying on them.
Building a policy for a shared application user
When a middle tier connects as one database user, it can store the end user’s identity with sp_set_session_context and the predicate can read it with SESSION_CONTEXT. @read_only = 1 stops the value changing until the connection closes.
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_TenantPredicate(@TenantId int)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
AND CAST(SESSION_CONTEXT(N'TenantId') AS int) = @TenantId;
GO
CREATE SECURITY POLICY Security.TenantFilter
ADD FILTER PREDICATE Security.fn_TenantPredicate(TenantId) ON dbo.Orders,
ADD BLOCK PREDICATE Security.fn_TenantPredicate(TenantId) ON dbo.Orders AFTER INSERT
WITH (STATE = ON);
-- In the application, after opening the connection:
EXEC sp_set_session_context @key = N'TenantId', @value = 42, @read_only = 1;STATE = ON enables the policy; ALTER SECURITY POLICY ... WITH (STATE = OFF) disables it. Data API builder can pass token claims into SESSION_CONTEXT in the same way (see “Secure access, auditing and securing endpoints”).
Permissions and behaviour
- Creating, altering or dropping a policy needs
ALTER ANY SECURITY POLICY, plusALTERon the schema; each predicate also needsSELECTandREFERENCESon the function, andREFERENCESon the target table and its predicate columns. SCHEMABINDING = ONis the default for policies: joins and functions inside the predicate work without extra permission checks for querying users. WithSCHEMABINDING = OFF, users needSELECTorEXECUTEon the function and anything it references.- Policies apply to everyone, including
dboanddb_owner; if administrators must see all rows, write that into the predicate. - Only one predicate per operation per table; altering a function bound to a schema-bound policy fails.
- Best practice: a separate schema for predicate functions and policies; avoid type conversions, recursion, many joins, and logic that depends on
SEToptions such asDATEFORMATorDATEFIRST.
Cross-feature effects
- Indexed views can’t be created on tables with a security policy.
- Temporal tables: the policy isn’t copied to the history table; add a predicate there too.
- Memory-optimised tables need a predicate function created
WITH NATIVE_COMPILATION. DBCC SHOW_STATISTICSis restricted because statistics reflect unfiltered data.- Change data capture can expose filtered rows to
db_ownerand the gating role; change tracking can expose primary keys of filtered rows. - Side channels remain: a crafted query such as
SELECT 1/(Salary-100000)reveals a value through a divide-by-zero error.
RLS and DDM together
RLS decides which rows come back; DDM changes how column values in those rows are displayed. A filtered-out row is never masked because it’s never returned, and a returned row shows masked columns unless the caller holds UNMASK.
Common trap: Adding only a filter predicate and assuming users can’t write other tenants’ rows - filter predicates don’t stop inserts. Add an
AFTER INSERT(and, where needed,AFTER UPDATE) block predicate, or deny updates to the tenant column.
Common trap: Expecting
db_ownerto bypass a security policy - policies filter or block rows fordbo,db_ownerand table owners as well; exemptions must be written into the predicate function.