Enmascarado dinámico de datos con SQL Server 2016

Buenas noticias! Ahora SQL Server 2016, así como Azure SQL Database V12 traen una nueva característica que simplifica el manejo de información sensible

Atrás quedaron los días en los que teníamos que programar procedimientos y funciones que alteraban y enmascaraban los datos. Esto se hacía normalmente cuando pasamos información desde producción hacia QA o desarrollo para hacer pruebas con datos reales. Hay un buen libro digital gratuito (en ingles) sobre seguridad con SQL Server, escrito por John Magnabosco, que orienta al respecto y lo pueden bajar desde este link: Protecting SQL Server Data

Otro problema se da cuando queremos ocultar cierta información directamente en los ambientes de producción. Allí había que implementar funciones para criptografía, como ENCRYPTBYKEY y DECRYPTBYKEY, las cuales implicaban hacer cambios en el código, probar e implementar

Entonces, la nueva funcionalidad de enmascarado se implementa al momento de crear una tabla. Para cada campo que queremos enmascarar se utiliza la sentencia MASKED WITH. Junto con dicha sentencia hay que utilizar una función. Tenemos 4 funciones a disposición:

Default.- Enmascarado total de acuerdo con los tipos de datos de los campos designados. Muestra el valor mínimo posible según el tipo de dato
Email.- Muestra la primera letra del correo electrónico y el sufijo “.com”
Aleatorio (random).-Para datos numéricos; muestra un valor aleatorio entre un rango mínimo y máximo
Personalizado (partial).-Para datos caracter; muestra la primera y última letra y en el medio un caracter personalizado

A continuación, un ejemplo de como implementar el enmascarado nativo, utilizando cada una de las funciones descritas en el párrafo anterior:

--CREACION DE UNA TABLA CON LA FUNCION MASKED WITH
CREATE TABLE dbo.Empleado (
EmpleadoID int IDENTITY PRIMARY KEY,  
Nombre varchar(100) NOT NULL,
Direccion varchar(100) MASKED WITH (FUNCTION = 'PARTIAL(1,"???",0)') NOT NULL,
FlagDeConfianza bit MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'EMAIL()') NULL,
FechaUltimoAscenso date MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
Salario decimal(15,2) MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
Bono decimal(15,2) MASKED WITH (FUNCTION = 'RANDOM(10,100)') NULL);

--CREACION DE UNA TABLA CON LA FUNCION MASKED WITH

CREATE TABLE dbo.Empleado (

EmpleadoID int IDENTITY PRIMARY KEY,

Nombre varchar(100) NOT NULL,

Direccion varchar(100) MASKED WITH (FUNCTION = 'PARTIAL(1,"???",0)') NOT NULL,

FlagDeConfianza bit MASKED WITH (FUNCTION = 'DEFAULT()') NULL,

Email varchar(100) MASKED WITH (FUNCTION = 'EMAIL()') NULL,

FechaUltimoAscenso date MASKED WITH (FUNCTION = 'DEFAULT()') NULL,

Salario decimal(15,2) MASKED WITH (FUNCTION = 'DEFAULT()') NULL,

Bono decimal(15,2) MASKED WITH (FUNCTION = 'RANDOM(10,100)') NULL);

NOTA: si la tabla ya existe se utilizan el comando ALTER TABLE …ALTER COLUMN para incluir el enmascarado

Una vez creada la tabla, insertamos registros de prueba

--INSERCION DE REGISTROS
INSERT INTO dbo.Empleado (Nombre,Direccion,FlagDeConfianza,Email,FechaUltimoAscenso,Salario,Bono)
VALUES ('Ana Ramirez', 'Calle 1 123 Surquillo', 0, 'aramirez@dblearner.com', '20160515', 2800, 300),
          ('Guillermo Ruiz', 'Calle 2 321 Lince', 1, 'gruiz@dblearner.com', null, 2400, null)

--INSERCION DE REGISTROS

INSERT INTO dbo.Empleado (Nombre,Direccion,FlagDeConfianza,Email,FechaUltimoAscenso,Salario,Bono)

VALUES ('Ana Ramirez', 'Calle 1 123 Surquillo', 0, 'aramirez@dblearner.com', '20160515', 2800, 300),

