By: Daniel Farina | Comments | Related: > SQL Server 2016
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', '[email protected]', N'6975550142', 'John');
INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Terri Duffy', N'819-555-0175', '[email protected]', N'8195550175', 'John');
INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Roberto Tamburello', N'212-555-0187', '[email protected]', N'2125550187', 'Peter');
INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Rob Walters', N'612-555-0100', '[email protected]', N'6125550100', 'Peter');
INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Gail Erickson', N'849-555-0139', '[email protected]', N'8495550139', 'Peter');
INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Jossef Goldberg', N'122-555-0189', '[email protected]', N'1225550189', 'Monica');
INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Dylan Miller', N'181-555-0156', '[email protected]', N'1815550156', 'Monica');
INSERT INTO Customers.Customers (Name, Phone, Email, CreditCard, UserName)
VALUES ('Diane Margheim', N'815-555-0138', '[email protected]', 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.
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.
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
- You can download an evaluation copy of SQL Server 2016 for free from this link: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.
- If you need to implement Cell Level Security too, you can take a look at my previous tip Implementing SQL Server Row and Cell Level Security.
- For those of you who don't know what an Inline Table Valued Function is you can read the following article on Technet: Inline User-Defined Functions.
- For more tips about SQL Server Security check out SQL Server Security Tips Category.
- This tip will introduce you to the new feature of SQL Server 2016 named Dynamic Data Masking: Use Dynamic Data Masking in SQL Server 2016 CTP 2.1.
- Stay tuned to the SQL Server 2016 Tips Category for more information about this release of SQL Server.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
