Seguridad de nivel de filaRow-Level Security

SE APLICA A: síSQL Server síAzure SQL Database síAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

Gráfico de seguridad de nivel de filaRow level security graphic

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.Row-Level Security enables you to use group membership or execution context to control access to rows in a database table.

La seguridad de nivel de fila (RLS) simplifica el diseño y la codificación de la seguridad de la aplicación.Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS permite implementar restricciones en el acceso a las filas de datos.RLS helps you implement restrictions on data row access. Por ejemplo, puede asegurarse de que los trabajadores accedan únicamente a aquellas filas de datos que sean pertinentes para su departamento.For example, you can ensure that workers access only those data rows that are pertinent to their department. Otro ejemplo es restringir el acceso de los clientes solo a los datos pertinentes para la empresa.Another example is to restrict customers' data access to only the data relevant to their company.

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.The access restriction logic is located in the database tier rather than away from the data in another application tier. El sistema de base de datos aplica las restricciones de acceso cada vez que se intenta acceder a los datos desde cualquier nivel.The database system applies the access restrictions every time that data access is attempted from any tier. Esto hace que el sistema de seguridad sea más sólido y confiable ya que reduce la superficie del sistema de seguridad.This makes your security system more reliable and robust by reducing the surface area of your security system.

Implemente RLS mediante la instrucción CREATE SECURITY POLICYTransact-SQLTransact-SQL y los predicados creados como funciones con valores de tabla insertadas.Implement RLS by using the CREATE SECURITY POLICYTransact-SQLTransact-SQL statement, and predicates created as inline table-valued functions.

Se aplica a: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) hasta la versión actual), SQL DatabaseSQL Database (Obtenerlo), Almacenamiento de datos SQLSQL Data Warehouse.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), SQL DatabaseSQL Database (Get it), Almacenamiento de datos SQLSQL Data Warehouse.

Nota

Azure SQL Data Warehouse solo admite los predicados de filtro.Azure SQL Data Warehouse supports filter predicates only. En la actualidad, los predicados de bloqueo no se admiten en Azure SQL Data Warehouse.Block predicates aren't currently supported in Azure SQL Data Warehouse.

DescripciónDescription

RLS admite dos tipos de predicados de seguridad.RLS supports two types of security predicates.

  • Los predicados de filtro filtran en modo silencioso las filas disponibles para leer operaciones (SELECT, UPDATE y DELETE).Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).

  • Los predicados de bloqueo bloquean explícitamente las operaciones de escritura (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) que infringen el predicado.Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

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.Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. Luego, la función se invoca y una directiva de seguridad la aplica.The function is then invoked and enforced by a security policy. Los predicados de filtro, la aplicación es consciente de las filas filtradas del conjunto de resultados.For filter predicates, the application is unaware of rows that are filtered from the result set. Si se filtran todas las filas, se devolverá un conjunto nulo.If all rows are filtered, then a null set will be returned. En el caso de los predicados de bloqueo, las operaciones que infrinjan el predicado generarán un error.For block predicates, any operations that violate the predicate will fail with an error.

Los predicados de filtro se aplican al leer los datos desde la tabla base yFilter predicates are applied while reading data from the base table. afectan a todas las operaciones get: SELECT, DELETE y UPDATE.They affect all get operations: SELECT, DELETE and UPDATE. Los usuarios no se pueden seleccionar o eliminar las filas filtradas.The users can't select or delete rows that are filtered. El usuario no puede actualizar las filas filtradas.The user can't update rows that are filtered. Pero, es posible actualizar las filas de tal manera que se filtren después.But, it's possible to update rows in such a way that they'll be filtered afterward. Los predicados de bloqueo afectan a todas las operaciones de escritura.Block predicates affect all write operations.

  • Los predicados AFTER INSERT y AFTER UPDATE pueden impedir que los usuarios actualicen las filas con valores que infrinjan el predicado.AFTER INSERT and AFTER UPDATE predicates can prevent users from updating rows to values that violate the predicate.

  • Los predicados BEFORE UPDATE pueden impedir que los usuarios actualicen las filas que actualmente infrinjan el predicado.BEFORE UPDATE predicates can prevent users from updating rows that currently violate the predicate.

  • Los predicados BEFORE DELETE pueden bloquear las operaciones de eliminación.BEFORE DELETE predicates can block delete operations.

Los predicados de filtro y de bloqueo y las directivas de seguridad tienen el siguiente comportamiento:Both filter and block predicates and security policies have the following behavior:

  • Puede definir una función de predicado que se combine con otra tabla o invoque una función.You may define a predicate function that joins with another table and/or invokes a function. Si la directiva de seguridad se crea con SCHEMABINDING = ON, entonces se puede acceder a la función o combinación desde la consulta y funciona como se espera sin comprobaciones de permisos adicionales.If the security policy is created with SCHEMABINDING = ON, then the join or function is accessible from the query and works as expected without any additional permission checks. Si la directiva de seguridad se crea con SCHEMABINDING = OFF, entonces los usuarios necesitarán los permisos SELECT o EXECUTE en estas funciones y tablas adicionales para consultar la tabla de destino.If the security policy is created with SCHEMABINDING = OFF, then users will need SELECT or EXECUTE permissions on these additional tables and functions to query the target table.

  • Puede emitir una consulta a una tabla que tenga un predicado de seguridad definido pero deshabilitado.You may issue a query against a table that has a security predicate defined but disabled. Todas las filas que se han filtrado o bloqueado no se ven afectadas.Any rows that are filtered or blocked aren't affected.

  • 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.If a dbo user, a member of the db_owner role, or the table owner queries a table that has a security policy defined and enabled, the rows are filtered or blocked as defined by the security policy.

  • Los intentos de modificar el esquema de una tabla enlazada por una directiva de seguridad enlazada a un esquema producirán un error.Attempts to alter the schema of a table bound by a schema bound security policy will result in an error. Sin embargo, se pueden modificar las columnas a las que el predicado no hace referencia.However, columns not referenced by the predicate can be altered.

  • Los intentos de agregar un predicado a una tabla que ya tiene uno definido para la operación especificada producen un error.Attempts to add a predicate on a table that already has one defined for the specified operation results in an error. Esto ocurrirá tanto si el predicado está habilitado como si no.This will happen whether the predicate is enabled or not.

  • 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.Attempts to modify a function, that is used as a predicate on a table within a schema bound security policy, will result in an error.

  • Definir varias directivas de seguridad activas que contienen predicados no superpuestos, será correcto.Defining multiple active security policies that contain non-overlapping predicates, succeeds.

Los predicados de filtro tienen el siguiente comportamiento:Filter predicates have the following behavior:

  • Definir una directiva de seguridad que filtre las filas de una tabla.Define a security policy that filters the rows of a table. La aplicación no es consciente de las filas que se han filtrado para las operaciones SELECT, UPDATE y DELETE.The application is unaware of any rows that are filtered for SELECT, UPDATE, and DELETE operations. 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.Including situations where all the rows are filtered out. The application can INSERT rows, even if they will be filtered during any other operation.

Los predicados de bloqueo tienen el siguiente comportamiento:Block predicates have the following behavior:

  • Los predicados de bloqueo para UPDATE se dividen en operaciones independientes para BEFORE y AFTER.Block predicates for UPDATE are split into separate operations for BEFORE and AFTER. En consecuencia, no puede, por ejemplo, bloquear a los usuarios para que no actualicen una fila con un valor mayor que el actual.Consequently, you can't, for example, block users from updating a row to have a value higher than the current one. 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.If this kind of logic is required, you must use triggers with the DELETED and INSERTED intermediate tables to reference the old and new values together.

  • 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.The optimizer will not check an AFTER UPDATE block predicate if the columns used by the predicate function weren't changed. Por ejemplo: Alice no debería poder cambiar un salario para que sea mayor de 100 000.For example: Alice shouldn't be able to change a salary to be greater than 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.Alice can change the address of an employee whose salary is already greater than 100,000 as long as the columns referenced in the predicate weren't changed.

  • No se han realizado cambios en las API masivas, incluida BULK INSERT.No changes have been made to the bulk APIs, including 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.This means that block predicates AFTER INSERT will apply to bulk insert operations just as they would regular insert operations.

Casos de usoUse Cases

