GDPR Right to Be Forgotten and Other Access Rights

GDPR is complex, and this post deals with only a small part of the law. GDPR is comprised of 99 Articles, of which three (Articles 15, 16 and 17) deal primarily with a consumer’s right to “be forgotten.” This includes the right to access the data that your business keeps about them (Article 15), the right to have incorrect information about them fixed (Article 16) and the right to delete information that your business has collected about them (Article 17).

A summary of the requirements of these GDPR Articles appears at the end of the post. However, the purpose of this article is not to explain these requirements, but rather to suggest approaches that your technology team can take to facilitate compliance with them.

Technical approaches to facilitate compliance

To ease compliance with Article 15, 16, and 17 of GDPR, and to support compliance with other GDPR Articles, we recommend the following steps with regard to handling of personal data. This is not intended to be a comprehensive list of everything you need to do to comply with GDPR. Instead, we see these as best practices to make compliance easier to achieve and more likely to be adhered to. Many of these steps are important for general data security as well—so even if you are not subject to GDPR these are good practices to follow.

  1. Keep an up-to-date data dictionary (metadata) that clearly identifies the location and meaning of all data elements that contain personal data (see definition below). From a GDPR perspective, it will be helpful to keep the following information in this dictionary. This will help when creating your privacy policy.
    • Name of the data element (column name)
    • Where the data element is stored (database name, table name)
    • The meaning of the data element
    • What the data element is used for in the system (i.e., the business reason for needing it)
    • How the data element is collected (what screen or input file)
    • The Personal Data Category from the GDPR regulation (see Article 15 below)
  2. This data dictionary must be updated each time the database is modified to add or remove data elements, or to change the meaning or use of an existing data element (which is not a good practice, but that is a topic for another blog)
  3. Create a set of data entry screens that customer service staff can use to easily perform the actions required under these GDPR articles. These screens can call stored procedures (recommended) or other code that does the work of gathering or modifying the information, and keeping a record of it when appropriate. Using a data entry screen rather than the command line allows a customer service representative to perform these actions rather than relying on manual steps by a developer or DBA. Note that all requests of this type should be validated by sending an email back to the recipient and waiting for confirmation before complying. These screens would call the following:

To support Article 15 — Access

Stored procedures to query all data for an individual, in order to comply with requests from an individual for the data you have about them. By providing this data in a machine-readable format, this can also support compliance with GDPR Article 20 (data portability).

To Support Article 16 — Rectification

One or more stored procedures to query a given user and to modify any personal data that an individual might request.

To Support Article 17 — Erasure

A stored procedure that will cleanly remove all personal data for an individual, as well as related data if the removal of personal data renders any remaining data for an individual useless. If you are using a relational database that has the capability, we recommend creating cascading delete constraints or triggers so removal of related data is simple, safe, and automatic. It is important not to log the removal of any personal data if the log would contain any of the data that was removed.

Depending on your business requirements, there is an alternate technical approach that can help you comply with the Article 17. GDPR requires that data be removed upon request, but that only applies after any legal obligation that your company has to keep the data. For example, if you have to keep contracts for seven years for tax purposes, then you do not have to comply with a request for erasure until that period passes. So an alternative approach to complying with Article 17 would be to write maintenance software that automatically removes all personal information from your system after legal obligations no longer require it. This removes some historical reporting capability, so it may not be appropriate for your business.

Details about the referenced GDPR Articles

The following information about the GDPR right to be forgotten and other rights mentioned above is taken directly from the GDPR recitals as published here: https://gdpr.eu/. In some cases I changed the structure to make it a bit easier to understand the rules.

Pertinent Definitions

  • 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 mor 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.
  • Processor means a natural or legal person, public authority, agency or other body which processes personal data on behalf of the controller.
  • Recipient means a natural or legal person, public authority, agency or other body, to which the personal data are disclosed, whether a third party or not. However, public authorities which may receive personal data in the framework of a particular inquiry in accordance with Union or Member State law shall not be regarded as recipients; the processing of those data by those public authorities shall be in compliance with the applicable data protection rules according to the purposes of the processing.

Article 15: Right of Access By The Data Subject

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:

  • the purposes of the processing;
  • the categories of personal data concerned;
  • 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 organizations;
  • where possible, the envisaged period for which the personal data will be stored, or, if not possible, the criteria used to determine that period;
  • 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;
  • the right to lodge a complaint with a supervisory authority;
  • where the personal data are not collected from the data subject, any available information as to their source;
  • 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.
  • Where personal data are transferred to a third country or to an international organization, the data subject shall have the right to be informed of the appropriate safeguards pursuant to Article 46relating to the transfer.
  • The controller shall provide a copy of the personal data undergoing processing. For any further copies requested by the data subject, the controller may charge a reasonable fee based on administrative costs.
  • Where the data subject makes the request by electronic means, and unless otherwise requested by the data subject, the information shall be provided in a commonly used electronic form.
  • The right to obtain a copy referred to in paragraph 3 shall not adversely affect the rights and freedoms of others.

Article 16: Right to Rectification

The data subject shall have the right to obtain from the controller without undue delay the rectification of inaccurate personal data concerning him or her. Taking into account the purposes of the processing, the data subject shall have the right to have incomplete personal data completed, including by means of providing a supplementary statement.

Article 17: Right to Erasure (right to be forgotten)

When does the right to be forgotten apply?

  • where the personal data are no longer necessary in relation to the purposes for which they are collected or otherwise processed
  • where a data subject has withdrawn his or her consent or objects to the processing of personal data concerning him or her
  • where the processing of his or her personal data does not otherwise comply with this Regulation
  • That right is relevant in particular where the data subject has given his or her consent as a child and is not fully aware of the risks involved by the processing, and later wants to remove such personal data, especially on the internet. The data subject should be able to exercise that right notwithstanding the fact that he or she is no longer a child.

Exceptions:

  • The further retention of the personal data should be lawful where it is necessary, for exercising the right of freedom of expression and information
  • for compliance with a legal obligation
  • for the performance of a task carried out in the public interest or in the exercise of official authority vested in the controller
  • on the grounds of public interest in the area of public health
  • for archiving purposes in the public interest, scientific or historical research purposes or statistical purposes
  • or for the establishment, exercise or defense of legal claims.

And further:

  • the right to erasure should also be extended in such a way that a controller who has made the personal data public should be obliged to inform the controllers which are processing such personal data to erase any links to, or copies or replications of those personal data. In doing so, that controller should take reasonable steps, taking into account available technology and the means available to the controller, including technical measures, to inform the controllers which are processing the personal data of the data subject’s request.

Disclaimers

This post is based on my reading of GDPR and technical approaches that I feel will facilitate compliance as I understand it.

This is not intended to be a comprehensive discussion or instructions for complying with GDPR.

After taking all technical steps you feel are necessary to comply, you should consult an attorney to determine if the steps you have taken are sufficient for compliance.

For expert help bringing your database environment into compliance with GDPR, CCPA or other emerging privacy legislation, contact Buda Consulting.

 

 

Ever Dropped an Oracle Table and Wish You Hadn’t?

If you’ve ever dreamed of going to a recycle bin like you can on Windows, and “undropping” an Oracle database table… Oracle has made your dream come true!

Introduced with Oracle 10g, Oracle’s recycle bin works a lot like what you’re used to in Windows. When this feature is enabled (the default setting), dropped tables don’t actually get deleted. Instead, they “land” in the recycle bin and can be restored.

The recycle bin is actually a data dictionary table containing data about dropped objects. What actually happens when recycling is enabled is that Oracle renames each dropped table and associated objects (indexes, LOB segments, triggers, etc.) with a system-generated name that starts with “BIN$.” The table data is still available and you can query it like any other table.

