Categoría: Sin categorizar

por Fran Lens 2019-06-04

📧 Monitorizar y Reportar accesos no autorizados a Datos Personales – Serie GDPR (5/5)

Ya terminamos con la serie GDPR, nuestros datos personales van a estar más seguros que nunca, pero no basta con hacer el trabajo una vez, este trabajo tiene que ser constante y por eso en este último punto vamos a ver como Monitorizar e Informar esos posibles accesos no autorizados a los datos personales.

Recordar que en las anteriores entradas vimos un vistazo general de las herramientas, aprendimos a Detectar donde se encuentran los datos personales, a administrar los accesos a estos datos y a protegerlos en todos los niveles.

¿Por qué esto es importante para el GDPR?

The GDPR, as part of the data protection requirement, stipulates a requirement that “Each controller… shall maintain a record of processing activities under its responsibility.”
GDPR Article 30(1)—“Records of processing activities.”
The GDPR has a clear requirement regarding data breaches: “In the case of a personal data breach, the controller shall without undue delay and, where feasible, not later than 72 hours after having become aware of it, notify the personal data breach to the supervisory authority.”
GDPR Article 33(1)—“Notification of a personal data breach to the supervisory authority.”

Para ayudar con la monitorización y el reporte, en SQL Server podemos utilizar varias herramientas:

System-Versioned temporal tables, lo primero destacar que aunque por su nombre lo parezca, NO ES UNA TABLA TEMPORAL, es un tipo de tabla de versionado que permite a SQL Server guardar un histórico completo de todos los cambios realizados y una forma rápida de realizar un análisis para saber cómo y cuándo un dato ha cambiado.
SQL Server Audit, para crear auditorias en las capas de servidor y base de datos, que pueden contener diferentes especificaciones para diferentes eventos; creación de logins, copias de seguridad, cambios en una tabla…
SQL Alerts and DB Mail, usando estas dos herramientas, podemos definir nuestras propias alertas para notificar rápidamente una brecha de seguridad, por ejemplo si detectamos que se ha realizado un backup en una localización que no es la usual.

Las herramientas SQL Server Audit, SQL Alerts y DB Mail son viejas conocidas y ya llevan muchas versiones con nosotros, por eso en esta entrada vamos a mostrar como utilizar System-Versioned Temporal Tables que está disponible desde SQL Server 2016.

System-Versioned Temporal Tables

Creación de la tabla de versionado

Antes de comenzar a crear nuestra tabla temporal con control de versiones del sistema (el nombre en inglés suena mejor) debemos cumplir con unos pequeños prerrequisitos:

La tabla a la que añadiremos el versionado debe tener una Clave Primaria
Deberemos agregar dos nuevas columnas a la tabla que queramos versionar, para controlar la fecha inicio y la fecha final, tienen que ser del tipo datetime2. Estas dos columnas se pueden ocultar usando el flag HIDDEN

Para nuestro ejemplo vamos a crear una nueva tabla que cumple con todos los prerrequisitos necesarios, y directamente vamos a crearle su tabla de versionado.

/*********************************************************************
 Temporal table creation
 ********************************************************************/
CREATE TABLE [dbo].[Customers](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[Surname] [varchar](100) NOT NULL,
	[Phone] [int] NULL,
	[Mail] [varchar](50) NULL,
	[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
 CONSTRAINT [Customers_PK] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[CustomersHistory] )
)
GO

En el código podemos ver que el campo Id será nuestra clave primaria, que los campos de control de fechas son ValidFrom y ValidTo y que se les ha aplicado el flag HIDDEN para que no se muestren al ejecutar consultas sobre la tabla.

También podemos ver la creación de la tabla de versionado en la linea SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[CustomersHistory] )

Ahora vamos a fijarnos un poco más en detalle en la tabla creada:

Vemos como las dos tablas (dbo.Customers y dbo.CustomersHistory) tienen la misma estructura de columnas, pero Customers tiene una clave primaria y un índice clustered sobre el campo Id, mientras que CustomersHistory no tiene clave primaria y el índice clustered está creado sobre los campos de fecha ValidFrom y ValidTo.

Inserción de datos

El siguiente paso es realizar algunos INSERT en la tabla para poder jugar con los datos y ver mejor su funcionamiento:

/*********************************************************************
 DML Instructions
 ********************************************************************/

INSERT INTO [dbo].[Customers]
           ([Name]
           ,[Surname]
           ,[Phone]
           ,[Mail])
     VALUES
           ('Bill'
           ,'Gates'
           ,555555551
           ,'bill.gates@microsoft.com')
		   