Estos son ejemplos de diseño de cómo se puede usar RLS:Here are design examples of how RLS can be used:

  • Un hospital puede crear una directiva de seguridad que permita a las enfermeras ver solo las filas de datos de sus pacientes.A hospital can create a security policy that allows nurses to view data rows for their patients only.

  • 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.A bank can create a policy to restrict access to financial data rows based on an employee's business division or role in the company.

  • 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.A multi-tenant application can create a policy to enforce a logical separation of each tenant's data rows from every other tenant's rows. Las eficiencias se obtienen con el almacenamiento de datos para varios inquilinos en una sola tabla.Efficiencies are achieved by the storage of data for many tenants in a single table. Cada inquilino solo puede ver sus filas de datos.Each tenant can see only its data rows.

Los predicados de filtro RLS son funcionalmente equivalentes a anexar una cláusula WHERE .RLS filter predicates are functionally equivalent to appending a WHERE clause. El predicado puede ser tan sofisticado como dictan las prácticas empresariales o la cláusula puede ser tan simple como WHERE TenantId = 42.The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42.

En términos más formales, RLS presenta control de acceso basado en predicado.In more formal terms, RLS introduces predicate based access control. Ofrece una evaluación flexible, centralizada y basada en predicados.It features a flexible, centralized, predicate-based evaluation. El predicado puede basarse en metadatos o en cualquier otro criterio que el administrador determine según corresponda.The predicate can be based on metadata or any other criteria the administrator determines as appropriate. 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.The predicate is used as a criterion to determine if the user has the appropriate access to the data based on user attributes. El control de acceso basado en etiquetas se puede implementar con el control de acceso basado en predicados.Label-based access control can be implemented by using predicate-based access control.

PermisosPermissions

Crear, modificar o quitar directivas de seguridad necesita el permiso ALTER ANY SECURITY POLICY .Creating, altering, or dropping security policies requires the ALTER ANY SECURITY POLICY permission. Crear o quitar una directiva de seguridad necesita el permiso ALTER en el esquema.Creating or dropping a security policy requires ALTER permission on the schema.

Además, son necesarios los siguientes permisos para cada predicado que se agrega:Additionally the following permissions are required for each predicate that is added:

  • Los permisosSELECT y REFERENCES de la función que se usa como predicado.SELECT and REFERENCES permissions on the function being used as a predicate.

  • El permisoREFERENCES de la tabla de destino que se enlaza a la directiva.REFERENCES permission on the target table being bound to the policy.

  • El permisoREFERENCES de cada columna desde la tabla de destino que se usa como argumento.REFERENCES permission on every column from the target table used as arguments.

Las directivas de seguridad se aplican a todos los usuarios, incluidos los usuarios dbo de la base de datos.Security policies apply to all users, including dbo users in the database. Los usuarios dbo pueden modificar o quitar directivas de seguridad, sin embargo, se pueden auditar los cambios en las directivas de seguridad.Dbo users can alter or drop security policies however their changes to security policies can be audited. 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.If high privileged users, such as sysadmin or db_owner, need to see all rows to troubleshoot or validate data, the security policy must be written to allow that.

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.If a security policy is created with SCHEMABINDING = OFF, then to query the target table, users must have the SELECT or EXECUTE permission on the predicate function and any additional tables, views, or functions used within the predicate function. 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.If a security policy is created with SCHEMABINDING = ON (the default), then these permission checks are bypassed when users query the target table.

Procedimientos recomendadosBest Practices

  • Se recomienda crear un esquema independiente para los objetos RLS, función de predicado y directiva de seguridad.It's highly recommended to create a separate schema for the RLS objects, predicate function, and security policy.

  • El permiso ALTER ANY SECURITY POLICY está destinado a los usuarios con privilegios elevados (como un administrador de directivas de seguridad).The ALTER ANY SECURITY POLICY permission is intended for highly privileged users (such as a security policy manager). El administrador de directivas de seguridad no necesita el permiso SELECT en las tablas que protege.The security policy manager doesn't require SELECT permission on the tables they protect.

  • Evite las conversiones de tipos en funciones de predicado para evitar posibles errores en tiempo de ejecución.Avoid type conversions in predicate functions to avoid potential runtime errors.

  • Evite la recursividad en funciones de predicado siempre que sea posible para evitar la degradación del rendimiento.Avoid recursion in predicate functions wherever possible to avoid performance degradation. El optimizador de consultas intentará detectar recursividades directas, pero no garantiza encontrar las indirectas.The query optimizer will try to detect direct recursions, but isn't guaranteed to find indirect recursions. Una recursividad indirecta es cuando una segunda función llama a la función de predicado.An indirect recursion is where a second function calls the predicate function.

  • Evite el uso de combinaciones de tablas de forma excesiva en funciones de predicado para maximizar el rendimiento.Avoid using excessive table joins in predicate functions to maximize performance.

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.Avoid predicate logic that depends on session-specific SET options: While unlikely to be used in practical applications, predicate functions whose logic depends on certain session-specific SET options can leak information if users are able to execute arbitrary queries. 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.For example, a predicate function that implicitly converts a string to datetime could filter different rows based on the SET DATEFORMAT option for the current session. En general, las funciones de predicado deben cumplir las reglas siguientes:In general, predicate functions should abide by the following rules:

