First published on MSDN on Oct 02, 2015
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
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!
For more information, check out the documentation on MSDN:
Row-Level Security
