20

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?

  • 5
    Have you considered storing the DB on a dedicated volume and encrypting it with dm-crypt and LUKS? Sure, it's not what you're asking, but everything will be encrypted. You could even encrypt the keyfile with GPG. – dsljanus Sep 13 '13 at 15:22
10

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.

  • 2
    Note: Unless the database is local, you need to encrypt the connection somehow or the password and key will be sent as plain text over the network. See security.stackexchange.com/questions/45838/… – Aaron Digulla Oct 8 '14 at 13:40
  • AES, MD5 is old and have exploits. what is new encryptions? – YumYumYum May 18 '18 at 9:00
  • I know this is just an example of how to encrypt / decrypt a field but using the password as an example is a bad idea. You should never have unhashed passwords in your database with or without encryption. – Caedmon Jun 30 '18 at 8:20
  • This answer is about field-level decryption, yet the question is not about that: "a good way to encrypt a mysql database" ... "I know I can encrypt particular fields of a database, but I'm interested in encrypting every field of the database." – LarsH Feb 21 at 11:14
7

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

2

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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