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.

row level security 1

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: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

The DBAs guide to stretch database

One of the new features in SQL Server 2016 – and there is a lot – is the ability to stretch the on premise databases to an Azure environment.

This blogpost will cover some of the aspects of this – including:

  • Primarily setup – how to get started
  • Monitoring state of databases that are in ‘stretch mode’
  • Daily work with stretch databases
  • Backup – what’s good to know

With the release of SQL Server 2016, the new feature called stretch database is also released. The feature lets you as a database administrator, make databases stretch (read: copy old data) to an Azure environment. The data is still able to respond to the normal queries that are used, in other way; there is no need to change the current setup for existing applications and other data-contracts to use this feature.

So when is the stretch database something you should consider

  • When you only sometimes need to query the historical data
  • The transactional data that are stored needs all historical data
  • The size of the database tables are growing out of control (but not an issue of bad design – then you need to take other actions…)
  • The backup times are too long in order to make the daily timeslots for maintenance

If you have one or more marks on the above list, then you have a database that are candidate for stretching into Azure.

A typical database in stretch mode are a transactional database with very large amounts of data (more than a billion rows) stored in a small number of tables.

The feature is applied to individual tables in the database – but a need for enabling the feature on database level is a prerequisite.

The limitations

No free goodies without limitations.

There are a list of limitations to a stretch database. Two types of limitations, datatypes and features.

The datatypes that are not supported for stretch database is:

  • filestream
  • timestamp
  • sql_variant
  • XML
  • geometry
  • geography
  • hierarchyid
  • CLR user-defined types (UDTs)

The features that are not supported:

  • Column Set
  • Computed Columns
  • Check constraints
  • Foreign key constraints that reference the table
  • Default constraints
  • XML indexes
  • Full text indexes
  • Spatial indexes
  • Clustered columnstore indexes
  • Indexed views that reference the table

Therefore, it is advisable to have an agreement with your developers if you plan to use the stretch feature. It is more likely that they can code without the above lists, but if they already have implemented features, and needs to work around them, then you are not in good standing for a while.

Security

In order to handle and maintain the stretch feature the current user must be a member of the db_owner group and CONTROL DATABASE permissions is needed for enabling stretch on database level.

Setup – how to get started

First, get an Azure account. If you not already have one. Then…

A small change in sp_configure is needed to get the feature ready.

EXEC sp_configure 'remote data archive' , '1';
 RECONFIGURE;

Enabling the database

It is a prerequisite to enable the database for stretch in order to enable its tables.

It is pretty straight forward – just right-click the database – choose tasks and select ‘Enable database for stretch’:

Then the SQL Server asks you to sign in to your Azure environment.

You need to choose a set of settings for your stretch database in Azure – including:

  • Location for the server
  • Credential for the server
  • Firewall rules

There is a summary page with all info – when complete, just hit ‘Finish’.

Note: the current applications and data-contracts are NOT able to access the data in Azure directly. The only way to access this data is through the normal on premise database. This database then makes the call to access the Azure database or not based on the current configuration and state of migration (see below for help in the latter).

Enabling tables for stretch

As easy as the database, so is the tables.

Right-click the table that you want to stretch – choose ‘Stretch’ and ‘Enable Stretch’.

Ass seen on the screenshot you can also here do the following tasks: Disable, Pause and Resume stretch. All 3 hopefully self-explainable.

Monitoring the state of databases and tables in stretch mode

There is released a list of Dynamic management views (DMVs) and updated to existing catalog views to help with the work of monitoring the state of stretch databases.

The DMV  sys.dm_db_rda_migration_status shows you the current state, in batches and rows, of the data in the stretched tables. For more information, refer to MSDN: sys.dm_db_rda_migration_status.

The catalog views sys.databases and sys.tables now also contains information about the stretch feature on each part respectively. See more as MSDN: sys.databases and sys.tables.

To view the remote databases and tables for stretch data use the two new catalog views sys.remote_data_archive_databases and sys.remote_data_archive_tables.

A big note for the current CTP 2.2 release:
This release only supports the stretch data for entire tables. This meaning that an architectural decision needs to be taken to move historical data to separate tables. I will assume that the final release will contain a query based configuration in order to find and detect the historical data to be moved to the Azure environment.

Backup and restore

The backup and restore is the same as before the stretch feature. The same strategy must be taken and also the same precautions for data storage in Azure.

One must keep in mind that the on premise backup only happens with on premise data.

The restore process adds a step to the checklist when restoring a database with stretch enabled.

Upon the end of restore a connection to the stretched database in Azure must be reestablished with the stored procedure sys.sp_reauthorize_remote_data_archive.

When this SP is executed, the vertical arrow on this illustration is reestablished:

Conclusion

The stretch database feature is a very nice and good feature to get with the release of SQL Server 2016. It enables the DBA to handle historical data and storage capacity without having the consult the developers and/or architects of new solutions. Also current applications and solutions can be configured to use this new feature.

This post makes a great place to begin with the stretch feature of SQL Server 2016. Personally, I hope that the final feature has a bit more configuration to handle the historical data.

Enlarge AdventureWorks2012

sql-banner

Just recently I had to have a big datawarehouse solution to test some performance optimization using BIML.
I could use the AdventureWorks2012 database, but I needed the clean datawarehouse tables in order to have minimum data maintennance when testing the BIML scripts.

I could not find it, and figures out it was faster to make my own.

So heavily inspired by this post from Jonathan Kehayias (blog), I’ve made a script that can be used to enlarge the dbo.FactInternetSales table.

The script creates a new table called dbo.FactInternetSalesEnlarged and copies data from dbo.FactInternetSales into it with a randomizer. Exploding the data to a 100 times bigger table – est. 6 mio rows.

Get the script here:

EnlargeAdventureWorksDW2012

Happy coding 🙂

en_USEnglish
da_DKDanish en_USEnglish