An online system (front end written in PHP but that's not too relevant) needs to store text in a MySQL database encrypted so that local sysadmins cannot view the data, also so that the data cannot be viewed in the event that the database is stolen. There are multiple users on the system who must have access to the data, they login/authenticate via a standard setup, i.e. username and hashed password in the same db.
As the stored data needs to be decrypted by the system for display to an authenticated user, but local sysadmins must have no way of decrypting the same data, the obvious method would be to have a secret key that is used to encrypt/decrypt the stored data using a symmetrical cipher. The problem (really the question I am asking for advice on) is how/where to store this key?
The authenticated users should not have direct access to the key, so it needs to be stored within the system somehow and used by the software to decrypt the stored files on demand, but also local sysadmins must not be able to learn this key or they could use it to decrypt the stored data.
So one way would be to store the key in the database encrypted, but in order for the system to decrypt and use the key on a per user basis, it would have to be encrypted against something unique to the authenticated user, e.g. their password. Ok, so far so good, but there's a problem...
What if the key needs to be changed? The person changing the key would either have to know everyone's password so they could encrypt the new key against every users account (impractical), or they would have to give the new key to each user and ask them to re-enter it (not an option).
Also, is it good idea, from a security point of view, to have this key effectively stored n times (where n is the number of users) in the database, encrypted with different keys (users password)? i.e. does this expose the key more by giving potential hackers multiple examples of the same encrypted data?
Is there a better way?
Thanks to jonrsharpe for pointing me at Tom Leak's post: https://security.stackexchange.com/a/71915
We will base our system on this method as follows:
Document is stored in a table encrypted using a symmetrical cipher.
The secret key for the above symmetrical cipher is stored in a separate table, once for every user that has access to the document, encrypted using an asymmetrical cipher with the user's public key.
The user's private key is stored in another table encrypted using a symmetrical cipher, the key to which is the user's password.
This means access to the document can be revoked by simply deleting that user's entry in the document key table; If the document is modified the system just needs to delete all the entries for the document in the key table then add them back encrypting with each users public key; additional users can be given access just by adding an entry to the document key table encrypting using the user's public key; user's that have access to the document can decrypt the document's secret key using their private key, which in turn is decrypted using their own password.
Just what we needed!