Empire Data Systems

Row Level Security with SQL Server 2016

Daniel AG by Daniel A G

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


Ever wanted an easier way to provide security at the record level? If so, now you can easily do this with the Row Level Security feature that is being introduced with SQL Server 2016.

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 predicates 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. 


Let us define a business problem, and then resolve the problem by implementing RLS using the new CREATE SECURITY POLICY feature that is co 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. 


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  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  record level security requirement for dbo.Customer table we will be creating a function and a security policy. 


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 that will use to create 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 customer table.It will do that by associating this function with a security policy that 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 defined above can see we 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 us run the following code to test 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 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 run this code ,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 created above you would see that that function didn’t reference my dbo.Customer.  This function was able to restricted rows on the dbo.Customertable, because the security policy FilterCustomer referenced that predicate function when I created this policy.  


Since 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 create and populate a new table then associate new table with same dbo.fn_RowLevelSecurity filter predicate functionusing 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 we have 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.

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. 



Statistics Says It All

16

Years of Experience

3000

Gratified Students

100

Training Batches

9600

Training Hours

Subscribe

Please subscribe our technical blog to get recent updates.

Empire Data Systems

Social Links