Seguridad de nivel de fila

Se aplica a: síSQL Server (todas las versiones admitidas) SíAzure SQL Database SíInstancia administrada de Azure SQL síAzure Synapse Analytics

Gráfico de seguridad de nivel de fila

La característica Seguridad de nivel de fila permite utilizar la pertenencia a un grupo o el contexto de ejecución para controlar el acceso a las filas de una tabla de base de datos.

La seguridad de nivel de fila (RLS) simplifica el diseño y la codificación de la seguridad de la aplicación. RLS permite implementar restricciones en el acceso a las filas de datos. Por ejemplo, puede asegurarse de que los trabajadores accedan únicamente a aquellas filas de datos que sean pertinentes para su departamento. Otro ejemplo es restringir el acceso de los clientes solo a los datos pertinentes para la empresa.

La lógica de la restricción de acceso está ubicada en el nivel de base de datos en lugar de estar alejado de los datos en otro nivel de aplicación. El sistema de base de datos aplica las restricciones de acceso cada vez que se intenta acceder a los datos desde cualquier nivel. Esto hace que el sistema de seguridad resulte más sólido y confiable al reducir el área expuesta del sistema de seguridad.

Implemente RLS mediante la instrucción CREATE SECURITY POLICYTransact-SQL y los predicados creados como funciones con valores de tabla insertadas.

Se aplica a: síSQL Server 2016 (13.x) y versiones posteriores, SQL Database (aquí) y Azure Synapse Analytics.

Nota

Azure Synapse solo admite los predicados de filtro. En la actualidad, los predicados de bloqueo no se admiten en Azure Synapse.

Descripción

RLS admite dos tipos de predicados de seguridad.

  • Los predicados de filtro filtran en modo silencioso las filas disponibles para leer operaciones (SELECT, UPDATE y DELETE).

  • Los predicados de bloqueo bloquean explícitamente las operaciones de escritura (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) que infringen el predicado.

El acceso a los datos de nivel de fila de una tabla está restringido por un predicado de seguridad que se define como una función con valores de tabla insertada. Luego, la función se invoca y una directiva de seguridad la aplica. Los predicados de filtro, la aplicación es consciente de las filas filtradas del conjunto de resultados. Si se filtran todas las filas, se devolverá un conjunto nulo. En el caso de los predicados de bloqueo, las operaciones que infrinjan el predicado generarán un error.

Los predicados de filtro se aplican al leer los datos desde la tabla base y afectan a todas las operaciones get: SELECT, DELETE y UPDATE. Los usuarios no se pueden seleccionar o eliminar las filas filtradas. El usuario no puede actualizar las filas filtradas. Pero, es posible actualizar las filas de tal manera que se filtren después. Los predicados de bloqueo afectan a todas las operaciones de escritura.

  • Los predicados AFTER INSERT y AFTER UPDATE pueden impedir que los usuarios actualicen las filas con valores que infrinjan el predicado.

  • Los predicados BEFORE UPDATE pueden impedir que los usuarios actualicen las filas que actualmente infrinjan el predicado.

  • Los predicados BEFORE DELETE pueden bloquear las operaciones de eliminación.

Los predicados de filtro y de bloqueo y las directivas de seguridad tienen el siguiente comportamiento:

  • Puede definir una función de predicado que se combine con otra tabla o invoque una función. Si la directiva de seguridad se crea con SCHEMABINDING = ON (el valor predeterminado), entonces se puede acceder a la función o combinación desde la consulta, y funciona como se espera sin comprobaciones de permisos adicionales. Si la directiva de seguridad se crea con SCHEMABINDING = OFF, entonces los usuarios necesitarán los permisos SELECT en estas funciones y tablas adicionales para consultar la tabla de destino. Si la función de predicado invoca una función escalar de CLR, se necesita además el permiso EXECUTE.

  • Puede emitir una consulta a una tabla que tenga un predicado de seguridad definido pero deshabilitado. Todas las filas que se han filtrado o bloqueado no se ven afectadas.

  • Si el usuario dbo, un miembro del rol db_owner o el propietario de la tabla consulta una tabla que tiene una directiva de seguridad definida y habilitada, las filas se filtran o bloquean según indique la directiva de seguridad.

  • Los intentos de modificar el esquema de una tabla enlazada por una directiva de seguridad enlazada a un esquema producirán un error. Sin embargo, se pueden modificar las columnas a las que el predicado no hace referencia.

  • Los intentos de agregar un predicado a una tabla que ya tiene uno definido para la operación especificada producen un error. Esto ocurrirá tanto si el predicado está habilitado como si no.

  • Los intentos de modificar una función, que se usa como predicado en una tabla dentro de una directiva de seguridad enlazada a un esquema, producen un error.

  • Definir varias directivas de seguridad activas que contienen predicados no superpuestos, será correcto.

