Row Level Security (RLS) has been implemented in SQL Server 2016 for both on-premise and v12 of Azure instances.
The problem this solves is: a company with multiple applications accessing sensitive data in one or more tables.
How do you ensure the data being read or written is only the data that login is authorized to see? In the past, this has been accomplished with a complicated series of views or functions, and there’s no guarantee a bug or malicious user wouldn’t be able to bypass those measures. With Row Level Security, it doesn’t matter what privileges you have (including sysadmin) or how you try to access the data.
How it Works
Row Level Security has two options: You can either FILTER the rows or BLOCK the operation entirely. The BLOCK functionality is not yet implemented in CTP 2.4, but the FILTER logic works like a charm.
The steps are very simple:
1 – Figure out what you’re going to associate with your users and data. You will need to create some link between your data and a login’s or user’s properties. Something that will allow the engine to say This Row is ok for This User.
2 – Create a Function defining the relationship between users and the data.
3 – Create a Security Policy for the function and table(s). You can use the same policy on multiple tables or views.
Once the Security Policy has been created, every query or DML operation on the tables or views you’re filtering will automatically have the function applied to the WHERE or HAVING clause. You can see the filter working by reviewing the execution plan as well. SQL Server will generate the Plan Hash value with the filtering logic in place. This allows Plan Re-Use with Row Level Security, and it’s a big improvement over Oracle’s implementation which doesn’t do this (as of Oracle 10g, the last time I worked with it) :-). See the bottom of this post for an example of the same query with RLS turned on & off.
What is particularly nice about these policies is that the data is filtered regardless of the user’s privileges. A sysadmin or other superuser who disables the policy is just an Audit log review away from having to explain what they were doing.
Row Level Security Walk Through
This is an example of setting up an RLS system for the Credit Card data in the AdventureWorks database. After this is completed, only users associated with a Business Entity in the Person.Person table will be able to see or update any credit card information, and the data they can touch will be limited to just their business.
Step 1: Add user_name column to Person.Person table
In this example, I’m associating the user_name() function’s value for each login with the BusinessEntityID. Of course, you can use any value you want, as long as you can access it from a SELECT statement in a Schema-Bound function. This means many system tables are off-limits.
ALTER TABLE person.person
ADD UserName nvarchar(128) NULL;
— Associate some person.person rows with a login too.
SET UserName = ‘Business1’
BusinessEntityID IN (301, 303, 305);
Step 2: Create Users to Test
I’m just creating a login named Business1 to demonstrate this. Note that the user has db_owner in AdventureWorks
USE [master] GO
CREATE LOGIN [business1] WITH PASSWORD=N'******', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
USE [AdventureWorks] GO
CREATE USER [business1] FOR LOGIN [business1] GO
USE [AdventureWorks] GO
ALTER ROLE [db_owner] ADD MEMBER [business1] GO
Step 3: Create Function to Filter Data
This function finds all credit cards for the user_name() running the query. Any values not returned by this function will be inaccessible to this user.
CREATE FUNCTION [Sales].[fn_FindBusinessCreditCard] (@CreditCardID INT)
1 AS result
person.person p INNER JOIN
sales.PersonCreditCard pcc ON p.BusinessEntityID = pcc.BusinessEntityID INNER JOIN
sales.CreditCard cc ON pcc.CreditCardID = cc.CreditCardID
cc.CreditCardID = @CreditCardID AND
p.UserName = user_name();
Step 4: Create a Security Policy
This creates a security policy on the Sales.CreditCard table.
CREATE SECURITY POLICY sales.RestrictCreditCardToBusinessEntity
ADD FILTER PREDICATE sales.fn_FindBusinessCreditCard(CreditCardID)
WITH (STATE = ON);
Step 5: Test Away
For all of the following examples, You should be logged in as the Business1 user who can only see 3 credit cards. In reality, there are 19,118 rows in that table.
--Will return three records
Sales.PersonCreditCard pcc INNER JOIN
Sales.CreditCard cc ON cc.CreditCardID = pcc.CreditCardID
— Will only update three records
ExpYear = ‘2020’
These are the execution plans for the above query with Row Level Security turned on and off:
Turned On: (and missing an index…)
Discover more about our expertise in SQL Server.
Interested in working with Scott? Schedule a tech call.