// 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;
GO
USE RowFilter;
GO
CREATE USER userBrian WITHOUT LOGIN;
CREATE USER userJames WITHOUT LOGIN;
GO
A table with examples and grant select to the new users:
CREATE TABLE dbo.SalesFigures (
[userCode] NVARCHAR(10),
[sales] MONEY)
GO
INSERT INTO dbo.SalesFigures
VALUES ('userBrian',100), ('userJames',250), ('userBrian',350)
GO
GRANT SELECT ON dbo.SalesFigures TO userBrian
GRANT SELECT ON dbo.SalesFigures TO userJames
GO
Now we’ll add a filter predicate function as below:
CREATE FUNCTION dbo.rowLevelPredicate (@userCode as sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS rowLevelPredicateResult
WHERE @userCode = USER_NAME();
GO
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);
GO
That’s it.
Let’s test the results with the users added before:
EXECUTE AS USER = 'userBrian';
SELECT * FROM dbo.SalesFigures;
REVERT;
GO
This gives me 2 rows:
EXECUTE AS USER = 'userJames';
SELECT * FROM dbo.SalesFigures;
REVERT;
GO
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