Starting May 25, 2018 all worldwide organizations and companies that process, store or in any other way handle personal information of EU citizens have to comply with the General Data Protection Regulation (GDPR) or face heavy annual fines and penalties. In order to comply with GDPR standard, organizations will have to protect and empower all EU citizens data privacy and reshape their approach to ensure that the security of the personal information is met as per the requirements and have full transparency on any data access, processing or more.
In this article we are going to look into the challenges and requirements of GDPR related to SQL Server auditing and show how to achieve them using ApexSQL Audit, a SQL Server auditing tool which audits almost 200 SQL Server events including all structure and data changes (DDL and DML), security changes, data access and more.
In General Data Protection Regulation (GDPR) compliance for SQL Server we’ve previously looked into and explained general GDPR requirements related to the SQL Server regarding both auditing and alerting on specific events. In this article, we will pay close attention to each GDPR article/section and how to understand and achieve the compliance using ApexSQL Audit
The following GDPR articles and requirements will be included:
- Article 5 – Principles relating to processing of personal data (Chapter II Principles)
- Article 24 – Responsibility of the controller (Chapter IV Controller and processor)
- Article 25 – Data protection by design and by default (Chapter IV Controller and processor)
- Article 32 – Security of processing (Chapter IV Controller and processor)
- Article 33 – Notification of a personal data breach to the supervisory authority (Chapter IV Controller and processor)
Article 5 – Principles relating to processing of personal data (Chapter II Principles)
Article 5 of the GDPR standard regulates general protection of the personal data and enforces several rules which require that the access to the data is monitored for both authorized and unauthorized attempts to access the data, or process it in any way, including data loss, destruction, damage and more. Furthermore, Article 5 also adds a requirement for organizations to be able to demonstrate compliance with all principles related to the processing of the personal data
To achieve this requirement and comply with the following:
Paragraph 1, section F
Personal data shall be 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’),
as well as
Paragraph 2
The controller shall be responsible for, and be able to demonstrate compliance with, paragraph 1,
… the following events/operations need to be audited with ApexSQL Audit.
Start by following this guide in order to configure general auditing of SQL Server database(s) and when required to choose specific events/operations to audit, ensure that the following is included:
On the server filter, choose the following operations
Next, add database(s) for auditing and choose the following filters
Last, but not least, in order to demonstrate the compliance with Article 5, the following out-of-the-box reports can be used:
- Logon activity history – this report will show a complete history of both successful and failed attempts to access the data
- Unauthorized access – this report will show all SQL Server login attempts that have failed
Both reports can be user-customized with all available filters to see the reports for specific SQL Servers, users, applications, date/time and more.
Additionally, since GDPR imposes a hard requirement that in case of any data breach, including access, processing, loss and more organizations/companies must provide full information on the event to the designated data protection authority as well as the customers concerned with the data breach in a maximum of 72 hours following the incident, it is of great importance to be immediately alerted on such events to allow designated personnel to act on the breach and ensure they comply with the requirement and avoid heavy fines imposed to those that fail to inform DPAs on the breach
Alert on unauthorized access
In the article, How to create a custom alert with an email notification article, we can see detailed step-by-step guide on how to create an alert which will not only be triggered within ApexSQL Audit, but additionally send to multiple email addresses using SMTP servers.
For this particular case, follow the steps in the guide, and use the following alert condition to ensure that proper personnel are informed on any potential unauthorized access
Uncheck the “Limit the number of notifications to one per minute (on each server)” checkbox in the alert wizard to ensure that all an alert is triggered on each access attempt and complete the wizard per provided guide
Article 24 – Responsibility of the controller (Chapter IV Controller and processor)
The article 24 requires controllers (organizations/companies) to achieve full transparency on any access or processing attempts to the personal data.
The article includes the following:
“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.
In order to achieve compliance with article 24, it is necessary to be able to demonstrate that all processing of data is within the scope of the regulation. This means that it is mandatory to audit all access to the data as well as all data changes. From the SQL Server point of view, it translates to auditing all login attempts, data querying (select operations) as well as all DML changes.
To configure ApexSQL Audit to achieve this task, it is required to configure auditing as shown before in the article 5, but this time, the following operations need to be included in the auditing configuration.
On the server level:
And on the database level:
In order to demonstrate compliance with Article 24, the following report can be used:
- Complete auditing trail – this report will show audited operations without any limitations
Article 25 – Data protection by design and by default (Chapter IV Controller and processor)
The article 25 of the GDPR compliance standard states that all data must be protected by default and that principles, such is data minimization or pseudonymizing, must be used when auditing this data in order to comply with the GDPR requirements.
… implement appropriate technical and organizational measures, such as pseudonymization, which are designed to implement data-protection principles, such as data minimization…
ApexSQL Audit was designed with the same premises explained in the article 25. When data changes or any SELECT operations are audited, ApexSQL Audit obfuscates the true values and does not capture or store them in any way. Instead, parameters are used to replace the values, as in the following example:
If a simple INSERT INTO operation to populate the table with specific values is executed and audited by ApexSQL Audit,
INSERT INTO Stores ([Store_id], [Store_name], [Address], [City], [Country], [Zip]) VALUES ('6380', 'Eric the Read Books', '788 Catamaugus Ave.', 'Seattle', 'WA', '98056')
Instead of auditing the true values used in the INSERT operations, ApexSQL Audit will obfuscate them and show parameters @1, @2, @3 and so on.
This means that compliance with article 25 is automatically achieved with ApexSQL Audit without the need to configure any particular options/features. Of course, the DML changes must be included in the auditing configuration in the same manner as shown for the previous articles descriptions.
To demonstrate the compliance with the Article 25, the DML History report can be used to investigate auditing results and confirm that the actual data values have not been audited or stored by ApexSQL Audit.
Article 32 – Security of processing (Chapter IV Controller and processor)
The Article 32 of the GDPR compliance standard regulates the processing security both for any personal information handled as well as for the audited data in general. In section 2, the standard requires organizations to achieve appropriate level of security which will protect and make transparent any accidental or unlawful destruction, loss, alteration unauthorized disclosure of, or access to personal data transmitted, stored or otherwise processed.
From the SQL Server perspective, this means that all DDL and DML changes, as well as login attempts must be audited, without exception. From the ApexSQL Audit perspective, Article 32 imposes a requirement that the mechanisms used for auditing must have self-auditing features which will provide functionality to be transparent when any auditing configuration changes occur and to provide information who made the change, when and from where, how and if the integrity of the audited data has been compromised.
To comply with the article 32, the following must be performed:
- Auditing must be configured in a similar manner as shown before in this article, but the auditing filters will be slightly different than before:
Server level
Database level
- Next, to achieve the required level of transparency, ApexSQL Audit includes an out-of-the-box report which will demonstrate self-auditing in case of any configuration changes. The report in question is Security configuration history and can be used to achieve this. The default report may include additional information not required by this regulation, and it is recommended to include only “Internal” events of all event sources
As can be seen in the image above, the information on who made the change, when and where is included in the report preview.
Finally, to comply with article 32, it is required to be able to demonstrate the integrity of the audited data. ApexSQL Audit stores all audited data inside a single central repository database protected by the 256-bit chain hashes. When the data audited by ApexSQL Audit is inserted into the central repository database, a hash value is automatically created for each data value (table row) and appended to the row as additional table column. A hash (or hash function) is a mathematical algorithm which combines the actual data that is being inserted into a database together with the current timestamp to create a unique string which will be a unique identifier for the specific row. When this hash value is created, it is also inserted into the same table row into the central repository values into the special column “RowHash” which holds information for hashes for each individual row. Additionally, in the ApexSQL Audit central repository database, hashes are combined with each other into the ‘chain-hash’ in order to elevate the integrity and protection of the data. When forming a chain hash whenever a new hash is being created (for new data being inserted into the central repository database), in addition to combining all the data with the time-stamp, a value of the hash for the previously inserted data is also added to the algorithm. This means that each hash is dependent on the previous one, and every new hash will be added to the chain.
Since all the hashes are dependent on each other by forming the previously mentioned chain-hash, the ApexSQL Audit central repository database is made tamper evident. In the event of changing the (audited) data that was previously inserted into the database (with a unique value in the hash column) the hash for that value will differ from the already inserted value, not only for the table row that was changed, but also for all subsequently rows. This means that each and every change to the audited data can be pinpointed to the exact table row in the event of any integrity breach allowing full transparency to the auditors in any event that could jeopardize the data integrity.
This allows ApexSQL Audit central repository database to support full transparency on the database integrity and allows integrity to be checked on demand by clicking on the “Verify” button in the main ribbon of ApexSQL Audit GUI. Next, choose to check current repository and/or any previously created repository archives and hit the “Start” button. After short period of processing, the check will return the results and list all potential tampering events that require attention.
Furthermore, ApexSQL Audit has built-in alert which will trigger a notification immediately once any tampering occurs, so a manual check will not be required to be run on a regular basis but on demand only. This built-in alert can be changed to send this notification to email, as shown before, just follow the instructions in this article
- Article 33 – Notification of a personal data breach to the supervisory authority (Chapter IV Controller and processor)
Last, but not least, Article 33 imposes a requirement which requires high responsiveness in case of any critical or disastrous effect such is the data breach, or any potential compromise of the data and auditing integrity.
As we’ve shown before, ApexSQL Audit can be configured to trigger alerts on events which require immediate attention and have them automatically sent to mailboxes of the personnel which needs to act on such occasions.
To comply with Article 33, the following should be done:
- As shown previously, configure the tampering detected alert to be send to an email of a designated agent/engineer. Also consider adding your designated data protection officer (authority) to the email cc to ensure they are immediately informed on the audited data integrity breach
- As already shown previously when describing compliance with the article 5, have an active alert in the event of any unauthorized access attempts to the personal data
Implementing ApexSQL Audit as the auditing mechanism in the production environment brings you several steps closer to achieving full GDPR compliance since it allows auditing of SQL Server databases on a level not achievable by the SQL Server itself and provides transparency and integrity levels required by the GDPR.
April 20, 2018
