In my last article I talked about restricting read access using the new SQL Server 2016 filter predicate feature of a security policy. With the new security policy feature in SQL Server 2016 you can also restrict write operations at the row level. To restrict write access you need to define a block predicate. In this article I will be reviewing why a block predicate is important when you implement row level security using SQL Server 2016.
What is a Block Predicate?
A block predicate is part of the new Row Level Security (RLS) that was introduced in SQL Server 2016. By using a block predicate you can define rules that identify which rows a database user is not allowed to perform an INSERT, UPDATE, or DELETE operation against. The block predicate is defined using the new CREATE SECURITY POLICY statement introduced in SQL Server 2016.
You might be asking yourself, why it is important to define a block predicate when using RLS? In my first article on RLS (https://www.databasejournal.com/ms-sql/row-level-security-with-sql-server-2016-part-1-allow-access-to-only-a-subset-of-rows-using-row-level-security/) I only talked about restricting SELECT access using RLS, with a security policy that only had a filter predicate. The problem with creating a new security policy for RLS, with only a filter predicate, is that the policy doesn’t restrict a database user from updating a row so it can no longer be read after the update statement. Additionally they could insert a brand new row that they might not be able to read because they set the filtered columns on the new row to values that made the filter predicate restrict them from reading the newly inserted row.
The block predicate covers both UPDATE, INSERT, and DELETE statements. These block predicates can be executed before or after the update statement. The before predicates check existing row values against the predicate rules before an UPDATE and DELETE statement, whereas the after predicate checks the new row values being assigned against the predicate rules with an INSERT or UPDATE statement.
To demonstrate why a block predicate is important when implementing RLS security, let me identify a business problem and then demonstrate how to solve the problem by implementing a block predicate.
Business Problem
When management first asked the DBA to implement RLS they only required RLS to restrict SELECT access to rows based on a Sales Person’s database user name. The DBA set up RLS security by implementing a security policy with only a filter predicate. This filter predicate was put in place to only allow sales persons to read their customer contact information and not the customer contact records of other sales people. This was to keep sales persons from stealing the customers of another sales person (note business problem associated with my first TLS article that can be found in the link above).
After the first iteration of RLS, management decided sales people should be allowed to UPDATE and INSERT rows in the table that contained the customer contact information. By allowing updates, a sales person could update their existing customer in the event that a customer wanted to change the customer contact email address. By also allowing INSERT statements sales people could also insert customer contacts if a sales person found a new customer.
After the DBA added the INSERT and UPDATE permissions on the table that contained the customer contact information, it was found the sales people where updating their records incorrectly, which caused them to no longer be able to read their own customer records. Not only that they were also incorrectly adding their new customers, so they couldn’t see the new customer record they inserted. Therefore management asked the DBA to fix the existing security policy to keep sales people from incorrectly updating or inserting records that didn’t allow them the access to the updated or inserted records.
To solve this problem let me work through this problem one code snippet at a time. As I work through this problem you will see how block predicates can solve the solution to the problem identified above.
Setting up Some Test Data
The code below will be used a couple of times in this article. This code sets up and populates the Customer table. The Customer table will then be used to simulate the business problem and a solution to the business problem by creating a block predicate:
SET NOCOUNT ON; USE master; GO DROP DATABASE RLS_DEMO; GO -- Code from Part 1: USE master; GO CREATE DATABASE RLS_DEMO; GO USE RLS_DEMO; GO CREATE USER Jane WITHOUT LOGIN; CREATE USER Dick WITHOUT LOGIN; CREATE USER Sally WITHOUT LOGIN; GO CREATE TABLE Customer( CustomerName varchar(100) NULL, CustomerEmail varchar(100) NULL, SalesPersonUserName varchar(20) NULL ); GO GRANT SELECT ON dbo.Customer TO Jane; GRANT SELECT ON dbo.Customer TO Dick; GRANT SELECT ON dbo.Customer TO Sally; GO INSERT INTO Customer VALUES ('ABC Company','Manager@ABC.COM','Jane'), ('Info Services','info@AInfaSerice.COM','Jane'), ('Washing-R-Us','HeadWasher@washrus.COM','Dick'), ('Blue Water Utilities','marketing@bluewater.COM','Dick'), ('Star Brite','steve@starbright.COM','Jane'), ('Rainy Day Fund','Tom@rainydayfund','Sally'); 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); -- Granting UPDATE and INSERT access GRANT UPDATE, INSERT ON dbo.Customer TO Jane; GRANT UPDATE, INSERT ON dbo.Customer TO Dick; GRANT UPDATE, INSERT ON dbo.Customer TO Sally; GO
This is the same code used in Part 1 of this article, with an extra set of GRANT statements at the bottom to provide UPDATE, and INSERT permissions for each one of the sales people.
Demonstrate How a Sales Person can Update Records
To show you how a sales person can use an UPDATE or INSERT statement to cause rows to no longer be viewable after it was inserted or updated I will run the following code snippet:
-- UPDATE customer record EXECUTE AS USER = 'Jane'; UPDATE Customer SET CustomerEmail = 'Jack@ABC.COM', SalesPersonUserName = 'Dick' WHERE CustomerName = 'ABC Company'; -- INSERT new customer INSERT INTO Customer VALUES ('Rock The Dock','Rocky@RockTheDock.COM','Sally'); PRINT 'Jane''s Customers after UPDATE and INSERT'; SELECT CustomerName, CustomerEmail, SalesPersonUserName FROM Customer; REVERT;
When I run this code I get the following output:
Jane's Customers after UPDATE and INSERT CustomerName CustomerEmail SalesPersonUserName ------------------------------- ------------------------ -------------------- Info Services info@AInfaSerice.COM Jane Star Brite steve@starbright.COM Jane
By reviewing this code you can see that Jane updated the SalesPersonUserName to “Dick” mistakenly when she updated the CustomerEmail for CompanyName “ABC Company”. By changing SalesPersonUserName to something other than her user name the filter predicate will no long allow Jane to see that record after she updated it. When Jane inserts a new record into the Customer table for Company “Rock the Dock” you can see she set the SalesPersonUserName to “Sally”. By doing this the filter predicate will not allow her to see this new record. You can verify this if you look at the output above.
In the next section I will show you how to keep Jane from changing or setting the SalesPersonUserName to something other than “Jane”.
Blocking Updates and Inserts at the Row Level
In order to keep Jane from updating or inserting records that she can’t see after the insert or update statement is executed, I will need to create a block predicate that keeps her from inserting our updating records that don’t have her user name in the SalesPersonUserName.
To implement the business logic requirements I’m going to alter my existing security profile by adding a couple of blocking predicates. Here is the code to do that:
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;
The first “ADD BLOCK PREDICATE” clause with the “AFTER UPDATE” clause, blocks updates to rows that don’t pass the filter predicate. The second “ADD BLOCK PREDICATE” clause, with the “AFTER INSERT” clause, blocks inserts of rows that don’t pass the filter predicate.
To verify these two new block predicates work I first run the code in the “Setting up some Test Data” section, and then I will run the following code:
-- UPDATE customer record EXECUTE AS USER = 'Jane'; UPDATE Customer SET CustomerEmail = 'Jack@ABC.COM', SalesPersonUserName = 'Dick' WHERE CustomerName = 'ABC Company'; -- INSERT new customer INSERT INTO Customer VALUES ('Rock The Dock','Rocky@RockTheDock.COM','Sally'); PRINT 'Jane''s Customers after UPDATE and INSERT'; SELECT CustomerName, CustomerEmail, SalesPersonUserName FROM Customer; REVERT;
When I run this code the UPDATE and INSERT statements each statement generates the following error:
The attempted operation failed because the target object
'RLS_DEMO.dbo.Customer' has a block predicate that conflicts with this
operation. If the operation is performed on a view, the block predicate might
be enforced on the underlying table. Modify the operation to target only the
rows that are allowed by the block predicate.
By implementing these two blocking predicates I was able to restrict Jane from inserting or updating rows when the values she was inserting or updating would cause the row to not be viewable by Jane because of the filter predicate.
Blocking Row Level UPDATE, and INSERT Statements
When using Row Level Security you need to make sure users can’t incorrectly insert, update or delete a row with a block predicate. The block predicate restricts users from updating an existing row, or inserting a new row that does not meet the block predicate. If you are interested in implementing Row Level Security with SQL Server 2016 remember to consider implementing block predicates along with filter predicates when users have insert, update, and delete permission, on tables for which filter predicates are defined.
