Whats a good way to encrypt a mysql database, and is it worth it?

Question

I know I can encrypt particular fields of a database, but I'm interested in encrypting every field of the database. I want to make sure no one who gains access to a mysql shell but who does not have access to a decryption key cannot read anything out of the database at all.

I also want to make sure that if someone got root access to the machine, but didn't have a decryption key, they couldn't read the data.

How should I do this? Does it make sense to do? I'm concerned if someone has access to the mysql database they inevitably will have access to the key, so this makes no sense. Am I missing something?

Answer 1

Minimal, field-level AES and DES encryption is available: https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_encrypt

Noone can read data without specifying key for every query (or without adding it to the triggers/procedures).

example:

INSERT:

INSERT INTO users (username, password) VALUES ('root', AES_ENCRYPT('somepassword', 'key12346123'));

and SELECT:

SELECT AES_DECRYPT(password, 'key12346123') FROM users WHERE username = 'root';

Also, this requires SSL connection to the database.

And on lower level - you can encrypt filesystem too.

Answer 2

MariaDB recently added table-level encryption for InnoDB and XtraDB tables. https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/#specifying-what-tables-to-encrypt

MySQL also supports table-level encryption for InnoDB. https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

Answer 3

First: You store your key with the application and handle all encryption at the application layer.

Next: you ensure that the MySQL instance and the application [server] are on separate machines so that a root compromise on the MySQL server doesn't allow the attacker to read the key from application source.

This approach seems excessive. Handle sensitive data properly (passwords, credit cards, etc) but encrypting everything is overkill. (And likely counter productive in the world of primary keys)