Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted January 3, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Using A Block Predicate to Restrict Row Updates and Inserts

By Greg Larsen

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



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.