How To: Row Level Security en SQL Server 

Pablo Dafonte Iglesias

Hace unos días os traía cómo enmascarar información en columnas de una tabla de SQL Server para que solo aquellos usuarios con permiso pudiesen ver la información en plano. Pues bien, hoy sigo con mi obsesión de salvaguardar la información y para ello os traigo un laboratorio para ver cómo ocultar filas de una tabla a usuarios. Para ello os explicaré cómo aplicar una política a nivel de tabla para que se oculten filas a usuarios a partir de valores de una columna de la tabla.  

Esta característica se trata de Row Level Security. 

¿ Qué es Row Level Security (RLS) ? 

RLS se trata de una característica que implementan múltiples gestores de datos relacionales para controlar el acceso a filas de una tabla de base de datos a partir de la pertenencia a un grupo de usuarios, asignación a un rol o el contexto de ejecución. 

La seguridad a nivel de fila simplifica el diseño y la codificación de aplicaciones e informes o cuadros de mando, ya que permite crear restricciones en el acceso a filas de datos en función de información del contexto de la ejecución de las consultas. Esto es, en el momento de lanzar una consulta o sentencia contra la tabla a la que se ha aplicado restricciones a nivel de fila, el gestor de base de datos aplicará las restricciones asociadas a la propia tabla teniendo en cuenta valores de sesión del usuario que lanza la sentencia. 

En SQL Server se puede aplicar esta funcionalidad a partir de la combinación de dos elementos: 

  • Una función en donde marcaremos el resultado filtrado esperado a partir de información de la sesión. 
  • La creación de una Security Policy aplicada a la tabla que queremos restringir vinculada a la función creada. 

En cuanto a la política de seguridad permite crear dos tipos de predicados (tenéis aquí el detalle): 

  • FILTER: que filtran en modo silencioso las filas. 
  • BLOCK: bloquean explícitamente las operaciones de escritura que infringen la función definida. Para este tipo de predicados, se debe indicar la operación DML a bloquear y en qué momento, pudiendo analizarse después de que se haya realizado la operación DML (AFTER), o antes de que se realice la operación DML (BEFORE). DE no indicar ninguna operación, el bloqueo se aplicará a todas las operaciones DML. 

La seguridad de nivel de fila se introdujo por primera vez en SQL Server 2016 (13.x).  Tenéis todo el detalle de esta característica en este enlace. 

Probando RLS en SQL Server 

Para este lab me voy a basar en el entorno creado para probar la característica de Dynamic Data Masking, os dejo aquí la referencia a este post, en donde podréis echarle un vistazo y si no queréis leeros ese artículo, tenéis en el último punto de este post, en referencias, el enlace al archivo en donde explico cómo montar el entorno de laboratorio. En el archivo 00_test_row_level.sql tenéis todo el código que veréis en este post 

Dentro de la base de datos del laboratorio hay una tabla de empleados que tiene una división por país, se puede ver que hay dos países de referencia: USA y UK.  

SELECT DISTINCT(Country) FROM [dbo].[Employees]; 
| Country | 
UK 
USA

Así que plantearé que existe un departamento de Recursos Humanos para cada país con usuarios distintos y que solo deban ver los empleados del país en el que está el usuario de Recursos Humanos.  

Partiendo de esto, lo primero es crear estos dos usuarios: 

-- Create Test Users
CREATE USER HumanResourcesUK WITHOUT LOGIN;
GO
CREATE USER HumanResourcesUSA WITHOUT LOGIN;
GO

Ahora haré un paso que igual os descoloca un poco, pero os lo explico. Como avanzaba, es necesario crear una función en donde se crearán realmente los filtros por usuario, y ésta se asignará a una policy. Bien, dentro de las recomendaciones de seguridad de Microsoft, está la de que esta función se cree en un esquema de base de datos distinto al esquema en donde está la tabla a la que vamos a aplicar el filtrado. La razón es la de aislar esta función, al estar en otro esquema, los permisos, sobre todo los de poder modificar funciones, estarán asociados a un esquema diferente al del esquema en donde estarán las tablas con los datos a filtrar; de esta manera, al asignar permisos en el esquema de datos no corremos el riesgo de dar permisos de modificación a esta función al estar en un esquema diferente. Explicado esto, nos creamos un nuevo esquema en la base de datos: 

-- Create Security Schema
CREATE SCHEMA Security;
GO

Y Sobre este nuevo esquema crearemos la función que se encargará de preparar los filtrados en función del usuario: 

-- Create Security Functions
CREATE FUNCTION Security.human_resource_filter(@Country AS nvarchar(15))
    RETURNS TABLE
WITH SCHEMABINDING
AS  
    RETURN SELECT 1 AS human_resource_filter_result
WHERE @Country like ( SELECT CASE
    WHEN USER_NAME() = 'HumanResourcesUK' THEN 'UK'
    WHEN USER_NAME() = 'HumanResourcesUSA' THEN 'USA'
    ELSE '%'
END);
GO