Los predicados de filtro tienen el siguiente comportamiento:

  • Definir una directiva de seguridad que filtre las filas de una tabla. La aplicación no es consciente de las filas que se han filtrado para las operaciones SELECT, UPDATE y DELETE. Incluidas las situaciones en las que todas las filas se filtran. La aplicación puede aplicar INSERT a las filas, aunque se filtren durante cualquier otra operación.

Los predicados de bloqueo tienen el siguiente comportamiento:

  • Los predicados de bloqueo para UPDATE se dividen en operaciones independientes para BEFORE y AFTER. En consecuencia, no puede, por ejemplo, bloquear a los usuarios para que no actualicen una fila con un valor mayor que el actual. Si se requiere este tipo de lógica, debe usar desencadenadores con las tablas intermedias DELETED e INSERTED para hacer referencia a los valores antiguos y nuevos juntos.

  • El optimizador no comprobará un predicado de bloqueo AFTER UPDATE si no se ha cambiado ninguna de las columnas usadas por la función de predicado. Por ejemplo: Alice no debería poder cambiar un salario para que sea mayor de 100 000. Alice puede cambiar la dirección de un empleado cuyo salario ya es superior a 100 000, siempre y cuando las columnas a las que se hace referencia en el predicado no hayan cambiado.

  • No se han realizado cambios en las API masivas, incluida BULK INSERT. Esto significa que los predicados de bloqueo AFTER INSERT se aplicarán a las operaciones de inserción masivas como si fueran operaciones de inserción normales.

Casos de uso

Estos son ejemplos de diseño de cómo se puede usar RLS:

  • Un hospital puede crear una directiva de seguridad que permita a las enfermeras ver solo las filas de datos de sus pacientes.

  • Un banco puede crear una directiva para restringir el acceso a las filas de datos financieros según la división de negocio de un empleado, o según el rol de la empresa.

  • Una aplicación multiinquilino puede crear una directiva para aplicar una separación lógica de cada fila de datos del inquilino de las filas de otros inquilinos. Las eficiencias se obtienen con el almacenamiento de datos para varios inquilinos en una sola tabla. Cada inquilino solo puede ver sus filas de datos.

Los predicados de filtro RLS son funcionalmente equivalentes a anexar una cláusula WHERE . El predicado puede ser tan sofisticado como dictan las prácticas empresariales o la cláusula puede ser tan simple como WHERE TenantId = 42.

En términos más formales, RLS presenta control de acceso basado en predicado. Ofrece una evaluación flexible, centralizada y basada en predicados. El predicado puede basarse en metadatos o en cualquier otro criterio que el administrador determine según corresponda. El predicado se usa como un criterio para determinar si el usuario tiene el acceso adecuado a los datos según los atributos del usuario. El control de acceso basado en etiquetas se puede implementar mediante el control de acceso basado en predicados.

Permisos

Crear, modificar o quitar directivas de seguridad necesita el permiso ALTER ANY SECURITY POLICY . Crear o quitar una directiva de seguridad necesita el permiso ALTER en el esquema.

Además, son necesarios los siguientes permisos para cada predicado que se agrega:

  • Los permisos SELECT y REFERENCES de la función que se usa como predicado.

  • El permiso REFERENCES de la tabla de destino que se enlaza a la directiva.

  • El permiso REFERENCES de cada columna desde la tabla de destino que se usa como argumento.

