5

I have a new requirement to delete sensitive data when requested by the user.

The problem is that the system is complex and there is a lot of existing data in the system and they are all related.

If I simply do a cascade delete, it can easily affect how other functionality might operate in unpredictable ways.

How should I handle this situation? Should I actually delete the rows or can I just go through the system and find the data I need to delete and clear them from the rows and then mark each row as deleted.

The second option would probably be safer but it could be argued that even through the data is stripped, there is still some evidence that the user entered the system because the rows still exist.

  • 1
    We can't say that for you, you have to ask to your client/project manager if "delete data" means not visible from UI or a true deletion of it. – Walfrat Aug 11 '16 at 6:39
  • @Walfrat - the requirement is definitely not "not visible from UI" - this is sensitive data that needs to not be retrievable, under any means. I'm wondering what the industry standard of that entails. Surely, large companies or government departments must face issues like this when dealing with sensitive data. – RoboShop Aug 11 '16 at 6:42
  • Well to be honest i find strange that sensitive data should be totally wyped out. Usually they stay somewhere for tracability purposes. And "not to be retrievable", well for a client that only use UI, not retrievable can mean not visible from the UI. – Walfrat Aug 11 '16 at 6:45
  • 2
    Ask the DBA where the database and the backups are stored and physically destroy them, otherwise you can never be sure nobody can retrieve the data. – billc.cn Aug 12 '16 at 15:12
  • 1
    Lol. Ask the DBA to destroy the backups! Oh... I'd love to a fly on the wall. – RubberDuck Aug 12 '16 at 16:46
16

This kind of requirement is impossible to fulfill until you clarify what level of 'deletion' is requested.

The easiest thing to achieve is to change the database so that the records in question are no longer returned by operations called on the normal interface. For that, you only have to mark things as deleted and extend your business logic so that it ignores items with this mark. That may or may not be enough to satisfy the user.

If the user (or the law) demands that the sensitive information is no longer available even to the operators of the software, things get much harder. This means that you have to actually delete records, and as you have seen, this may involve deleting much more than is obvious at first glance.

But even then the question is whether just deleting previously existing stuff is enough. The data within a record are gone when you delete it, but what about the fact that there was a record? Sensitive information can consist merely in the fact that a user used a system at all at a specific time, and simply deleting things leaves recognizable gaps in ID sequences, allocation tables etc. that could be reverse engineered to reconstruct some of them. Also, what about log files? System backups? Internet search engines? The list of places where the data might or might not still exist is surprisingly long.

As you see, the amount of work to be done in each case is very different. Therefore, the only useful way to go about purging sensitive data is to first establish clearly which level of purging is required. For non-technical users, you might phrase the alternatives in terms of capabilities:

  • simple revocation: other users can no longer reconstruct your data.
  • true deletion: you yourself cannot reconstruct your data.
  • system-level deletion: the operators cannot reconstruct your data, even if you wanted them to.
  • weapons-grade deletion: the NSA couldn't reconstruct your data.
  • thanks - yes you're right. There's actually lots to consider in addition to just the live database – RoboShop Aug 11 '16 at 6:56
  • Good answer. As a DBA, I always had to include the caveat to clients that sensitive data would still exist in DB backups for a given period of time. – Robbie Dee Aug 11 '16 at 10:30
6

Kilian is right that there is more to consider than the production database, and that one has to think about how secure the deletion must be. But there is still your core question to answer, how to delete the right things, within the database, assuming you have already his other points in mind, too. You wrote

the system is complex ... If I simply do a cascade delete, it can easily affect how other functionality might operate in unpredictable ways.

This gives me the impression you did not make a thorough impact analysis about the deletion, which semantics the data record relations have, if all integrity requirements of the business are explicitly modeled correctly by using referential integrity mechanisms of the database, which of the existing applications deal with the parts of the data model where the deletion will take place, and so on.

But this is mandatory to get it right , there is no shortcut to this. You need to understand exactly the consequences of your action as good as you can, especially because the system is complex, not "in spite of". Typically, you cannot just look at the data model, you need to read the documentation, find out in which "portions" data is added currently to the database (so you can deduce what to delete by reversing that process), and understand the related business processes, at least to a certain point. If you are lucky, you can ask other colleagues who have that information or, if you are very lucky, others who have helped to develop the parts of the system you have to deal with.

Once you understand the relations in your data model, you will have solved more than 50% of your task and I am pretty sure then you can answer your question by yourself.

Of course, through such an analysis, you might overlook something, and that is why your deletion scripts have to be tested just like any other piece of software - not on the "live database", but on a test system, ideally filled with data from the live system.

1

Yes, there is no way round a deeper analysis of this deletion requirement. And there's no shortcut for protecting sensitive information. In addition to the nice answers already made, I'd like to share some thoughts:

  • if you'd manage to delete everything you had to from the database, there would still be plenty of evidence of this data in the older back- ups, in the logs, and most of all in the files managed by the DBMS (i.e. Many db systems use this file space as a kind of memory block and do no necessarily overwrite data that was deleted until enough new data would be added). If your db is kept on SSD storage it's even worse because the SSD driver will avoid overwriting as much as possible. So total deletion for ensuring governmental grade of sensitivity would require several operating procedures to complement your database action.
  • but from your wording, I understand that your issue is more about dependency management, i.e. deleting just the minimum but if possible nothing because of all the relations that could break your application consistency. If it's like that (and the sensitivity is not of classified level) you could consider the approach of marking for deletion with overwriting of critical info (e.g. You'd mark for deletion the record, filter this at app level, and overwrite some sensitive fields like product name in the db which could allow to recognize the marked for deleted data). This approach is a workaround for legacy systems but in the long run you have to get full understanding of you relations so to be able to make surgical interventions like that.
  • finally, you have to understand fully the need, not only to chose the best solution, but also to see if it's reasonable and avoid being accomplice of some obviously illegal activities ( especially if you work on financial systems or systems submitted to SOX or GMP regulation)
0

I had a similar requirement for a government case management application where the database stored personal information for many people. Legal requirements were that after 10 years all the information should be removed/deleted/destroyed. The database structure was such that deleting the personal information was impossible as it was linked to work that had to be retained.

My solution was to update the personal information. Where information like phone numbers was not critical to data integrity I updated it to null. Street addresses and zip/postal codes could also be set to null.

Given the choice of updating thousands of records of people's names to John Doe or Jane Doe I created a table of fake names and used that as the source for the update.

The only issue I was not able to resolve was the city where people lived. This was also used by corporations whose addresses did not have to be cleaned. if I updated New York to a fake name then all the corporations would show that name as well.

This underlines the issue that removing sensitive information or retention is a business requirement that should be baked in when the application is designed or it can be very painful to do afterwards.

protected by gnat Jul 26 at 12:14

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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