Is it possible: Derived key based on variable number of private keys?

Question

I have a MySQL database that I want to encrypt with AES_ENCRYPT() and have to provide access to a variable and possibly changing number of users.

Is it possible to derive an encryption key based on a variable number of users such that each user can access the data with their unique private password, but no attacker can access the data without such a password?

The original process encrypted the database with a key held on the server. I proposed as one step better to do so with a shared key held by all users with access to the system, but not stored on the server.

This would be better but ideally each user would access the data with individual private keys such that

1. Any given key(s) could be revoked
2. New key(s) could be issued
3. No access would be possible without a valid key

Is such a system possible?

Answer 1

Yes it is possible and already in use in multiple way (LUKS for example). The way it works:

• Have a master key that encrypts the whole data
• Append a header in front that contains the master key encrypted by the password you want
• To add a password add a keyslot (master key encrypted by another password)
• To revoke a password remove said keyslot (the user cannot decrypt the master key anymore)

A crude implementation could be that your put your db in a LUKS partition (it could be a file) and add or remove password as wanted.