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.