Category: GDPR

by Fran Lens 2019-06-04

📧 Monitor and Report unauthorized access to Personal Data – GDPR Series (5/5)

We finished with the GDPR series, our personal data will be more secure than ever, but it is not enough to do the work once, this work has to be constant and that is why in this last entry we will see how to Monitor and Inform those possible unauthorized access to personal data.

Remember that in the previous entries we saw a general overview of the tools, we learned to Detect where personal data are located, to manage access to these data and to protect them at all levels.

Why is this important for the 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.”

To help with monitoring and reporting, in SQL Server we can use several tools:

System-Versioned temporal tables, the first thing to note is that although it looks like it, IT IS NOT A TEMPORARY TABLE, it is a type of versioning table that allows SQL Server to save a complete history of all the changes made and a quick way to perform an analysis to know how and when a data has changed
SQL Server Audit, to create audits in the server and database layers, which may contain different specifications for different events; creation of logins, backups, table modifications…
SQL Alerts and DB Mail, using these two tools, we can define our own alerts to quickly notify a security breach, for example if we detect that a backup has been made in a location that is not the usual one.

The tools SQL Server Audit, SQL Alerts and DB Mail are old known and have been with us for a long time, so in this post we will show how to use System-Versioned Temporal Tables that is available from SQL Server 2016.

System-Versioned Temporal Tables

Creation of the versioning table

Before starting to create our System-Versioned Temporal Tables we must meet some small prerequisites:

The table to which we will add the version control must have a Primary Key
We will have to add two new columns to the table that we want to version, to control the start date and the final date, they must be datetime2 type. These two columns can be hidden using the HIDDEN flag

For our example we are going to create a new table that meets all the necessary prerequisites, and we will directly create your versioning table.

/*********************************************************************
 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

In the code we can see that the Id field will be our primary key, that the date control fields are ValidFrom and ValidTo and that the HIDDEN flag has been applied so that they are not shown when executing queries on the table.

We can also see the creation of the versioning table in the line SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[CustomersHistory] )

Now let’s look a little more in detail at the created table:

We see how the two tables (dbo.Customers and dbo.CustomersHistory) have the same column structure, but Customers has a primary key and a clustered index on the Id field, while CustomersHistory has no primary key and the clustered index is created on the ValidFrom and ValidTo date fields.

Data insertion

The next step is to make some INSERT in the table to be able to play with the data and see its operation better:

/*********************************************************************
 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')

We check that the two records have been inserted well in dbo.Customers

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

And we also check that in dbo.CustomersHistory there is nothing inserted because they are new data and have not had any change.

-- 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'

After these Updates, we will wait about 10-15 seconds and perform two more.

-- 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'

If we now check the data in the dbo.Customers table, we should see how the Phone column of the second row has changed:

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

And if we check the table dbo.CustomersHistory we will see how new rows have been inserted, 4 in particular, one for each update made.

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

We can also try to delete a record

-- 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]

And that in the table dbo.CustomersHistory a new row has been added

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

Playing with versioning data

Now that we have made a series of operations and simple checks in the tables, we will see what other functionalities bring us the System-Versioned Temporal Tables.

We can see all the different versions that have had the data of a table using 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

We can see how a row was at a specific moment in time using FOR SYSTEM_TIME AS OF (that’s why I waited 10-15 seconds between the first Updates and the second ones)

-- 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'

Deleting the table

Finally, we will see the correct way to perform the drop or to undo everything we have created.

Let’s try to delete the table dbo.Customers

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

An error occurs because we first need to disable versioning.

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

By disabling the versioning, we can check how the two tables have been converted into two simple tables.

And once everything is disabled is when we can proceed to the deletion of the two tables.

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

You can get more information about System-Versioned Temporal Tables in the official documentation.

Through all the entries in the GDPR series we have covered all the necessary aspects to comply with this regulation and to protect the personal data that our database contains.

If you have any questions about it, do not hesitate to post a comment and I will respond as soon as possible.

GDPR Series:

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.