Las directivas de seguridad se aplican a todos los usuarios, incluidos los usuarios dbo de la base de datos. Los usuarios dbo pueden modificar o quitar directivas de seguridad, sin embargo, se pueden auditar los cambios en las directivas de seguridad. Si los usuarios con privilegios elevados, como sysadmin o db_owner, necesitan ver todas las filas para solucionar problemas o validar los datos, la directiva de seguridad debe estar escrita de modo que lo permita.

Si se crea una directiva de seguridad con SCHEMABINDING = OFF, los usuarios deben tener el permiso de SELECT o EXECUTE en la función de predicado y cualquier función, vista o tabla adicional que se use dentro de la función de predicado para consultar la tabla de destino. Si se crear una directiva de seguridad con SCHEMABINDING = ON (el valor predeterminado), entonces estas comprobaciones de permiso se omiten cuando los usuarios consultan la tabla de destino.

Procedimientos recomendados

  • Se recomienda crear un esquema independiente para los objetos RLS: función de predicado y directivas de seguridad. Esto ayuda a separar los permisos que son necesarios en estos objetos especiales de las tablas de destino. Puede ser necesario separar adicionalmente las distintas directivas y funciones de predicado en las bases de datos multinquilino, pero no por norma en cada caso.

  • El permiso ALTER ANY SECURITY POLICY está destinado a los usuarios con privilegios elevados (como un administrador de directivas de seguridad). El administrador de directivas de seguridad no necesita el permiso SELECT en las tablas que protege.

  • Evite las conversiones de tipos en funciones de predicado para evitar posibles errores en tiempo de ejecución.

  • Evite la recursividad en funciones de predicado siempre que sea posible para evitar la degradación del rendimiento. El optimizador de consultas intentará detectar recursividades directas, pero no garantiza encontrar las indirectas. Una recursividad indirecta es cuando una segunda función llama a la función de predicado.

  • Evite el uso de combinaciones de tablas de forma excesiva en funciones de predicado para maximizar el rendimiento.

Evite la lógica del predicado que dependa de opciones SET específicas de la sesión: aunque es improbable que se usen en aplicaciones prácticas, las funciones de predicado cuya lógica depende de determinadas opciones SET específicas de la sesión pueden perder información si los usuarios pueden ejecutar consultas arbitrarias. Por ejemplo, una función de predicado que convierte implícitamente una cadena en datetime podría filtrar filas diferentes según la opción SET DATEFORMAT de la sesión actual. En general, las funciones de predicado deben cumplir las reglas siguientes:

Nota de seguridad: Ataques de canal lateral

Administrador de directivas de seguridad malintencionado

Es importante observar que un administrador de directivas de seguridad malintencionado, con permisos suficientes para crear una directiva de seguridad en una columna confidencial, y con permisos para crear o modificar funciones insertadas con valores de tabla, puede conspirar con otro usuario que tenga permisos SELECT en una tabla para exfiltrar datos creando de forma malintencionada funciones insertadas con valores de tabla diseñadas para usar ataques del lado de canal para inferir los datos. Estos ataques necesitarían una confabulación (o excesivos permisos concedidos a un usuario malintencionado) y es probable que necesiten varios cambios de la directiva (con permisos para quitar el predicado con el fin de romper el enlace de esquema), modificación de las funciones con valores de tabla insertadas y ejecución repetida de instrucciones SELECT en la tabla de destino. Se recomienda limitar los permisos según sea necesario y supervisar cualquier actividad sospechosa. Deben supervisarse actividades tales como el cambio constante de directivas y las funciones con valores tablas relacionadas con la seguridad a nivel de fila.

Consultas cuidadosamente diseñadas

Es posible perder información con el uso de consultas cuidadosamente diseñadas. Por ejemplo, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' permitiría que un usuario malintencionado sepa que el salario de Juan García es 100.000 $. Aunque hay un predicado de seguridad para impedir que un usuario malintencionado consulte directamente el salario de otras personas, el usuario puede determinar el momento en que la consulta devuelve una excepción de división por cero.

Compatibilidad entre características

En general, la seguridad de nivel de fila funcionará como se espera en todas las características. Sin embargo, hay algunas excepciones. En esta sección se describen varias notas y advertencias sobre el uso de la seguridad de nivel de fila con otras características de SQL Server.

  • DBCC SHOW_STATISTICS informa de las estadísticas en datos sin filtrar y puede filtrar información que no esté protegida con una directiva de seguridad. Por esta razón, el acceso para ver un objeto de estadísticas de una tabla con una directiva de seguridad a nivel de fila está restringido. El usuario debe ser el propietario de la tabla o miembro del rol fijo de servidor sysadmin, del rol fijo de base de datos db_owner o del rol fijo de base de datos db_ddladmin.

  • Secuencia de archivos: RLS no es compatible con la secuencia de archivos.

  • PolyBase: RLS es compatible con tablas externas en Azure Synapse y SQL Server 2019 CU7 o versiones posteriores.

  • Tablas optimizadas para memoria: la función con valores de tabla insertados que se usa como predicado de seguridad en una tabla optimizada para memoria debe definirse mediante la opción WITH NATIVE_COMPILATION. Con esta opción, se prohibirán las características del lenguaje incompatibles con las tablas optimizadas para memoria y se emitirá el error adecuado en tiempo de creación. Para obtener más información, vea la sección Seguridad de nivel de fila en tablas con optimización para memoria en Introducción a las tablas con optimización para memoria.

  • Vistas indexadas: en general, se pueden crear directivas de seguridad sobre las vistas y se pueden crear vistas sobre las tablas que están enlazadas mediante directivas de seguridad. Sin embargo, no se pueden crear vistas indexadas sobre las tablas que tienen una directiva de seguridad, ya que las búsquedas de filas mediante el índice podrían omitir la directiva.

  • Captura de datos modificados: la captura de datos modificados puede filtrar filas enteras que se deben filtrar a miembros de db_owner o a usuarios que son miembros del rol de "acceso" especificado cuando se habilita CDC para una tabla (Nota: Esta función se puede establecer de forma explícita en NULL para permitir que todos los usuarios tengan acceso a los datos modificados). De hecho, db_owner y los miembros de este rol de acceso pueden ver todos los cambios en los datos de una tabla, incluso si hay una directiva de seguridad en la tabla.

  • Seguimiento de cambios: el seguimiento de cambios puede perder la clave principal de las filas que se deben filtrar a los usuarios con permisos SELECT y VIEW CHANGE TRACKING. No se pierden los valores de datos reales; solo el hecho de que la columna A se actualizó, se insertó o se eliminó de la fila con la clave principal B. Esto es problemático si la clave principal contiene un elemento confidencial, como un número del seguro social. Sin embargo, en la práctica, CHANGETABLE casi siempre se combina con la tabla original para obtener los datos más recientes.

  • Búsqueda de texto completo: se espera una disminución del rendimiento en las consultas que usan las siguientes funciones de búsqueda de texto completo y búsqueda semántica, debido a una combinación adicional introducida para aplicar seguridad de nivel de fila y evitar la pérdida de las claves principales de las filas que se deben filtrar: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Índices de almacén de columnas: RLS no es compatible con los índices de almacén de columnas agrupados y no agrupados. Pero como la seguridad de nivel de fila aplica una función, es posible que el optimizador pueda modificar el plan de consulta para que no use el modo por lotes.

  • Vistas con particiones: no se pueden definir predicados de bloqueo en vistas con particiones, y no se pueden crear vistas con particiones sobre tablas que usan predicados de bloqueo. Los predicados de filtro son compatibles con vistas con particiones.

  • Tablas temporales: las tablas temporales son compatibles con RLS. Sin embargo, los predicados de seguridad en la tabla actual no se replican automáticamente a la tabla del historial. Para aplicar una directiva de seguridad a las tablas actual y del historial, debe agregar individualmente un predicado de seguridad en cada tabla.

Ejemplos

A. Escenario para los usuarios que se autentican en la base de datos

