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?

2
  • 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, 2013 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 :=) ) Aug 16, 2021 at 6:42

3 Answers 3

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.

4
  • 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 security.stackexchange.com/questions/45838/… Oct 8, 2014 at 13:40
  • AES, MD5 is old and have exploits. what is new encryptions?
    – YumYumYum
    May 18, 2018 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, 2018 at 8: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, 2019 at 11:14
10

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

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)

1
  • 1
    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. Sep 27, 2021 at 18:18

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.