Using A Block Predicate to Restrict Row Updates and Inserts

If you are using row level security in SQL Server 2016 you might find users are updating or inserting rows of data that keep them from seeing the row after they have performed the update or insert statement.  This happens when a user updates or inserts a row that doesn’t pass the filter rules for row level security.  If you want to prevent this from occurring, you can use a blocking predicate.

A blocking predicate can be used to keep users from shooting themselves in the foot.  Meaning it will keep a user from updating or inserting a row into a SQL Server table that they will not be able to view due to a row level filter rule.  To demonstrate this let’s assume I have the following Customer table defined with a row filter rule and security policy as shown below:

CREATE TABLE Customer(
       CustomerName varchar(100) NULL,
       CustomerEmail varchar(100) NULL,
       SalesPersonUserName varchar(20) NULL
);
GO
CREATE FUNCTION fn_RowLevelSecurity (@FilterColumnName sysname)
RETURNS TABLE
WITH SCHEMABINDING
as
RETURN SELECT 1 as fn_SecureCustomerData
-- filter out records based on database user name 
where @FilterColumnName = user_name();
GO
CREATE SECURITY POLICY FilterCustomer
ADD FILTER PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer
WITH (STATE = ON); 
 

As you can see this table has a row level filter rule that says a user could only SELECT data from the Customer table if their user name was in the SalesPersonUserName column.   Therefore, in order to keep users from updating rows that they will not be able to see after the update I will ALTER the existing SECURITY POLICY and add two different BLOCK PREDICATEs:

ALTER SECURITY POLICY FilterCustomer
ADD BLOCK PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer AFTER UPDATE, 
ADD BLOCK PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer AFTER INSERT;

With these two new blocking predicates in place, I’ve told SQL Server to not allow a user to UPDATE or INSERT a row into the Customer table unless the row being updated or inserted has their user name in the SalesPersonUserName column. 

See all articles by Greg Larsen