GDPR and the SQL Security Suite

by Jul 20, 2018

On May 25th, 2018, GDPR (General Data Protection Regulation) went into effect.  This regulation affects anyone who collects PII (Personally Identifiable Information) on EU Members.  This means that even if you are a US-based company, you may still be held accountable to GDPR standards and you may still be assessed GDPR fines.   

You may have seen a flurry of emails from service providers with privacy updates.  Many organizations from small businesses to large enterprises are experiencing the impact of these regulatory changes and have taken actions like sending emails with privacy policy updates, language updates on websites regarding the usage of cookies.  Organizations should have on-going monitoring to ensure that they are compliant, and using security applications can help to define and implement consistent processes.

As with all regulations, you should engage your own legal counsel to read and interpret the regulation and make sure that you are in compliance for your company.  Reading articles and blog posts can help you to navigate the sea of information but only full legal counsel can determine what is right for you.

The SQL Security Suite (which includes SQL Compliance Manager and SQL Secure) addresses many of the needs that are outlined in the GDPR documentation.  This blog will point out some of the key articles of GDPR that we work to address and describe how our products can meet your security and audit needs.

In a general sense, SQL Compliance Manager can help you to:

  • Observe and record logins and failed logins
  • Detect security changes and administrative changes
  • Document who has had access to personally identifiable information via before-after data and sensitive column searches
  • Track DDL (Database Definition) and DML (Database Modification) Events
  • Turn up the volume on events conducted by the users who you have granted privileged access to

SQL Secure can help you to:

  • Audit server roles, users and principals as well as server configurations and settings
  • Configure policies on what data should be masked/encrypted
  • Check servers for security best practices using permission checks

We’ll take a moment to break down a few of the articles in GDPR that the SQL Security Suite directly addresses.

Article 5

Article 5 addresses the standards regarding processing personal data.  In general it says that you must process data lawfully, fairly, and in a way that your users understand.  You need to ensure that you are very specific about how you process that data. You need to limit the collection of data to only what is necessary.

What the GDPR text says:

  1. Personal data shall be:
  1. processed lawfully, fairly and in a transparent manner in relation to the data subject (‘lawfulness, fairness and transparency’);
  2. collected for specified, explicit and legitimate purposes and not further processed in a manner that is incompatible with those purposes; further processing for archiving purposes in the public interest, scientific or historical research purposes or statistical purposes shall, in accordance with Article 89(1), not be considered to be incompatible with the initial purposes (‘purpose limitation’);
  3. adequate, relevant and limited to what is necessary in relation to the purposes for which they are processed (‘data minimisation’);
  4. accurate and, where necessary, kept up to date; every reasonable step must be taken to ensure that personal data that are inaccurate, having regard to the purposes for which they are processed, are erased or rectified without delay (‘accuracy’);
  5. kept in a form which permits identification of data subjects for no longer than is necessary for the purposes for which the personal data are processed; personal data may be stored for longer periods insofar as the personal data will be processed solely for archiving purposes in the public interest, scientific or historical research purposes or statistical purposes in accordance with Article 89(1) subject to implementation of the appropriate technical and organisational measures required by this Regulation in order to safeguard the rights and freedoms of the data subject (‘storage limitation’);
  6. processed in a manner that ensures appropriate security of the personal data, including protection against unauthorised or unlawful processing and against accidental loss, destruction or damage, using appropriate technical or organisational measures (‘integrity and confidentiality’)
  7. The controller shall be responsible for, and be able to demonstrate compliance with, paragraph 1 (‘accountability’).

How the SQL Security Suite addresses this:

In SQL Compliance Manager, you can set up monitoring of DDL and DML data for the privileged users in your system. This will help you to demonstrate compliance by showing that only those with authorized access to the data are able to interact with it.  Additionally, you can set up specific tracking on the tables and columns that contain PII data using Sensitive Columns and Before-After Data.

Article 13 (1)

Article 13 talks about when PII data is collected from the user, and what information needs to be provided to the user so that they know how their data is going to be used.

What the GDPR text says:

Where personal data relating to a data subject are collected from the data subject, the controller shall, at the time when personal data are obtained, provide the data subject with all of the following information:

  1. the identity and the contact details of the controller and, where applicable, of the controller’s representative;
  2. the contact details of the data protection officer, where applicable;
  3. the purposes of the processing for which the personal data are intended as well as the legal basis for the processing;
  4. where the processing is based on point (f) of Article 6(1), the legitimate interests pursued by the controller or by a third party;
  5. the recipients or categories of recipients of the personal data, if any
  6. where applicable, the fact that the controller intends to transfer personal data to a third country or international organisation and the existence or absence of an adequacy decision by the Commission, or in the case of transfers referred to in Article 46 or 47, or the second subparagraph of Article 49(1), reference to the appropriate or suitable safeguards and the means by which to obtain a copy of them or where they have been made available.

How the SQL Security Suite addresses this:

SQL Compliance Manager allows you to set up auditing on sensitive columns and before-after data so that you can prove that you are remaining in compliance by only using the data in the ways that you have outlined to the user.

Article 15 (1)

Article 15 talks about the rights that a user has to know whether their data is being processed and what data is being processed.

What the GDPR text says:

The data subject shall have the right to obtain from the controller confirmation as to whether or not personal data concerning him or her are being processed, and, where that is the case, access to the personal data and the following information:

  1. the purposes of the processing
  2. the categories of personal data concerned;
  3. the recipients or categories of recipient to whom the personal data have been or will be disclosed, in particular recipients in third countries or international organisations;
  4. where possible, the envisaged period for which the personal data will be stored, or, if not possible, the criteria used to determine that period;
  5. the existence of the right to request from the controller rectification or erasure of personal data or restriction of processing of personal data concerning the data subject or to object to such processing;
  6. the right to lodge a complaint with a supervisory authority;
  7. where the personal data are not collected from the data subject, any available information as to their source;
  8. the existence of automated decision-making, including profiling, referred to in Article 22(1) and (4) and, at least in those cases, meaningful information about the logic involved, as well as the significance and the envisaged consequences of such processing for the data subject.

How the SQL Security Suite addresses this:

SQL Compliance Manager can provide audit details surrounding the information about a user that was processed via our sensitive column and before-after data functionality.

SQL Secure can view and configure policies around server principals, users, roles, etc. to ensure that least privilege access is maintained.

SQL Secure checks servers for dozens of security best practices and hardening techniques including user permission checks.  Changes to roles or server settings can be captured through Snapshots Assessments. SQL Secure can be configured to send email alerts for risk items.

Article 24

Article 24 says that the data controller (the person who gathers and uses the data) has to ensure that processing is handled as GDPR expects.

What the GDPR text says:

  • 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 organisational measures to ensure and to be able to demonstrate that processing is performed in accordance with this Regulation. 2 Those measures shall be reviewed and updated where necessary
  • 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.
  • 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.

How the SQL Security Suite addresses this:

SQL Compliance Manager can track Logins and Failed Logins to ensure that only the authorized people are accessing and processing the PII data.  

SQL Compliance Manager can audit sensitive columns and before-after data to ensure that the data controller is meeting their processing responsibilities.

Article 25

Article 25 states that data controllers (those who gather and process PII data) must implement data protection principles in an effective manner and integrate any safeguards necessary to protect the data.

What the GDPR text says:

  • 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 organisational measures, such as pseudonymisation, which are designed to implement data-protection principles, such as data minimisation, 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.
  • 1 The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. 2 That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. 3 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.
  • An approved certification mechanism pursuant to Article 42 may be used as an element to demonstrate compliance with the requirements set out in paragraphs 1 and 2 of this Article.

How the SQL Security Suite addresses this:

SQL Compliance Manager can track logins and failed logins which will allow you to document that only those who should have access to the data have access.  

SQL Compliance Manager can track the activity of privileged users to show in an audit that they are only performing the activities that they are authorized for.

SQL Compliance Manager can track sensitive columns and before-after data (information before and after a change) to show in an audit that the data was protected at all times.

SQL Secure can set Dynamic Data Masking on specific rows in a database and obscure sensitive data via encryption.  It can let you know if data masking and encryption are enabled.

Database role members and server role members should be regularly audited to determine that users have the appropriate levels of permissions set.  It can also detect whether there are an excessive number of users with elevated permissions or any orphaned users (users that have been granted access to a database originally through Windows Domain Group but were removed from the Windows Domain Group without being removed from SQL Server permissions).

SQL Secure can help you review the public and guest roles to make sure that they are not being exploited or granted any explicit permissions.

SQL Secure can detect if SQL Server has been configured to force connect clients to use transport-layer encryption.

Article 30

Article 30 states that you need to maintain a record of processing activities.

What the GDPR text says:

  • 1 Each controller and, where applicable, the controller’s representative, shall maintain a record of processing activities under its responsibility. 2 That record shall contain all of the following information:
  • the name and contact details of the controller and, where applicable, the joint controller, the controller’s representative and the data protection officer;
  • the purposes of the processing;
  • a description of the categories of data subjects and of the categories of personal data;
  • the categories of recipients to whom the personal data have been or will be disclosed including recipients in third countries or international organisations;
  • where applicable, transfers of personal data to a third country or an international organisation, including the identification of that third country or international organisation and, in the case of transfers referred to in the second subparagraph of Article 49(1), the documentation of suitable safeguards;
  • where possible, the envisaged time limits for erasure of the different categories of data;
  • where possible, a general description of the technical and organisational security measures referred to in Article 32(1).
  • Each processor and, where applicable, the processor’s representative shall maintain a record of all categories of processing activities carried out on behalf of a controller, containing:
  • the name and contact details of the processor or processors and of each controller on behalf of which the processor is acting, and, where applicable, of the controller’s or the processor’s representative, and the data protection officer;
  • the categories of processing carried out on behalf of each controller;
  • where applicable, transfers of personal data to a third country or an international organisation, including the identification of that third country or international organisation and, in the case of transfers referred to in the second subparagraph of Article 49(1), the documentation of suitable safeguards;
  • where possible, a general description of the technical and organisational security measures referred to in Article 32(1).
  • The records referred to in paragraphs 1 and 2 shall be in writing, including in electronic form.
  • The controller or the processor and, where applicable, the controller’s or the processor’s representative, shall make the record available to the supervisory authority on request.
  • The obligations referred to in paragraphs 1 and 2 shall not apply to an enterprise or an organisation employing fewer than 250 persons unless the processing it carries out is likely to result in a risk to the rights and freedoms of data subjects, the processing is not occasional, or the processing includes special categories of data as referred to in Article 9(1) or personal data relating to criminal convictions and offences referred to in Article 10.

How the SQL Security Suite addresses this:

SQL Compliance Manager is built to make audits easy and capture all of the activities, especially where relevant to PII data. With SQL Compliance Manager, you can track DDL and DML activity, security and administrative activities, activities conducted by privileged users, user access via logins and failed logins and specific activity regarding sensitive columns and before-after data.

Article 32

Article 32 says that you need to secure the data appropriately to the potential risk of data breach.

What the GDPR text says:

  • 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 organisational measures to ensure a level of security appropriate to the risk, including inter alia as appropriate:
  • the pseudonymisation and encryption of personal data;
  • the ability to ensure the ongoing confidentiality, integrity, availability and resilience of processing systems and services;
  • the ability to restore the availability and access to personal data in a timely manner in the event of a physical or technical incident;
  • a process for regularly testing, assessing and evaluating the effectiveness of technical and organisational measures for ensuring the security of the processing.
  • 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, unauthorised disclosure of, or access to personal data transmitted, stored or otherwise processed.
  • Adherence to an approved code of conduct as referred to in Article 40 or an approved certification mechanism as referred to in Article 42 may be used as an element by which to demonstrate compliance with the requirements set out in paragraph 1 of this Article.
  • 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.

How the SQL Security Suite addresses this:

SQL Compliance Manager can track activity on sensitive columns and audit that activity via sensitive column functionality and before-after data functionality.

SQL Secure can configure policies on what data should be masked and encrypted.

SQL Secure can notify you if encryption (TDE or Always Encrypted) has been disabled.

SQL Secure can limit access to specific rows for read or write operations.

Article 33

Article 33 says that when a data breach has occurred, you must notify a supervising authority within 72 hours.

