4

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:

  1. I have a database which stores data for multiple groups.
  2. Each group's data should be private. I.e. encryption for different groups use different keys.
  3. 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.
  4. 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).

3
  • That answer had a very specific scenario - the user wanted to encrypt his own data which he himself just created, in such a way that nobody else could extract the data, and without depending on the password for encryption (the first requirement mandates that they still must depend on it for encryption key retrieval). I agree with @maple_shaft that this setup seems over-engineered for your needs.
    – LSerni
    Jun 30, 2016 at 16:14
  • More to the point, you do not say what your specifications are. The database is to be encrypted - who has access to what? Is it acceptable for the application to be able to read everything, in principle, and simply refuse to allow a given user to read some data? (which means that if the user manages to circumvent that refusal, he will have access to those data).
    – LSerni
    Jun 30, 2016 at 16:22
  • @Iserni you're absolutely right. Your answer was right for the other question, and the reason I'm asking this is because it was so good I wanted to extend it for this. I've added my specifications to the question.
    – Miryafa
    Jun 30, 2016 at 16:37

2 Answers 2

2

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 Key
lserni      PUB             XXXXXXX

Group       Group Encryption Key
poponi      ZZZZZZZ symmetrically encrypted with YYYY

ZZZZZZ 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 key

When 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.

3
  • Thank you. This doesn't seem to solve the core problem though: 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. That's the same as it was in the answer to the previous question, and I don't see how I can automate adding a user if someone has to sign in to grant them a password for the group key.
    – Miryafa
    Jun 30, 2016 at 21:22
  • 1
    On a separate note, it seems to me that the group password YYYY can be entirely cut out of this solution (so the mapping table has E(ZZZZZZ, PUB)) without losing anything. Also the answer says both ZZZZZZZ asymmetrically encrypted with YYYY and ZZZZZZ... is symmetrically encrypted with the cryptographic key YYYY - is it supposed to be the latter?
    – Miryafa
    Jun 30, 2016 at 21:23
  • Yes, you can do without the double indirection, but this may impact the time taken to change the group password. If YYYY and ZZZZZZZ are the same (i.e. you only have the latter), changing it entails a complete decrypt/reencrypt of the data. As regards the asymmetricity of ZZZZZZ's encryption, that was my mistake - answer fixed. Thanks for pointing that out!
    – LSerni
    Jun 30, 2016 at 23:59
1

The issue I have with the answer to the question you posted is that if you view the requirements of the OP for security, nowhere does it actually mention that each user must provide their own decryption keys. In fact the question even goes so far as to suggest that they explicitly do not want the users password to be a determination in the encryption of data in the database.

I disagree with the answer provided here.

The appropriate way to handle this is as follows:

  • Authenticate the user independently of encrypted data
  • Properly Authorize users to only be able to view data they are privileged to see.
  • Persist the sensitive information in an Encrypted form with a strong cryptographic algorithm
  • Use a general application specific key or set of keys to encrypt all data
  • Ensure that compromising the database server does not secure access to the encryption/decryption key (perform all cryptographic operations outside the database, Eg. on the application server, in an HSM, etc..)

The data is still encrypted and with a strong algorithm like AES, there are no feasible attacks on encrypted text without also having the key. Anybody just peeking in the database is not going to see sensitive information.

This raises the bar however on your authorization logic in the application. It should be heavily unit tested and integration tested to ensure that only authorized users are able to view the data they are supposed to see.

The answer you reference is far more complicated than what the OP suggests through requirements and is in fact potentially less secure in the process. The key should always be kept secret and private and never shared. Further it is over-engineering at its finest.

9
  • I agree on this solution - which is why I'm upvoting it :-) - but in my answer to the other question, which "even goes so far as to suggest that they explicitly do not want the users password to be a determination in the encryption of data in the database", the password was not, in fact, used to encrypt the data. Rather it was used to encrypt the data's encryption key itself. The encrypted data could not be independent of the user authentication, since some data were not to be readable except by the appropriate user, so each user needed their own key.
    – LSerni
    Jun 30, 2016 at 16:20
  • @lserni some data were not to be readable except by the appropriate user, so each user needed their own key But of course that would implicitly be the case in my design proposal no? If the application itself owns the sole key used for decryption, then logically only interfacing with the application will give you decrypted data. If I interface with the application then barring an authorization bug, data would never be readable by anybody that wasn't appropriate. A == B and B == C therefore A == C. This is why I think this is over-engineering.
    – maple_shaft
    Jun 30, 2016 at 16:41
  • @maple_shaft Thanks for the answer. The specific part "Properly Authorize users to only be able to view data they are privileged to see." is the part that I don't know how to do, and is the reason for my question. I added my specifications to the question at Iserni's suggestion to make it clearer what I'm looking for.
    – Miryafa
    Jun 30, 2016 at 16:42
  • @lserni the password was not, in fact, used to encrypt the data. Rather it was used to encrypt the data's encryption key itself. Which would need its own application specific key to encrypt/decrypt for the key :) You are not really removing the single point of failure here, just unnecessarily obfuscating the solution.
    – maple_shaft
    Jun 30, 2016 at 16:43
  • @Miryafa There is not really a specific solution for "properly authorize". This is just something that you ensure through code that certain user actions (Eg. update this record, read this record) are allowed or not based on the users current allowable privileges. It is just something you code to your requirements and test well.
    – maple_shaft
    Jun 30, 2016 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.