By: Rajendra Gupta | Last Updated: 2015-09-18 | Comments (6) | SQL Server 2016
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?
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:
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.
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:
- As you look to fine tune your security consider the opportunities available with Row Level Security in SQL Server 2016.
- Check out these additional resources:
- SQL Server 2016 Row Level Security Introduction
- SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting
- Implement Row Level Security Natively with SQL Server 2016
- Check out all of the SQL Server 2016 Tips
- Check out all of the SQL Server Security Tips
Last Updated: 2015-09-18
About the author
View all my tips