Row-Level Security with SQL Server 2016


Security has been one of the focus areas of the Microsoft SQL Server product team. Almost all the releases either have some new security feature or enhancement of existing features. The same story continues with SQL Server 2016. There are many security features such as “Always Encrypted,” “Dynamic Data Masking,” “enhancement of Transparent Data Encryption,” and “Row-Level Security” added in SQL Server 2016. In this chapter, we will talk about the Row-Level security feature.

Row-level security provides fine-grain access control over a particular row in a table in the database. While selecting the data from the table, rows are filtered based on the execution context of every query. What should be filtered can be decided by a security architect and implemented using new objects provided in SQL Server 2016. If you are unfamiliar with the row-level security concept, you can read more about the basics at Microsoft's Developer's Network reference on row-level security.

If you have implemented a similar logic in an application, it might have been via views on the table having filter conditions or stored procedures. The logic you used can be simplified and contained within the database using the row-level security feature.

The simplest way to understand row-level security is that there is an additional hidden filter that is applied before data is shown to the user. The filter can be designed in such a way that a super user can see all the rows. This would mean that a user can run SELECT * FROM myTable without a WHERE clause and view only that data which they have authorized access to.

Before we jump into the demo, here are a couple new terms which one needs to learn to use row-level security.

Security Predicate:

This is not a new object but an inline table value function (inline TVF) which contains the logic for filtering the rows.

Security Policy:

This is a new object which can be CREATEed ALTERed and DROPed. You can imagine this as a container of predicates which can be applied to tables. One policy can contain security predicate for many tables. A policy can be in an ON or OFF state.

USE master
GO
IF DB_ID('RLSDemo') IS NOT NULL
BEGIN
ALTER DATABASE RLSDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE RLSDemo
END
GO
CREATE DATABASE RLSDemo
GO
USE RLSDemo
CREATE USER Employee1 WITHOUT LOGIN;
CREATE USER Employee2 WITHOUT LOGIN;
CREATE TABLE Salary ( EmployeeId INT IDENTITY(1, 1) NOT NULL
PRIMARY KEY CLUSTERED ,
EmployeeName varchar(50) NOT NULL ,
Salary decimal(10, 4)
)
GO
SET NOCOUNT ON
GO
INSERT INTO [Salary] ( EmployeeName, Salary ) VALUES
('Employee1', 900), ('Employee2', 1500)
GO

Step 2: Create schema which contains objects related to row-level security.

CREATE SCHEMA [RLS]
GO
CREATE FUNCTION RLS.fn_CanSeeSalary ( @UserName AS sysname )
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN( SELECT 1 AS 'CanSeeSalary' WHERE @UserName = USER_NAME())
GO
GRANT SELECT ON Salary TO PUBLIC
GO
USE RLSDemo
GO
CREATE SECURITY POLICY Security_Policy
ADD FILTER PREDICATE RLS.fn_CanSeeSalary(EmployeeName) ON
dbo.Salary WITH (STATE = ON

Step 3: Let’s do the testing.

EXECUTE ('SELECT * FROM Salary') AS USER = 'Employee1'
EXECUTE ('SELECT * FROM Salary') AS USER = 'Employee2'

As shown below, both employees can see their own data even if there is no filter applied in the query.

This is a very basic implementation based on a single requirement. The Security Predicate can be made more complex.

Whenever there is a discussion of implementation, one of the questions which might come up would be, “Is there any performance overhead due to this?” The simple answer is, it depends on the predicate defined.

In SQL Server Management Studio, we can see the predicate under “Security” available in each database.

Here are few new DMVs which can help in looking at security policies and security predicates.

SELECT * FROM sys.security_policies
GO
SELECT * FROM sys.security_predicates
GO

In summary, row-level security would take care of custom filtering in the form of a user-defined table-valued function, implementing it on the object for everyone. We can add more predicate in the security policy and apply a filter on multiple tables. Since we can turn the policy ON and OFF, it makes it easy to manage all filters at the same time.