Row Level Security in SQL Server 2016 – Allow access to Subset of Records.
There might be requirement to provide security at the record or row level, you will able to do this Row Level Security feature that is introduced with newer version of SQL Server i.e.; SQL Server 2016. In this post, I will guide you how to implement this feature to provide access to the user for the specific set of records.
This Concept of Row Level Security is to provide security at the record / row level of a table in the database instead of application layer. This can be accomplished with SQL Server 2016 without changing code at the database or server layer.
To enforce this Row Level Security on a table in the Database, I will create a table valued function that will restrict rows based on a where clause, this where clause is based on database user who is executing the SQL Server command being executed. If this inline table value function is associated with a security policy of SQL Server engine will filter rows based on the function which returns the results or rows.
Let me define a business problem to understand this concept, then resolve this by implementing Row Level Security that is coming with SQL Server 2016.
As of today, in a company all sales persons are able to view the customer information from a table without any restriction. Sales Persons can view all customers who are not associated to him and also view customers of other sales persons. Sales Person can steal the customers of other Sales Person.
The table that contains customer account information looks like this:
Create Table Customer
(
FirstName varchar (20),
MiddleName varchar (20),
LastName varchar (20),
EmailId varchar (100),
Address1 varchar (50),
Address2 varchar (50),
City varchar (20),
State varchar (20),
Country varchar (20),
SalesPersonName varchar (20)
)
Any Sales Person in the company can view the Customer Information/data by executing the SELECT Statement
Select * from Customer
or
Select FirstName, MiddleName, LastName, EmailId, Address1, Address2, City, State, Country, SalesPersonName
From
Customer
For the demo purpose, we will try to write few scripts for creating users, customer table and sample data in the customer table for implementing Row Level Security feature of SQL Server 2016
Step 1: Create Database
Use Master
Go
Create Database Sample
Go
Step 2: Create users
Use Sample
Go
Create user SalesPerson1 without login;
Go
Create user SalesPerson2 without login;
Go
Create user SalesPerson3 without login
Go
Step 3: Create Table and Insert Sample data
Use Sample
Go
Create Table Customer
(
FirstName varchar (20),
MiddleName varchar (20),
LastName varchar (20),
EmailId varchar (100),
Address1 varchar (50),
Address2 varchar (50),
City varchar (20),
State varchar (20),
Country varchar (20),
SalesPersonName varchar (20)
)
GO
insert into Customer values (‘FirstName 1′,’MiddleName 1′,’LastName 1’, ‘FirstName1@yahoo.com’,’FirstName1 Address1′,
‘LastName1 Address2′,’City1′,’State1′,’Country1′,’SalesPerson1’)
GO
insert into Customer values (‘FirstName 2′,’MiddleName 2′,’LastName 2’, ‘FirstName2@yahoo.com’,’FirstName2 Address1′,
‘LastName2 Address2′,’City2′,’State2′,’Country2′,’SalesPerson2’)
GO
insert into Customer values (‘FirstName 3′,’MiddleName 3′,’LastName 3’, ‘FirstName3@yahoo.com’,’FirstName3 Address1′,
‘LastName3 Address2′,’City3′,’State3′,’Country3′,’SalesPerson3’)
GO
insert into Customer values (‘FirstName 4′,’MiddleName 4′,’LastName 4’, ‘FirstName4@yahoo.com’,’FirstName4 Address1′,
‘LastName4 Address2′,’City4′,’State4′,’Country4′,’SalesPerson4’)
GO
insert into Customer values (‘FirstName 5′,’MiddleName 5′,’LastName 5’, ‘FirstName5@yahoo.com’,’FirstName5 Address1′,
‘LastName5 Address2′,’City5′,’State5′,’Country1′,’SalesPerson2’)
GO
Solution:
To build a solution to the problem as stated above, I will be using the new feature of SQL Server 2016 called Row Level Security. I will be creating a function and security policy.
The function I create will filter out rows based on the database user. Each sales person logs on to SQL Server with their own SQL Account. Every sales person has different user in my Sample database.
CREATE FUNCTION fn_RLSecurity (@FilterColumn sysname)
RETURNS TABLE
WITH SCHEMABINDING
as
RETURN SELECT 1 as fn_SecureCustomerInfo
where @FilterColumn = user_name();
The above function requires input parameter @FilterColumn, and it will filter the records based on database user, using the user_name function.
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_RLSecurity(SalesPersonName)
ON dbo.Customer
Using security policy, I have defined the FILTER PREDICATE that is referenced dbo.fn_RLSecurity function. By creating this security policy, whenever the database user runs a SQL command that references the dbo.Customer table that filter predicate dbo.fn_SecureCustomerInfo function will be executed, thus enforcing the Row Level Security rules.
Let me run the code in my Sample Database, for all the three database users accessing customer table.
EXECUTE AS USER = ‘SalesPerson1’;
SELECT * FROM Customer;
REVERT;
Result:
FirstName LastName SalesPersonName
——————– ——————– ——————–
FirstName 1 LastName 1 SalesPerson1
FirstName 4 LastName 4 SalesPerson1
(2 row(s) affected)
EXECUTE AS USER = ‘SalesPerson2’;
SELECT * FROM Customer;
REVERT;
FirstName LastName SalesPersonName
——————– ——————– ——————–
FirstName 2 LastName 2 SalesPerson2
FirstName 5 LastName 5 SalesPerson2
(2 row(s) affected)
EXECUTE AS USER = ‘SalesPerson3’;
SELECT * FROM Customer;
REVERT;
FirstName LastName SalesPersonName
——————– ——————– ——————–
FirstName 3 LastName 3 SalesPerson3
(1 row(s) affected)
Limitations:
1) Row Level Security is incompatiable with FileStream
2) Row Level Security is incompatiable with Polybase
3) We cannot create an indexed view on top of a table that has a security policy defined.