Nota de seguridad: Ataques de canal lateralSecurity Note: Side-Channel Attacks

Administrador de directivas de seguridad malintencionadoMalicious security policy manager

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.It is important to observe that a malicious security policy manager, with sufficient permissions to create a security policy on top of a sensitive column and having permission to create or alter inline table-valued functions, can collude with another user who has select permissions on a table to perform data exfiltration by maliciously creating inline table-valued functions designed to use side channel attacks to infer data. 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.Such attacks would require collusion (or excessive permissions granted to a malicious user) and would likely require several iterations of modifying the policy (requiring permission to remove the predicate in order to break the schema binding), modifying the inline table-valued functions, and repeatedly running select statements on the target table. Se recomienda limitar los permisos según sea necesario y supervisar cualquier actividad sospechosa.We recommend you limit permissions as necessary and monitor for any suspicious activity. Deben supervisarse actividades tales como el cambio constante de directivas y las funciones con valores tablas relacionadas con la seguridad a nivel de fila.Activity such as constantly changing policies and inline table-valued functions related to row-level security should be monitored.

Consultas cuidadosamente diseñadasCarefully crafted queries

Es posible perder información mediante el uso de consultas cuidadosamente diseñadas.It is possible to cause information leakage through the use of carefully crafted queries. 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 $.For example, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' would let a malicious user know that John Doe's salary is $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.Even though there is a security predicate in place to prevent a malicious user from directly querying other people's salary, the user can determine when the query returns a divide-by-zero exception.

Compatibilidad entre característicasCross-Feature Compatibility

