Row level database encryption scheme

Question

I am developing a row level database encryption scheme. Ideally I would not do this and instead rely on something vetted, but I haven't been able to find such a scheme online.

My primary concerns are:

• A database dump leaking.
• Somebody unauthorized gets access to the database server.
• Authorized personell with access to the database server being able to read confidential data without first going through my web application which performs audit logging, etc.

Algorithm wise I am currently using AES-256-GCM for encryption and a SHA-512 HKDF to derive keys for each row using a single 'master key'. The master key is 32 bytes generated by a CSPRNG.

To facilitate key rotation without downtime, I store a master key version identifier with the encrypted data. On rotation, a new key and its version are added to the application configuration. Encryption operations then use the new key, while decryption remains possible for rows encrypted with the old key. Rows are re-encrypted in the background, allowing for the retirement of the old key once finished.

Here's an example table layout in the database using my scheme:

Id	EncryptedData
1	v5;<base64 encoded encrypted JSON>
2	v4;<base64 encoded encrypted JSON>

(v4 and v5 here are the master key versions)

To derive a row key I do the following:

RowKey = HKDF(hash: SHA512, ikm: MasterKey, outputLen: AES_256_GCM_KEY_LEN, salt: SHA512(RowId), info: TableName)

The resulting row key is then used to encrypt data as follows:

Ciphertext, Tag = AES-256-GCM(RowKey, Nonce, Plaintext)

The nonce used above is 12 bytes generated by a CSPRNG. I generate a new nonce for every encryption operation.

For final serialization into the EncryptedData column I simply Base64 encode the concatenated ciphertext, nonce, and tag. I then prepend the master key version identifier and a semicolon.

Questions:

• Are there any glaring security flaws in this approach?
• Is there any prebuilt scheme I can use instead rolling my own?
• Am I using the HKDF correctly? I'm not sure if the salt & info parameters are being correctly used.
  • Is there any benefit to hashing the row id before using it as the salt?
• Is SHA-512 overkill or would I be fine using SHA-256?

Thanks for reading, I greatly appreciate any insights and suggestions!

Answer 1

Are there any glaring security flaws in this approach?

There is no complete security architecture shared, and the security of these an entire schemes is considered off topic on this site. That said, the idea that random keys are used, that a known good KDF is used and that an AEAD cipher is used for encryption shows that in principle the algorithms are at least OK.

Obviously the master key is stored in software if a CSPRNG is used. The use of an explicit key generator which may allow for storage of the master key in a HSM or similar could be considered. Currently the version number is obviously the version number of the master key. That might mean that there is no method of updating the protocol.

Is there any prebuilt scheme I can use instead rolling my own?

Possibly, but in my limited experience any scheme for encrypting values in databases is severely lacking, mainly in competence of those creating the schemes.

Am I using the HKDF correctly? I'm not sure if the salt & info parameters are being correctly used.

I'd use TableName - RowID or something similar as information and leave the salt optional in case you want to add a random salt later. And that might be closer than you thing because ...

A RowID seems static within a database, however I would be very cautious:

Once a row is deleted, a new row will re-use the rowid identifier. Likewise, deleting and re-creating a row can result in the row getting a new ROWID as will a dump and load.

This means that the key may be reused for the same row. An adversary may store previously generated rows. At this point the row may only be protect the plaintext by the random IV, rather than the uniqueness of the key.

Is there any benefit to hashing the row id before using it as the salt?

No, HKDF and other schemes will already "hash" the salt and info so there is no need to hash separately.

Is SHA-512 overkill or would I be fine using SHA-256?

Probably, but SHA-512 is fine for this purpose. As SHA-512 isn't used on a large input message for HKDF nor the hash over the salt it really doesn't matter that much. Then again, SHA-256 would work equally well and is not likely to have any impact on the security of this scheme, as the hash algorithm doesn't seem to be used for collision resistance in this scheme.

---

Disclaimer: This answer tries to indicate some points with regards to the cryptographic security of the scheme. This should not be considered a full system review nor as an endorsement of the scheme in any way.