Problem

Suppose we have a SQL Server table which stores supplier and order information. This data is critical to our business and we want to restrict access for some employees. We want employees to only see the orders they processed based on their employee ID. Do we have to build custom logic with either a view or lookup tables supported by custom stored procedures? I have heard SQL Server 2016 has new features. Are there any features in SQL Server 2016 that can meet this need?

Solution

SQL Server 2016 has introduced Row Level Security (RLS) which is a feature that enables fine grained control over access to rows in a table. RLS allows you to easily control which users can access which data with complete transparency to the application. This enables us to easily restrict the data based on the user identity or security context.

Sample Data for SQL Server 2016 Row Level Security

To see how it works first we will create a test table and insert some sample values.

Create table dbo.Orders
(
Supplier_Code int,
[Supplier_Name] varchar(10),
[Orderdate] datetime,
[OrderQuantity] int,
[ProcessedBy] Varchar(10)
)           
 
 -- Sample data
Insert into dbo.orders values(101,''AXP Inc'',''2015-08-11 00:34:51:090'',1789,''LAX'')
Insert into dbo.orders values(102,''VFG Inc'',''2014-01-08 19:44:51:090'',767,''AURA'')
Insert into dbo.orders values(103,''ZAD Inc'',''2015-08-19 19:44:51:090'',500,''ZAP'')
Insert into dbo.orders values(102,''VFG Inc'',''2014-08-19 19:44:51:090'',1099,''ZAP'')
Insert into dbo.orders values(101,''AXP Inc'',''2014-08-04 19:44:51:090'',654,''LAX'')
Insert into dbo.orders values(103,''ZAD Inc'',''2015-08-10 19:44:51:090'',498,''LAX'')
Insert into dbo.orders values(102,''VFG Inc'',''2015-04-17 19:44:51:090'',999,''LAX'')
Insert into dbo.orders values(101,''AXP Inc'',''2015-08-21 19:44:51:090'',543,''LAX'')
Insert into dbo.orders values(103,''ZAD Inc'',''2015-08-06 19:44:51:090'',876,''LAX'')
Insert into dbo.orders values(102,''VFG Inc'',''2015-08-26 19:44:51:090'',665,''LAX'')

As a point of reference, here is the sample data:

SQL Server 2016 Predicate Function for Row Level Security

Based on our requirements, we will create a predicate function:

Create Function fn_securitypredicateOrder (@processedby sysname)
returns table
with Schemabinding
as
return select 1 as [fn_securityPredicateOrder_result]
from 
dbo.orders
where @processedby = user_name()  -- it will be Filter applied while running the query

In the next step we need to define a Security Policy that will use the predicate function created above:

Create security Policy fn_security
add Filter Predicate
dbo.fn_securitypredicateOrder(processedby)
on dbo.orders

Now lets create some test users for which we want to give the access in this case LAX , AURA and ZAP which is the user in the processedby column of the dbo.orders table.

If we execute the query under the security context of the users the output will be:

So the SQL Server users can see the records processed by them per the filter logic.

SQL Server Execution Plan for RLS

Now to see how this works we will have a look at the execution plans of the query created after and before RS Policy.

Note: We have to give Grant Showplan permission to view the execution plan for the user.

So if we look at the execution plan we can see the query is now executed as:

Select * from dbo.orders where processedby=user_name()

--User_name is the Security Context of the User executing the query 

While without having the RS Security in place it just creates the table scan operator to view the records.

Modifying SQL Server 2016 Row Level Security

Here is how to disable SQL Server Row Level Security for a particular policy:

Alter Security Policy fn_security with (State = off)

Here is how to drop the filter and security policy:

Drop Security Policy fn_security
Drop Function dbo.fn_securitypredicateOrder 

Additional Options for SQL Server 2016 Row Level Security

As a second example, let''s create a more complex predicate function. In this example, we want employees to only view their own orders processed within last year. Here is that logic:

Create Function fn_securitypredicateOrder (@processedby sysname,@Orderdate datetime)
returns table
with Schemabinding
as
return select 1 as [fn_securityPredicateOrder_result]
from 
dbo.orders
where @processedby= user_name()
and @orderdate= getdate()-365

Create security Policy fn_security
add Filter Predicate
fn_securitypredicateOrder(processedby,Orderdate)
on dbo.orders

As an example, AURA has two records that are older than 1 year: