RLS no SQL Server ( row level security)

Fala pessoal, tudo bem?
Hoje vou escrever um pouco sobre a funcionalidade RLS (row level security).
Ela apareceu no SQL 2016 (é meio antiga, mas precisei relembrar e fazer alguns testes, por isso estou escrevendo.. rs).

Basicamente o SQL Server vai restringir o acesso a determinados dados em nível de linha/registro de acordo com o usuário ou grupo do contexto. Esse acesso pode ser restringido mesmo se o usuário tiver a permissão de sysadmin e db_owner, que são os mais poderosos usuários dentro de uma instância e de um banco de dados, respectivamente, e nada precisa ser alterado na consulta para o usuário.

A RLS permite dois tipos de predicado de segurança.

• Os predicados de filtro filtram as linhas disponíveis para operações de leitura (SELECT, UPDATE e DELETE). Sim, update e delete tb fazem um select. 🙂
• Os predicados de bloqueio bloqueiam explicitamente as operações de gravação (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) que violam o predicado.

Beleza, mas o que são os predicados? Nesse caso, são basicamente as condições que precisam ser verdadeiras para os dados serem retornados.

Vamos a um breve exemplo e alguns testes.

Vou aproveitar a tabela tab_vendas criada no meu banco de dados ( o script está nesse post https://viniciusfonsecadba.wordpress.com/2023/07/09/otimizando-performance-em-procedures-no-sql-server/).

É permitido utilizar tanto login quanto usuário para a criação das regras, então vou utilizar o meu “DBA” que já existe e é sysadmin, vou criar um USR_RLS e atribuir a permissão de leitura em todo o banco e de insert, update e delete na tab_vendas.

-- Cria usuário
USE master 
GO
CREATE LOGIN USR_RLS WITH PASSWORD = 'teste123!@#' 
GO

USE dba
GO
CREATE USER USR_RLS FOR LOGIN USR_RLS 
GO

ALTER ROLE db_datareader ADD MEMBER USR_RLS;  
GO

-- Libera permissões do usuário
GRANT INSERT, UPDATE, DELETE ON tab_vendas TO USR_RLS

Em seguida criar uma tabela de usuários que irá vincular o usuário da execução com uma filial (id_filial da tabela tab_vendas).

-- Cria tabela de usuários
DROP TABLE IF EXISTS tab_usuarios
GO

CREATE TABLE tab_usuarios (id_usuario int PRIMARY key, login_usuario varchar(100), id_filial int)
GO

INSERT INTO tab_usuarios (id_usuario, login_usuario, id_filial)
VALUES(1,'dba',-1), 
(2,'vinicius',1), 
(3,'vinicius2',2),
(4,'vinicius3',3),
(5,'vinicius4',4),
(6,'vinicius5',5),
(7,'vinicius6',6),
(8,'USR_RLS',2)
GO

Com isso criado vou levantar algumas evidências e temos o mesmo resultado ao executar com o dba e com o usr_rls, confie em mim, vou postar apenas um.

-- total de registros da tabela 
SELECT id_filial, COUNT(*) qtd FROM dbo.tab_vendas GROUP BY id_filial
GO

Vamos a parte legal agora, a criação das regras do RLS.

Para funcionar, precisamos de um schema, uma função e uma política de segurança.

-- Criando o schema
CREATE SCHEMA RLSFiltro; 
GO

-- Cria a função com a regra 
IF (OBJECT_ID('RLSFiltro.fn_usuariofilial') IS NOT NULL) 
	DROP FUNCTION RLSFiltro.fn_usuariofilial
GO

CREATE FUNCTION RLSFiltro.fn_usuariofilial (@id_filial INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS Retorno
    WHERE @id_filial = (SELECT id_filial FROM dbo.tab_usuarios WHERE login_usuario = SUSER_NAME() OR login_usuario = USER_NAME())
GO

--Cria a politica de segurança na tabela tab_vendas
---DROP SECURITY POLICY FiltroFilial
CREATE SECURITY POLICY FiltroFilial
    ADD FILTER PREDICATE RLSFiltro.fn_usuariofilial (id_filial) ON dbo.tab_vendas,
    ADD BLOCK PREDICATE RLSFiltro.fn_usuariofilial(id_filial) ON dbo.tab_vendas
    AFTER INSERT--,AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE
WITH (STATE = ON);

Depois das regras criadas, testei com o usuário dba.

SELECT SUSER_NAME()

SELECT TOP 10 * FROM dbo.tab_vendas 

Fiz de propósito incluindo a id_filial igual a -1 na tabela tab_usuarios para o usuário dba. Então até tudo bem, apesar de ter demorado um tanto.

Então criei um índice em id_filial que não exisia.

CREATE INDEX ix_tab_vendas_id_filial ON tab_vendas (id_filial) WITH (DATA_COMPRESSION = page)
GO

Mas não ajudou em nada.

Vou terminar o teste da funcionalidade do RLS com uma consulta executada pelos usuários USR_LRS (que possui um login vinculado) e criar um novo usuário vinicius3 (sem login) .

USE dba
GO
CREATE USER vinicius3 WITHOUT LOGIN
GO

ALTER ROLE db_datareader ADD MEMBER vinicius3;  
GO

Usuários criados, vamos as consultas:

EXECUTE AS LOGIN = 'USR_RLS' -- Veja que o impersonate desse é pelo login
SELECT TOP 10 * FROM dbo.tab_vendas 
SELECT id_filial, COUNT(1) qtd FROM dbo.tab_vendas GROUP BY id_filial
REVERT

EXECUTE AS USER = 'vinicius3' -- Veja que o impersonate desse é pelo user
SELECT TOP 10 * FROM dbo.tab_vendas 
SELECT id_filial, COUNT(1) qtd FROM dbo.tab_vendas GROUP BY id_filial
REVERT

EXEC('SELECT TOP 10 * FROM dbo.tab_vendas') AS USER = 'vinicius3' -- é possível tb executar dessa forma

Vimos como funciona o filtro na consulta, porém, o usuário USR_LRS também tem permissão para INSERT, DELETE e UPDATE. Vai funcionar para qualquer id_filial?

Ao tentar executar o script abaixo, recebemos o erro, então certificamos que a regra vale tb para o insert, update e delete.

Msg 33504, Level 16, State 1, Line 183
The attempted operation failed because the target object ‘dba.dbo.tab_vendas’ 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.

EXECUTE AS LOGIN = 'USR_RLS'

INSERT INTO dbo.tab_vendas
(
    id_filial,    nome,    id_tipo,    data_cadastro,    status_entrega
)
VALUES
(   1,    -- id_filial - int
    NEWID(),    -- nome - varchar(100)
    1,    -- id_tipo - int
    DEFAULT, -- data_cadastro - datetime
    1     -- status_entrega - bit
    )
REVERT

Para exemplicar de outra forma e melhor o entendimento, e testar a funcionalidade e o desempenho criei views fazendo a mesma lógica do filtro pelo usuário, porém para testar precisei apagar a lógica do RLS.

CREATE OR ALTER VIEW VW_QTD_VENDAS_POR_FILIAL 
WITH SCHEMABINDING
AS 
SELECT v.id_filial, COUNT(1)  qtd
FROM dbo.tab_vendas v 
INNER JOIN dbo.tab_usuarios u ON u.id_filial = v.id_filial 
WHERE u.login_usuario = SUSER_NAME()
GROUP BY v.id_filial
GO

DROP SECURITY POLICY IF EXISTS  FiltroFilial
DROP FUNCTION IF EXISTS RLSFiltro.fn_usuariofilial
DROP SCHEMA IF EXISTS RLSFiltro; 
GO

Seguem os testes.

EXECUTE AS LOGIN = 'USR_RLS'
SELECT * FROM VW_QTD_VENDAS_POR_FILIAL 
revert

Por hoje é isso pessoal, espero que tenham entedido e cuidado com a implementação das regras com muita lógica e funções.

valeu e seguem as referências.

https://learn.microsoft.com/pt-br/sql/relational-databases/security/row-level-security?view=sql-server-ver16

SQL Server 2016 – Como proteger seus dados utilizando o Row Level Security (RLS)