Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Row Level Security Example


By:   |   Read Comments (6)   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


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_code] 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:

Sample data from the dbo.orders table

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

SQL Server User Creation and Permissions Granted

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

SQL Server 2016 Row Level Security Verification

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.

Query Plan for SQL Server 2016 Row Level Security

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.

SQL Server Query Plan is a Table Scan with Row Level Security Implemented

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:

Final Data for a Single User with Row Level Security Implemented
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, March 02, 2017 - 1:54:07 AM - jen Back To Top

 Thanks! i have worked out something on it, pass userid(from AD login) from SSRS and it run perfectly. But I have to create more than 200 of user because most of the users can only view the report of their branch and we will have roughly 5 users a branch! Do you think this is practical?


Tuesday, February 28, 2017 - 8:29:47 AM - Rajendra gupta Back To Top

 Jen,  user will be able to see only his data even if he selects different parameter.  We can use it in applications also where we are directly taking user id as input and display the results set. 

 


Monday, February 27, 2017 - 9:11:24 PM - jen Back To Top

 SOrry dumb me. what is the different if I simply select * from order when processedby = 'AUX? 

since we still need to speficy name from the query execute('select * from order') as user = 'AUX'

 


Monday, September 19, 2016 - 4:17:55 PM - Eric Zierdt Back To Top

 in your first code sample you have Supplier_Code twice.  I think one is meant to be Supplier_name

 


Thursday, August 25, 2016 - 9:22:22 PM - Victor Back To Top

 Msg 4512, Level 16, State 3, Line 1
Cannot schema bind security policy 'fn_security' because name 'fn_securitypredicateOrder' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


Saturday, September 19, 2015 - 4:36:14 AM - manu Back To Top

Very useful feature in security contrained environments. Thanks for sharing..


Learn more about SQL Server tools