Simple Row Level Security for SQL Server 2016

Tuesday Jan 3rd 2017 by Greg Larsen
Share:

Have you ever had a need to place some simple row level security on a SQL Server table? Well now you can do that in SQL Server 2016 by using row level security.

Have you ever had a need to place some simple row level security on a SQL Server table?  Meaning you only want certain people to select or update a row.  Well now you can do that in SQL Server 2016 by using row level security.  To implement row level security all you need to do is create a filter predicate, which is just a function, and have a security policy that references that function.  Let me show you how to accomplish this.

Suppose you have a table named dbo.Customers that looks like this:

CREATE TABLE Customer(
       CustomerName varchar(100) NULL,
       CustomerEmail varchar(100) NULL,
       SalesPersonUserName varchar(20) NULL
);  


Where you only want the sales person to be able to update their rows of data in the Customer table, but you don’t want them to update records of other sales people.   A sales person row is identified by having their user name in the SalesPersonUserName column for a row.  To implement row level security in SQL Server 2016, all I need to do is restrict access to rows in the Customer table by creating the following FUNCTION and  SECURITY POLICY:

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();

 CREATE SECURITY POLICY FilterCustomer
 ADD FILTER PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
 ON dbo.Customer
 WITH (STATE = ON); 
 
 

By creating these two objects in a SQL Server 2016 environment I only allow a user to see or update records in the Customer table if their user name is in the SalesPersonUserName column.   

See all articles by Greg Larsen

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved