2

As part of dealing with the UK's GDPA 'right to be forgotten' in a small database I want to update the (autoinc) PK of a row in table 'member' to an obfuscated value and let it cascade down through the FKs in related tables, such as the dates they held certain positions.

If I then set the names of the member in table 'member' to empty strings I can still use my related tables, for example to determine that there was someone holding a certain position at a certain time but I will no longer be able to ascertain either the name or the id of the person who held it, which is what I want.

I have written a simple a UDF obfuscate_id() that takes a positive integer ID and returns a negative ID calculated by combining the id passed with a random number.

FUNCTION obfuscate_id(id INT) RETURNS INT
COMMENT 'returns the negative version of the id passed, obfuscated by a random number'

BEGIN
  DECLARE result INT;
  SET result =  (FLOOR(RAND(id)*10000) + id ) * -1;
  IF result > 0  THEN -- ensures result is -ve even if called with a -ve parameter
     SET result = result * -1; 
  END IF;  
  RETURN  result;
END;

And I am using the sql

UPDATE member
SET  member_id = obfuscate_id(member_id)
WHERE member_id = 1234;

However, this might generate a negative id that already exists from a previous update of another member_id. If so I want to set it to something else, maybe by obfuscating the id again as in

SET  member_id = obfuscate_id(obfuscate_id(member_id));

Is there a simple way, like ON DUPLICATE KEY ... to check if the value I am about to update to already exists and if so update to something else?

I did look at how-to-use-on-duplicate-key-for-update and update-on-duplicate-key-update and mysql-update-with-on-duplicate-key-update but am no wiser

I would add that given the size of table 'member' and the particular application, even the first update is going to be a very rare occurrence, so the chance of having any obfuscated IDs is extremely slim, let alone duplicate ones, but I'd like to cover the eventuality if I can.

12
  • Please provide the structure of your member table... Apr 1, 2021 at 9:59
  • Wouldn't it be simpler / easier to just choose a random number who's range is outside your current range of values in member? E.g. something like (FLOOR(RAND(id)) * -1) + (SELECT MIN(id) FROM member) roughly speaking? (There's probably a simpler way to achieve the same outcome even.)
    – J.D.
    Apr 1, 2021 at 11:44
  • Sounds like you need a TRIGGER.
    – Rick James
    Apr 1, 2021 at 18:39
  • @Vérace. I don't really see how that would help you to answer my question. Suffice to say that its a InnoDB table with PK member_id int(11) NOT NULL AUTO_INCREMENT and then a couple of dozen VARCHAR data fields such as first_name, surname, dob, etc, and ten or so TINYINT fields used as Booleans to record things like is_full_time_student or prefers_home_phone_in handbook, All that data I can set to null so the member_id cannot be used to retrieve anything anyway. Apr 2, 2021 at 18:30
  • @J.D. Yes, there might be something in that idea as even if `(FLOOR(RAND(id)) * -1)1 produced a result that I had calculated previously the final member_id should still be different that any I've used before. I'll investigate the code. Apr 2, 2021 at 18:38

1 Answer 1

0

Wouldn't it be simpler / easier to just choose a random number who's range is outside your current range of values in the member table? E.g. something like (FLOOR(RAND(id)) * -1) + (SELECT MIN(id) FROM member) (semi-pseudocode)?

5
  • 1
    That would work and it answers my original question so I'll accept it. Thank you. But thinking about it further, I might also consider the suggestion by Vérace and just not bother to change the id at all. Apr 2, 2021 at 21:17
  • @user2834566 Just caught up on the other details. GDPR is a tough thing and grey area to support. Many people fail to realize that even if they remove the user's data at their request from the database, they still aren't fully following GDPR until they go back and delete that data from every database backup and database server backup they have too. It's actually a near impossible task to be 100% GDPR compliant until the EU makes a ruling that those auxillary copies of the data don't matter. Not sure if you're doing database or server backups. That being said, to work towards...
    – J.D.
    Apr 2, 2021 at 22:32
  • ... GDPR compliancy at least, I'd recommend Verace's solution even in addition to mine. Obfuscating the actual data with an UPDATE either by randomizing it or NULLing it out is a good first step.
    – J.D.
    Apr 2, 2021 at 22:33
  • I keep daily backups on my PC and every now and then I delete all those over a month old . The server keeps one weekly and one monthly backup. Apparently backups are OK as you are not processing the data in them. That is until you use one of them to restore from. At that point you are once again 'processing' the data and so must have somehow already erased the personal data from the backup. - and also from any old copies of emails sent or received, any old documents, electronic or printed etc. I generally get the feeling that those who designed the GDPA have never also designed a database! Apr 3, 2021 at 18:59
  • @user2834566 Agreed, don't think the people who came up with GDPR / GDPA were very technologically savvy. I do agree, I think general accepted practice in the developer community with the backups is that they're OK baring they're not in use. But I think the actual language of GDPR and the lack of an official ruling makes it a grey area officially, currently. But that's one for the lawyers whenever that bridge is eventually crossed.
    – J.D.
    Apr 3, 2021 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.