To “undrop” a dropped table that’s “in the recycle bin,” you perform an operation known as a “flashback drop.” The command syntax is: FLASHBACK TABLE <name> TO BEFORE DROP. This command just renames the BIN$<name> table back to its original name. (You’ll find an excellent overview of Oracle 10g’s Flashback features here.)

The downside of the recycle bin is that dropped tables are really only renamed. Their table segments are still taking up space in your tablespace, which still counts against your user tablespace quotas. To recover the space associated with dropped tables you need to explicitly “purge” unwanted tables and associated objects from the recycle bin.

Fortunately, purging is pretty easy and flexible. If you have the SYSDBA privilege, you can purge everything from all the recycle bins with PURGE DBA_RECYCLEBIN. Or you can purge just the user recycle bin with PURGE RECYCLEBIN. You can even purge “recycled” objects by schema and user with PURGE TABLESPACE <tablespace> or PURGE USER <user>. Users have access in the recycle bin only to those objects that they themselves dropped.

It’s normal to end up with multiple versions of a table in the recycle bin. In these situations, Oracle always restores the newest version. To restore an earlier version, you can simply refer to it by its unique, BIN$-prepended name. Alternatively, you can use FLASHBACK multiple times until you restore the version you want.

The RECYCLEBIN initialization parameter turns the recycle bin feature on or off at the system or session level. Turning recycling off doesn’t prohibit you from restoring objects that were in the recycle bin before you disabled it.

If you want to keep the recycle feature enabled but bypass it when dropping specific tables, just add the PURGE clause to the DROP TABLE statement; e.g.:

SQL> DROP TABLE TABLE_XYZ PURGE;

If you’re not already using the recycle bin feature, why not turn it on and get it working for you today? There’s really nothing to lose—literally!

For expert advice on how to fine-tune use of Oracle’s recycle bin for your environment, schedule a free consultation with Buda Consulting.

For more information:

Using the Data Dictionary to find hidden data in SQL Server

Using the Data Dictionary to find hidden data in SQL Server

A client asked me recently how he could find a string in his SQL Server database without knowing what table or column it was in. The string was a translation of a code that appeared on one of the UI screens. This was a packaged CRM database and he had no documentation on the schema. With hundreds of tables whose names were not obvious, he had no idea where the translation might be kept, but wanted to do some reporting using the translations. It was like finding a needle in a haystack!

In order to help, I wrote a utility to find the data for him. Using the data dictionary tables and a little dynamic sql,  I created a script that would search for a string in every column of every table in the database.

We can do this by using the information_schema.columns view to create statements that insert search results into a temporary table. We want to record the table, the column, and the full contents of any column that contained the string we wanted to search for in order to provide context for the search results.

There are some complications that we have to address as we do this.  First, since we want to do a like comparison against any of the fields we must restrict the search to char and varchar fields. This is necessary because the like comparison cannot be used against xml and some other datatypes. That restriction works in this case because I was searching for a string and it was very unlikely that this string would be embedded in an xml field. Second, to prevent errors resulting from spaces, hyphens, or other special characters in table or field names, we must surround the object names with brackets — this is always a good practice when using dynamic sql with sql server.

Since this utility uses the like comparison with a wilcdard before and after the search string, indexes will not be used so performance will be an issue. This utility is best run during non-production hours and may take a long time to complete. This can be mitigated by modifying the application to remove the leading wildcard and then indexes on any of the columns would be used, but this will only find strings that start at the beginning of the column value that is being checked.

We now have a nice utility that give a report of all places where the string lives. The utility can easily be extended to handle numbers, dates, and other data types as necessary. This script works with SQL Server but similar scripts can be created for any major database that has a user accessible data dictionary.

Note that this procedure does not automatically delete the prior contents of the search_findings table. You may wish to add that to the script if you don’t want the results to be cumulative.

The instructions follow.

1. Start by downloading find-it

2. Create the table that will hold the search results using create_search_results_table.sql

3. Create the find-it procedure using create_find-it_procedure.sql

4. Issue the search with the following command:

exec find_it  ‘search string’