Row level security in SQL Server 2016

With the release of SQL Server 2016 comes many great new features. One of these is the implementation of row level security in the database engine.

This blogpost will cover the aspects of this new feature – including:

Setup
Best practice
Performance
Possible security leaks

Introduction

The row level security feature was released earlier this year to Azure – following Microsoft’s cloud-first release concept.

A past big issue with the SQL Server engine was that in only understands tables and columns. Then you had to simulate security using secured views, stored procedures or table value functions. The problem here was to make sure that there were no way to bypass them.

With SQL Server 2016, this is no longer an issue.

Now the SQL Server engine handles the security policy in a central controlled area.

Setup and best practice

The Row-level security is based on a special inline table valued function. This function returns either a single row with a 1 or no rows based on the users rights to that specific row.

Let us take an example:

First of all, I’ll create a database and some users to test with:

CREATE DATABASE RowFilter;

USE RowFilter;

CREATE USER userBrian WITHOUT LOGIN;

CREATE USER userJames WITHOUT LOGIN;

A table with examples and grant select to the new users:

CREATE TABLE dbo.SalesFigures (

[userCode] NVARCHAR(10),

[sales] MONEY)

INSERT INTO dbo.SalesFigures

VALUES ('userBrian',100), ('userJames',250), ('userBrian',350)

GRANT SELECT ON dbo.SalesFigures TO userBrian

GRANT SELECT ON dbo.SalesFigures TO userJames

Now we’ll add a filter predicate function as below:

CREATE FUNCTION dbo.rowLevelPredicate (@userCode as sysname)

RETURNS TABLE

WITH SCHEMABINDING

RETURN SELECT 1 AS rowLevelPredicateResult

WHERE @userCode = USER_NAME();

This illustrates that the current user must have associated records in order to get any results. Notice that the functions does not have access to the rows itself.

Furthermore the function can contain joins and lookup tables in the where clause – but beware of the performance hit here. Look further down this post for more info.

The last thing to do is to add a filter predicate to the table dbo.SalesFigures:

CREATE SECURITY POLICY UserFilter

ADD FILTER PREDICATE dbo.rowLevelPredicate(userCode)

ON dbo.SalesFigures

WITH (STATE = ON);

That’s it.

Let’s test the results with the users added before:

EXECUTE AS USER = 'userBrian';

SELECT * FROM dbo.SalesFigures;

REVERT;

This gives me 2 rows:

EXECUTE AS USER = 'userJames';

SELECT * FROM dbo.SalesFigures;

REVERT;

This gives me 1 row:

The execution plan shows a new filter predicate when this row level security is added:

To clean up the examples.

USE master;

DROP DATABASE RowFilter;

Performance

Some might ask, “what about the performance – isn’t there a performance hit in this use of functions?”

The short answer is “It depends”.

If you only use a direct filter on the table there is very little to no impact on the performance. The filter is applied directly to the table as any other filter. Compared to the old way of doing the row filter with stored procedures or table valued functions this new approach is performing better.

If you plan to use lookup tables or joins in the predicate function, then you must beware of the helper tables’ indexes and how fast they can deliver data to the function. If the tables are large and slow performing (without indexes etc.) then you will experience bad performance in the row filter function. But that’s just like any other lookup or join that you might do in your solutions.

Best practices

There are some best practices given from Microsoft:

It is highly recommended to create a separate schema for the RLS objects (predicate function and security policy).
The ALTER ANY SECURITY POLICY permission is intended for highly-privileged users (such as a security policy manager). The security policy manager does not require SELECT permission on the tables they protect.
Avoid type conversions in predicate functions to avoid potential runtime errors.
Avoid recursion in predicate functions wherever possible to avoid performance degradation. The query optimizer will try to detect direct recursions, but is not guaranteed to find indirect recursions (i.e., where a second function calls the predicate function).
Avoid using excessive table joins in predicate functions to maximize performance.

Possible security leaks

This new row filter context can cause information leakage using some carefully codes queries.

Above example can be breached with the following query:

SELECT 1/([sales]-250) FROM dbo.SalesFigures

WHERE Usercode = 'userJames'

This will give an error: Divide by zero error encountered.

This will tell the user trying to access the table, that userJames has a sale of 250. So even though the row filter prevents users from accessing data that they are not allowed, hackers can still try to determine the data in the table using above method.

Conclusion

The new row level security feature has been very much a wanted feature for quite a while now, and with the function now in place, and planned to be released in the RTM version of SQL Server 2016, the DBA’s and other people working with security can use this out-of-the-box.

I hope this post makes a great start for you if you would like to try out the row level security function. Currently the feature is awailable in the latest CTP version (2.2) – which can be downloaded here:
SQL Server 2016 Community Technology Preview

Author
Recent Posts

Brian Bønk Rueløkke

Brian works as a Business Intelligence and Database architect at Rehfeld – part of IMS Health.

His work spans from the small tasks to the biggest projects. Engaging all the roles from manual developer to architect in his 11 years experience with the Microsoft Business Intelligence stack. With his two certifications MSCE Business Intelligence and MCSE Data Platform, he can play with many cards in the advisory and development of Business Intelligence solutions. The BIML technology has become a bigger part of Brians approach to deliver fast-track BI projects with a higher focus on the business needs.

View all posts by Brian Bønk Rueløkke

Latest posts by Brian Bønk Rueløkke (see all)

How to import flat files with a varying number of columns in SQL Server - February 22, 2017
Ready, SET, go – How does SQL Server handle recursive CTE’s - August 19, 2016
Use of hierarchyid in SQL Server - July 29, 2016