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

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


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

Featured Database Articles

MS SQL

Posted April 4, 2016

Row Level Security with SQL Server 2016: Part 1 – Allow Access to Only a Subset of Rows Using Row Level Security

By Greg Larsen

Have you ever wanted an easier way to provide security at the record level? If so, well now you can easily do this with the Row Level Security feature that is being introduced with SQL Server 2016.  In this article I will introduce you to how easy it is to use this new feature as a way to provide user access to a specific set of records.

What is Row Level Security?

Row Level Security (RLS) is a concept that provides security at the row level within the database layer, instead of at the application layer.  RLS is accomplished by using a function and the new security policy feature that is being rolled out with SQL Server 2016.    With this new feature you can implement RLS without even changing your application code.  

In order to enforce RLS on a SQL Server table SQL you need to be able to build an inline table value function that will restrict rows based on a WHERE predicate.  Typically that WHERE predicate is based off the database user associated with the SQL Server command being executed.  When the inline table value function is associated with a security policy the SQL Server engine is able to filter out rows based on the results of the function. 

To understand this concept better let me define a business problem, and then resolve the problem by implementing RLS using the new CREATE SECURITY POLICY feature that is coming out with SQL Server 2016. 

Business Problem

Suppose you currently allow sales people to view the customer account information for your company without any row level restriction. The table that contains customer account information looks like this:

CREATE TABLE [dbo].[Customer](
       [CustomerName] [varchar](100) NULL,
       [CustomerEmail] [varchar](100) NULL,
       [SalesPersonUserName] [varchar](20) NULL
);

Currently the Sales people browse the customer information by running the following SELECT statement:

SELECT CustomerName, CustomerEmail, SalesPersonUserName
FROM Customer;

Management has realized that some sales people have not only been looking at their individual customer records, but they have also been looking at other sales person’s customers,  in hopes of stealing those customers away from the current sales person.  Therefore I’ve have been asked to make sure when sales people run the SELECT statement above, the command will only return the customer information for records where the SalesPersonUserName is equal to the login for the salesperson. 

Customer Data

For testing purposes the following script generates a database, database users, my dbo.Customer table, and some sample data that I will use for testing the SQL Server 2016 Row Level Security feature:

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','Manger@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

Solution to Business Problem

To build a solution so each salesperson can only see their customers I will be utilizing a new SQL Server 2016 feature known as Record Level Security (RLS).   In order to build my record level security requirement for my dbo.Customer table I will be creating a function and a security policy. 

My function will be used to filter out rows based on the database user.   Each sales person logs on to SQL Server with their own SQL account, which means each sales person has a different database user in my RLS_Demo database.  Therefore my function is able to use the user_name function to drive the filtering process for my RLS example.  Below is the code I will use to create my filtering predicate function:

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

The function code above accepts a parameter named @FilterColumnName, and then uses this parameter to filter out the rows based on the database user, using the user_name function.    You might be asking yourself how this function will be used to filter rows from my customer table.

It will do that by associating this function with a security policy that I will create by running the following code:

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

In the security policy I defined above you can see I added a FILTER PREDICATE that referenced my dbo.fn_RowLevelSecurity function.  By creating this security policy the SQL Server engine will make sure every time that a database user runs a SQL command that references the dbo.Customer table that the filter predicate dbo.fn_SecureCustomerData function will also be executed, thus enforcing the RLS rules I defined in the filter. 

To see this in action let me run the following code to test my new RLS setup in my RLS_DEMO database for all three of my different databases users (Jane, Dick and Sally) that have accesses to the dbo.Customer table in my RLS_DEMO database.  To run my demo I will run the following code:

EXECUTE AS USER = 'Jane';
PRINT 'Jane''s Customers';
SELECT CustomerName, CustomerEmail, SalesPersonUserName
FROM Customer;
REVERT;
 
EXECUTE AS USER = 'Dick';
PRINT 'Dick''s Customers';
SELECT CustomerName, CustomerEmail, SalesPersonUserName
FROM Customer;
REVERT;
 
EXECUTE AS USER = 'Sally';
PRINT 'Sally''s Customers';
SELECT CustomerName, CustomerEmail, SalesPersonUserName
FROM Customer;
REVERT;

