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
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.
As always, we are eager for your feedback. If you have any questions, comments, or ideas, please leave us a comment below.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.