What the GDPR text says:

  • 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. 2 Where the notification to the supervisory authority is not made within 72 hours, it shall be accompanied by reasons for the delay.
  • The processor shall notify the controller without undue delay after becoming aware of a personal data breach.
  • The notification referred to in paragraph 1 shall at least:
  • describe the nature of the personal data breach including where possible, the categories and approximate number of data subjects concerned and the categories and approximate number of personal data records concerned;
  • communicate the name and contact details of the data protection officer or other contact point where more information can be obtained;
  • describe the likely consequences of the personal data breach;
  • describe the measures taken or proposed to be taken by the controller to address the personal data breach, including, where appropriate, measures to mitigate its possible adverse effects.
  • Where, and in so far as, it is not possible to provide the information at the same time, the information may be provided in phases without undue further delay.
  • 1 The controller shall document any personal data breaches, comprising the facts relating to the personal data breach, its effects and the remedial action taken. 2 That documentation shall enable the supervisory authority to verify compliance with this Article.

How the SQL Security Suite addresses this:

In the case where a data breach has occured, it’s essential to identify exactly what data has been impacted.  SQL Compliance Manager can help you with the forensic analysis by telling you who has accessed the data via logins and failed logins.  Once you identify a malicious user, you can track their progress through your data via DDL, DML and privileged user events.  You can also detect if sensitive columns were accessed or if data was manipulated via before-after data information.

Article 35 (1)

Article 35 says that some types of processing are likely to result in a higher risk and exposure so you must conduct an impact assessment.

What the GDPR text says:

1 Where a type of processing in particular using new technologies, and taking into account the nature, scope, context and purposes of the processing, is likely to result in a high risk to the rights and freedoms of natural persons, the controller shall, prior to the processing, carry out an assessment of the impact of the envisaged processing operations on the protection of personal data. 2 A single assessment may address a set of similar processing operations that present similar high risks.

How the SQL Security Suite addresses this:

SQL Compliance Manager can conduct an audit of your data events and activities to help you prepare for your data impact assessment. Enabling sensitive columns and before-after data can help you to detect where that information is being processed and via what systems.

Recital 39

Recital 39 covers how you should process data to be in compliance with the regulation.

What the GDPR text says:

1 Any processing of personal data should be lawful and fair. 2 It should be transparent to natural persons that personal data concerning them are collected, used, consulted or otherwise processed and to what extent the personal data are or will be processed. 3 The principle of transparency requires that any information and communication relating to the processing of those personal data be easily accessible and easy to understand, and that clear and plain language be used. 4 That principle concerns, in particular, information to the data subjects on the identity of the controller and the purposes of the processing and further information to ensure fair and transparent processing in respect of the natural persons concerned and their right to obtain confirmation and communication of personal data concerning them which are being processed. 5 Natural persons should be made aware of risks, rules, safeguards and rights in relation to the processing of personal data and how to exercise their rights in relation to such processing. 6 In particular, the specific purposes for which personal data are processed should be explicit and legitimate and determined at the time of the collection of the personal data. 7 The personal data should be adequate, relevant and limited to what is necessary for the purposes for which they are processed. 8 This requires, in particular, ensuring that the period for which the personal data are stored is limited to a strict minimum. 9 Personal data should be processed only if the purpose of the processing could not reasonably be fulfilled by other means. 10 In order to ensure that the personal data are not kept longer than necessary, time limits should be established by the controller for erasure or for a periodic review. 11 Every reasonable step should be taken to ensure that personal data which are inaccurate are rectified or deleted. 12 Personal data should be processed in a manner that ensures appropriate security and confidentiality of the personal data, including for preventing unauthorised access to or use of personal data and the equipment used for the processing.

How the SQL Security Suite addresses this:

SQL Compliance Manager allows you to audit your data to ensure that information is being processed in the manner that you expect.  SQL Compliance Manager can track DDL, DML and Privileged user activities. It can also track sensitive column and before-after data activities.

SQL Secure can detect if potentially dangerous settings are enabled like CLR, TRUSTWORTHY, OLE, FILESTREAM, XP_CmdShell and Ad Hoc queries so that they can be addressed where necessary.

SQL Secure can detect if sample databases or cross-database ownership are still enabled which could warrant attacks.

SQL Secure can ensure that the correct authentication methods are enabled.

In conclusion, SQL Security Suite can help you to meet many of your GDPR compliance needs by helping you to audit your data and ensure that safeguards are in place to protect it.

For more information about SQL Security Suite, SQL Compliance Manager or SQL Secure, click on these links or contact your Sales Representative.

If you’d like a custom template of these settings that you can import into your SQL Compliance Manager installation, please contact your Sales Representative.