I am developing an application for Desktop PCs (Mac primarily, Windows later) that uses an encrypted SQLite database. I want to give multiple authenticated users access to this "main" database. I like to make this as secure as possible.

My constraints are:

  • Everything happens on a single computer. No server involved (I plan to change that, as well as introduce the use of SmartCards, but that's quite the challenge on OS X).
  • I roll my own user authentication (I don't want to use something complex like Kerberos or whatever else is out there as I don't have the time to dig into and manage that all as well. I'm a single fighter and need this all as simple as possible).
  • I understand the risk that a hacker will be able to infiltrate the system and then grab the encryption key as soon as a user logs in. I also understand the risk that a running computer may still have the encryption key lingering in memory, so a system memory dump may be a viable attack. I have to live with that for now.
  • My main goal is to protect the database against someone simply breaking in and stealing the computer, or copying files off it, then trying to gain access to the database.

Currently, I envision this to work as follows:

  • The DB encryption key is stored with another ("secondary") password using symmetric encryption (not sure what to use here, I've used Blowfish before, is that good?). That lets me change the DB encryption key centrally if needed.

  • That secondary password is stored in a separate "users" database, protected by the user's password, e.g. using a cypher or whatever is appropriate (I need help here).

    So, when a user logs in, I simply look up the value from the users database, decrypt it using the entered password, and then use that to decrypt the DB key, and use that DB key to open the main SQLite database. If that opens the database, the user's entered password was valid.

Is that reasonable and sound, or am I missing something? Can it be improved to make it more secure or is that the best I can do under the mentioned constraints?

  • $\begingroup$ There is no mention of an actual encryption primitive in the question (neither SHA512 nor PBKDF2 are), when there appears to be DB encryption, and encryption of the secondary password. There is no mention of authenticated encryption, despite the tag authenticated-encryption. And there is no mention on how it is checked that the user password and secondary password are correct (which is critical to security against password search). That makes a review difficult, to say the least. And I'm uncertain that this is topical. $\endgroup$
    – fgrieu
    Aug 29, 2016 at 17:16
  • $\begingroup$ I only added the first tag, some editor added the others (he also changed the title, which I didn't like but left changed - I've reverted it now. Better?). About the encryption - I simply overlooked that part, I am still in design phase. I don't even know what to use for that. Blowfish? Does it matter? I am open to suggestions. And checking - yes, I thought I explained that: I check if the decrypted password / key is accepted by the encrypted SQLite database - if it opens, the user's entry was valid. $\endgroup$ Aug 29, 2016 at 20:14
  • $\begingroup$ @fgrieu That confusion about the “authenticated encryption” tag (and related title change while transforming the title into a – what the help center calls – “real question”) is completely my fault. I partly misinterpreted the description when the Q was first posted and bluntly derailed towards authenticated encryption… which was in fact not mentioned at all. My derailment meanwhile became more than obvious after Thomas clarified some things and corrected my related mistakes. (BTW: thanks for reverting my faulty edits Thomas.) TL;DR: Sorry for any related inconveniences I might have caused. $\endgroup$
    – e-sushi
    Aug 31, 2016 at 16:39

1 Answer 1


It is a no-no to use SHA-512 alone as entropy-stretching key derivation function protecting a password: that would leave dangerously vulnerable to brute force password search, even if salt is mixed. Other than that, the principle outlined in the question is sound, though slightly more complex than needed; also, I'd replace PBKDF2 by a better substitute like bcrypt, scrypt, or Argon2.

Sharing the main database key among many users each having a login/password can be achieved with a single level of a PDKDF2 (or better) rather than two, and nothing more complex than comparison and XOR (the later being used as the currently unspecified encryption method for the main database key).

Recall that an entropy-stretching key derivation function like PBKDF2 produces a bytestring of desired size, from

  • key (the password)
  • salt (typically at least concatenation of an application-specific constant and user id)
  • desired output size (not available for stock bcrypt, see note)
  • difficulty parameter(s), including at least an iteration count (set as high as tolerable), and for modern entropy-stretching key derivation functions (scrypt and later) maximum CPU count, and size of memory to use.

We can make the output size parameter say 48 bytes: 16 bytes for password verification purpose, and 32 bytes for the main database key. What's stored in the password database for each user includes its user id (the search entry, as plaintext), and the 48-byte output of the password entropy stretching key derivation function, with the 32-byte portion combined using XOR with the 32-byte database key. The user's record can additionally contain security parameters currently used for the entry, extra random salt, and other details like when the password was last changed.

On login we fetch the (alleged) user's record from the login/password database, compute the entropy-stretching key derivation function with the given password, and combine the result with the above 48-byte user record using XOR. We allow login only if the 16-byte subfield is all-zero, and then only use the rest as the database key, without need for further verification.

Finding the database key is as hard as finding any user's password (the stated security goal). Changing a user's password only involves changing the corresponding entry in the login/password database.

This leaves unspecified the cryptography used to protect the database by way of its key; that one is a complex issue.

Note per comment: bcrypt has fixed output size 24 bytes. It would be tolerable to halve the fields used for password check and data base key; or we could change bcrypt's OrpheanBeholderScryDoubt string constant to something twice as long, taking care that no 8 byte segment repeats (but then that's no longer bcrypt).

  • $\begingroup$ I understand the idea with the XOR. But that would mean that if I changed the DB key, I'd have to update the values for every user in the user's database, right? With "That lets me change the DB encryption key centrally if needed." I meant that I wanted to avoid exactly that. Maybe you misunderstood me there? $\endgroup$ Aug 29, 2016 at 22:49
  • $\begingroup$ @Thomas Tempelmann: yes, with the solution that I proposed, changing the main DB key requires update of all user DB records (but since you seem to have to update the whole content of the main DB anyway, is not that comparatively negligible overhead?). Worse, old key and user DB before key change allows access to data entered in the main DB after the key change. But is your solution immune from that, and allowing main DB key change without knowing all user passwords (as I allow)? Note: you did not tell how PBKDF2 becomes password acceptance/rejection and key protecting the DB or next stage. $\endgroup$
    – fgrieu
    Aug 30, 2016 at 6:29
  • $\begingroup$ I still seem to be confusing KDF with symmetric encryption. Though, for short derived key lengths, they're effectively the same, aren't they? Anyway, bcrypt doesn't seem to have a "desired output size" parameter, its usable output it always 184 bit, correct? Also, it seems you misunderstand what I meant by "encrypted SQLite database". I'm using SEE (hwaci.com/sw/sqlite/see.html), which encrypts the entire DB file with one key. So, when I open the DB, I provide that key, and if that fails I can tell that the key is incorrect. $\endgroup$ Aug 30, 2016 at 10:48
  • $\begingroup$ PBKDF2 and similar do not provide encryption functionality: no decryption is possible. See updated answer for your remark on bcrypt. I do not know SEE; but on the face of it, the AES-128 CCM option seems reasonable for the main database, keyed the way I suggest. Except if you had one small encrypted sqlite database per user, I fail to see how the question suggests any plan to transform the output of the KDF processing the user password into the key for the main database by way of the encryption built into SEE (which at first glance seems to support a single key per database). $\endgroup$
    – fgrieu
    Aug 30, 2016 at 12:36
  • $\begingroup$ What makes you think I'd have one encrypted DB per user? I have one shared encrypted DB for a set of authorized users. Wasn't that clear from the intro of my question? Or do I misunderstand you? And thanks for clarifying the difference between derived keys and symmetric encryption. I get it now. $\endgroup$ Aug 30, 2016 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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