En donde podemos ver que creamos la función y esperamos el campo Country como entrada a dicha función como parámetro a utilizar en el filtrado: 

CREATE FUNCTION Security.human_resource_filter(@Country AS nvarchar(15))

Se marca que el resultado de la función será de tipo TABLE; que se generará a partir de un filtrado de la tabla a la que se aplicará la restricción por usuario: 

RETURNS TABLE

Se especifica que se aplique la característica de SCHEMABINDING. Con esto se especifica que la función está enlazada a los objetos de base de datos a los que hace referencia; los objetos base no se pueden modificar de una manera que afecte a la definición de función. 

WITH SCHEMABINDING

Y ahora viene lo divertido, que es la definición en sí de la función lo que va a ejecutar para generar la salida que afectará a lo que veamos al realizar consultas contra la tabla, en función del usuario con el que lancemos las sentencias: 

AS  
    RETURN SELECT 1 AS human_resource_filter_result
WHERE @Country like ( SELECT CASE
    WHEN USER_NAME() = 'HumanResourcesUK' THEN 'UK'
    WHEN USER_NAME() = 'HumanResourcesUSA' THEN 'USA'
    ELSE '%'
END);
GO

Lo que se hace en este caso es utilizar la función USER_NAME() para obtener el usuario con el que se está lanzando la consulta. Si el usuario es el de Recursos Humanos de UK, el Select anidado devolverá el texto UK, si es el usuario de Estados Unidos, devolverá el texto USA, y en otro caso, devolverá el carácter % para que no filtre nada, básicamente. Este texto será la referencia que se utilizará en la cláusula WHERE en donde se igualará el campo Country de la tabla de empleados con este texto devuelto en la consulta anidada. Aquí es en donde está la magia, en función del usuario filtramos la tabla origen y se genera la respuesta de la función. 

El siguiente paso es crear la Security Policy aplicada a la tabla de empleados: 

-- Create Security Policies
CREATE SECURITY POLICY CountryFilter
ADD FILTER PREDICATE Security.human_resource_filter(Country)
ON [dbo].[Employees]
WITH (STATE = ON);
GO

Este código genera la policy, la aplica a la tabla empleados y le asocia la función definida anteriormente. Tiene ciertas restricciones que debemos tener en cuenta a la hora de definir la función de filtrado: 

  • Solo se puede aplicar una security policy por tabla. 
  • Cada policy solo puede tener una cláusula de FILTER por tabla, pero sí se puede incluir en una misma política varios filtros, aplicando cada fitltro a una tabla distinta; es decir, podríamos definir en la misma política un filtro para la tabla empleados y otro para la tabla de clientes. 
  • Se pueden añadir varias entras de tipo BLOCK considerando distintos predicados de bloqueo. Lo podéis ver en la documentación oficial de creación de políticas de seguridad, tenéis el enlace en las referencias al final del post. 

Por último se marca la política como activada al incluir: 

WITH (STATE = ON);

Para deshabilitar una política de seguridad, bastará con hacer un ALTER de la policy cambiando este parámetro STATE a OFF: 

ALTER SECURITY POLICY CountryFilter 
WITH (STATE = OFF);

Antes de ponernos a lanzar consultas con cada uno de los usuarios creados, les damos permisos suficientes. Se darán permisos de consulta a la tabla de empleados, y también a la función creada para poder obtener el resultado del filtrado aplicado por esta función: 

-- GRANT SELECT to user
GRANT SELECT ON [dbo].[Employees] TO HumanResourcesUK;
GRANT SELECT ON [dbo].[Employees] TO HumanResourcesUSA;
GRANT SELECT ON Security.human_resource_filter TO HumanResourcesUK;
GRANT SELECT ON Security.human_resource_filter TO HumanResourcesUSA;
GO

Ahora ya podemos lanzar consultas con los distintos usuarios y validar el filtrado configurado. Así pues, lanzando la siguiente consulta con el usuario de Reino Unido: 

EXECUTE AS USER = 'HumanResourcesUK';
SELECT * FROM [dbo].[Employees];
REVERT;

Podemos ver que solo se accede a las filas de los empleados del Reino Unido: 

Haciendo lo mismo pero con el usuario creado para los Estados Unidos: 

EXECUTE AS USER = 'HumanResourcesUSA';
SELECT * FROM [dbo].[Employees];
REVERT;

Vemos que solo accedemos a la información de empleados de Estados Unidos: 

Si lanzamos la consulta con otro usuario, yo por ejemplo la lanzo con un usuario owner de la base de datos, podemos ver que se obtiene la información de ambos países: 

Con esto quedaría validado el comportamiento de esta característica que ofrece SQL Server, entre otros gestores de bases de datos, para ocultar filas a usuarios limitando el acceso a la información en tiempo de ejecución de consultas sobre tablas de nuestras bases de datos.

Referencias 

Entradas relacionadas