When I run this code I get the following output:

Jane's Customers
CustomerName                    CustomerEmail            SalesPersonUserName
 ------------------------------ ------------------------ --------------------
ABC Company                     Manger@ABC.COM          Jane
Info Services                   info@AInfaSerice.COM     Jane
Star Brite                      steve@starbright.COM     Jane
 
Dick's Customers
CustomerName                    CustomerEmail            SalesPersonUserName
 ------------------------------ ------------------------ --------------------
Washing-R-Us                    HeadWasher@washrus.COM   Dick
Blue Water Utilities            marketing@bluewater.COM  Dick
 
Sally's Customers
CustomerName                    CustomerEmail            SalesPersonUserName
 ------------------------------ ------------------------ --------------------
 Rainy Day Fund                  Tom@rainydayfund         Sally

As you can see, by looking at this output, the SELECT statement that was run when the database user was set to Jane returned 3 rows and each row had the value ‘Jane’ in the SalesPersonUserName column.  The user Dick returned just 2 rows and Sally only had one Customer record associated with her user name. 

Reusing the Predicate Function for other Security Policies

If you review the predicate function dbo.fn_RowLevelSecurity that I created above you would see that that function didn’t reference my dbo.Customer.  This function was able to restricted rows on the dbo.Customer table, because the security policy FilterCustomer referenced that predicate function when I created this policy.   

Since my predicate filter logic didn’t filter on a specific table I can reuse that filter to restrict row level access from other tables, provided they require the same filter logic.   To demonstrate this let me create and populate a new table then associate my new table with same dbo.fn_RowLevelSecurity filter predicate function using the following code:

CREATE TABLE Supplier(
       SupplierName varchar(100) NULL,
       SupplierEmail varchar(100) NULL,
       SalesPersonUserName varchar(20) NULL
);  
GO
GRANT SELECT ON dbo.Supplier TO Jane;
GRANT SELECT ON dbo.Supplier TO Dick;
GRANT SELECT ON dbo.Supplier TO Sally;
GO
INSERT INTO Supplier VALUES 
   ('ABC Parts','Maanger@ABC_Parts.COM','Jane'), 
   ('Cool Tech','info@CoolTech.COM','Jane'),
   ('US Printing','info@USPrinting.COM','Dick'),                                         
   ('Widget NW','marketing@WidgetNW.COM','Sally');
GO
CREATE SECURITY POLICY FilterSupplier
ADD FILTER PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Supplier
WITH (STATE = ON);

Now that I created this new security policy named FilterSupplier let me verify that my Supplier table will be filtering rows using the same predict function that I used to filter the dbo.Customer table by running the following code.  Run this code yourself to verify that my new security policy FilterSupplier does in fact filter out rows correctly when each database user tries to select rows from the dbo.Supplier table:

EXECUTE AS USER = 'Jane';
PRINT 'Jane''s Suppliers';
SELECT * FROM dbo.Supplier;
REVERT;
 
EXECUTE AS USER = 'Dick';
PRINT 'Dick''s Suppliers';
SELECT * FROM dbo.Supplier;
REVERT;
 
EXECUTE AS USER = 'Sally';
PRINT 'Sally''s Suppliers';
SELECT * FROM dbo.Supplier;
REVERT;

Limitations

As with any new feature there are a number of limitation.   Here are a few of those limitations:

  • Side-channel attacks might lead to users determining data values for rows for which they don’t have access.
  • You cannot create an indexed view on top of a table that has a security policy defined.
  • Because RLS is implemented via a function, it is possible that the query optimize will re-write queries that use column store indexes so batch mode might not be used. 
  • RLS is incompatible with FileStream.
  • RLS is incompatible with Polybase.

For more information about Row-Level Security and limitations with RLS you can read this MSDN document: https://msdn.microsoft.com/library/dn765131.aspx

Summary

As you can see, implementation of row level security can be done easily and without changing the existing application.  This new feature will allow you to add record level security rules in your database design.  If you need to provide record level security then you should consider rolling out those record level security predicates as soon as you have migrated or implemented your application in a SQL Server 2016 environment.    

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


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

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM