SQL Server 2016 - How to protect your data using Row Level Security (RLS)

Row Level Security allows you to control access to rows in a table according to the user or group they are part of, as if a filter had been applied to the WHERE of the query to limit which records they can view. For the user who is performing queries on the database, the data filtering process is completely transparent, so that he cannot realize that, although he is executing a SELECT * FROM table, the query is not returning all the data in the table.

This simplifies the design and security of systems and applications, ensuring that, for example, employees can access only those rows of data that are relevant to their department or restrict customer access to only data relevant to that customer's company. All of this is done at the database level, without having to make any changes to the application.

It is worth mentioning that there are 2 types of use of RLS:

• Line filter (FILTER PREDICATE): Allows you to apply a row filter to queries performed with SELECT, returning only records that the user can view
• Blocking operations (BLOCK PREDICATE): Allows you to block certain operations at row level, which the user in question could not perform (Ex: INSERT into a table using a User_Id different from theirs)

To implement RLS in your tables, you will create a function to filter records and use the command CREATE SECURITY POLICY to force the use of this function in queries performed by the table.

Creating test data for the example

USE [dirceuresende]
GO

------------------------------------------------------------------------
-- Criação das tabelas de Usuários e Pedidos
------------------------------------------------------------------------

IF (OBJECT_ID('dbo.Usuarios') IS NOT NULL) DROP TABLE dbo.Usuarios
CREATE TABLE dbo.Usuarios (
    Id_Usuario INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Cd_Usuario NVARCHAR(50) NOT NULL,
    Nm_Usuario NVARCHAR(100) NOT NULL
)

INSERT INTO dbo.Usuarios
(
    Cd_Usuario,
    Nm_Usuario
)
VALUES ('dirceu.resende', 'Dirceu Resende'), ('tiago.neves', 'Tiago Neves'), ('fabricio.lima', 'Fabrício Lima'), ('vithor.silva', 'Vithor Silva')


IF (OBJECT_ID('dbo.Pedidos') IS NOT NULL) DROP TABLE dbo.Pedidos
CREATE TABLE dbo.Pedidos (
    Id_Pedido INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Dt_Pedido DATETIME DEFAULT GETDATE() NOT NULL,
    Id_Usuario INT NOT NULL CONSTRAINT [FK_Usuario] FOREIGN KEY(Id_Usuario) REFERENCES dbo.Usuarios(Id_Usuario),
    Id_Cliente INT NOT NULL,
    Id_Produto INT NOT NULL
)
GO

INSERT INTO dbo.Pedidos
(
    Dt_Pedido,
    Id_Usuario,
    Id_Cliente,
    Id_Produto
)
SELECT
    (SELECT DATEADD(DAY, CAST(RAND() * 365 AS INT), '2017-01-01')) AS Dt_Pedido,
    (SELECT TOP 1 Id_Usuario FROM dbo.Usuarios ORDER BY NEWID()) AS Id_Usuario,
    (SELECT CAST(RAND() * 1000 AS INT)) AS Id_Cliente,
    (SELECT CAST(RAND() * 1000 AS INT)) AS Id_Produto
GO 15

SELECT * FROM dbo.Pedidos

Result:

Creation of users used in tests and permissions
Now I will create some users in the database to test RLS and give these users permission to access the Orders and Users tables.

CREATE USER [dirceu.resende] WITHOUT LOGIN
GO

CREATE USER [tiago.neves] WITHOUT LOGIN
GO

CREATE USER [fabricio.lima] WITHOUT LOGIN
GO

CREATE USER [vithor.silva] WITHOUT LOGIN
GO

GRANT SELECT ON dbo.Pedidos TO [dirceu.resende]
GRANT SELECT ON dbo.Usuarios TO [dirceu.resende]
GRANT SELECT ON dbo.Pedidos TO [tiago.neves]
GRANT SELECT ON dbo.Usuarios TO [tiago.neves]
GRANT SELECT ON dbo.Pedidos TO [fabricio.lima]
GRANT SELECT ON dbo.Usuarios TO [fabricio.lima]
GRANT SELECT ON dbo.Pedidos TO [vithor.silva]
GRANT SELECT ON dbo.Usuarios TO [vithor.silva]

Creation of the “rls” schema
As a good practice, I will also create a new schema (rls) to add the functions that will be created for use with RLS. As you will need to create 1 function for each criteria or filter that you want to apply RLS to and 1 policy for each table, it is more organized for all these functions to be in a dedicated schema.

USE [dirceuresende]
GO

CREATE SCHEMA [rls]
GO

