3

To give a quick background, we need to implement a solution where we can guarantee that information is stored encrypted. Access to the encryption data will only be possible through an application that has dedicated access to the database. With every "request" to this application, authentication details will be provided that is then used to create a log of who has read what information and when.

My main requirements are:

  • MySQL 5.5
  • Database will be replicated for backup purposes. One should be able to restore from such replicated database, but by accessing a replicated database I should not be able to read any information.

My idea is to use application-level encryption and store explicit encrypted values in the database. That is, on a technical level, the database has no way of knowing that information is encrypted. The actual "structure" of the database (tables, columns etc) isn't something we consider secret. To implement the application-level encryption I'm thinking of applying AES_ENCRYPT/AES_DECRYPT that is built-in MySQL, using a passphrase that is only known by the application.

Does anyone see a problem with this approach? Surely, the passphrase must be kept secret. If the passphrase would leak, I thinking that it would be trivial to re-encrypt all values with a new passphrase. The database isn't expected to be large, performance requirements are low. Development and testing environments would be easy to have, as the only difference would be the passphrase used.

Improve this question

3 Answers 3

Reset to default
1

Potential problems -

  • You have to ensure you use SSL - otherwise the keys are sent in plaintext.
  • Your code is going to be littered with encrypt/decrypt calls. There is also the overhead of injecting in the key and sending it every time - if the network is your bottleneck then you could have majorly reduced capacity.
  • The keys have to be in the web application and are constantly sent to the DB server. This means two points of failure instead of one.
  • This relies on developers being vigilant. If at any point someone adds in a method which sends/receives data without the calls then its going to sit there in plaintext.
  • Some data may not be possible to encrypt this way without crippling the DB performance since it can no longer look at values directly or store them in a logical order.
  • Debugging becomes much much harder. Querying the DB to look at what is stored there is no longer trivial.

I thinking that it would be trivial to re-encrypt all values with a new passphrase

How much data do you expect to have? "Trivial" often doesn't scale. This might involve taking your entire system offline for an extended period of time.

Improve this answer
6
  • Thank you for the answer. I'm aware of that keys would be sent as plaintext and that SSL must be used with this approach. A better approach might be to perform the encryption/decryption entirely within the application. That way, I could easily implement common logic in our ORM for handling all the details. True that database won't be able to use indices in a normal way. For example, a date column. I'll have to give it some thought. As for scale, I would expect the total number of rows across all tables to be less than 1 million. The "domain" that we are storing is simply not that large.
    – Matthias
    Commented Nov 16, 2017 at 13:25
  • @Matthias - Considering this approach will require explicit encrypt / decrypt calls everywhere personally i'd suggest unless there are resource reasons doing it all in the web-application makes more sense and would be a similar amount of effort. Personally i'd either encrypt the DB disk or the DB itself - although this does mean the passphrase would have to be entered on restarts.
    – Hector
    Commented Nov 16, 2017 at 13:29
  • Will that work with replication? That is, guarantee that I can not see the information in the replicated database without knowing that passphrase?
    – Matthias
    Commented Nov 16, 2017 at 13:40
  • @Matthias - I think you mean backup rather than replication? Replication is usually to another live DB instance. If you had full disk encryption (with the same or a different key) on the backup machine then you would be safe. Alternatively you could encrypt the dump before sending it to the backup medium using something like openssl.
    – Hector
    Commented Nov 16, 2017 at 13:57
  • Perhaps our setup is uncommon, but we perform replication to several other MySQL installations that we don't consider being live instances. Periodically we then create snapshots from these replicated instances (using mysqldump). With a combination of these snapshots and binlogs we get near-realtime replication and the ability to restore data to more or less any point in time.
    – Matthias
    Commented Nov 16, 2017 at 14:12
1

It seems like your goal is to not trust the database with knowledge of the original data. If that's correct, then you would need to encrypt / decrypt on the other machine, NOT in MySQL. If you use MySql to encrypt / decrypt, then you are trusting MySQL with access to the original data (and the key), thus defeating the goal.

Before designing the solution, I suggest you sketch out the threat model that interests you. Each mitigation should then fully address something in that threat model. Remember, even low fences are more useful than really really tall poles which you hope your adversary will choose to run into for you. Threat models help identify gaps in that fence (like giving database access to the keys and plaintext.)

Improve this answer
2
  • Thank you for your input. It became obvious that we were trying to solve two problems at once with not trusting the database with the knowledge of the original data. Or rather, what we tried to achieve was that we didn't trust the data in the databases that acted as replication/backup nodes in some instances.
    – Matthias
    Commented Jan 19, 2018 at 15:52
  • We decided to model the database correctly with no application-level encryption and instead use encryption outside on the disk level. Furthermore, as for replication safety, this was easily worked around in a different manner.
    – Matthias
    Commented Jan 19, 2018 at 15:53
1

My idea is to use application-level encryption and store explicit encrypted values in the database.

You will only ever be able to do indexed retrieval on hash keys with explicit matches. It is a very unusual database design where that is a viable approach, and in such a scenario, there's little point in using a relational database at all, nevermind a 10 year old version of MySQL. Essentially your database is reduced to a fancy key-value store.

This is only going to work if your application is specifically designed to operate on a database with such characteristics.

I'm left wondering what the threat model is which merits such an approach. If you think you can protect the data against a malicious root user, you are very mistaken.

If it were me I would be challenging these requirements and trying to see if the objective can be met using native database encryption (I'm currently more up to date with the MariaDB implementation of this than MySQL's) or the OS facilities (LUKS, FUSE).

Improve this answer
1
  • Thank you. As I just wrote in another comment, the application-level encryption approach is no longer an option and the database will be modeled as a proper database instead. Encryption and security will instead be handled outside of the scope of the MySQL installation (though we will use MySQL's user concept to limit connections from only whitelisted sources).
    – Matthias
    Commented Jan 19, 2018 at 15:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .