SQL 2016 — New Features (2) — Row Level Security

As a running up to the new release of SQL 2016 I would like to share some research I did about the new features that will be available.
Last time I talked about Dynamic Data Masking. This time I will discuss another very interesting new feature namely Row Level Security.
We know Row Level Security on Analysis Service level. But Microsoft now also implemented it on Database Service level.

With the security we know in the current SQL versions (everything before SQL 2016) it is possible to grant or deny users to do select’s on tables, schemas,… to deny execution of stored procedures, … and so on. What was not possible is to hide certain rows for a certain user. At least not without any joins to other tables.

With this new feature in SQL it is possible. And through this post I would like to share how you can do this practically. Of course more detailed info can be found on Books Online.

Personally I find this new feature very interesting because it can decrease your development time for reports. Because you don’t need to implement all the security each time in all your queries that you are using. You only implement the security once on database level.

So my example is all about a DWH that stores the data of a supermarket. We will focus on a table that contains aggregated data.

Schema:

So we have 2 dimensions (Departments & Sales Regions), 1 fact table (Sales) and 1 table that keeps track of who can see what. (UserRights).
Below here you can find the script to generate the tables. Also 3 logins are created to test our functions.
As a personal best practice I like to keep everything that has to do with security in a seperate Schema.

Script:
[code lang=”sql”]
— 1. Schema Creation
CREATE SCHEMA DWH;
GO
CREATE SCHEMA [Security];
GO

— 2. Table Creation
IF OBJECT_ID(‘DWH.DimDepartment’) IS NOT NULL DROP TABLE DWH.DimDepartment
CREATE TABLE DWH.DimDepartment(
DepartmentID INT IDENTITY PRIMARY KEY,
DepartmentName NVARCHAR(50)
)

IF OBJECT_ID(‘DWH.DimSalesRegion’) IS NOT NULL DROP TABLE DWH.DimSalesRegion
CREATE TABLE DWH.DimSalesRegion(
SalesRegionID INT IDENTITY PRIMARY KEY,
SalesRegionName NVARCHAR(50)
)

IF OBJECT_ID(‘DWH.FactSales’) IS NOT NULL DROP TABLE DWH.FactSales
CREATE TABLE DWH.FactSales(
SalesID INT IDENTITY PRIMARY KEY,
DepartmentID INT,
SalesRegionID INT,
SalesDateID INT,
SalesAmount DECIMAL(18,8),
CONSTRAINT FK_FactSales_DepartmentID FOREIGN KEY (DepartmentID) REFERENCES DWH.DimDepartment(DepartmentID),
CONSTRAINT FK_FactSales_SalesRegionID FOREIGN KEY (SalesRegionID) REFERENCES DWH.DimSalesREgion(SalesRegionID)
)

IF OBJECT_ID(‘Security.UserRights’) IS NOT NULL DROP TABLE Security.UserRights
CREATE TABLE Security.UserRights(
UserRightsID INT IDENTITY PRIMARY KEY,
UserLogin NVARCHAR(50),
DepartmentID INT,
SalesRegionID INT
)

— 3. Table Data
INSERT INTO DWH.DimDepartment VALUES
(‘Ice Cream’),
(‘Soft Drinks’),
(‘Healthy Snacks’)

INSERT INTO DWH.DimSalesRegion VALUES
(‘Central Europe’),
(‘Eastern Europe’),
(‘Northern Europe’),
(‘Southern Europe’),
(‘Western Europe’)

INSERT INTO DWH.FactSales (DepartmentID, SalesRegionID, SalesDateID, SalesAmount)
SELECT DepartmentID, SalesRegionID, DateID, ABS(CAST(NEWID() AS binary(6)) %1000) AS SalesAmount
FROM DWH.DimDepartment
CROSS JOIN DWH.DimSalesRegion
CROSS JOIN (SELECT TOP 100 CONVERT(NVARCHAR, DATEADD(DAY,- ABS(CAST(NEWID() AS binary(6)) %1000) + 1, GETDATE()), 112) AS DateID FROM sys.columns) Dates
ORDER BY DateID

INSERT INTO Security.UserRights VALUES
(‘User1’, 1, NULL),
(‘User2’, NULL, 1),
(‘User3’, 1, 1)