En general, la seguridad de nivel de fila funcionará como se espera en todas las características.In general, row-level security will work as expected across features. Sin embargo, hay algunas excepciones.However, there are a few exceptions. 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 ServerSQL Server.This section documents several notes and caveats for using row-level security with certain other features of SQL ServerSQL 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.DBCC SHOW_STATISTICS reports statistics on unfiltered data, and can leak information otherwise protected by a security policy. 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.For this reason, access to view a statistics object for a table with a row-level security policy is restricted. 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.The user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

  • Secuencia de archivos: RLS no es compatible con la secuencia de archivos.Filestream: RLS is incompatible with Filestream.

  • PolyBase: RLS se solo se admite con tablas externas de Polybase para Azure SQL Data Warehouse.PolyBase: RLS is supported with Polybase external tables for Azure SQL Data Warehouse only.

  • 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.Memory-Optimized Tables: The inline table-valued function used as a security predicate on a memory-optimized table must be defined using the WITH NATIVE_COMPILATION option. 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.With this option, language features not supported by memory-optimized tables will be banned and the appropriate error will be issued at creation time. 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.For more information, see the Row-Level Security in Memory Optimized Tables section in Introduction to Memory-Optimized Tables.

  • 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.Indexed views: In general, security policies can be created on top of views, and views can be created on top of tables that are bound by security policies. 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.However, indexed views cannot be created on top of tables that have a security policy, because row lookups via the index would bypass the policy.

  • 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).Change Data Capture: Change Data Capture can leak entire rows that should be filtered to members of db_owner or users who are members of the "gating" role specified when CDC is enabled for a table (note: you can explicitly set this function to NULL to enable all users to access the change data). 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.In effect, db_owner and members of this gating role can see all data changes on a table, even if there is a security policy on the table.

  • 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.Change Tracking: Change Tracking can leak the primary key of rows that should be filtered to users with both SELECT and VIEW CHANGE TRACKING permissions. 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.Actual data values are not leaked; only the fact that column A was updated/inserted/deleted for the row with B primary key. Esto es problemático si la clave principal contiene un elemento confidencial, como un número del seguro social.This is problematic if the primary key contains a confidential element, such as a Social Security Number. Sin embargo, en la práctica, CHANGETABLE casi siempre se combina con la tabla original para obtener los datos más recientes.However, in practice, this CHANGETABLE is almost always joined with the original table in order to get the latest data.

  • 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.Full-Text Search: A performance hit is expected for queries using the following Full-Text Search and Semantic Search functions, because of an extra join introduced to apply row-level security and avoid leaking the primary keys of rows that should be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Índices de almacén de columnas: RLS no es compatible con los índices de almacén de columnas en clúster y no clúster.Columnstore Indexes: RLS is compatible with both clustered and non-clustered columnstore indexes. 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.However, because row-level security applies a function, it is possible that the optimizer may modify the query plan so that it doesn't use batch mode.

  • 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.Partitioned Views: Block predicates cannot be defined on partitioned views, and partitioned views cannot be created on top of tables that use block predicates. Los predicados de filtro son compatibles con vistas con particiones.Filter predicates are compatible with partitioned views.

  • Tablas temporales: las tablas temporales son compatibles con RLS.Temporal tables: Temporal tables are compatible with RLS. Sin embargo, los predicados de seguridad en la tabla actual no se replican automáticamente a la tabla del historial.However, security predicates on the current table are not automatically replicated to the history table. Para aplicar una directiva de seguridad a las tablas actual y del historial, debe agregar individualmente un predicado de seguridad en cada tabla.To apply a security policy to both the current and the history tables, you must individually add a security predicate on each table.

EjemplosExamples

A.A. Escenario para los usuarios que se autentican en la base de datosScenario for users who authenticate to the database

Este ejemplo crea tres usuarios y crea y rellena una tabla con seis filas.This example creates three users and creates and populates a table with six rows. Después, crea una función con valores de tabla insertados y una directiva de seguridad para la tabla.It then creates an inline table-valued function and a security policy for the table. En este ejemplo se muestra cómo seleccionar instrucciones filtradas para los distintos usuarios.The example then shows how select statements are filtered for the various users.

Cree tres cuentas de usuario que mostrarán las distintas capacidades de acceso.Create three user accounts that will demonstrate different access capabilities.

Nota

Azure SQL Data Warehouse no admite EXECUTE AS USER, por lo que debe crear el inicio de sesión para cada usuario con antelación.Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so you must CREATE LOGIN for each user beforehand. Más adelante, iniciará la sesión como el usuario apropiado para probar este comportamiento.Later, you will log in as the appropriate user to test this behavior.

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

Cree una tabla que contenga datos.Create a table to hold data.

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.Populate the table with six rows of data, showing three orders for each sales representative.

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;

Conceda acceso de lectura en la tabla para cada usuario.Grant read access on the table to each of the users.

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

Cree un esquema y una función con valores de tabla insertada.Create a new schema, and an inline table-valued function. 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').The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (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 agregando la función como un predicado de filtro.Create a security policy adding the function as a filter predicate. El estado se debe configurar en ON para habilitar la directiva.The state must be set to ON to enable the policy.

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

Pruebe ahora el predicado de filtrado seleccionando de la tabla Ventas como cada usuario.Now test the filtering predicate, by selected from the Sales table as each user.

EXECUTE AS USER = 'Sales1';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Sales2';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;
REVERT;  

Nota

Azure SQL Data Warehouse no admite EXECUTE AS USER, de modo que inicie sesión como el usuario apropiado para probar el comportamiento anterior.Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so log in as the appropriate user to test the above behavior.

El administrador debe ver las seis filas.The Manager should see all six rows. Los usuarios Sales1 y Sales2 solo deben ver sus propias ventas.The Sales1 and Sales2 users should only see their own sales.

Modifique la directiva de seguridad para deshabilitar la directiva.Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

Ahora los usuarios Sales1 y Sales2 pueden ver las seis filas.Now Sales1 and Sales2 users can see all six rows.

