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.

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:

We check that the two records have been inserted well in 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.

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

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

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

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.

We can also try to delete a record

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

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

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

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)

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

An error occurs because we first need to disable versioning.

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.

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:

  1. How to adapt SQL Server to the GDPR
  2. Discovering and classifying personal data with SQL Server
  3. Managing access and use of data with SQL Server
  4. Protecting data at Rest, in Use and in Transit
  5. Monitor and Report unauthorized access to Personal Data

If you want us to help your business or company to comply with the GDPR contact us at info@aleson-itc.com or call us at +34 962 681 242

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.