Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Implement Row Level Security Natively with SQL Server 2016


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

One of the new features of SQL Server 2016 is native support for Row Level Security. In this tip I will introduce you to the concept of Row Level Security and show you how to implement this new feature.

Solution

These days information is the most valuable asset of every enterprise whether it is public or private. To help protect your data, SQL Server 2016 offers new features: Always Encrypted, Dynamic Data Masking and Row Level Security. In this tip, we will cover Row Level Security.

What is Row Level Security?

One way to understand Row Level Security is to think about an implicit WHERE clause for the underlying table. In fact, in previous versions of SQL Server if you wanted to implement Row Level Security you had to rely on Views in which you add a WHERE filter on a column in the table used as a security identifier.

How Does SQL Server 2016 Row Level Security Work

The SQL Server 2016 implementation of Row Level Security follows the previous definition, but without the need for a view. Instead the filter is enforced by a Table Valued Function where SQL Server does an implicit INNER JOIN of the base table and the Table Valued Function. The function must be an Inline Table Valued Function created with SCHEMABINDING, so the function is not dropped or altered accidently.

Querying a table with Row Level Security enabled is transparent; you won't notice that the resulting data is filtered like when you query a view. It is so transparent that even if you are the database owner or the DBA and the security policy is enabled, you can't see the data if you don't comply with the policy. This is something to note because when you implement Row Level Security with views the database owner and the DBA were still able to access the base table data.

Sample Implementation of SQL Server 2016 Row Level Security

Now I will show you the steps to implement Row Level Security with SQL Server 2016. Suppose we want users to only see their own customer information.

First we need to create a sample database.

USE [master]
GO

CREATE DATABASE [sampleDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'sampleDB', FILENAME = N'C:\MSSQL\sampleDB.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'sampleDB_log', FILENAME = N'C:\MSSQL\sampleDB_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

When you implement Row or Cell Level Security it's highly recommended that you have one schema for the security definition separate from the schema that holds the data. The next script will create two schemas, one for the base table and the other for the security definition.

USE [sampleDB]
GO

CREATE SCHEMA Customers
GO
CREATE SCHEMA SecurityInfo
GO

The following script will create three users without logins for test purposes.

USE [sampleDB]
GO

CREATE USER John WITHOUT LOGIN WITH DEFAULT_SCHEMA =Customers
GO

CREATE USER Peter WITHOUT LOGIN  WITH DEFAULT_SCHEMA =Customers
GO

CREATE USER Monica WITHOUT LOGIN  WITH DEFAULT_SCHEMA =Customers
GO

Since we have two separate schemas for the data and the security definition we can assign the user permissions to the schema that holds the data.

USE [sampleDB]
GO

GRANT SELECT ON SCHEMA :: Customers TO John
GRANT SELECT ON SCHEMA :: Customers TO Peter
GRANT SELECT ON SCHEMA :: Customers TO Monica
GO

GRANT INSERT ON SCHEMA :: Customers TO John
GRANT INSERT ON SCHEMA :: Customers TO Peter
GRANT INSERT ON SCHEMA :: Customers TO Monica
GO

GRANT UPDATE ON SCHEMA :: Customers TO John
GRANT UPDATE ON SCHEMA :: Customers TO Peter
GRANT UPDATE ON SCHEMA :: Customers TO Monica
GO

The next script will create the Customers table in the Customers schema. The UserName column will store the name of the user that can see the row, so it will be our Row Level Security ID.

USE [sampleDB]
GO

IF OBJECT_ID('Customers.Customers',  'U') IS NOT NULL
  DROP TABLE Customers.Customers
GO

CREATE TABLE Customers.Customers
(ID int IDENTITY NOT NULL,
 Name nvarchar(50) NOT NULL,
 Phone NVARCHAR(20), 
 Email nvarchar(50), 
 CreditCard NVARCHAR(296), 
 UserName sysname NOT NULL, --Row Level Securiry ID
 PRIMARY KEY (ID))
GO

Now we insert some test data.

USE sampleDB
GO

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName) 
VALUES ('Ken Sánchez', N'697-555-0142', 'Ken@mail.com',   N'6975550142', 'John');

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Terri Duffy', N'819-555-0175', 'Terri@mail.com',   N'8195550175', 'John');

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName) 
VALUES ('Roberto Tamburello', N'212-555-0187', 'Roberto@mail.com',   N'2125550187', 'Peter');

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName) 
VALUES ('Rob Walters', N'612-555-0100', 'Rob@mail.com',   N'6125550100', 'Peter');

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName) 
VALUES ('Gail Erickson', N'849-555-0139', 'Gail@mail.com',   N'8495550139', 'Peter');

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName) 
VALUES ('Jossef Goldberg', N'122-555-0189', 'Jossef@mail.com',   N'1225550189', 'Monica');

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName) 
VALUES ('Dylan Miller', N'181-555-0156', 'Dylan@mail.com',   N'1815550156', 'Monica');

INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName) 
VALUES ('Diane Margheim', N'815-555-0138', 'Diane@mail.com',   N'8155550138', 'Monica');