Este ejemplo crea tres usuarios y crea y rellena una tabla con seis filas. Después, crea una función con valores de tabla insertados y una directiva de seguridad para la tabla. En este ejemplo se muestra cómo seleccionar instrucciones filtradas para los distintos usuarios.

Cree tres cuentas de usuario que mostrarán las distintas capacidades de acceso.

CREATE USER Manager WITHOUT LOGIN;  
CREATE USER SalesRep1 WITHOUT LOGIN;  
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

Cree una tabla que contenga datos.

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders 
    (  
    OrderID int,  
    SalesRep nvarchar(50),  
    Product nvarchar(50),  
    Quantity smallint  
    );  

Rellene la tabla con seis filas de datos que muestren tres pedidos para cada representante de ventas.

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sales.Orders;

Conceda acceso de lectura en la tabla para cada usuario.

GRANT SELECT ON Sales.Orders TO Manager;  
GRANT SELECT ON Sales.Orders TO SalesRep1;  
GRANT SELECT ON Sales.Orders TO SalesRep2; 
GO

Cree un esquema y una función con valores de tabla insertada. La función devuelve 1 cuando una fila de la columna SalesRep es la misma que el usuario que ejecuta la consulta (@SalesRep = USER_NAME()) o si el usuario que ejecuta la consulta es el usuario administrador (USER_NAME() = 'Manager').

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';  
GO

Cree una directiva de seguridad agregando la función como un predicado de filtro. El estado se debe configurar en ON para habilitar la directiva.

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);  
GO

Permisos SELECT para la función fn_securitypredicate

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;  
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;  
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;  

Pruebe ahora el predicado de filtrado seleccionando de la tabla Ventas como cada usuario.

EXECUTE AS USER = 'SalesRep1';  
SELECT * FROM Sales.Orders;
REVERT;  
  
EXECUTE AS USER = 'SalesRep2';  
SELECT * FROM Sales.Orders;
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales.Orders;
REVERT; 

El administrador debe ver las seis filas. Los usuarios Sales1 y Sales2 solo deben ver sus propias ventas.

Modifique la directiva de seguridad para deshabilitar la directiva.

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

Ahora los usuarios Sales1 y Sales2 pueden ver las seis filas.

Conexión a la base de datos SQL para limpiar los recursos

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

B. Escenarios para el uso de Seguridad de nivel de fila en una tabla externa de Azure Synapse

Este breve ejemplo crea tres usuarios y una tabla externa con seis filas. Después, crea una función con valores de tabla insertados y una directiva de seguridad para la tabla externa. El ejemplo muestra cómo seleccionar instrucciones filtradas para los distintos usuarios.

Prerrequisitos

  1. Debe tener un grupo de SQL dedicado. Consulte Creación de un grupo de SQL dedicado.
  2. El servidor que hospeda el grupo de SQL dedicado debe estar registrado con AAD y debe tener una cuenta de almacenamiento de Azure con permisos de colaborador de blog de almacenamiento. Siga estos pasos.
  3. Cree un sistema de archivos para la cuenta de Azure Storage. Use el Explorador de Storage para ver la cuenta de almacenamiento. Haga clic con el botón derecho en los contenedores y seleccione Crear sistema de archivos.

Después de implementar los requisitos previos, cree tres cuentas de usuario que mostrarán las distintas funciones de acceso.

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in master and your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;  
CREATE USER Sales1  FOR LOGIN Sales1;  
CREATE USER Sales2  FOR LOGIN Sales2 ;

Cree una tabla que contenga datos.

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );  

Rellene la tabla con seis filas de datos que muestren tres pedidos para cada representante de ventas.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sales;

Cree una tabla externa de Azure Synapse a partir de la tabla Sales que acaba de crear.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);
  
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Conceda permisos SELECT para los tres usuarios de la tabla externa Sales_ext que ha creado.

GRANT SELECT ON Sales_ext TO Sales1;  
GRANT SELECT ON Sales_ext TO Sales2;  
GRANT SELECT ON Sales_ext TO Manager;

