What's the best method to use / store encryption keys in MySQL

Question

I plan on using MySQL and it's built-in encryption functionality to encrypt / decrypt certain columns in certain tables. The concern I have is that I need to store the key somewhere. I could certainly store the key in a file and control the permissions of that file and the permissions of the application that accesses it, but is that enough? I could also create a web service to get the key or something.

I am in a small shop where I would be the only one (possibly one other person) that would have access to the machine that the application was on. Edit: I should add that there is a web facing part of this application that would need to decrypt the data unless I added a tier.

I have looked ad nauseum, but no one seems to have a bulletproof answer.

Is this one of those problems where you have to settle for good enough? Given that I am using MySQL and PHP (possibly Python) is there a better way to approach this?

Answer 1

It seems that you are considering the use of a 'column-specific' key to use with 'AES_ENCRYPT' and 'AES_DECRYPT'. As the commenter said, this is a bad idea, because any intrusion will have access to all the data.

If you use a 'user-supplied' password, with/without a salt, you are being much more secure.

That said, if the encryption key is only readable by the application using it, you are probably 'good enough'. If the machine is broken into, they're going to get your data faster with a single key though.

Answer 2

I'd probably store it in a file that is in a non-web-accessible directory and locked down with file system permissions as much as possible.

Your web-facing script should not open any file system files using variables, especially user-provided ones. Don't even give them the option of slipping something passed your input filter (you are filtering your user-provided data right?) and possibly giving up the contents of the key file. Keep file paths to hard-coded strings and define()'s only. Since most of your data is stored in MySQL, this shouldn't be a problem.

When doing the decryption, read the key into a cleanly initialized variable, do the decryption, then overwrite the key variable (say with a string of x's) and unset the variable. This probably all sounds a bit paranoid but if you minimize the time the key is in the clear in memory and isolate it from all the other variables flying around your PHP script it can't make you any less secure.

If you and the one other person are the only ones that have physical access to the machine, this is probably just fine. If someone breaks in and steals the box, well, they've got all your data anyway so game over.

Answer 3

I'm not sure if using MySQL build in encryption would be the best solution to your problem.

PHP's M_CRYPT package is thought to be quite good and it gives you the flexibility to choose the algorithm that is best suited for your needs.

Storing your key on some other server has one big advantage: the key is not on the same machine as the encrypted data^*). So as long as the attacker does not have enough control over the compromised machine, they cannot get to the key.
If the attacker gains full control of the machine the data is stored on, they most likely will be able to query the web-service for the key.

However, transmitting the key from one machine to another opens up an entire new area that needs to be secured. Probably involving more key's and more encryption layers, thereby increasing the chance of mistakes being made.

^*) The other option is to enter the password upon webserver start up and only keep it in memory.

Possible solution
If seen a solution employed that used the following method for encrypting files for users with web access (I'm not sure of your environment, but it might be helpful):

• Upon user creation, a long random key is assigned to the new user.
• This random key is stored in an encrypted column in the user record.
  (only this column is encrypted as to not affect the performance of the rest of the record!)
• The encryption of the random-key column is done with 1 master password, stored in a file or in memory.
  (The better option is to enter the password upon staring your webserver and only store it in memory.)
  (Another approach would be to let the user enter a password and use that to encrypt/decrypt the random-key column, but I'm not sure if that would increase or decrease security)
• Every document that needs to be encrypted is encrypted with the random-key for that user and then stored on disk.
• Documents are stored with minimal permissions in the file-system.

The advantages of this approach are:
1. The random-key is encrypted in the database. So you still have the added security of the database-server, in combination with the encrypted column. 2. Documents are stored with different keys, if the attacker gets hold of a key, only part of the documents is compromised.

However:
If the attacker gets hold of the master password and has read access to the user-table, the entire system is, once more, broken.