3

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?

Paul
  • 103
  • 6
  • 1
    Related: https://security.stackexchange.com/questions/71911/pattern-to-allow-multiple-persons-to-decrypt-a-document-without-sharing-the-enc, https://security.stackexchange.com/questions/91704/which-strategy-to-encrypt-data-accessed-by-multiple-users – jonrsharpe Jul 03 '17 at 11:28
  • @zaph - good point - post edited, thanks. – Paul Jul 03 '17 at 15:51
  • Is your front end really written in PHP? I'd be surprised if it was. – Luke Joshua Park Jul 03 '17 at 20:20
  • @LukePark - Yes it is PHP, why would you be surprised? – Paul Jul 04 '17 at 21:58
  • Incredibly uncommon to be using PHP as a front-end language. Almost unheard of. Can you disclose the interpreter you are using? – Luke Joshua Park Jul 04 '17 at 22:01
  • @LukePark - I think you may have misunderstood... The application is online, it's SAAS and is served by a web server, so in fact PHP is probably the most commonly used language in this scenario. – Paul Jul 05 '17 at 14:15
  • Then it is back-end, not front-end! – Luke Joshua Park Jul 05 '17 at 14:16
  • @LukePark - I was referring to the UI which is commonly called the "front end". The back end would be the MySQL database and other supporting systems that the user has no direct access to. – Paul Jul 05 '17 at 14:59
  • Yep, your UI will be written in HTML/JS/CSS. PHP is executed server-side, where the user has no access. Thus, back-end. – Luke Joshua Park Jul 05 '17 at 15:01

2 Answers2

5

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!

Paul
  • 103
  • 6
  • 1
    Pretty good solution, thanks for an idea Paul. However, I see one issue I can't overcome at the moment. What if the user needs to reset his password? In this case his private key is lost and therefore you need someone to give you access to all documents again. This is quite a roadblock for me at the moment as I can't expect system administrators will be manually resetting peoples' document keys every time they reset their password. Would you have any idea on this or you do not face this issue? – Vočko Apr 12 '21 at 06:57
0

One solution is to add one more level of a key between the users keys and the DB key. Then if the data is re-encrypted only that one extra key needs to be re-encrypted. It also may be that the need to change the encryption key can be accomplished just by changing this last key rather than re-encrypting the data under a new key.

But perhaps there are other better solutions such as DB roles can be used.

Keep in mind that is just one of the passwords of a user who has access to the encrypted data is compromised the data is compromised and that is escalated with each such user.

Another solution to reduce the number of sysadmins who have access is to put the DB on a dedicated server with only a very few sysadmins with 2-factor authentication and no direct Internet access, only secure access from the server online server.

zaph
  • 111,848
  • 21
  • 189
  • 228