INSERT INTO [dbo].[Customers]
           ([Name]
           ,[Surname]
           ,[Phone]
           ,[Mail])
     VALUES
           ('Satya'
           ,'Nadella'
           ,555555552
           ,'satyan@microsoft.com')

Comprobamos que los dos registros se han insertado bien en dbo.Customers

-- Data Inserted
SELECT * FROM [dbo].[Customers]

Y también comprobamos que en dbo.CustomersHistory no hay nada insertado debido a que son datos nuevos y no han tenido ningún cambio.

-- Nothing at History
SELECT * FROM [dbo].[CustomersHistory]

Ahora vamos a probar a cambiar los datos que tenemos insertados, primero realizaremos dos UPDATE:

-- Update Satya's Phone 1
UPDATE [dbo].[Customers] SET Phone = 666666662 WHERE Name = 'Satya'

-- Update Satya's Phone 2
UPDATE [dbo].[Customers] SET Phone = 666666663 WHERE Name = 'Satya'

Después de estos Updates, esperaremos unos 10-15 segundos y realizaremos otros dos.

-- WAIT 10 SECONDS
-- Update Satya's Phone 3
UPDATE [dbo].[Customers] SET Phone = 666666664 WHERE Name = 'Satya'

-- Update Satya's Phone 4
UPDATE [dbo].[Customers] SET Phone = 666666665 WHERE Name = 'Satya'

Si ahora comprobamos los datos de la tabla dbo.Customers, tendríamos que ver como la columna Phone de la segunda fila ha cambiado:

-- Data Updated
SELECT * FROM [dbo].[Customers]

Y si comprobamos la tabla dbo.CustomersHistory veremos como se han insertado nuevas filas, 4 en concreto, una por cada update realizado.

-- New rows at History
SELECT * FROM [dbo].[CustomersHistory]

También podemos probar a borrar un registro

-- Delete Satya's row
DELETE FROM [dbo].[Customers] WHERE Name = 'Satya'

Comprobamos que el dato se ha borrado de la tabla dbo.Customers

-- Data Deleted
SELECT * FROM [dbo].[Customers]

Y que en la tabla dbo.CustomersHistory se ha añadido una nueva fila

-- New row at History
SELECT * FROM [dbo].[CustomersHistory]

Jugando con los datos de versionado

Ahora que ya hemos realizado una serie de operaciones y comprobaciones simples en las tablas, vamos a ver que otras funcionalidades nos aportan las System-Versioned Temporal Tables.

Podemos ver todas las distintas versiones que han tenido los datos de una tabla utilizando FOR SYSTEM_TIME ALL

-- All the versions of the rows in the table using FOR SYSTEM_TIME ALL
SELECT * FROM [Customers]
FOR SYSTEM_TIME ALL

Podemos ver cómo se encontraba una fila en un momento específico del tiempo usando FOR SYSTEM_TIME AS OF (por eso he esperado 10-15 segundos entre los primeros Updates y los segundos)

-- Data in Customers table at a specific point in time
SELECT * FROM [Customers]
FOR SYSTEM_TIME AS OF '2019-05-21 14:26:34.2551348'

Borrado de la tabla

Por último, vamos a ver la correcta forma de realizar el borrado o de deshacer todo lo que hemos creado.

Vamos a intentar borrar la tabla dbo.Customers

-- Try to Drop the Temporal TABLE
DROP TABLE [dbo].[Customers]
-- ERROR!!

Ocurre un error debido a que primero tenemos que deshabilitar el versionado.

-- First you have to disable System Versioning FOR DROP OR ALTER TABLE
ALTER TABLE [dbo].[Customers] SET (SYSTEM_VERSIONING = OFF);

Al deshabilitar el versionado, podemos comprobar como las dos tablas se han convertido en dos tablas simples.

Y una vez todo deshabilitado es cuando podemos proceder al borrado de las dos tablas.

-- With System Versioning Off both tables will become in normal tables and you can drop it
DROP TABLE [dbo].[Customers]
DROP TABLE [dbo].[CustomersHistory]

Puedes obtener más información sobre System-Versioned Temporal Tables en la documentación oficial.

A través de todas las entradas de la serie GDPR hemos cubierto todos los aspectos necesarios para cumplir con esta reglamentación y para que los datos personales que albergue nuestra base de datos estén protegidos.

Si tienes alguna duda al respecto no dudes en poner un comentario y lo responderé a la mayor brevedad posible.

Serie completa de GDPR:

Fran Lens

Consultor Senior SQL Server & BI con 9 años de experiencia, MCSE Data Platform con conocimientos de toda la herramienta y enfocado principalmente a la detección y mejora de problemas de rendimiento en Base de Datos. En mi tiempo libre soy un gran aficionado a la fotografía de estilo urbano y de lugares abandonados.