('Guillermo Ruiz', 'Calle 2 321 Lince', 1, 'gruiz@dblearner.com', null, 2400, null)

De aquí en adelante, el enmascarado de los datos va a depender del nivel de seguridad del usuario que realiza la consulta. Si el usuario tiene privilegio de db_owner, podrá ver los datos sin problemas:

--VISUALIZACION DE DATOS SIN MASCARA
SELECT * FROM dbo.Empleado;

1 2	--VISUALIZACION DE DATOS SIN MASCARA SELECT * FROM dbo.Empleado;

Pero si el usuario no es db_owner, visualizará los datos enmascarados. Vamos a crear un usuario para probar el enmascarado de datos , otorgándole únicamente permiso de lectura a la tabla de prueba creada en los pasos previos

--CREACION DE UN USUARIO SIN LOGIN PARA PRUEBAS, SOLO CON PERMISO DE LECTURA
CREATE USER Analista WITHOUT LOGIN; 
GRANT SELECT ON dbo.Empleado TO Analista ;

--CREACION DE UN USUARIO SIN LOGIN PARA PRUEBAS, SOLO CON PERMISO DE LECTURA

CREATE USER Analista WITHOUT LOGIN;

GRANT SELECT ON dbo.Empleado TO Analista ;

Ahora procedemos a consultar los datos con el usuario creado. Para simplificar la prueba usamos impersonación con el comando EXECUTE AS

--VISUALIZACION DE LOS DATOS CON UN USUARIO QUE NO ES ADMNINISTRADOR
EXECUTE AS USER = 'Analista ';  
SELECT * FROM dbo.Empleado;  
REVERT;

--VISUALIZACION DE LOS DATOS CON UN USUARIO QUE NO ES ADMNINISTRADOR

EXECUTE AS USER = 'Analista ';

SELECT * FROM dbo.Empleado;

REVERT;

Obsérvese como los datos se muestran enmascarados, según la definición al momento de crear la tabla.

El campo Dirección fue enmascarado con una función partial; muestra la primera letra seguida del caracter “?”
El campo FlagDeConfianza fue encriptado con la función default; muestra el menor valor posible para el tipo de dato
El campo Email fue encriptado con la función Emal, muestra la primera letra seguida X y el sufijo “.com”
El campo FechaUltimoAscenso fue encriptado con la función default; muestra el menor valor posible para el tipo de dato
El campo Salario fue encriptado con la función default; muestra el menor valor posible para el tipo de dato
El campo Bono fue encriptado con la función random; muestra un valor aleatorio con un rango de 10 a 100

También podemos otorgar privilegios a un usuario que no es db_owner para que pueda ver los datos sin la máscara, a través del permiso UNMASK:

--SE OTORGA PERMISO DE UNMASK (QUITAR MASCARA AL USUARIO)
GRANT UNMASK TO Analista;

1 2	--SE OTORGA PERMISO DE UNMASK (QUITAR MASCARA AL USUARIO) GRANT UNMASK TO Analista;

Con esto el usuario podrá ver los datos originales al momento de hacer las consultas. Si queremos que nuevamente enmascare los datos revocamos el permiso otorgado en el paso previo:

--SE REVOCA PERMISO DE UNMASK (QUITAR MASCARA AL USUARIO)
REVOKE UNMASK TO Analista;

1 2	--SE REVOCA PERMISO DE UNMASK (QUITAR MASCARA AL USUARIO) REVOKE UNMASK TO Analista;

Un tema muy importante es que esto no se trata de encriptación o cifrado de datos, Es solo una máscara para visualización. El dato original no es alterado. Entonces, si el usuario tiene acceso apara consultas directas a la base de datos podría hacer esto:

EXECUTE AS USER = 'Analista ';  
SELECT * FROM dbo.Empleado WHERE Salario BETWEEN 2500 AND 3000
REVERT;

EXECUTE AS USER = 'Analista ';

SELECT * FROM dbo.Empleado WHERE Salario BETWEEN 2500 AND 3000

REVERT;

Mediante un predicado, el usuario ha filtrado el dato de salario en base a un rango. De esa forma puede ver el nombre del empleado y conocer que su salario está entre 2,500 y 3,000, aunque los datos se muestren enmascarados

Comparte Este artículo:

Deja una respuesta Cancelar la respuesta