For the field-based encryption of a database containing sensitive information, I was thinking of the following design:

  • Every user has a smartcard used for client certificate login.
  • After login the user gets the symmetric key (AES for example) encrypted with the public key of his smartcard.
  • The user decrypts the key and uses it to encrypt/decrypt any sensible data on the client.

In the database there is a table containing all the public keys of the user smartcards and the global symmetric key encrypted with the public key of each user. The rest of the sensitive data is encrypted with the symmetric key.

Is the data safe if the database gets compromised?

Another possible solution would be, to store additionally the same global RSA key on all the smartcards and use this key with RSA-KEM to encrypt all the sensible data. Disadvantage would be that this makes it harder to change the global key in the production setup as all the smardcards would have to be replaced/reprogrammed.

What do you think?


Storing the same symmetric key encrypted multiple ways is not a problem, but using the same key for every client is, unless you have an unusually high degree of trust between the clients, as it enables the clients to decrypt each other's communication with server.

It would be more secure to generate a new symmetric key for each session, which is then encrypted using the clients asymmetric key, and stored for the duration of session.

  • actually I thought about using two keys on the smartcard, one for the client side certificate (generated on the smartcard), the other one for the encryption of the data (generated once and the same written on all the smartcards) So the data is actually encrypted twice, once on the transport layer (ssl/tls) the other time with the encryption key. – st_dx Feb 27 '12 at 18:07

A secret which is shared by more than two people is not really a secret anymore.

Encrypting the data with a symmetric key K, and then encrypting K with the asymmetric (RSA) key of each recipient, means giving access to the recipient to all the data encrypted with K; "all the data" includes that which will be encrypted with K. This encryption method is the normal situation for secured emails (e.g. with OpenPGP), but a crucial point is that an email is "one shot" and each email is encrypted with its own, specific, random K.

With a database, things are more hairy, because a database is a set of data which evolves over time. By giving K to each user, you potentially allow them to read the database contents not only right now, but also the future contents which will be added later on. This is not necessarily a problem: as long as a given user has access to a database, he can (at least theoretically) dump all the data into a file of his own, and there is no way to make him "forget" the data. However, in the long run of things, this calls for key updates.

For instance, suppose that, at some point, you want to grant access to the database to a new user. This is simple: simply encrypt K with the RSA key of that new user. The dual operation of removing a user is more complex. Since you cannot force users to forget data, the best you can achieve is to deny access to new data (data which is added after the user removal), and this implies choosing a new K' distinct from K (and K' must not be computable from K, so we are talking about selecting a new random K' from scratch). So there are two choices:

  1. Set your database format so that each record can be tagged with an identifier for the key K which is used for that record. Each key update implies creating a new K with a new identifier, and using that new identifier in each newly added or modified record. A repository for all the encrypted K is maintained (indexed by identifier and by user). Upon usage, each user accesses the repository by using the tag on the target record, so as to obtain the key K which is to be used to decrypt the record.

  2. When the key is updated, all the data in the database is decrypted with the old K and reencrypted with the new K. A repository for the encrypted version of each K (indexed by user) is maintained.

The second method makes storage simpler, and have the nice benefit of "kicking out" removed users (in the attack model, we assume that a removed user took care to copy all the data he could, but if he did not, then kicking him out explicitly cis nice). However, key updates with the second method can be quite expensive, depending on their frequency and the database size. The first method is more generic and allows for fine-grained access control (e.g. making data accessible to some subsets of users).

Microsoft SQL Server has a lot of support functions (as the SQL language level) to implement such things (however, be warned that Microsoft documentation on cryptographic elements has an irksome tendency of redefining terminology, sometimes abruptly in the middle of the documentation itself).


My understanding is that OpenPGP and many other cryptosystems do exactly this -- they store a single symmetric key in many places, each place encrypted with a different public key. Each person uses their own unique private key to extract the one-time symmetric key, and then uses the symmetric key to decode other stuff.

" https://crypto.stackexchange.com/questions/2666/hash-decrypts-key-key-decrypts-cipher-why "

" GPG File size with multiple recipients? "

" https://crypto.stackexchange.com/questions/1680/information-leakage-from-the-ecryptfs-filesystem "

" https://crypto.stackexchange.com/questions/425/how-can-two-different-passphrases-unlock-the-same-content "

However, those system assume that it's OK if everyone who extracts that symmetric key can read everything encrypted with that key.

That doesn't seem to be the case in your situation, so I'm agreeing with JGWeissman: generate a new fresh symmetric key for each session, which is then encrypted using the public key of the user's smartcard and sent to the user. Store that new symmetric key on both ends only for the duration of the session.

It's not clear to me if this "sensitive data" is just a few shared files, each of which many but not all of your users should be able to read; or if this "sensitive data" is one or more fields in your database, each of which only the user (and perhaps one or two other people) should be able to read.

Assuming this is non-shared per-user data that is effectively fields in your database, each time those fields are changed I would

  • make up a fresh new symmetric key
  • encrypt the new version of that field(s) data with the new symmetric key. (Ideally, we would prefer doing this encryption at the user's end, so the database server never sees the plaintext data or the symmetric key).
  • store that encrypted data in the "encrypted data" column of the database in that user's row
  • encrypt the new symmetric key with the user's public key, and store that encrypted key in the database in that user's row
  • (optional) encrypt the new symmetric key with the public key of the other person authorized to read that data, and store that encrypted key in another column in that user's row.
  • destroy any plaintext copies of the new symmetric key

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.