Ejercicio: Administración de la autorización mediante la seguridad de nivel de columna y de fila

  • 9 minutos

En este ejercicio, se muestran ejemplos en los que puede administrar la autorización mediante la seguridad de nivel de fila y de columna.

Un ejemplo de seguridad de nivel de columna

En el ejemplo siguiente, se muestra cómo impedir que TestUser acceda a la columna SSN de la tabla Membership:

Cree la tabla Membership con la columna SSN destinada a almacenar números del seguro social:

CREATE TABLE Membership
  (MemberID int IDENTITY,
   FirstName varchar(100) NULL,
   SSN char(9) NOT NULL,
   LastName varchar(100) NOT NULL,
   Phone varchar(12) NULL,
   Email varchar(100) NULL);

Permita que TestUser acceda a todas las columnas excepto a la columna SSN, que contiene información confidencial:

GRANT SELECT ON Membership(MemberID, FirstName, LastName, Phone, Email) TO TestUser;

Las consultas ejecutadas como TestUser producirán un error si incluyen la columna SSN:

SELECT * FROM Membership;

-- Msg 230, Level 14, State 1, Line 12
-- The SELECT permission was denied on the column 'SSN' of the object 'Membership', database 'CLS_TestDW', schema 'dbo'.

Un ejemplo de seguridad de nivel de fila

En este escenario se ofrece un ejemplo 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

  • Debe tener un grupo de SQL. Vea Creación de un grupo de Synapse SQL
  • El servidor que hospeda el grupo de SQL debe estar registrado con AAD y debe tener una cuenta de almacenamiento de Azure con permisos de colaborador de blog de Storage. Siga estos pasos.
  • 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 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;