Developing a database for many users, and thousands more "people" in the database, one section requires we encrypt the personal data - Youth's information, medical information, etc.

We have users who are able to access the personal data on people within their control, but we are thinking that we will need to also encrypt the data when it's stored on our server. I believe that if I use each user's salted passwords to encrypt the data, only that user can access the data (not good). If I have one master key, then it would need to be stored outside of the application to access the data.

Are there techniques that can encrypt the data in such a way that a data breach on the server won't show the data in the database in clear text, but that multiple users of certain permissions can decrypt the data they need?

  • Must encrypt the data in the database
  • Multiple users may need to access the same encrypted data, based on their permissions
  • Cannot be derived from the user's passwords (since some users may leave or change, requiring access to be removed/edited/added)
  • HTTPS is already getting used, and I understand encrypting data client-side is a Bad IdeaTM
  • If it matter: MySQL is the database system
  • I can't write a proper answer since this isn't my area of expertise, but cl.cam.ac.uk/~rja14/Papers/SEv2-c09.pdf might be helpful. – Ixrec Feb 2 '15 at 19:25
  • @Ixrec I'll read through that in the mean time. Thanks! – Canadian Luke Feb 2 '15 at 19:29
  • Not sure, but this question may be better suited to security.stackexchange.com. – Jules Feb 3 '15 at 11:22

Are there techniques that can encrypt the data in such a way that a data breach on the server won't show the data in the database in clear text, but that multiple users of certain permissions can decrypt the data they need?

Yes. You need two things:

  • modify your existing table structure so that all fields that you want encrypted are appropriately altered in size and type. For example a VARCHAR(50) would become VARBINARY(64) in order to employ AES algorithm, that uses a code block of 128 bits (16 bytes) and would output blocks of multiples of 16 bytes.

  • then you add a mapping table for each entity which you need to encrypt. For example you want to encrypt fields in tables User and Report, and want them independent; you add two tables User_Map and Report_Map. If Reports belong to Users as a one-to-many relationship, and access to User 12345 will therefore grant access to its Reports, you only need User_Map (any Report will use the secret key of its User). And so on.

The mapping table holds three columns: Accessor, Accessed, and Key. Accessor identifies whoever is accessing the data (it could be a user_id for example). Accessed identifies the entity which is being accessed (user_id or report_id). Finally key is a cryptographically strong random value associated with the Accessed resource instance at the moment of its creation, encrypted with Accessor's password.

All data in the mapped resource is encrypted using that random value, which will never be disclosed and will never be changed; changing it, while possible, would be really awkward.

So if you have five Books in your table, each of those will have a random value of its own, that exists nowhere in the clear.

The operations you need to support are:

  • access to a resource by Accessor: Accessor has identified and his password is in memory. When accessing resource XYZ, a search for user_id = Accessor_id AND resource_id = XYZ will yield XYZ's encrypted key. Decrypt it with the password. The key thus obtained will allow decryption/reencryption of XYZ's data.

Multiple Accessors can access the same Resource:

Accessor_id     Accessed_id     Key
1(joe)          101             ENCRYPT('random101', 'joespassword');
2(alice)        101             ENCRYPT('random101', 'alicespassword');
1(joe)          102             ENCRYPT('random102', 'joespassword');
  • update Accessor's password: you need to decrypt all Key values that are encrypted with the old password and reencrypt them with the new one:

    UPDATE Users_Map SET CKey = ENCRYPT(DECRYPT(CKey, :OldPassword), :NewPassword) WHERE Accessor_id = :MyId;

  • delete Accessor's credentials: just kill all tuples with that Accessor_id (note: if all Accessors with access to a given Accessed are eliminated, the Accessed's data will become unavailable. One user (e.g. root) should have access and not be deletable).

Some operations you might want to support:

  • access all data accessible to Accessor in a hierarchical way (i.e. you are Accessor's supervisor): that's tricky. You need to encrypt and maintain Accessor's password, which you can only do if Supervisor is logged in. To provide for this, you can supply a asymmetric encryption scheme whereby if user A is supervised by user B, then the t-uple (B_id, A_id, (A's private key encrypted with B's public key)) is available in the hierarchic database. This way, it is always possible for a supervisor to access his people's private keys, and they can update them without needing the supervisor's intervention.

  • granting rights to a Resource to a user also requires using asymmetric ciphers, because while user A is in possession of the resource instance's unique key, s/he can neither send it in the clear to user B (it would permanently disclose that resource instance's plaintext for everyone), nor can s/he encrypt it with B's password since it is known only to B. The solution is encrypt the instance key with user B's public key (which can be known), while the private key remains encrypted.

Since sending the private key might be difficult, being a couple of kilobytes, you could generate a public/private key pair and encrypt the private key symmetrically using a BCrypt hash of the user's password. When the user logs in, his private key becomes accessible to him or her, and can be used to unlock all asymmetrically encrypted information, if there is any (if you don't need grants and hierarchies, there might be no need).

Performances and limitations

The performance impact is not too large, if we consider that encrypted data are used mainly upon receipt or sending data to a user (which are slow operations in themselves). But this introduces the main limitation - encrypted data cannot be easily used in the system.

For example, straight search is either impossible or very slow: techniques for encrypted searching exist, but with this scheme, looking for records containing "Hello world" would require changing something like

FROM Resource
WHERE searchField LIKE :searchText


FROM Resource
JOIN Record_Map ON (Resource.id = Record_Map.Resource_id)
WHERE DECRYPT(searchField, DECRYPT(Record_Map.Key, :myPassword)) LIKE :searchText
AND Record_Map.Accessor_id = :myId


The database contains no plaintext keys or passwords, so capturing a database would not compromise data security. Standard caveats apply: user passwords must not be guessable and must be properly stored and checked (bcrypt is good for that). Accessing a user's password only allows accessing the data that user is granted read rights to. Possession of a currently valid password and physical access to database would allow recovering the accessible data not only in the present but in the future (provided physical access to the database is secured again).

Example: Eve steals a copy of the database. She is unable to read anything since she has no passwords. She manages to acquire Alice's password and is thus able to decrypt Bob's record, that Alice has access to. Alice changes her password, and updates Bob's record; after Dane is added to her people, Eve steals a fresh copy of the database. She can still read Bob's updated record (she has Bob's key from old database), but not Dane's (Dane's key is encrypted with Alice's new password, that Eve lacks).

To "re-secure" a record with N managers it is necessary to change its encryption key, which requires updating all N extant mapping records for that record; which requires access to all N passwords at the same time.


I do not think encrypting all the data in the database is a good idea, as it will be a major performance issue (especially when it comes to searching).

Instead I'd suggest to implement application level security for users, and encrypt the database files from the operating system level or store them in encrypted file container such as (veracrypt / truecrypt)

But if you are willing to accept reduced performance (and relatively complex logic),

say you have 2 records in the database (A,B) and 3 users (Admin,User1,User2)
A -> Admin and User1 can access
B -> Admin and User2 can access

In this scenario, to access record A a key needs to be used which Admin and user1 has access to while for record B key needs to be shared among Admin and user2. The issue is now admin has 2 keys and when admin tries to access record A he needs to know which key to be used.

To solve this, system needs to keep list of keys and needs to record which key used to encrypt the record. This again is a security threat, as if someone managed to gain access to database it's easy enough to find keys and decrypt data.

So although not perfect solutio it looks like this,

int KeyId;
string Key;

int UserId;
List<EncryptionKey> EncryptionKeys;

    //encryption and decryption can be done in both db and application level depending on   the algorithm used
    string EncryptedField1;
    string EncryptedField2;
    int EncryptionKeyId;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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