28

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?

Snitse
  • 413
  • 1
  • 4
  • 6
  • 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. – dlyk1988 Sep 13 '13 at 15:22
  • Could you please elaborate what you mean by that? "And likely counter productive in the world of primary keys" (I was not allowed to comment was able to post this :=) ) – Soundar Rajan Aug 16 '21 at 06:42

3 Answers3

11

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.

GioMac
  • 4,444
  • 3
  • 24
  • 41
  • 3
    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 http://security.stackexchange.com/questions/45838/is-why-should-you-avoid-aes-in-mysql-true – Aaron Digulla Oct 08 '14 at 13:40
  • AES, MD5 is old and have exploits. what is new encryptions? – YumYumYum May 18 '18 at 09: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 08:20
  • 1
    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 '19 at 11:14
9

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

Community
  • 1
Scott C
  • 211
  • 2
  • 5
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)

Daniel Widrick
  • 3,418
  • 2
  • 12
  • 26
  • Just wanted to point out this would prevent the "LIKE" query issued against the fields so encrypted. Imagine a user search where the first and last name were encrypted for PII or PHI concerns. There would be no autocomplete, no partial search. Now imagine searching for a user that you know phonetically sounds like "Mohammed" but it could be spelled Mohammed or Muhamad or dozens of different ways in between. Now you need to spell it exactly correctly in order to find the correct user. – Arlo Guthrie Sep 27 '21 at 18:18