In the accepted answer to Encrypting stored data for multiple unique users to access information, @lserni describes a mapping table for users. But among the operations for the table, they didn't mention adding a user, nor forgot my password reset requests, which are required for my (growing/changing) environment. How can I do these?The only solution I can imagine off the top of my head is to force someone like root to log in, decrypt their own key, then give it to the new user to re-encrypt. But that's inefficient and I'd like to automate the process - meaning I'd like to allow a user to create a new user without forcing someone else to log in.In short, my specifications are:I have a database which stores data for multiple groups.Each group's data should be private. I.e. encryption for different groups use different keys.Each group has multiple users. Users can belong to multiple groups. I.e. each user needs to be able to read/write (and thus encrypt/decrypt) all the data for each group they belong to.I want to be able to add, remove, and update: the users (especially their passwords/key-encrypting-keys), the groups, the data-encrypting keys, and the data.Aside from the problem I'm asking about, Iserni's solution seems ideal (recognizing of course that a password should be run through PBKDF2 or a similar key-stretching algorithm before being used as an encryption key).
How to add users to a mapping table?
database design;encryption
I want to be able to add, remove, and update: the users (especially their passwords/key-encrypting-keys), the groups, the data-encrypting keys, and the data.I think you can gain the required independence between entities by using a mixture of asymmetric and symmetric encryption. The data storage requirements are quite high but only little data need to be thus protected.You can have:User        Public Key      Private Keylserni      PUB             XXXXXXXGroup       Group Encryption Keypoponi      ZZZZZZZ symmetrically encrypted with YYYYZZZZZZ is the cryptographic key used to symmetrically encrypt the data of group poponi; this value is symmetrically encrypted with the cryptographic key YYYY, which is the group password.Mapping     lserni      poponi     YYYY encrypted with lserni's public keyWhen you create a group, and assign an administrator to it, a random symmetric encryption key ZZZZZZ is generated and used to encrypt that group's data. This value is not stored in the clear, but encrypted itself with another random password YYYY, which will be associated to the users.This decoupling of YYYY and ZZZZZZ allows making the data inaccessible by just changing YYYY (which only affects a column) instead of having to change ZZZZZZ (which requires decryption and reencryption of whatever data the group has). Of course, if the encrypted data is not so much more than a single column of the database, there's little to be gained by this second level of indirection, and one can get rid of YYYY altogether and store the encryption of ZZZZZZZ into the user mapping table instead of the encryption of YYYY.Every user has also a public/private key pair assigned, with the private key being symmetrically encrypted with the user's password.This allows third parties (group administrators etc.) to supply any user with an information, YYYY, that only he will be able to read back.When the user logs in, he decrypts his private key using his own password, uses the private key to access the YYYY value for the group password, and retrieves ZZZZZZZ. He can now read and write the group (and forget about YYYY altogether).User and group deletion is straightforward. Except that a group with no users is lost forever and could as well be deleted. For this reason, user deletion should ensure that one administrative user at least is always present.Simple user addition is also straightforward (we generate the keypair and store it, the private key encrypted with the temporary password used for the first login). At this point the user is assigned to no groups, so nothing more is required.When the user changes his password, his private key is decrypted with the old password and reencrypted with the new password.To assigning an user to a group, the group password must be known by the application, which requires someone with group access to be logged in. This someone can create a mapping line since the public key of the assignee is in the clear.When the group password is changed, all users in that group get their data updated, and this can be done without knowing the user's password. The operation is done by someone which has group access, so he has the group password. user     group   pwd_of_group_encrypted_with_user_privkey lserni   poponi  *****To reencrypt the group data, all data needs to be decrypted with the old key and reencrypted with the new key, and the group information row has to be updated. The group password, with which the old key was encrypted, is used to encrypt the new key and store it in the group information row.This operation should never be necessary since the group encryption key ZZZZZZZ is never leaked outside (nor is the group password YYYY).This architecture also ensures that someone without access to a group can never add anyone else (or himself) to said group. If one user succeeds in stealing the whole database, he will still be able to only get the data he was entitled to.Who assigns a user to a group?To assign a user to a group, one must have access to the group, which means knowing the password. If there is a group administrator deciding who belongs to what group, that's OK.But what if a user has to be assigned to the committers group automatically? Then the application needs to have access to the password, which means that the system can't be self-contained and secure.We can have it being not self-contained, storing the password in a different system with restricted access and a very small attack surface. Even if someone stole the database, the group password wouldn't be there.I don't think the problem can be solved, because whoever can grant access to a group can do so in all circumstances; if the whoever resides on the system, because it's the application itself, capturing the system will entail obtaining access to all groups' data.