After months (years ?) seeing warnings and calls for attention to companies that are in a way or another impacted by the GDPR regulation and after being asked every now and then the same questions over and over again, I decided to write a few posts covering the technologies that can be used to implement the technical mechanisms required to enforce EU GDPR. I will cover major RDBMS and today we start with one of the most populars : Microsoft SQL Server.

Emphasis is put in synthetizing as much information as possible about what is available to enforce this or that GDPR recommendation. My aim is not to discuss every feature, just to guide the reader to accredited sources of information.

Let’s first start recalling GDPR Data Security requirements :

 

Risk Assessment

Controllers must carry on a Data Protection Impact Analysis on sensitive Personal Data and have to provide evidence that Personal Data is safe.

 

Attack Prevention

GDPR recommends a number of techniques to prevent security attacks.

Encryption: core technology to ensure data is not made accessible to unauthorized parties.
Anonymization: complete obfuscation of data.
Pseudonymization: minimize the linkability of data with the actual identity of data subjects.
Data Minimization: data collection and retention must be minimized to the strictly necessary.
Access Control: GDPR recommends measures at two levels, namely,
Privileged users having access to Personal Data must be scrutinized to avoid breaches due to compromised accounts.
Fine-grained access control to Personal Data ensures data is accessed only for a specific purpose and within a well-defined process.

 

Monitoring

GDPR not only requires that all activities on Personal Data must be recorded, it also recommends a centralized audit management by the Controller officer.

Moreover, alerting mechanisms must be in place to notify the controller in case of unusual activities on Personal Data.

After receiving the above list of GDPR compliance requirements, many IT departments assign this task to a person who will be responsible to tackle GDPR-related activities and organize the required actions, which could be summarized as follows :

Risk Assessment

• Personal Data identification
• Access, role and privilege analysis
• Security configuration analysis

Attack Prevention

• Encryption of data and data transfers
• Anonimyzation/Pseudonymization of Personal Data
• Personal Data Access Control

Monitoring

• Audit implementation and centralization
• Audit event notification implementation

As you can see, that’s not straightforward; especially considering that many applications are not designed with Data Protection as a core requirement (eg “Security By Design”).

Obviously the more Personal Data is involved, the more work will be required to comply with GDPR, so the sooner the Risk Assessment is started, the better, as there are many complications that will inevitably appear, technical or organizational.

The typical one is realizing that the current infrastructure does not support or provide the technology required by GDPR and that a substantial budget will need to be allocated to be compliant. Another common situation is realizing that there’s no single customer view and that there is duplicate data all over

the organization, prompting for a long data-cleaning process to prepare Personal Data for GDPR.

Fortunately for SQL Server users, enforcing GDPR will be (almost) painless, and we will explain why.

 

SQL Server built-in features and tools of the trade

SQL Server provides the necessary tools and technologies to address each of the above mentioned tasks.

 

Risk Assessment

• Personal Data identification: SQL Server Management Studio (SSMS) and Dynamic Management Views (DMVs).
• Access, role and privilege analysis: SSMS, DMVs
• Security configuration analysis: SSMS, DMVs

This task will involve data analysts and business people with domain knowledge helping in the identification of Personal Data,  plus DBAs with expertise in SQL Server security. The latter role should have a through understanding of SQL Server configuration and data access. An attack surface area analysis must be done in order to identify security vulnerabilities that might open access to Personal Data in not so obvious ways.

One of the first practical actions that a DBA can undertake is detecting (and disabling) all unnecessary features. Here are a few of these features to consider :

• Disabling the trustworthy bit
• Filestream
• OLE automation
• Cross-database ownership chaining
• XP_CMDSHELL
• CLR
• External scripts
• Distributed queries

Moreover, access, role and privilege analysis will naturally precede Personal Data access control, part of the Attack Prevention task we describe below. In this contect, SQL Server provides a number of built-in features to enforce data access by authorized users : Authentication and Authorization.

Authentication can be implemented in 2 ways: Windows Authentication and Mixed Authentication (with SQL Server Authentication). It is generally a best practice to use Windows Authentication as it helps centralizing access management through Active Directory and an appropriate role and privilega management through user mappings.

Authorization is the implementation of an Access policy through role membership and object-level permissions. SQL Server provides all that’s needed to ensure that only authorized users (and applications) can access Personal Data.

 

Attack Prevention

• Encryption of data and data transfers: Transparent Data Encryption (TDE), Transport Layer Security (TLS), Always Encrypted
• Anonimyzation/Pseudonymization of Personal Data: Dynamic Data Masking
• Personal Data Access Control: Object-level permissions, Row-level security

In addition to these features for SQL Server databases, SQL Server Azure databases have other features

 

Monitoring

• Audit implementation and centralization: SQL Server Audit, Azure SQL Server Threat Detection, System-versioned Temporal Tables
• Audit event notification: SQL Server Agent and basic T-SQL

Conclusion

SQL Server provides a rich set of features and a toolset to enforce GDPR.

Most – if not all – of these features are available in all database editions (as from version 2016 RC1). This is remarkable since features such as encryption, fine-grained database audit or Dynamic Data Masking would require a licence upgrade with other RDBMs vendors.

A newer feature – Temporal Tables – comes in very handy for reconstructing the state of data at any point in time and will be very appreciate as an auditing companion for Personal Data.

Next post will cover GDPR and Oracle in the same way. Stay tuned.

 

References

SQL Server 2012 Security Best Practices whitepaper