Secure encrypted database design

Question

I have a web based (perl/MySQL) CRM system, and I need a section for HR to add details about disciplinary actions and salary.

All this information that we store in the database needs to be encrypted so that we developers can't see it.

I was thinking about using AES encryption, but what do I use as the key? If I use the HR Manager's password then if she forgets her password, we lose all HR information. If she changes her password, then we have to decrypt all information and re-encrypt with the new password, which seems inefficient, and dangerous, and could go horrifically wrong if there's an error half way through the process.

I had the idea that I could have an encryption key that encrypts all the information, and use the HR manager's password to encrypt the key. Then she can change her password all she likes and we'll only need to re-encrypt the key. (And without the HR Manager's password, the data is secure)

But then there's still the problem of multi-user access to the encrypted data.

I could keep a 'plaintext' copy of the key off site, and encrypt it with each new HR person's password. But then I know the master key, which doesn't seem ideal.

Has anyone tried this before, and succeeded?

Answer 1

GnuPG allows documents to be encrypted using multiple public keys, and decrypted using any one of the corresponding private keys. In this way, you could allow data to be encrypted using the public keys of the everyone in the HR department. Decryption could be performed by any one having one of the private keys. Decryption would require both the private key and the passphrase protecting the key to be known to the system. The private keys could be held within the system, and the passphrase solicited from the user.

The data would probably get quite bloated by GnuPG using lots of keys: it has to create a session key for the payload and then encrypt that key using each of the public keys. The encrypted keys are stored alongside the data.

The weak parts of the system are that the private keys need to be available to the system (ie. not under the control of the user), and the passphrase will have to pass through the system, and so could be compromised (ie. logged, stolen) by dodgy code. Ultimately, the raw data passes through the system too, so dodgy code could compromise that without worrying about the keys. Good code review and release control will be essential to maintain security.

You are best avoiding using MySQL's built in encryption functions: these get logged in the replication, slow, or query logs, and can be visible in the processlist - and so anyone having access to the logs and processlist have access to the data.

Answer 2

Why not just limit access to the database or table in general. That seems much easier. If the developer has access to query the production, there is no way to prevent them from seeing the data b/c at the end of the day, the UI has to decrypt / display the data anwyays.

In the experience I've had, the amount of work it takes to achieve the "developers cannot see production data at all" is immense and nearly imposible. At the end of the day, if the developers have to support the system, it will be difficult to achieve. If you have to debug a production problem, then it's impossible not to give some developers access to production data. The alternative is to create a large number of levels and groups of support, backups, test data, etc..

It can work, but it's not as easy as business owners may think.