— 3. Create Users & Grant permissions
CREATE USER User1 WITHOUT LOGIN;
CREATE USER User2 WITHOUT LOGIN;
CREATE USER User3 WITHOUT LOGIN;

GRANT SELECT ON DWH.FactSales TO User1;
GRANT SELECT ON DWH.FactSales TO User2;
GRANT SELECT ON DWH.FactSales TO User3;

GRANT SELECT ON DWH.DimDepartment TO User1;
GRANT SELECT ON DWH.DimDepartment TO User2;
GRANT SELECT ON DWH.DimDepartment TO User3;

GRANT SELECT ON DWH.DimSalesRegion TO User1;
GRANT SELECT ON DWH.DimSalesRegion TO User2;
GRANT SELECT ON DWH.DimSalesRegion TO User3;
[/code]

Our first step is to create a function that will tell us if the user can see the data (yes/no).
[code lang=”sql”]
CREATE FUNCTION [Security].RowLevelSecurityPredicate(@DepartmentId INT, @SalesRegionID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT TOP 1 1 RowLevelSecurityPredicateResult
FROM Security.UserRights
WHERE (DepartmentID = @DepartmentID OR SalesRegionID = @SalesRegionID)
AND UserLogin = USER_NAME();
GO
[/code]
This function has 2 parameters, our department and salesregion. By making use of these together with our logged on user, we filter our UserRights table. If this query returns data, it means the user can see the info. So 1 row is more then enough.

Next step is the new feature. Implementing the security. Because the function alone, will not do anything.

[code lang=”sql”]
CREATE SECURITY POLICY [Security].UserFilter
ADD FILTER PREDICATE [Security].RowLevelSecurityPredicate(DepartmentID, SalesRegionID)
ON DWH.FactSales,
ADD FILTER PREDICATE [Security].RowLevelSecurityPredicate(DepartmentID, NULL)
ON DWH.DimDepartment,
ADD FILTER PREDICATE [Security].RowLevelSecurityPredicate(NULL, SalesRegionID)
ON DWH.DimSalesRegion
WITH (STATE = ON);
GO
[/code]

Let’s get in the code.
First line is like for any sql object telling what we want to make. For row-level-security this is the SECURITY POLICY object.
Lines 2–3, 4–5 and 5–6 is your policy definition. For each table we want to filter we add a FILTER PREDICATE to tell which function can tell us what data can be shown or not. Your function accepts the columns from the table that you want to filter.
You can add as much PREDICATE’s as you want.
The last is to enable or disable a security policy. This can be done with a separate statement too:

[code lang=”sql”]
ALTER SECURITY POLICY [Security].UserFilter
WITH (STATE = OFF);

ALTER SECURITY POLICY [Security].UserFilter
WITH (STATE = ON);
[/code]

So what are the results?

[code lang=”sql”]
EXECUTE AS USER = ‘User1’;
SELECT DepartmentId, SalesRegionId, SUM(SalesAmount) AS SalesAmount FROM DWH.FactSales GROUP BY DepartmentId, SalesRegionId;
SELECT * FROM DWH.DimDepartment;
SELECT * FROM DWH.DimSalesRegion;
REVERT;
[/code]

[code lang=”sql”]
EXECUTE AS USER = ‘User2’;
SELECT DepartmentId, SalesRegionId, SUM(SalesAmount) AS SalesAmount FROM DWH.FactSales GROUP BY DepartmentId, SalesRegionId;
SELECT * FROM DWH.DimDepartment;
SELECT * FROM DWH.DimSalesRegion;
REVERT;
[/code]

[code lang=”sql”]
EXECUTE AS USER = ‘User3’;
SELECT DepartmentId, SalesRegionId, SUM(SalesAmount) AS SalesAmount FROM DWH.FactSales GROUP BY DepartmentId, SalesRegionId;
SELECT * FROM DWH.DimDepartment;
SELECT * FROM DWH.DimSalesRegion;
REVERT;
[/code]

So as you can see, it works nicely! The user can only see the data he is eligible to see.

What is even cooler about all of this, is that it not only works for SELECT statements but also for UPDATE.
Personally I’m really looking forward to implement this new feature into the solutions I will develop at client side.

Next blogpost will not be a new SQL Database feature, will talk about the fully revamped Reporting Service.