Cree un esquema y una función con valores de tabla insertada; es posible que lo haya completado en el ejemplo A. La función devuelve 1 cuando una fila de la columna SalesRep es la misma que el usuario que ejecuta la consulta (@SalesRep = USER_NAME()) o si el usuario que ejecuta la consulta es el usuario administrador (USER_NAME() = 'Manager').

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';  

Cree una directiva de seguridad en una tabla externa mediante la función con valores de tabla insertada como predicado de filtro. El estado se debe configurar en ON para habilitar la directiva.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext  
WITH (STATE = ON);

Pruebe ahora el predicado de filtrado mediante la selección de la tabla externa Sales_ext. Inicie sesión como cada usuario, Sales1, Sales2 y administrador. Ejecute el siguiente comando como cada usuario.

SELECT * FROM Sales_ext;

El administrador debe ver las seis filas. Los usuarios Sales1 y Sales2 solo deben ver sus propias ventas.

Modifique la directiva de seguridad para deshabilitar la directiva.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);  

Ahora los usuarios Sales1 y Sales2 pueden ver las seis filas.

Conexión a la base de datos de Azure Synapse para limpiar los recursos

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred; 
DROP MASTER KEY;

Conéctese con la lógica principal para limpiar los recursos.

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C. Escenario para los usuarios que se conectan a la base de datos a través de una aplicación de nivel intermedio

Nota

En este ejemplo, la funcionalidad de predicados de bloque no se admite actualmente para Azure Synapse, por lo que la inserción de filas para el identificador de usuario incorrecto no se bloquea con Azure Synapse.

Este ejemplo muestra cómo una aplicación de nivel intermedio puede implementar el filtrado de conexiones, donde los usuarios de la aplicación (o inquilinos) comparten el mismo usuario de SQL Server (la aplicación). La aplicación configura el identificador de usuario de la aplicación actual en SESSION_CONTEXT (Transact-SQL) después de conectarse a la base de datos y, luego, las directivas de seguridad filtran de forma transparente las filas que no deberían ser visibles para este identificador e impiden también que el usuario inserte filas para el identificador de usuario incorrecto. No es necesario ningún otro cambio en la aplicación.

Cree una tabla que contenga datos.

CREATE TABLE Sales (  
    OrderId int,  
    AppUserId int,  
    Product varchar(10),  
    Qty int  
);  

Rellene la tabla con seis filas de datos en las que se muestren tres pedidos para cada usuario de la aplicación.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),  
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);  

Cree un usuario con pocos privilegios que la aplicación usará para conectarse.

-- Without login only for demo  
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;  
  
-- Never allow updates on this column  
DENY UPDATE ON Sales(AppUserId) TO AppUser;  

Cree un esquema y una función de predicado nuevos, que usarán el identificador de usuario de la aplicación almacenado en SESSION_CONTEXT para filtrar las filas.

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)  
    RETURNS TABLE  
    WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result  
    WHERE  
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO  

Cree una directiva de seguridad que agregue esta función como un predicado de filtro y un predicado de bloqueo en Sales. El predicado de bloqueo solo necesita AFTER INSERT, ya que BEFORE UPDATE y BEFORE DELETE ya están filtrados y AFTER UPDATE no es necesario porque la columna AppUserId no se puede actualizar con otros valores debido al permiso de columna que se ha establecido anteriormente.

CREATE SECURITY POLICY Security.SalesFilter  
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,  
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);  

Ahora podemos simular el filtrado de conexiones al seleccionar la tabla Sales después de configurar los distintos identificadores de usuario en SESSION_CONTEXT. En la práctica, la aplicación es responsable de establecer el identificador de usuario actual en SESSION_CONTEXT después de abrir una conexión.

EXECUTE AS USER = 'AppUser';  
EXEC sp_set_session_context @key=N'UserId', @value=1;  
SELECT * FROM Sales;  
GO  
  
/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;
  
SELECT * FROM Sales;  
GO  
  
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID  
GO  
  
REVERT;  
GO  

Limpie los recursos de la base de datos.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

D. Escenario de uso de una tabla de búsqueda para el predicado de seguridad