Creation of the fncId_Usuario function
Using the fncId_Usuario function, I can apply a filter to all tables (if I create policies for this) that have a field with the user identifier. In this function, the User_Id that is in the table in question is informed and the function will search the user table for the identifier (User_Id) of the logged in user who is performing the SELECT on the table.

It is worth remembering that the filter is applied even for sysadmin users. In other words, even the sysadmin user will only be able to view records where the User_Id is the same as that user's Id (or he will not be able to view any records).

IF (OBJECT_ID('rls.fncId_Usuario') IS NOT NULL) DROP FUNCTION [rls].fncId_Usuario
GO

CREATE FUNCTION [rls].fncId_Usuario (
    @Id_Usuario INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS Retorno
    WHERE @Id_Usuario = (SELECT Id_Usuario FROM dbo.Usuarios WHERE Cd_Usuario = USER_NAME())
GO

Creation of the rls.Pedidos policy
With the command below, we will apply the rls.Pedidos policy using the fncId_Usuario function and thus activate Row Level Security (RLS) in the Orders table.

IF (OBJECT_ID('rls.Pedidos') IS NOT NULL) DROP SECURITY POLICY [rls].Pedidos
GO

CREATE SECURITY POLICY [rls].Pedidos
    ADD FILTER PREDICATE [rls].fncId_Usuario(Id_Usuario) ON dbo.Pedidos WITH (STATE = ON) 

Row Level Security Tests:
Now let's get down to practical testing and see how Row Level Security actually works.

EXEC('SELECT * FROM dbo.Pedidos') AS USER = 'dirceu.resende'
EXEC('SELECT * FROM dbo.Pedidos') AS USER = 'fabricio.lima'
EXEC('SELECT * FROM dbo.Pedidos') AS USER = 'tiago.neves'
EXEC('SELECT * FROM dbo.Pedidos') AS USER = 'vithor.silva'

-- Consulta utilizando um login sysadmin (Sem personificar outro usuário)
SELECT * FROM dbo.Pedidos

Result:

I ended up demonstrating a very common use of RLS, but little documented in articles, which is applying RLS using an ID from another table. Using the same previous basis, I will quickly demonstrate a simpler use, which is using the user's own login without using a SELECT to retrieve any information.

Creation of the role and policy

IF (OBJECT_ID('rls.fncUsuario') IS NOT NULL) DROP FUNCTION [rls].fncUsuario
GO

CREATE FUNCTION [rls].fncUsuario (
    @Login NVARCHAR(100)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS Retorno
    WHERE @Login = USER_NAME()
    OR IS_MEMBER('db_owner') = 1
    OR IS_SRVROLEMEMBER('sysadmin') = 1
GO

IF (OBJECT_ID('rls.Usuarios') IS NOT NULL) DROP SECURITY POLICY [rls].Usuarios
GO

CREATE SECURITY POLICY [rls].Usuarios
    ADD FILTER PREDICATE [rls].fncUsuario(Cd_Usuario) ON dbo.Usuarios WITH (STATE = ON) 

Real RLS Tests

-------------------------------------------------------------------------------
-- Testes do Row Level Security em um cenário real
-------------------------------------------------------------------------------

EXEC('SELECT * FROM dbo.Usuarios') AS USER = 'dirceu.resende'
EXEC('SELECT * FROM dbo.Usuarios') AS USER = 'tiago.neves'
EXEC('SELECT * FROM dbo.Usuarios') AS USER = 'fabricio.lima'
EXEC('SELECT * FROM dbo.Usuarios') AS USER = 'vithor.silva'

-- Consulta utilizando um login sysadmin (Sem personificar outro usuário)
SELECT * FROM dbo.Usuarios

Result:

After demonstrating the use of FILTER PREDICATE, which allows you to filter records from a query (SELECT), I will now demonstrate the use of BLOCK PREDICATE, which prevents a certain DML operation (INSERT, UPDATE and DELETE)

It is worth remembering that we can use the FILTER and BLOCK predicates at the same time, in the same policy.

Policy source code

IF (OBJECT_ID('rls.Usuarios_Blk') IS NOT NULL) DROP SECURITY POLICY [rls].Usuarios_Blk
GO

CREATE SECURITY POLICY [rls].Usuarios_Blk
    ADD BLOCK PREDICATE [rls].fncUsuario(Cd_Usuario) ON dbo.Usuarios AFTER INSERT,
    ADD BLOCK PREDICATE [rls].fncUsuario(Cd_Usuario) ON dbo.Usuarios AFTER UPDATE 
WITH (STATE = ON)

-- Libera as permissões para permitir o insert e update na tabela Usuarios para o login "dirceu.resende"
GRANT INSERT, UPDATE ON dbo.Usuarios TO [dirceu.resende]
GO

Now, let's impersonate the user “dirceu.resende” and try to insert a user with another login into the table other than “dirceu.resende”:

EXECUTE AS USER = 'dirceu.resende'
GO

INSERT INTO dbo.Usuarios
(
    Cd_Usuario,
    Nm_Usuario
)
VALUES
(
    'edvaldo.castro', -- Cd_Usuario - nvarchar(60)
    'Edvaldo Castro' -- Nm_Usuario - varchar(60)
)

Result:

As you could see, it was not possible to insert the record, as BLOCK PREDICATE prevented the insertion since the Cd_Usuario that was being inserted was not what the user “dirceu.resende” had permission to insert.

The same happens when we try to perform an UPDATE:

EXECUTE AS USER = 'dirceu.resende'
GO

UPDATE dbo.Usuarios
SET Nm_Usuario = 'Teste'
WHERE Cd_Usuario = 'fabricio.lima'

Result:

However, if we try to update the logged-in user's own records, we see that the operation is carried out successfully:

EXECUTE AS USER = 'dirceu.resende'
GO

UPDATE dbo.Usuarios
SET Nm_Usuario = 'Teste'
WHERE Cd_Usuario = 'dirceu.resende'

Result:

As we created 2 more conditions in the fncUsuario function to allow sysadmin and/or db_owner users to access the data, these 2 user profiles will also be able to insert/update the data normally:

• Avoid type conversions in predicate functions to avoid potential runtime errors.
• Avoid using other functions within the filtering function to avoid performance degradation. The query optimizer will attempt to detect direct recursions, but there is no guarantee that it will find indirect recursions (that is, where a second function calls the predicate function).
• Avoid using excessive table joins in predicate functions to maximize performance.
• Avoid creating filtering functions that depend on certain session settings applied using the SET command (NULL comparisons/operations, first day of the week functions and functions that depend on the current language or DATEFORMAT).

As I said above, when using the Row Level Security (RLS) feature, we are applying a filter to our query through a policy and a filter function. This already causes the query execution plan to change and, depending on the logic of your function, it can even cause performance problems depending on the number of uses.

Execution plan WITHOUT RLS enabled

Execution plan WITH RLS enabled

To disable RLS, simply use the syntax below:

ALTER SECURITY POLICY [rls].Usuarios WITH (STATE = OFF)

And to delete objects from RLS, you can use the commands below:

DROP SECURITY POLICY [rls].Usuarios
GO

DROP FUNCTION [rls].fncUsuario
GO

Generally speaking, row-level security will work as expected across features. However, there are some exceptions. This section documents several notes and limitations for using row-level security with certain SQL Server features.

• DBCC SHOW_STATISTICS: Statistics reports can display information that would be protected by a security policy. For this reason, to view statistics for a table protected with RLS, the user must be the owner of the table or must be a member of the sysadmin, db_owner, or db_ddladmin role.
• Filestream: RLS is not supported by Filestream.
• Polybase: RLS is not supported by Polybase.
• Memory-Optimized Tables: The function used as a security predicate on a memory-optimized table must be defined using the WITH NATIVE_COMPILATION option. With this option, language features not allowed by memory-optimized tables will be banned and the appropriate error will be thrown at creation time.
• Indexed Views: In general, security policies can be created over views, and views can be created over tables that are associated by RLS. However, indexed views no can be created on top of tables that have an RLS security policy, as row searches through the index would bypass the policy.
• Change Data Capture (CDC): CDC can allow information to be leaked from entire rows, since, even using RLS, all changes made will be stored in the history table, and any user with permission to this table will be able to view this information
• Change Tracking: Change Tracking may leak the primary key of rows that must be filtered for users with SELECT and VIEW CHANGE TRACKING permissions. Actual data values ​​are not leaked; just the fact that column A was updated/inserted/deleted for the row with primary key B. This is a problem if the primary key contains a sensitive element such as a Social Security Number. However, in practice, this CHANGETABLE is almost always joined to the original table to obtain newer data.
• Full-Text Search: A drop in performance is expected in queries that use the Full-Text Search and Semantic Search functions due to an extra join introduced to enforce row-level security and avoid losing the primary keys of rows that must be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.
• Columnstore Indexes: RLS supports both clustered and non-clustered columnstore indexes. However, because row-level security applies to a function, it is possible that the optimizer can modify the query plan so that it does not use batch mode.
• Partitioned Views: Locking predicates cannot be defined on partitioned views, and partitioned views cannot be created on top of tables that use locking predicates. Filter predicates support partitioned views.
• Temporal tables: Temporal tables are compatible with RLS. However, security predicates in the current table are not automatically replicated in the history table. To apply a security policy to the current and history tables, you must individually add a security predicate to each table.