Best Practices for exporting sensitive database data and transforming it whilist keeping it encrypted?

Question

Say one has to export database data to use in another tool, such as an Excel Spreadsheet or for use as a csv file with some other program.

There's a problem with this, and that's that it ends up in an unencrypted form on the disk, where it can be forgotten about, snatched up by phishing, etc.

So when storing / using / getting rid of this sort of data, is there some sort of OS level solution (LUKS) for working with then getting rid of these sorts of temporary files?

I know when one uses SSH-agent the keys can be given a time limit to be stored in memory, before it's deleted, is it possible to do the same thing with an encrypted directory or encrypted volume, that can be "scrubbed" automatically, after a certain period of time?

Also, it would be crazy to do something like upload such a csv file stored encrypted on the disk to a web application that runs over HTTP and thus leave such things moving over a network encrypted when it moves across the network, that would destroy the point of encrypting it, even if it was encrypted on disk.

And also what about allowing an approved running process to access the information but not allowing it to be copied elsewhere? That sort of thing sounds in the domain of permissions / ACLs to me.

Is it best to have a designated machine for doing this and limiting it's access to the outside world using a separate subnet / firewall / proxy or some combination there of?

Answer 1

I would generally be more concerned about the unencrypted Excel Spreadsheet with sensitive data 'getting lost' than the temporary file, which can be dealt with a good procedure stating that it must be deleted immediately after generation.

Anyway, here are a few ideas:

• shred the temporary file after processed.
• Store the file on a ramdisk. For instance /dev/shm is always in-memory only¹ (sometimes /tmp too). If the temporary file is written there and forgotten, it will be gone on reboot.
• Boot a live disk to perform the transformation.
• Do not use temporary files but pipes. Using some command-line trickery (and depending on your data!), you may be able to get the desired outcome avoiding temporary file, eg. mysqldump --skip-extended-insert sensitivedatabase | sed -n -e '/^INSERT.*VALUES(//p' -e 's/);//' }' | gpg -e -r recipient > encrypted-spreadsheet.csv.gpg
• Use a service that already exports the data. If this data is already handled in a web application, add the ability to the application to export it in the final format (we are actually moving the problem, but if that application is already handling this sensitive data in a way deemed secure, it makes sense to incorporate this feature there, too).

¹ Nitpicker's note: it may get swapped.

As for the other questions:

is it possible to do the same thing with an encrypted directory or encrypted volume, that can be "scrubbed" automatically, after a certain period of time

You could have a solution that automatically unmounts or deletes temporary files somewhere. But it seems overcomplicated. I would make a script that performs the full exporting process, and that scrubs the unencrypted temporary file as soon as it is no longer needed.

I have no idea what you mean with the encrypted csv moving over the network using HTTP part.

And also what about allowing an approved running process to access the information but not allowing it to be copied elsewhere? That sort of thing sounds in the domain of permissions / ACLs to me.

You certainly can do that. Be that a script, embedded on the web application, etc. If you get fancy, all of this could use selinux labels to enforce the process.

Is it best to have a designated machine for doing this and limiting it's access to the outside world using a separate subnet / firewall / proxy or some combination there of?

This probably will depend too much on your specific case and the way of transformation for giving a generic answer. Obviously, the most isolated, the better. But as mentioned, I would start by questioning why this highly sensitive data needs to be given to someone on an Excel Spreadsheet, to begin with.