En este ejemplo se usa una tabla de búsqueda para el vínculo entre el identificador de usuario y el valor que se está filtrando, en lugar de tener que especificar el identificador de usuario en la tabla de hechos. Crea tres usuarios y crea y rellena una tabla de hechos con seis filas y una tabla de búsqueda con dos filas. A continuación, crea una función insertada con valores de tabla que une la tabla de hechos a la búsqueda para obtener el identificador de usuario y una directiva de seguridad para la tabla. En este ejemplo se muestra cómo seleccionar instrucciones filtradas para los distintos usuarios.

Cree tres cuentas de usuario que mostrarán las distintas capacidades de acceso.

CREATE USER Manager WITHOUT LOGIN;  
CREATE USER Sales1 WITHOUT LOGIN;  
CREATE USER Sales2 WITHOUT LOGIN;  

Cree un esquema de ejemplo y una tabla de hechos para almacenar los datos.

CREATE SCHEMA Sample;

CREATE TABLE Sample.Sales  
    (  
    OrderID int,  
    Product varchar(10),  
    Qty int 
    );    

Rellene la tabla de hechos con dos filas de datos.

INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sample.Sales;

Cree una tabla que contenga los datos de búsqueda; en este caso, una relación entre SalesRep y Product.

CREATE TABLE Sample.Lk_Salesman_Product
  ( Salesrep sysname, 
    Product varchar(10)
  ) ;

Rellene la tabla de búsqueda con datos de ejemplo y vincule un producto a cada representante de ventas.

INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;

Conceda acceso de lectura en la tabla de hechos a cada uno de los usuarios.

GRANT SELECT ON Sample.Sales TO Manager;  
GRANT SELECT ON Sample.Sales TO Sales1;  
GRANT SELECT ON Sample.Sales TO Sales2;  

Cree un esquema y una función con valores de tabla insertada. La función devuelve 1 cuando un usuario consulta la tabla de hechos Sales y la columna SalesRep de la tabla Lk_Salesman_Product es igual que el usuario que ejecuta la consulta (@SalesRep = USER_NAME()) cuando se une a la tabla de hechos en la columna Product, o bien si el usuario que ejecuta la consulta es el usuario administrador (USER_NAME() = 'Manager').

CREATE SCHEMA Security ;

CREATE FUNCTION Security.fn_securitypredicate
         (@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS 
           RETURN ( SELECT 1 as Result
                     FROM Sample.Sales f
            INNER JOIN Sample.Lk_Salesman_Product s
                     ON s.Product = f.Product
            WHERE ( f.product = @Product
                    AND s.SalesRep = USER_NAME() )
                 OR USER_NAME() = 'Manager'
                   ) ;
 

Cree una directiva de seguridad agregando la función como un predicado de filtro. El estado se debe configurar en ON para habilitar la directiva.

CREATE SECURITY POLICY SalesFilter 
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;

Permisos SELECT para la función fn_securitypredicate

GRANT SELECT ON security.fn_securitypredicate TO Manager;  
GRANT SELECT ON security.fn_securitypredicate TO Sales1;  
GRANT SELECT ON security.fn_securitypredicate TO Sales2;  

Pruebe ahora el predicado de filtrado seleccionando de la tabla Ventas como cada usuario.

EXECUTE AS USER = 'Sales1'; 
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for ‘Sales1’ in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Sales2'; 
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for ‘Sales2’ in the Lk_Salesman_Product table above)
REVERT; 

EXECUTE AS USER = 'Manager'; 
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;

El administrador debe ver las seis filas. Los usuarios Sales1 y Sales2 solo deben ver sus propias ventas.

Modifique la directiva de seguridad para deshabilitar la directiva.

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

Ahora los usuarios Sales1 y Sales2 pueden ver las seis filas.

Conexión a la base de datos SQL para limpiar los recursos

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security; 
DROP SCHEMA Sample;

Consulte también

CREATE SECURITY POLICY (Transact-SQL)
ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
SESSION_CONTEXT (Transact-SQL)
sp_set_session_context (Transact-SQL)
sys.security_policies (Transact-SQL)
sys.security_predicates (Transact-SQL)
Crear funciones definidas por el usuario (motor de base de datos)