Conexión a la base de datos SQL para limpiar los recursosConnect to the SQL database to clean up resources

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

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

B.B. Escenarios para el uso de Seguridad de nivel de fila en una tabla externa de Azure SQL Data WarehouseScenarios for using Row Level Security on an Azure SQL Data Warehouse external table

Este breve ejemplo crea tres usuarios y una tabla externa con seis filas.This short example creates three users and an external table with six rows. Después, crea una función con valores de tabla insertados y una directiva de seguridad para la tabla externa.It then creates an inline table-valued function and a security policy for the external table. El ejemplo muestra cómo seleccionar instrucciones filtradas para los distintos usuarios.The example shows how select statements are filtered for the various users.

Cree tres cuentas de usuario que mostrarán las distintas capacidades de acceso.Create three user accounts that will demonstrate different access capabilities.

CREATE LOGIN Manager WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales1 WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales2 WITH PASSWORD = 'somepassword'
GO

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 a table to hold data.

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.Populate the table with six rows of data, showing three orders for each sales representative.

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 SQL Data Warehouse a partir de la tabla Sales creada.Create an Azure SQL Data Warehouse external table from the Sales table created.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword';

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

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

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

Conceda el permiso SELECT para la tabla externa de los tres usuarios.Grant SELECT for the three users external table.

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

Cree una directiva de seguridad en una tabla externa mediante la función de la sesión A como predicado de filtro.Create a security policy on external table using the function in session A as a filter predicate. El estado se debe configurar en ON para habilitar la directiva.The state must be set to ON to enable the policy.

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.Now test the filtering predicate, by selecting from the Sales_ext external table. Inicie sesión como cada usuario, Sales1, Sales2 y administrador.Sign in as each user, Sales1, Sales2, and manager. Ejecute el siguiente comando como cada usuario.Run the following command as each user.

SELECT * FROM Sales_ext;

El administrador debe ver las seis filas.The Manager should see all six rows. Los usuarios Sales1 y Sales2 solo deben ver sus propias ventas.The Sales1 and Sales2 users should only see their sales.

Modifique la directiva de seguridad para deshabilitar la directiva.Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);  

Ahora los usuarios Sales1 y Sales2 pueden ver las seis filas.Now the Sales1 and Sales2 users can see all six rows.

Conexión a la base de datos SQL Data Warehouse para limpiar los recursosConnect to the SQL Data Warehouse database to clean up resources

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.Connect to logical master to clean up resources.

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

C.C. Escenario para los usuarios que se conectan a la base de datos a través de una aplicación de nivel intermedioScenario for users who connect to the database through a middle-tier application

Nota

Este ejemplo no es aplicable a Azure SQL Data Warehouse, ya que en la actualidad no se admiten SESSION_CONTEXT ni los predicados de bloqueo.This example isn't applicable to Azure SQL Data Warehouse since both SESSION_CONTEXT and block predicates aren't currently supported.

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 ServerSQL Server (la aplicación).This example shows how a middle-tier application can implement connection filtering, where application users (or tenants) share the same SQL ServerSQL Server user (the application). 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.The application sets the current application user ID in SESSION_CONTEXT (Transact-SQL) after connecting to the database, and then security policies transparently filter rows that shouldn't be visible to this ID, and also block the user from inserting rows for the wrong user ID. No es necesario ningún otro cambio en la aplicación.No other app changes are necessary.

Cree una tabla que contenga datos.Create a table to hold data.

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.Populate the table with six rows of data, showing three orders for each application user.

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.Create a low-privileged user that the application will use to connect.

-- 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 a new schema and predicate function, which will use the application user ID stored in SESSION_CONTEXT to filter rows.

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.Create a security policy that adds this function as a filter predicate and a block predicate on 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.The block predicate only needs AFTER INSERT, because BEFORE UPDATE and BEFORE DELETE are already filtered, and AFTER UPDATE is unnecessary because the AppUserId column cannot be updated to other values, due to the column permission set earlier.

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.Now we can simulate the connection filtering by selecting from the Sales table after setting different user IDs in 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.In practice, the application is responsible for setting the current user ID in SESSION_CONTEXT after opening a connection.

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.Clean up database resources.

DROP USER AppUser;

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

Consulte tambiénSee Also

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