General Data Protection Regulation (GDPR) compliance for SQL Server

On April 27, 2016, the European Parliament and the council of the European Union adopted a new standard, the General Data Protection Regulation on the protection of natural persons with regard to the processing of personal data and on the free movement of such data. The GDPR takes effect on May 25, 2018 and introduces numerous security and compliance regulations and obligations to all organizations worldwide, that handle, process, collect or store personal information of EU citizens. This means that organizations, both the data processors and data controllers, will have to elevate security measures and auditing mechanisms when handling personal identifiable information (PII) of EU citizens and be able to demonstrate compliance with GDPR standard at all time, not only on regular basis (monthly, yearly), but also on demand.

To better understand personal data and the process of handling it, GDPR defines the following:

  • “‘Personal data’ means any information relating to an identified or identifiable natural person (‘data subject’); an identifiable natural person is one who can be identified, directly or indirectly, in particular by reference to an identifier such as a name, an identification number, location data, an online identifier or to one or more factors specific to the physical, physiological, genetic, mental, economic, cultural or social identity of that natural person;
  • “‘Processing’ means any operation or set of operations which is performed on personal data or on sets of personal data, whether or not by automated means, such as collection, recording, organization, structuring, storage, adaptation or alteration, retrieval, consultation, use, disclosure by transmission, dissemination or otherwise making available, alignment or combination, restriction, erasure or destruction;
  • “‘Controller’ means the natural or legal person, public authority, agency or other body which, alone or jointly with others, determines the purposes and means of the processing of personal data; where the purposes and means of such processing are determined by Union or Member State law, the controller or the specific criteria for its nomination may be provided for by Union or Member State law; “

Source: REGULATION (EU) 2016/679 OF THE EUROPEAN PARLIAMENT AND OF THE COUNCIL of 27 April 2016 on the protection of natural persons with regard to the processing of personal data and on the free movement of such data, and repealing Directive 95/46/EC (General Data Protection Regulation)

As per GDPR, organizations will have to ensure that only people which should have access to the personal information of EU citizens will have the access, and in case of unauthorized access, organizations must have mechanisms to detect and be alerted on any such event which will enable them to act swiftly and resolve any possible issues. Furthermore, in case of any data breach, organizations must provide full information on these events to their local data protection authority (DPA) and all customers concerned with the data breach in no more than 72 hours so they can act accordingly as needed.

In case of GDPR violations and failing to comply with the standard, heavy fines are imposed on organizations, resulting in fines for up to 4% of their global revenue, or up to €20 million, whichever is greater. With this in mind, it is important for all organizations that will be required to comply with GDPR to immediately take action and prepare for May 25, 2018 when non-compliant organizations will immediately face fines and penalties defined by the standard.

In general, GDPR requires access control, information integrity, control of auditing and more, similar to other compliance regulations. Per GDPR, it is necessary to utilize various methods, as well as hardware and software mechanisms that provide auditing, monitoring and capturing of user activities that use PII as well as to ensure against any unauthorized access and achieve maximum data integrity.

To Meet GDPR, it is necessary to achieve uninterrupted, continuous auditing of data flow and access and be able to report and investigate all access attempts, including unauthorized and failed access attempts. In addition, it is mandatory to audit all changes on PII and to be able to report and be alerted on any events or efforts to change or access PII.

GDPR and SQL Server

When organizations regulated by GDPR compliance standards use SQL Server to handle, process, collect and store personal identifiable information of EU citizens, it is important to ensure that all access to the sensitive data is fully transparent, that all access attempt can be detected, to ensure that auditors can always forensically investigate who saw what when and where, and that any changes to the data can be thoroughly inspected or caught.

ApexSQL Audit is a SQL Server auditing tool which allows live auditing of unlimited number of databases located on multiple SQL Server instances within domain or local area network. ApexSQL Audit allows users to audit almost 200 SQL Server events, including data (DML) and structure (DDL) changes, security events on both server and database levels, queries, backup/restore tasks, warnings, errors and more.

All audited data is stored in the central repository database which is tamper-evident and can store audited data indefinitely which allows auditing on demand via variety of out-of-the-box or fully customizable reports. Furthermore, ApexSQL Audit has built-in alert mechanisms which can alert users immediately on specific auditing events such are unauthorized access attempts, malicious changes and more. These alerts can be triggered inside the application, written to the windows event log or send via SMTP to multiple email recipients.

Achieving compliance with GDPR will not be an easy task for both small and big organizations across the globe, and they will need all the help they can get to achieve compliance before the deadline on May 25, 2018. ApexSQL Audit can help!

Here is the list of regulative that can be easily achieved with ApexSQL Audit:

Article 5 – Principles relating to processing of personal data (Chapter II Principles)

“Personal data shall be:

(f) processed in a manner that ensures appropriate security of the personal data, including protection against unauthorized or unlawful processing and against accidental loss, destruction or damage, using appropriate technical or organizational measures (‘integrity and confidentiality’).

2. The controller shall be responsible for, and be able to demonstrate compliance with, paragraph 1 (‘accountability’).”

Source: Article 5 EU GDPR “Principles relating to processing of personal data”

As mentioned above, ApexSQL Audit can audit all DDL and DML changes to structure and data as well as any access attempts to the data in SQL Server databases. Additionally, the tool allows users to create on-demand reports (both out-of-the-box or custom) at any given time so controllers and auditors can demonstrate compliance on any occasion.

Article 24 – Responsibility of the controller (Chapter IV Controller and processor)

“1. Taking into account the nature, scope, context and purposes of processing as well as the risks of varying likelihood and severity for the rights and freedoms of natural persons, the controller shall implement appropriate technical and organizational measures to ensure and to be able to demonstrate that processing is performed in accordance with this Regulation. Those measures shall be reviewed and updated where necessary.

2. Where proportionate in relation to processing activities, the measures referred to in paragraph 1 shall include the implementation of appropriate data protection policies by the controller.

3. Adherence to approved codes of conduct as referred to in Article 40 or approved certification mechanisms as referred to in Article 42 may be used as an element by which to demonstrate compliance with the obligations of the controller. “

Source: Article 24 EU GDPR “Responsibility of the controller”

Since Article 24 enforces controllers to achieve transparency of access and any processing of PII, it is necessary to perform full auditing on the data and to be able to demonstrate that the processing is performed in accordance with GDPR.

ApexSQL Audit allows users to achieve compliance with this article by providing mechanisms for users to audit all access and changes made to the SQL Server database, and also allows controllers to swiftly and timely react in case of any compliance breach, such is unauthorized access or any unauthorized processing with its alerting mechanisms which can alert controller or other relevant personnel on any even which is in breach of the compliance regulative.

Article 25 – Data protection by design and by default (Chapter IV Controller and processor)

“1. Taking into account the state of the art, the cost of implementation and the nature, scope, context and purposes of processing as well as the risks of varying likelihood and severity for rights and freedoms of natural persons posed by the processing, the controller shall, both at the time of the determination of the means for processing and at the time of the processing itself, implement appropriate technical and organizational measures, such as pseudonymizing, which are designed to implement data-protection principles, such as data minimization, in an effective manner and to integrate the necessary safeguards into the processing in order to meet the requirements of this Regulation and protect the rights of data subjects.

2. The controller shall implement appropriate technical and organizational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual’s intervention to an indefinite number of natural persons. “

Source: Article 25 EU GDPR “Data protection by design and by default”

Again, ApexSQL Audit can help users to achieve compliance with article 25. While ApexSQL Audit can be configured to audit before-after changes and capture exact values as a special feature, auditing of SQL Server events is achieved without reading the actual values in both changes or queries – instead, these are parametrized which allows full data-protection and allows ApexSQL Audit to safely audit all SQL Server events without exposing or endangering safety of sensitive/critical data in any way. This allows maximum security and allows compliance with article 25 of GDPR.

Article 32 – Security of processing (Chapter IV Controller and processor)

“1. Taking into account the state of the art, the costs of implementation and the nature, scope, context and purposes of processing as well as the risk of varying likelihood and severity for the rights and freedoms of natural persons, the controller and the processor shall implement appropriate technical and organizational measures to ensure a level of security appropriate to the risk, including inter alia as appropriate:

(b) the ability to ensure the ongoing confidentiality, integrity, availability, and resilience of processing systems and services;

(c) the ability to restore the availability and access to personal data in a timely manner in the event of a physical or technical incident;

(d) a process for regularly testing, assessing and evaluating the effectiveness of technical and organizational measures for ensuring the security of the processing.

2. In assessing the appropriate level of security account shall be taken in particular of the risks that are presented by processing, in particular from accidental or unlawful destruction, loss, alteration, unauthorized disclosure of, or access to personal data transmitted, stored or otherwise processed.

4. The controller and processor shall take steps to ensure that any natural person acting under the authority of the controller or the processor who has access to personal data does not process them except on instructions from the controller unless he or she is required to do so by Union or Member State law.”

Source: Article 32 EU GDPR “Security of processing”

With similar mechanisms mentioned above, ApexSQL Audit can help both processors and controllers achieve compliance with this article by allowing them to audit not only access attempts and changes, but also providing full information on who, when, from where, how has accessed sensitive data, if the data was changed in any way or if integrity has been compromised – both for the personal information, as well as for audited data.

Article 33 – Notification of a personal data breach to the supervisory authority (Chapter IV Controller and processor)

“1. 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 competent in accordance with Article 55, unless the personal data breach is unlikely to result in a risk to the rights and freedoms of natural persons. Where the notification to the supervisory authority is not made within 72 hours, it shall be accompanied by reasons for the delay.”

Source: Article 33 EU GDPR “Notification of a personal data breach to the supervisory authority”

Article 33 enforces a swift and actionable reaction of controller is any critical event of data breach or integrity compromise. It is of crucial to be able to react immediately and to arm controllers with all relevant data on any potential breach of personal data, and ApexSQL Audit provides such a solution. Due to its comprehensive review and alerting mechanisms, ApexSQL Audit can be configured to raise an alert which can be immediately emailed or written to windows event log on any potentially critical event that can endanger personal data safety. Furthermore, alerts triggered by ApexSQL Audit can be fully customized to include variety of information on the event which will allow full details on who, when, and how compromised the data safety, or even allow users to create their own SQL Scripts that will trigger specific alerts if a defined threshold has been reached.

With the advent of GDPR, it is critical for all organizations that in any way handle personal information of EU citizens, whether the organizations are in the European Union or anywhere else in the world, to make immediate preparations to achieve GDPR compliance, or suffer heavy penalties in case of a failure to do so. ApexSQL Audit can greatly help anyone using SQL Server databases to store/handle PII and check out several critical regulations included in GDPR.

Useful links:

October 13, 2017