Block predicates are now available as a preview enhancement for Row-Level Security (RLS) on Azure SQL Database. Block predicates address a common point of customer feedback, by enabling security policies to prevent users from inserting, updating, and/or deleting rows that violate the predicate. You can try block predicates today on any Azure SQL Database (V12) server.
Common use cases for block predicates include:
- Preventing cross-tenant inserts in multi-tenant databases
- Enforcing granular control over write access to data for different users, including scenarios that require separate access logic for INSERT, UPDATE, and DELETE operations
Block predicates are defined just like filter predicates, so if you're already familiar with the basics of RLS, it's easy to get started. For example, if you're already using RLS to filter which rows are visible to users, you can now re-use the same predicate function as a block predicate to prevent users from inserting or updating rows to be outside of what's visible:
CREATE SECURITY POLICY Security.userAccessPolicy ADD FILTER PREDICATE Security.userAccessPredicate(UserId) ON dbo.MyTable, ADD BLOCK PREDICATE Security.userAccessPredicate(UserId) ON dbo.MyTable
Whereas filter predicates apply to read operations, block predicates apply to write operations:
- AFTER INSERT and AFTER UPDATE predicates check the new row values against the predicate
- BEFORE UPDATE and BEFORE DELETE predicates check the existing row values against the predicate
If no operation is specified (as above), then the block predicate will apply to all operations. Otherwise, you can specify one operation per block predicate. For instance, if you want to have a block predicate for BEFORE UPDATE and BEFORE DELETE, you should add a separate block predicate for each of these operations.
Here's a short example illustrating how block predicates can be used to prevent cross-tenant inserts in multi-tenant databases. As in earlier examples, the application uses CONTEXT_INFO to identify tenants:
-- Create sample table, where each row has a TenantId CREATE TABLE Sales ( OrderId int, Qty int, Product varchar(10), TenantId int ) INSERT INTO Sales VALUES (1, 53, 'Valve', 1), (2, 71, 'Bracket', 2), (3, 60, 'Wheel', 2) go -- Create shared user for application to connect CREATE USER AppUser WITHOUT LOGIN go -- Tenants will have both read and write access GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser DENY UPDATE ON Sales(TenantId) TO AppUser -- never allowed to change TenantId go -- Enable RLS CREATE SCHEMA Security go CREATE FUNCTION Security.tenantAccessPredicate(@TenantId int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS accessResult WHERE @TenantId = CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) go -- Note: We only need a block predicate AFTER INSERT, because -- rows for BEFORE UPDATE and BEFORE DELETE are already filtered, and -- AFTER UPDATE is unnecessary due to the column permission CREATE SECURITY POLICY Security.tenantPolicy ADD FILTER PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales, ADD BLOCK PREDICATE Security.tenantAccessPredicate(TenantId) ON dbo.Sales AFTER INSERT go -- Try it out by simulating queries as AppUser connected with TenantId = 2 EXECUTE AS USER = 'AppUser' SET CONTEXT_INFO 2 go SELECT * FROM Sales -- only rows for current tenant are visible go INSERT INTO Sales VALUES (4, 1000, 'Wheel', 1) -- blocked from inserting for wrong tenant! go REVERT go
We're really excited about block predicates, in large part because this functionality stems directly from customer feedback. Please give it a try, and let us know what you think in the comments below.
For more information, check out the Row-Level Security documentation on MSDN.