Row-level security never got this easy with SQL Server 2016

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

Row-level security provides fine-grained access control over a particular row in a table in the database. While selecting the data from the table, rows are filtered based on execution context of every query. What would be filtered can be decided and implemented by security architect and can be implemented by new objects provided in SQL Server 2016. If you have ever implemented similar logic in application, it might have been via some views on the table having filter condition or stored procedures. The logic can be simplified and contained within the database using row-level security feature. Simplest way to understand is that there is an additional hidden filter has been applied before data is shown to the user. The filter can be designed in such a way that super user can see all the rows. This would mean that a user can run SELECT * FROM myTable without a WHERE clause and view the only that data to which they have access to.

Before we jump into the demo, here are the few new terms which one need to learn to understand this feature.

Security Predicate: This is not a new object but an inline table valued function (inline TVF) which contains the logic of filtering the rows.

Security Policy: This is a new object which can be CREATE’ed ALTER’ed and DROP’ed. You can imagine this as a container of predicates which can be applied to tables. One policy can contain security predicate to many tables. A policy can be in an ON or OFF state.

Let’s sail through the demo to understand how this can be implemented.

Step 1: Create two users to mimic two employees. As per security standard, they should see only their own data. In below script, we are also creating sample database, table and inserting some data.

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
GO
CREATE USER Employee1 WITHOUT LOGIN;
GO
CREATE USER Employee2 WITHOUT LOGIN;
GO
USE RLSDemo
GO
CREATE TABLE Salary
    (
      EmployeeId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED ,
      EmployeeName varchar(50) NOT NULL ,
      Salary decimal(10, 4)
    )

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)

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 which we have applied in the query.

This is one of the very basic implementation can based on requirement, the Security Predicate can be made more complex.

Whenever there is a discussion of implementation, one of the question which might come up would be that “is there any performance overhead due to this” and simple answer to this is – “it depends on the predicate defined”

In SQL Server Management Studio, we can see it under “Security” available under 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, this feature would take care of custom filtering, in the form of a user defined table-valued function, and implementing it on the object for everyone. We can add more predicate in security policy and apply 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. With this simple demo of RLS, hope this blog gives you an idea about this feature.