Encrypt data stored in a database

Question

I recently talked to a potential customer who wants to build up a database which contains fairly sensitive data. One of her key requirements is, that the data storeed will be encrypted in a way that only the user will be able to access it. Even if there are certainly some disadvantages I thought about how I could achieve this and I came up with the following approach:

When a user is created I am creating a public/private key pair for him. The private key will be encrypted symmetrically with the users password. All data that is stored in the database (most likely except the timestamp - and of course the id) is encrypted beforehands with the public key. When the user logs in the password will be used to decrypt the private key, which can in turn be used to decrypt the data from the database when reading it. All en- and decryption may happen transparently in the data access layer.

There are some major drawbacks to be addressed here:

• We'll have to de- and encrypt the private key when the user wants to change her password
• If the user loses her password we won't be able to reconstruct the data
• Either the private key or the users password has to be stored in plain text temporarily
• Whenever we suspect that a private key has been corrupted (in the sense of stolen) we'll have to reencrypt all of the users data

and of course some more that did not come up to me, yet.

The first issue is inconvenient for us, but may happen transparently to the user, hence no big issue.

The second one is a bit more intricate. We do not want the user to lose her data, but we'll have to assume that the user will loose her password (storing and/or using a fixed password are no options for obvious reasons - never). A possible solution would be to create a very strong passphrase (30+ characters) which will be used to encrypt a backup of the private key. This might be sent to the user via snail mail or the like. The user may use this to restore her data if she forgot her password.

I have a bad feeling about no. 3, but I have no better idea at the moment.

The fourth issue again is inconvenient for us and may take a while, but should be no big issue. The only problem is the human factor here. We'd need the user to log in to perform the re-encryption and would not be able to do this automatically.

Are there any serious flaws about this, especially concerning the data security? Is this a good idea anyway?

Answer 1

One of her key requirements is, that the data stored will be encrypted in a way that only the user will be able to access it.

The only way to truly guarantee this is to have the encryption key reside on the client, under the control of the user. This introduces (at least) two problems -

• Having a thick enough client to support key management and decryption
• Users will lose their keys and, correspondingly, their data

The first tends to rule out browser-based applications (although I'm sure someone, somewhere, has been smart enough to find a way to do it). The second is an unavoidable implication of the requirement, and may well be acceptable to users (a user with sufficiently strong secrecy requirements may prefer data be lost than compromised).

The protections you've devised - using the user password to protect the key - only protect the user from you if you're honest. If you're not honest, you could capture their password when they unlock the key, or capture the unlocked key when they're using it. Or maybe you're honest, but there's a government agent standing behind you with a warrant and/or a gun. Either way, if you really mean "encrypted in a way that only the user will be able to access it," then the user has to be the key custodian.

(Now - in fact - if you supply the client, then they have to trust that you didn't backdoor it to capture their key, their data, etc. etc. But theoretically, since they have the client in hand, they can analyze it and attempt to detect such misbehavior on your part - just like anti-virus vendors and forensic incident responders analyze code to see what it's doing under the hood. This improves their assurance over what it would be if the key is stored/used server side. Realistically few people have the skills to do so, and even fewer have the motivation, but the fact that it is possible results in the perception that the client can be trusted more than the server in this scenario)

Answer 2

If you are going to go down that route a key derivation function (KDF) from the users password might be the way to go. That way the encryption keys are generated from the user's input so you would not have to store any of the keys. However this also runs into the issue that if the user cannot remember his or her password the data will be lost and KDF's can have implementation issues as well.

more on KDF's

That being said why go this route? Most major databases have the ability to encrypt at rest as well as encrypt their backups. If you are careful to implement proper security in your website, network and database your end users won't have to worry about losing their data or you losing their data to hackers.