Anonymise and tag simultaneously

Nobody suffers from reading md5-sums instead of real family names. If someone wants to correlate family names with height of salary; too bad. This person should request access to the crown jewels and will probably be turned down.

Otherwise, most reports on department sizes, income, revenues and kpi's can do without written out names, but can't do without access to employee records. Note that grouping by department, or even by name, still works as expected in the above resultset.

Dates go wrong however and small text-fields turn into 32-character fields, but these are small things and can be dealt with.

For more on this subject, but then a bit more theoretical and in depth, refer to John D. Cook on Applied privacy and John D. Cook on Adding Laplace or Guassian Noise

Of course this works fine, but not 100%. I'm quite sure an auditor will be impressed by all these md5-sums in reports, in-between-data and temporary results. It's debatable whether it is acceptable for making harmless data from riskful subjects. There are other hashing and encryption algorithms though, please choose the one which suits you best. See Pgcrypto for more information on how to do this in PostgreSQL.

One note however. It is not enough to just hash the name of an individual if one can still make out which department he or she is working and if one can still find all courses he or she went to. Two or three queries further and any SQL script-kid can identify people. So, please pseudonymise more than just the name.

For third parties it is not easy to go from an md5-sum back to the real name of the subject; i.e. the process of going from harmless to riskful is hard. I will leave how hard this is to the math people.

For a trusted DBA to do this, this is not hard at all. Given that he or she knows all available names, firstnames, inbetweens and departments, it is not that hard to write a 15-line program which checks who retrieved leaked data and on which day exactly. The bigger the cardinality, the bigger the job to decipher names. This is in line with pretext item (26) of the GDPR and will help you making data harmless.

Vital for any scheme to work is that applications need no change.

For an implementation I've given a lot away, really, in the above text. But it is a bit clumsy to prepare a view for all possible authorisation levels. When adding or deleting columns in the original table, one must redo all view e.g.. Also when changing types, views must be redone.

Another solution is to use rules. These can be used to rewrite queries and allows the application to use the datamodel it is built for.

CREATE RULE "harmless" AS
ON SELECT TO employees
DO INSTEAD
    SELECT pseudonymise( name ) as name,  
           pseudonymise( firstname ) as firstname,  
           col2, col3
      FROM employees;

I will implement and test these rules and some authentication levels in a life-like environment later, check on PostgreSQL RULES for pseudonymisation at litpro.nl for more on this and why the above doesn't work exactly.

If you need to implement different authorization levels, this gets quite complicated. There is row-level-security, allowing access to roles and role-membership automatically. There is column-level-security in the same way.

The snag is that these columns cannot be mentioned in the select-clause without returning a permission error. This makes applications barf.

One would like access to any column in a table for any role, but for some columns some kind of scrambling should be applied first. If these columns are then exempt from being updated/inserted (with traditional column level security), things are fine and it works with any application.

This is a feature which is non-existent in SQL and also in any implementation I know of¹.

One of the feature-requests for PostgreSQL under the flag of GDPR now is: In a GRANT SELECT statement allow, in the column-list, a way to apply some kind of function on columns. The following could be valid SQL (this one allows a lot to the head of HR, and likewise, but scrambled, to HR interns):

GRANT SELECT ( name, firstname, inbetween, department )
          ON employees to head_of_hr;
GRANT SELECT ( department )
      MAPPED ( name, firstname, inbetween ) BY pseudonymise( text )
          ON employees to hr_interns;

The first four or five titles were written in some kind of rage after visiting the Big Data Expo, Utrecht 2017. I then knew about GDPR and had implemented various mechanisms to avoid running risks. The commercial heavy lifting on that expo was terrible. People should be informed about GDPR without FUD.