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?