Updated
There are many variation on how you could implement what you have requested, they all have pros and cons so it really depends on your business requirements as to which is the best.
Keychain: Prompt for group password and cache
The least complicated way would be to ask the each user for the password and then cache the group password privately for each user (keychain), so as that they will only be required to enter the password once the first time and once after each time it changes.
The key thing here is making sure that the group password can't easily be exposed in the database.
One example of how this could be done would be to:
- Create a keychain for each individual and encrypt it with a common key.
- Encrypt the common key with the user's password, or whatever is being used to authenticate the user.
A keychain could be as simple as a row in a database, one for each user mapping to a group data store.
You don't need to do the second step but this prevents having to decrypt/re-encrypt each group password when the user's password changes, you only need to decrypt/re-encrypt the common key.
Pros:
- Fairly easy to implement.
- Can be done natively in the database with newer version of MySQL.
Cons:
- You need to distribute the group password securely to each user once + each time the group password changes.
- If the user forget his/her password and it is administrative reset, the common key/group passwords would need to be re-entered as they can't be decrypted, which mean redistribution or writing them down, etc.. - a recovery key would mitigate this.
- Group password can be easily shared by individuals that have access with individuals that shouldn't have access. Access control would mitigate the risk of this.
Keychain: Using asymmetric key distribution
This is a variation on the first method. In this method you rely on the strengths of asymmetric/public key encryption such as RSA for distribution and protection of the group password. The key advantage to this method is that you can eliminate the user having to enter the group password.
- Each individual has an asymmetric encryption key.
- The group admin sets who has access to the group data by encrypting the group password with an individual's public key.
One example of how is could be could be done is:
- By storing a public key in the database with user information.
- When a person needs access to the group data store the group admin adds group password to the individual's keychain. In practical terms this could mean, getting the group password, encrypting it with the individual's public key then adding a row in the database mapping user to data store with the encrypted group password.
- The individual with access might now get an invitation message letting them know that admin has added them to the data store.
- Encrypt the private key with the user's password, or whatever is being used to authenticate the user.
Pros:
- Reduce risk of group password being exposed.
- User's does have to enter the group password.
- Group admin has more control on who gets the group password, because the group password is not directly shared with the individual, only within the application.
Cons:
- If the user forget his/her password and it is administrative reset, the private key can't be decrypted and would need to be regenerated. This would require each group admin to re-add the individual - a recovery key would mitigate this.
Use access control in the application
Store the group password in the application/database, using a master encryption/decryption key that the application uses to decrypt the group passwords.
Pros:
- Commonly used.
- Most frameworks/databases will natively support this.
Cons:
- Relies on access control to restrict access. But that is expected.
- Relies on secure storage of the master key.
- Application admins may be able to gain access to the data because they have access to change access control or access to the master key. Generally not an issue.
Use a smart card
If individuals can use a smart card (which might not be possible) you can store the group password or the common/private keys mentioned in the first to options. Then use a pin to unlock it. Windows 8.x supports virtual smart cards on a Trusted Platform Module (TPM) chip which might also be an option if each person are all part of the same enterprise.
Store in escrow
The last option is to store the group password, common or private key in an source outside the application, that uses access control but can be integrated into the application.
This could take many forms including:
- A restricted attribute, deployed certification of a directory service such as Active Directory Domain Services
- A claim from a federation services such as Active Directory Federation Services
- Other custom application/identity service
Other notes
Newer version of MySQL natively support AES_DECRYPT/AES_ENCRYPT methods which can offload some of the encryption from the application.
I mentioned recovery key a few times, which would be an asymmetric key that the application or an admin uses to recover the common or private key used by an individual. In practical terms when the common or private key is stored for an individual it is encrypted by the individual's password and stored, a second copy of is also encrypted a with the recovery key.
In the event of a password reset of the individual the recovery key can be used to decrypt the recovery copy of the common/private key and then re-encrypt it with the user's new password.
I hope that answers your question.
