I want to store encrypted data in a database.


  1. Application-level encryption (encrypting before sending to the DB) is not a reasonable option for my use case.
  2. The DB providers I'm considering do not allow me to create multiple users/manage permissions at the price point I'll be paying. This means that separating permissions for logs vs reads and writes is not an option.


  1. Allow MySQL to handle AES encryption (via aes_encrypt/decrypt) for me (I'm aware of both the ECB block issue and the fact that MySQL logs sensitive data in plain text if not configured correctly)
  2. Use a DB provider that offers encryption at rest (the kind that happens transparently--automatically encrypt on SET and decrypt on SELECT)


Question 1

Why would I ever choose this type of encryption at rest over aes_encrypt? (As a note, I'm not storing credit card info or anything else that would encourage me legally to encrypt at rest).

As I understand it, the point of encrypting data in the database is to confront the reality that DB breaches happen. With that in mind, if someone gains access to my DB (meaning they can execute queries), wouldn't this type of encryption at rest actually help them because it would simply decrypt data when theySELECT? It seems to me it's slowing down queries without adding security.

aes_decrypt, on the other hand, requires that the intruder have access to a key stored on a different machine behind different credentials. Isn't that safer?

Question 2

In the scenario I described in question 1, an attacker has gained query access to a DB. If that's the case, couldn't they also just enable logging and retrieve the plain text AES key thereby rendering aes_encrypt useless as well?

(Remember constraint number 2 here.)

Question 3

Excluding sql injection, are there other scenarios in which an attacker would be able to view what's in my database without executing a SELECT and therefore without triggering a transparent at rest decryption?


If you have any recommendations that fit within my constraints please let me know. Thank you!

  • 1
    Great set of questions. I think there are a number of considerations, including performance, price point, and threat model, but I can't give a real answer. – Jesse K May 12 '16 at 19:32
  1. Transparent vs manual encryption

    Transparent encryption can be easier and safer, because the provider is responsible for key management. To them this is just another operational duty, along with backups and monitoring and replication and so forth.

    It is very easy to screw up key management- from picking bad keys to losing them to exposing them to failing to rotate them properly- just like it is easy to screw up other operational activities. And as with backups, if the keys are lost then the data is lost. Which problems does one want to spend one's time solving?

    In terms of attack scenarios, there are many to consider- attackers could have the ability to passively monitor traffic on the network between the application and the database; could have the ability to read data from application or database file systems; could have access to physical disks; could have access to account credentials; could have a webshell operating in the application. Under most scenarios, transparent and manual encryption are equally vulnerable.

    It is certainly possible for an attacker to have access to database credentials but not the application, so there is potentially some additional hurdle for them to overcome in the manual encryption case. But someone doing manual encryption has to have done a lot of things right, and a determined attacker with access to encrypted data has a lot of potential paths forward. The number of data breaches where data has reportedly been encrypted or hashed but then is later exposed is uncountable.

  2. Query access and query logging

    Query access to a database is not the same as administrative access, which is usually required to enable logging. However, when talking specifically about provider offerings, credentials for query access can perhaps also be used to configure some control panel from the provider, which would likely allow for enabling some sort of query logging.

    But take a step back- in the common case, the impact of an attacker achieving access to the database, especially write access, does not change depending on whether data is encrypted or not. Taking a page from ransomware attacks, they could run their own "UPDATE $table SET $encrypted_column = aes_encrypt(encrypted_column, 'attacker-key');" and require the victim's decryption key, or just money, to return custody of the encrypted data.

  3. Access to data outside of sql injection or the query engine

    As above, there are many scenarios in which an attacker may be able to view what's in the database without going through the query engine. Someone may have visibility on the network. Someone may be able to see the data files the database is using. Someone may be able to get to the physical disks as they get rotated out of service. Someone may be able to get to backups. All of these things happen every day.

Recommendation: absent a requirement for encryption at rest and specific scenarios that have to be defended against and need special handling or consideration, just let the provider run the show.

  • Thank you for this thoughtful insight. I am of a mind that more encryption is better as a general rule. Anything to make it harder. And this would actually make more sense in a scenario where the attacker may have filesystem access but not application access. Although MySQL Community Edition only allows for cleartext master key storage on the same filesystem. Use of Oracle's Vault plugin requires an Enterprise license. So is the former option adding any real security? It feels like an almost useless feature preview locked behind a "Pro Upgrade." – gillytech Apr 24 '20 at 3:15

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.