I have a MySQL database that stores sensitive (personal) data about individuals; and have been tasked with ensuring that this data is encrypted in some way to protect the individuals and their data if e.g. the server should be compromised, or a malicious user from our hosting service provider accesses the server without authorization. The database is used by a PHP web framework residing on the same server.

I'm struggling with a good scheme that allows the data to be encrypted & impossible to read without proper authorization; while maintaining the functionality (indexes, database relations, being able to read back the data in the web framework). What are best options?

Two approaches I've considered are:

1) Encrypting specific fields/data in the database with a key so that if the database is compromised, the information in it will not be deducable to an individual user (e.g. we maintain indeces and relations, but personally identifiable information is encrypted). The app would decrypt the information using the key at runtime. The challenge is in how to manage the key - if it's placed in the app logic or accessible by the app logic as a file on the same server, it can still be compromised. Possibly it could be located on another server; but it would still be necessary to access at run-time by the app logic; i.e. access to the app logic would make it possible to gain possession of the key. Possibly I could store the key in memory upon boot of the server; but it introduces a possible stability issue (service down after reboot). What are options? Is this a good approach?

2) Implementing some sort of logical data division between personally identifiable information, and the remaining database. E.g. a table with personal information (user name, email) an index; a table with the sensitive data (e.g. table of health info) with another index; and then introducing some type of one-way key-based encryption (think e.g. http://en.wikipedia.org/wiki/Message_authentication_code) table mapping between the two, where the relation between the personal information & sensitive data can only be made (even run-time by the app-logic) if a key can be supplied to match the table together. But again, I stumble upon the need to manage access to the key used in the above scenario; similar to above.

What is best practice?


All the details of proper database information handling go well beyond the scope of a quick StackExchange answer. You really want to do this the right way. Part of your problem is the architecture where the database, and the web app accessing the sensitive info are on the same server. If that server gets compromised, so does the keying material for any encryption you do.

If you are just looking for a practical fix, rather than design theory, I have two suggestions.

One one is free, and open source, but actively under development (so I have no idea how suitable it would be for your business production use).

The other is a commercial solution you could buy (suitable for business, but I don't know your budget or business needs).

Open source - cryptdb (code available on github)

Commercial - Voltage SecureData

I've used both, but without knowing more about your circumstances, I couldn't say which is right for you. Voltage is more business-ready, and used by fortune 500 companies, cryptdb is more of a research project which may appeal to do-it-yourself types.


This is quite similar to Protect database data from everyone, including sys admins, etc.

Your second option doesn't work (at least in isolation). Just knowing the health info data may be "bad enough" (and indirectly matcheable to the user). It may be interesting to additionally hide that relationship, but I'm not convinced it's worth doing it.

The second option is correct. As you want to maintain the db searchability, you keep indices on the fields used as keys (eg. name, patient number), which you are not encrypting. The other fields are decrypted by the application.

The best solution about where to store the key is not to store it. Make the user manually input the key. Or encrypt it to several public keys, and the user needs to unlock its own key before using.

Suppose we have the following schema:

Table doctors:
 Username, public key, disabled

Table patients:
 Name, encrypted info

The first time a new user opens the app, it locally creates a new (password protected) public key and registers itself into the doctors table (subject to validation, etc).

The next time, the user is asked for its password (used to unlock its key), and knowledge of the private key is used to control login.

When fetching the details from a patient, recover the blob, decrypt with your public key and extract the details (it may be more efficient to store multiple fields in one blob if they are actually going to be used together).

You can store either in plain in the db (which would allow an attacker to insert its own key) or encrypted in the blob, so an existing doctor needs to add the new doctor as "treating the patient".

If the app founds that a patient is linked to a doctor with the disabled bit is set (eg. it left the company), such is removed from the list of people things are encrypted to.


I think you need to decide do you want to encrypt the whole Db, or do you want to Encrypt Each record individually.

Encrypting the whole Db - is easy to accomplish. There are various mechanisms that you could implement. This mechanism provides good protection from being hacked/db stolen, but will be easy for your IT staff to read/steal the data.

Encrypting each record - gives your users the best data protection. As if the Db is stolen then the hackers would have to brute force every record. Implement as a 2 server solution, 1 server generating the key, the other server storing the data. This protects your data from your own internal staff, as well "Mr Black Hat", as long as the 2 database are separately administered. Mr "Black Hat" now needs to steal both Db from 2 systems in order to access your data.

Hope this can provide you with some ideas as to how to approach the issue.

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