SQL Server 2016 - How to Protect Your Data Using Row Level Security (RLS)

Views: 1.384 views

In this article

Reading Time: 10 minutes

Speak guys !!
In this article, I would like to introduce to you a very interesting security feature that Microsoft SQL Server has gained from the 2016 release, which is Row Level Security (RLS) or Row Level Security.

How Row Level Security (RLS) Works

View content

Row Level Security allows you to control access to rows in a table according to the user or group you are part of, as if a filter had been applied to the query WHERE to limit which records it can view. For the user querying 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 data from the table. .

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

It is noteworthy that there are 2 types of RLS usage:

Line Filter (FILTER PREDICATE): Allows you to apply a row filter to queries made with SELECT, returning only the records that the user can view
BLOCK PREDICATE: Allows you to block certain row-level operations that the user in question could not perform (eg INSERT into a table using a different User_ID)

How to implement Row Level Security (RLS)

View content

To implement RLS on 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.

Creation of test data for 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

USE [dirceuresende]

------------------------------------------------------------------------

-- 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

)

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 for testing and permissions
Now I will create some users in the database to test RLS and give them 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]

CREATE USER [dirceu.resende] WITHOUT LOGIN

CREATE USER [tiago.neves] WITHOUT LOGIN

CREATE USER [fabricio.lima] WITHOUT LOGIN

CREATE USER [vithor.silva] WITHOUT LOGIN

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 aggregate the functions that will be created for use with RLS. Because you will need to create 1 function for each criterion or filter that you want to apply RLS and 1 policy to each table, it is more organized that all these functions are in a dedicated schema.

USE [dirceuresende]
GO

CREATE SCHEMA [rls]
GO

USE [dirceuresende]

CREATE SCHEMA [rls]

Creation of the fncId_User function
Using the fncId_User function, I can apply a filter to all tables (if I create policies for it) 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 that is SELECTing the table.

Remember that the filter is applied even to sysadmin users. That is, 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 cannot 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

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

CREATE FUNCTION [rls].fncId_Usuario (

@Id_Usuario INT

)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT 1 AS Retorno

WHERE @Id_Usuario = (SELECT Id_Usuario FROM dbo.Usuarios WHERE Cd_Usuario = USER_NAME())

Policy rls creation.
With the command below, we will apply the policy rls. Orders using the fncId_User function and thus enable 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)

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

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 to the practice tests and see how row-level security really 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

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:

Another example for Row Level Security (RLS)

View content

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

Role and policy creation

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)

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

CREATE FUNCTION [rls].fncUsuario (

@Login NVARCHAR(100)

)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT 1 AS Retorno

WHERE @Login = USER_NAME()

OR IS_MEMBER('db_owner') = 1

OR IS_SRVROLEMEMBER('sysadmin') = 1

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

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

-------------------------------------------------------------------------------

-- 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:

Using Row Level Security (RLS) BLOCK PREDICATE

View content

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

Remember 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

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

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]

Now, let's impersonate the user “dirceu.resende” and try to insert a user with another login in 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)
)

EXECUTE AS USER = 'dirceu.resende'

INSERT INTO dbo.Usuarios

(

Cd_Usuario,

Nm_Usuario

)

VALUES

(

'edvaldo.castro', -- Cd_Usuario - nvarchar(60)

'Edvaldo Castro' -- Nm_Usuario - varchar(60)

)

Result:

Msg 33504, Level 16, State 1, Line 4
The attempted operation failed because the target object 'dirceuresende.dbo.Usuarios' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

As you can 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” was allowed 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'

EXECUTE AS USER = 'dirceu.resende'

UPDATE dbo.Usuarios

SET Nm_Usuario = 'Teste'

WHERE Cd_Usuario = 'fabricio.lima'

Result:

Msg 33504, Level 16, State 1, Line 17
The attempted operation failed because the target object 'dirceuresende.dbo.Usuarios' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

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

EXECUTE AS USER = 'dirceu.resende'
GO

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

EXECUTE AS USER = 'dirceu.resende'

UPDATE dbo.Usuarios

SET Nm_Usuario = 'Teste'

WHERE Cd_Usuario = 'dirceu.resende'

Result:

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

Best Practices for Row Level Security

View content

Avoid type conversions to predicate functions to avoid potential runtime errors.
Avoid using other functions within the filtering function to avoid performance degradation. The query optimizer will try 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 excess table joins in predicate functions to maximize performance.
Avoid creating filtering functions that depend on certain session settings that are applied by using the SET command (NULL Comparisons / Operations, Day First Week functions, and functions that depend on the current language or DATEFORMAT).

Row Level Security and its impact on performance

View content

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 its function, can even cause performance issues according to the number of uses.

Execution plan WITHOUT RLS enabled

RLS COM execution plan enabled

How to disable Row Level Security (RLS)

View content

To disable RLS, simply use the syntax below:

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

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

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

DROP SECURITY POLICY [rls].Usuarios
GO

DROP FUNCTION [rls].fncUsuario
GO

DROP SECURITY POLICY [rls].Usuarios

DROP FUNCTION [rls].fncUsuario

Row Level Security (RLS) and feature compatibility

View content

Overall, row-level security will work as expected between 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 an RLS-protected table statistics, the user must be the table owner or must be a member of the sysadmin, db_owner, or db_ddladmin role.
Filestream: RLS is not compatible with Filestream.
Polybase: RLS is not compatible with Polybase.
Memory-Optimized Tables: The function used as a security predicate in 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 issued at creation time.
Indexed Views: In general, security policies can be created over views, and views can be created over tables that are joined by RLS. However, indexed views not they can be created over tables that have an RLS security policy because index row searches would bypass the policy.
Change Data Capture (CDC): CDC can allow whole line information to be leaked since, even using RLS, all changes made will be stored in the history table, and any user with permission in that table can view this information.
Change Tracking: Change Tracking can leak the primary row key that must be filtered out to users with SELECT and VIEW CHANGE TRACKING permissions. Actual data values do not leak; just the fact that column A has been updated / inserted / deleted for row with primary key B. This is a problem if the primary key contains a confidential element, such as a Social Security Number. However, in practice, this CHANGETABLE is almost always joined to the original table for more recent 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 presented to enforce row-level security and prevent the loss of row primary keys that must be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.
Columnstore Indexes: RLS supports clustered and nonclustered 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 tables that use locking predicates. Filter predicates support partitioned views.
Temporal tables: Time tables are RLS compliant. However, security predicates in the current table are not automatically replicated in the history table. To apply a security policy to the current and historical tables, you must individually add a security predicate to each table.

References: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security

That's it folks!
A hug and see you next.

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

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

How Row Level Security (RLS) Works

How to implement Row Level Security (RLS)

Another example for Row Level Security (RLS)

Using Row Level Security (RLS) BLOCK PREDICATE

Best Practices for Row Level Security

Row Level Security and its impact on performance

How to disable Row Level Security (RLS)

Row Level Security (RLS) and feature compatibility

You may also like ...

Leave a Comment Cancel reply

How Row Level Security (RLS) Works

How to implement Row Level Security (RLS)

Another example for Row Level Security (RLS)

Using Row Level Security (RLS) BLOCK PREDICATE

Best Practices for Row Level Security

Row Level Security and its impact on performance

How to disable Row Level Security (RLS)

Row Level Security (RLS) and feature compatibility

You may also like ...

SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM)

Performing a textual search on all columns of all tables in SQL Server

SQL Server - How to identify how to change the default schema for database users

Leave a Comment Cancel reply