3

I have a password conundrum. I need to store an encrypted password in a DB table but in such a manner that would allow me to retrieve the password as plain text afterwards.

Please note that this is a business-driven decision that I have been tasked to find a technical solution for. Could anyone please provide suggestions how this could be accomplished?

I came across these questions which don't seem to provide the solution to my issue.

Storing a user's password in a retrievable manner
Secure storage retrieval of data in a database

7

As long as you are working with only online needs for the password for integrating with a system while the user is logged in, then you should store the passwords encrypted with both a database key as well as a derived key based on the user's password for your service. When the user submits the password for their login, you can use that to produce a password derived key and use that PDK to decrypt the value from the DB.

You encrypt with the DB key itself to ensure strong encryption against an offline attack where only the DB data is lost. You utilize the (much weaker) PDK to prevent trivial compromise in the event that the DB and the DB key are both lost.

The key in designing a system like this is to try to put the pieces needed to get to the password in as many distinct places as possible. If you have a piece on the DB server (db key), a piece on the web server (db connection credentials), a piece in the DB data and a piece that is only in the user's possession, then an attacker must compromise far more areas to make the system fail.

  • What do you mean by DB key? – neaumusic May 10 at 4:23
  • It's otherwise known as a data encryption key or DEK. You'd need some mechanism to protect it, ideally something like a hardware security module, but somewhere that won't be compromised with the database hopefully. The pdk pass provides extra protection if both dek and data are lost, but is only as secure as the password. – AJ Henderson May 10 at 4:47
2

If you need to retrieve the plaintext password at some point, then you indeed need encryption and not hashing (beware that many people call "encryption" what really is hashing). I suppose that your "business need" comes from the need to support some protocol where the server must know the plaintext password (e.g. the APOP authentication method in the POP protocol).

The crucial point with encryption is that it is done with a key, which is a very sensitive piece of data, since knowing the key allows decrypting the passwords. Your server will need to know the key when a password must be stored (upon user registration and password change) and when the password must be retrieved (following your business needs). If you store the key in the database itself, as is, then you have gained nothing with encryption: you encrypt the password because you envision the case of an attacker who gets read access to the database contents (e.g. with some SQL injection attack) but not to the whole server.

Your DB might be able to do the encryption for you, without storing the key in an emplacement that could be revealed through SQL injection. You may find some guidance for SQL Server there.

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.