SQL Server 2016 - Cómo proteger tus datos usando Row Level Security (RLS)

Row Level Security permite controlar el acceso a las filas de una tabla según el usuario o grupo al que pertenecen, como si se hubiera aplicado un filtro al DÓNDE de la consulta para limitar qué registros pueden ver. Para el usuario que está realizando consultas en la base de datos, el proceso de filtrado de datos es completamente transparente, de modo que no puede darse cuenta de que, aunque esté ejecutando una tabla SELECT * FROM, la consulta no está devolviendo todos los datos de la tabla.

Esto simplifica el diseño y la seguridad de los sistemas y aplicaciones, garantizando que, por ejemplo, los empleados puedan acceder solo a aquellas filas de datos que son relevantes para su departamento o restringir el acceso del cliente solo a datos relevantes para la empresa de ese cliente. Todo esto se hace a nivel de base de datos, sin tener que realizar ningún cambio en la aplicación.

Cabe mencionar que existen 2 tipos de uso del RLS:

• Filtro de línea (FILTRO PREDICADO): Le permite aplicar un filtro de fila a las consultas realizadas con SELECT, devolviendo solo registros que el usuario puede ver
• Operaciones de bloqueo (PREDICADO DE BLOQUE): Le permite bloquear ciertas operaciones a nivel de fila, que el usuario en cuestión no pudo realizar (Ej: INSERTAR en una tabla usando un User_Id diferente al suyo)

Para implementar RLS en sus tablas, creará una función para filtrar registros y usará el comando CREAR POLÍTICA DE SEGURIDAD para forzar el uso de esta función en consultas realizadas por la tabla.

Creando datos de prueba para el ejemplo

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

Resultado:

Creación de usuarios utilizados en pruebas y permisos.
Ahora crearé algunos usuarios en la base de datos para probar RLS y les daré permiso para acceder a las tablas Pedidos y Usuarios.

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]

Creación del esquema “rls”
Como buena práctica, también crearé un nuevo esquema (rls) para agregar las funciones que se crearán para usar con RLS. Como necesitará crear 1 función para cada criterio o filtro al que desee aplicar RLS y 1 política para cada tabla, es más organizado que todas estas funciones estén en un esquema dedicado.

USE [dirceuresende]
GO

CREATE SCHEMA [rls]
GO

Creación de la función fncId_Usuario
Usando la función fncId_Usuario puedo aplicar un filtro a todas las tablas (si creo políticas para esto) que tengan un campo con el identificador del usuario. En esta función se informa el User_Id que está en la tabla en cuestión y la función buscará en la tabla de usuarios el identificador (User_Id) del usuario logueado que está realizando el SELECT sobre la tabla.

Vale la pena recordar que el filtro se aplica incluso para usuarios administradores de sistemas. En otras palabras, incluso el usuario administrador de sistemas solo podrá ver registros donde User_Id sea el mismo que el ID de ese usuario (o no podrá ver ningún registro).

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

Creación de la póliza rls.Pedidos
Con el siguiente comando aplicaremos la política rls.Pedidos usando la función fncId_Usuario y así activar Row Level Security (RLS) en la tabla Orders.

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) 

Pruebas de seguridad a nivel de fila:
Ahora pasemos a las pruebas prácticas y veamos cómo funciona realmente la seguridad de nivel de fila.

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

Resultado:

Terminé demostrando un uso muy común de RLS, pero poco documentado en artículos, que consiste en aplicar RLS usando una identificación de otra tabla. Utilizando la misma base anterior, demostraré rápidamente un uso más simple, que consiste en utilizar el propio inicio de sesión del usuario sin utilizar SELECT para recuperar ninguna información.

Creación del rol y política.

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) 

Pruebas reales de SPI

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

Resultado:

Después de demostrar el uso de FILTER PREDICATE, que le permite filtrar registros de una consulta (SELECT), ahora demostraré el uso de BLOCK PREDICATE, que evita una determinada operación DML (INSERT, UPDATE y DELETE).

Vale recordar que podemos utilizar los predicados FILTER y BLOCK al mismo tiempo, en la misma política.

Código fuente de la política

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

Ahora, suplantemos al usuario “dirceu.resende” e intentemos insertar en la tabla un usuario con otro inicio de sesión que no sea “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)
)

Resultado:

Como se pudo ver no fue posible insertar el registro, ya que BLOCK PREDICATE impedía la inserción ya que el Cd_Usuario que se estaba insertando no era lo que el usuario “dirceu.resende” tenía permiso para insertar.

Lo mismo sucede cuando intentamos realizar una ACTUALIZACIÓN:

EXECUTE AS USER = 'dirceu.resende'
GO

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

Resultado:

Sin embargo, si intentamos actualizar los propios registros del usuario que ha iniciado sesión, vemos que la operación se realiza correctamente:

EXECUTE AS USER = 'dirceu.resende'
GO

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

Resultado:

Como creamos 2 condiciones más en la función fncUsuario para permitir que los usuarios sysadmin y/o db_owner accedan a los datos, estos 2 perfiles de usuario también podrán insertar/actualizar los datos normalmente:

• Evite conversiones de tipos en funciones de predicados para evitar posibles errores de tiempo de ejecución.
• Evite el uso de otras funciones dentro de la función de filtrado para evitar la degradación del rendimiento. El optimizador de consultas intentará detectar recursiones directas, pero no hay garantía de que encuentre recursiones indirectas (es decir, cuando una segunda función llama a la función predicada).
• Evite el uso excesivo de combinaciones de tablas en funciones predicadas para maximizar el rendimiento.
• Evite crear funciones de filtrado que dependan de ciertas configuraciones de sesión aplicadas usando el comando SET (comparaciones/operaciones NULL, funciones del primer día de la semana y funciones que dependen del idioma actual o DATEFORMAT).

Como dije anteriormente, cuando utilizamos la función Seguridad de nivel de fila (RLS), estamos aplicando un filtro a nuestra consulta a través de una política y una función de filtro. Esto ya hace que el plan de ejecución de la consulta cambie y, dependiendo de la lógica de tu función, puede incluso causar problemas de rendimiento dependiendo de la cantidad de usos.

Plan de ejecución SIN RLS habilitado

Plan de ejecución CON RLS habilitado

Para deshabilitar RLS, simplemente use la siguiente sintaxis:

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

Y para eliminar objetos de RLS, puede utilizar los siguientes comandos:

DROP SECURITY POLICY [rls].Usuarios
GO

DROP FUNCTION [rls].fncUsuario
GO

En términos generales, la seguridad a nivel de fila funcionará como se espera en todas las funciones. Sin embargo, existen algunas excepciones. Esta sección documenta varias notas y limitaciones para usar la seguridad a nivel de fila con ciertas características de SQL Server.

• DBCC MOSTRAR_ESTADÍSTICAS: Los informes estadísticos pueden mostrar información que estaría protegida por una política de seguridad. Por este motivo, para ver las estadísticas de una tabla protegida con RLS, el usuario debe ser el propietario de la tabla o debe ser miembro de la función sysadmin, db_owner o db_ddladmin.
• Flujo de archivos: Filestream no admite RLS.
• Polibase: Polybase no admite RLS.
• Tablas optimizadas para memoria: La función utilizada como predicado de seguridad en una tabla optimizada para memoria debe definirse mediante la opción CON NATIVE_COMPILATION. Con esta opción, las funciones de idioma no permitidas por las tablas optimizadas para memoria se prohibirán y se generará el error correspondiente en el momento de la creación.
• Vistas indexadas: En general, las políticas de seguridad se pueden crear sobre vistas y las vistas se pueden crear sobre tablas asociadas mediante RLS. Sin embargo, las vistas indexadas No se puede crear encima de tablas que tienen una política de seguridad RLS, ya que las búsquedas de filas a través del índice omitirían la política.
• Captura de datos modificados (CDC): CDC puede permitir que se filtre información de filas enteras, ya que, incluso usando RLS, todos los cambios realizados se almacenarán en la tabla del historial, y cualquier usuario con permiso para esta tabla podrá ver esta información.
• Seguimiento de cambios: El seguimiento de cambios puede filtrar la clave principal de las filas que deben filtrarse para los usuarios con permisos SELECCIONAR y VER SEGUIMIENTO DE CAMBIOS. Los valores de datos reales no se filtran; solo el hecho de que la columna A se actualizó/insertó/eliminó para la fila con la clave principal B. Esto es un problema si la clave principal contiene un elemento sensible como un número de seguro social. Sin embargo, en la práctica, este CHANGETABLE casi siempre se une a la tabla original para obtener datos más nuevos.
• Búsqueda de texto completo: Se espera una caída en el rendimiento en las consultas que utilizan las funciones de búsqueda de texto completo y búsqueda semántica debido a una combinación adicional introducida para aplicar la seguridad a nivel de fila y evitar la pérdida de las claves principales de las filas que deben filtrarse: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.
• Índices de almacén de columnas: RLS admite índices de almacén de columnas agrupados y no agrupados. Sin embargo, debido a que la seguridad a nivel de fila se aplica a una función, es posible que el optimizador pueda modificar el plan de consulta para que no utilice el modo por lotes.
• Vistas particionadas: Los predicados de bloqueo no se pueden definir en vistas particionadas y las vistas particionadas no se pueden crear sobre tablas que utilizan predicados de bloqueo. Los predicados de filtro admiten vistas particionadas.
• Tablas temporales: Las tablas temporales son compatibles con RLS. Sin embargo, los predicados de seguridad de la tabla actual no se replican automáticamente en la tabla de historial. Para aplicar una política de seguridad a las tablas actual e histórica, debe agregar individualmente un predicado de seguridad a cada tabla.