Azure SQL Database’s Row-Level Security (RLS) feature now officially supports block predicates. In contrast to filter predicates, which limit the rows visible to a user, block predicates prevent a user from inserting, updating, or deleting rows that violate your access criteria. You should use filter and block predicates together to control both read and write access to specific rows in a table.

For example, RLS is commonly used to prevent tenants from accessing each other’s data in multi-tenant databases. You can use a filter predicate to limit which rows are visible to each tenant, and now you can re-use the same function as a block predicate to prevent cross-tenant inserts or updates:

CREATE SECURITY POLICY Security.tenantAccessPolicy
 ADD FILTER PREDICATE Security.fn_tenantAccessPredicate(TenantId) ON dbo.MyTable,
 ADD BLOCK PREDICATE Security.fn_tenantAccessPredicate(TenantId) ON dbo.MyTable

Overview and demo

When should you use RLS?

• Enforcing tenant isolation in SaaS applications with multi-tenant databases
• Enabling analysts to access different subsets of data through reporting applications (e.g. Excel or PowerBI), based on their department, region, security clearance, or other criteria
• Centralizing row-level access logic within the database, in order to simplify your application code and safeguard against accidental data leakage

Code samples to get started

In addition to block predicates, I’d like to highlight the availability of SESSION_CONTEXT, a new session-scoped key-value store that is very useful for implementing RLS in middle-tier applications (e.g. web apps) where all database connections are made through a shared service account. Check out our RLS-Projects-MidTier-Demo.sql sample to try this yourself.

Of course, RLS can also leverage SQL Database’s built-in security system of logins, users, and roles, or even Azure Active Directory groups. Check out our RLS-Hospital-BuiltIn-Demo.sql sample to try this yourself.

For an end-to-end multi-tenancy sample using Entity Framework, check out our Tutorial: Web app with a multi-tenant database using Entity Framework and Row-Level Security.

For technical details, head over to the Row-Level Security documentation on MSDN.

Talk to us!

As always, we are eager for your feedback. If you have any questions, comments, or ideas, please leave us a comment below.