As I previously said, SQL Server 2016 implements Row Level Security with an Inline Table Valued Function. So the next script will create that function in the SecurityInfo schema. A requirement is that the function returns 1 if the user can see the row.

USE [sampleDB]
GO

IF OBJECT_ID('SecurityInfo.fn_CustomersSecurity', 'IF') IS NOT NULL
BEGIN
DROP FUNCTION SecurityInfo.fn_CustomersSecurity
END
GO
 
CREATE FUNCTION SecurityInfo.fn_CustomersSecurity(@UserName AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS

RETURN SELECT 1 AS IsAccessGranted 
WHERE @UserName = USER_NAME();

GO

At this point we haven't done anything new, we just created an ordinary table within a schema, some users and an ordinary Inline Table Valued Function. But the next script is what makes Row Level Security work. It contains a new instruction, CREATE SECURITY POLICY. This instruction will bind the table to the Inline Table Valued function that contains the security definition.

USE sampleDB
GO

IF OBJECT_ID('SecurityInfo.CustomersPolicy', 'SP') IS NOT NULL
BEGIN
DROP SECURITY POLICY SecurityInfo.CustomersPolicy
END
GO

CREATE SECURITY POLICY SecurityInfo.CustomersPolicy
ADD FILTER PREDICATE SecurityInfo.fn_CustomersSecurity(UserName) 
ON Customers.Customers
WITH (STATE= ON);

GO

Something interesting is that if you want to disable an existing Security Policy you don't need to drop it. You can use the ALTER SECURITY POLICY command to change its status like in the next query.

USE sampleDB
GO

-- Disables Security Policy
ALTER SECURITY POLICY SecurityInfo.CustomersPolicy WITH (STATE = OFF )

-- Enables Security Policy
ALTER SECURITY POLICY SecurityInfo.CustomersPolicy WITH (STATE = ON )
GO

Also you can create a Security Policy that affects multiple tables by adding more Filter Predicates. The next query shows the creation of a security policy named UserAccessRights in the SecurityInfo schema. As you can see the policy has three filter predicates, the first two binds the same security function to tables Customers.Customers and HR.Employees and the last filter binds the fn_DepartmentAccess function to the Finances.BankAccounts table.


CREATE SECURITY POLICY SecurityInfo.UserAccessRights
ADD FILTER PREDICATE SecurityInfo.fn_UserAccess(VendorID) 
ON Customers.Customers,
ADD FILTER PREDICATE SecurityInfo.fn_UserAccess(EmployeeID) 
ON HR.Employees,
ADD FILTER PREDICATE SecurityInfo.fn_DepartmentAccess(DepartmentID) 
ON Finances.BankAccounts
WITH (STATE= ON);

GO

Something to note is the fact that you can have two different security policies referencing the same table, but only one can be enabled.

Now with the security policy enabled, let's execute a select statement on the table impersonating the users we created early and with the "sa" user account that doesn't comply with the Security Policy.

USE [sampleDB]
GO

SELECT * FROM customers.Customers;

EXECUTE AS USER = 'Monica'
SELECT * FROM customers.Customers;
REVERT

EXECUTE AS USER = 'John'
SELECT * FROM customers.Customers;
REVERT

EXECUTE AS USER = 'Peter'
SELECT * FROM customers.Customers;
REVERT

As you can see in the next image, when the SELECT statement is executed as "sa" it returns an empty result set and when we execute the same SELECT impersonating each user we only see the rows the user is meant to see.

Different Output For Different Users.

Side-Channel Attacks

Basically a Side-Channel Attack is an attempt to access the hidden data by a malicious user that knows how the security mechanism is implemented. In our case, we know how SQL Server parses and executes the queries. The trick is to force the query to raise an exception, like an overflow or a divide by zero error. This type of attack won't show you the underlying data, but will allow you to infer the data.

Take for instance the next three queries. In all of them I tried to raise a Divide By Zero exception. The first and the second queries will fail if the table contains a row with UserName equal to "Marta" and "Peter" respectively. Since our table doesn't have a row with UserName equal to "Marta" the first query won't raise an exception like the second query. The third query will test for the existence of a credit card number 6975550142 in the table and will fail because that credit card number exists.

USE sampleDB
GO

SELECT  *
FROM    Customers.Customers
WHERE   1 / ( CASE WHEN UserName = 'Marta' THEN 0
                   ELSE 1
              END ) = 1
GO

-- Divide By Zero Error!
SELECT  *
FROM    Customers.Customers
WHERE   1 / ( CASE WHEN UserName = 'Peter' THEN 0
                   ELSE 1
              END ) = 1
GO

-- Divide By Zero Error!
SELECT * FROM customers.Customers  
WHERE   1 / ( CASE WHEN CreditCard = '6975550142' THEN 0
                   ELSE 1
              END ) = 1
GO

The next image is a screen capture of the previous queries execution.

Infering Data With Side-Channnel Attack

Security Policies Related Catalog Views

This new feature also has two new Catalog Views:

  • sys.security_policies: Returns a row for each Security Policy in the database.
  • sys.security_predicates: Returns a row for each Security Predicate in the database. You can Join this view with sys.security_policies to get the predicates for each Security Policy.
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools