I have a database that I want to implement an encryption per x amount of users.

Say I have 1000 Users in the database and I wanted every 1000 encrypted differently then I would generate a new key and use it for users 1001 - 2000 that registered. this would then be repeated for every 1000. What I can't figure out is the best way to do this and I am trying to do this with hopefully 0 changes to the database as to give as little away should my database get compromised.

  • Are you encrypting passwords? Because those should be cryptographically hashed and salted—not encrypted. Oct 8, 2019 at 22:14
  • I meant to day that no this would not be it's only for unique personally identifiable information such as names, email etc...
    – Max
    Oct 8, 2019 at 22:24
  • Who will need to access the data?
    – Ángel
    Oct 9, 2019 at 1:14

3 Answers 3


To do this, you're gonna need to do some learning, I'm afraid. Googling "how to do field encryption in a database?" will probably get you started.

In essence, you're gonna need to do the encryption (probably AES-GCM-256 encryption) in your application layer. Data comes in, application uses the AES key to encrypt it, and stick the encrypted blob in the database. When you retrieve a field from the database, the application uses the AES key to decrypt it and then use it. Once you have that infrastructure in place, using a different AES key based on the ID number of the record should be easy.

The problem you're going to run into is how you securely store the AES keys. You can't put them in the database, because storing the decryption key next to the encrypted data is a big no-no. So do you just put the key in a flat file on disk of the application server? Do you use some obfuscation trick to obscure the key on disk? Do you buy a server with a good TPM on the motherboard, and write vendor-specific code to store your keys on the TPM? Do you buy a full HSM?

These are tricky issues that don't have a good universal solution. If you're going to design and build such a system, you should become familiar with the tricky parts, and design a solution that fits what you have available.

EDIT addressing comments:


In comments you say that you have access to an HSM for storing the encryption keys. In that case, I'm imagining that the data flow is something like this:


App ---data---> HSM ---ciphertext---> App ---ciphertext---> DB


DB ---ciphertext---> App ---ciphertext---> HSM ---data---> App

In that case, the chances of the decryption key becoming compromised are very very small and realistically you probably have bigger security issues to worry about elsewhere in your network than creating a new key for every 1,000 users.

That said, if you want to do it, then you'll hit the problem of running out of key slots in the HSM. One good trick for this is "key wrapping"; you have one master symmetric key in the HSM, which you use to encrypt the keys you actually use. The flow would look something like this:


# fetch key_i from the DB and get the HSM to decrypt it.
DB ---ciphertext(key_i)--> App ---ciphertext(key_i)--> HSM ---key_i---> App

# encrypt the data and store it in the DB
App: encrypt(data, key_i) ---ciphertext---> DB


# fetch key_i from the DB and get the HSM to decrypt it
DB ---ciphertext(key_i)--> App ---ciphertext(key_i)--> HSM ---key_i---> App

# fetch the encrypted data from the DB
DB ---ciphertext---> App: decrypt(ciphertext, key_i)
  • I hope you do excuse my first question on this and the lack of information I have given in this, I do have a HSM system in place in order to hold the keys which would decrypt the data, I however wanted to make this operation unique to as many people as possible to limit and data that can be exposed with 1 key
    – Max
    Oct 9, 2019 at 9:47
  • If the key is in an HSM, then the risk of the key becoming compromised is very very small. I'll add another paragraph to my answer though. Oct 9, 2019 at 14:09
  • 1
    Thanks this gives me a lot more guidance :)
    – Max
    Oct 9, 2019 at 17:13

Generally, for something like this you're doing one of two things:

  • Encrypting with key(s) you control. In this case, there's little benefit in splitting up the keys this way; if one of them gets compromised, the odds are good that the others will too. If the keys are stored separately and only combined at runtime you do get some degree of additional security (against having a backup stolen, or similar), but it's a lot of complication for a relatively minor improvements. If you're just worried about the DB being directly breached, this approach is all you need so long as you store the keys separately from the DB (ideally in a hardware security module or similar). However, this approach provides essentially no protection if your user-facing server (typically a web server) is compromised, as the server needs to be able to use the key and can therefore presumably decrypt all data in the database.
  • Encrypting with per-user keys. Necessary for end-to-end encryption but sometimes used without it, this approach assumes the key is, in some way, supplied by the user themselves. Typically this is either a key file, or a key derived from a password. In either case, the server does not store the key anywhere. However, an approach like this needs a unique key for every user. With this approach, an attacker would be able to decrypt their own data, but would have no way to access the data of any other customers even with unrestricted access to the DB (or, in the case of end-to-end, even with unrestricted control over the user-facing server). However, this approach is often less convenient for users; among other things, if the user loses all access to their keys, they also permanently lose access to their data.

More detailed advice on how to set up your encryption requires knowing a lot more about what you're trying to do, what your threat model is, and how many resources you're willing to put into this, and is probably not suitable for a StackExchange question, though individual elements might be.

  • I have a HSM key storage system in place where the keys are stored, however I wanted to try and do this with 0 changes to the database, however it's becoming clearer that this isn't possible
    – Max
    Oct 9, 2019 at 9:49

From an architecture perspective, I would recommend against field level encryption. Eventually the data has to be decrypted. Field level encryption moves the security risk away from your database and towards your application. However, your application is likely to have more complexity than your database. Your solution increases application complexity and (in my opinion) significantly increases risk.

Following and correctly implementing security best practices on your database should mitigate any need for field level encryption. Specifically, at-rest encryption of database and backups and proper authentication security. This offloads (spreads) the complexity and risk away from the application. I would argue that complexity is an exponential issue. Meaning the more complexity you have, the exponential more risk you have, and the less complexity you have, the exponential less risk you have.

If a bad actor has privileged access to your application server, whether or not you have field level encryption, he or she should be able to manipulate the application to expose that information. On the other hand, if he or she gains privileged access to database server, he only needs to find the encryption scheme and related keys to access the fields.

The consideration to make is: If a bad actor has privileged access to your database server, you either didn't follow good security practices or he exploited your system. In either case, how can you prove your field-level-encryption solution (or application server) is well implemented and not exploitable? I would argue that well implemented database security precludes the need for your field level security.

Finally, I have seen databases that have field level encryption. In particular a credit card field. This prevents reports from displaying the CC number and users with read access to the database from accessing the numbers. I would argue that this is a failure to architect the database well. Specifically an authentication/access-control issue.

  • This is the eternal debate of transparent encryption vs field encryption, isn't it? It all comes down to the security and threat model of your system. Certainly field encryption is easier to show to an auditor, especially if dual-control is required: "These fields are encrypted with that key stored in that HSM that only these people have the PIN for". Doing HSM-backed field encryption also means that the attack needs to happen live in your network, vs extracting a copy of the DB and being able to work on it offline. In some contexts, that's an important difference. Oct 9, 2019 at 22:21
  • @MikeOunsworth, Can you elaborate on why HSM-backed field encryption requires the attack to happen live? I'm not sure how either option (transparent vs field) has any difference. "...only these people have the PIN for the database" versus "only these people are authenticated to the database" is the same for an auditor. I've honestly never heard of that debate. I was answering from a design/architectural standpoint where SQL Server security is easier to reason about versus a custom solution. Oct 10, 2019 at 17:32
  • You can extract data from a database, onto your laptop, and go work on it at home. That's what databases are designed to do. The app that's accessing the db has the credentials needed to do this. You can not extract a private key from an HSM. So with an HSM, the attack needs you to read from the DB, then pass that data to the HSM, then exfiltrate it back to your laptop. More complexity to the attack, more suspicious network traffic to maybe be noticed, etc. You can't pull off this attack by, for example, getting a copy of the DB VM's disk. It needs to be a live attack inside the network. Oct 10, 2019 at 17:42
  • @MikeOunsworth, This is a false equivalency. Accessing a decrypted database is not the same as accessing an encrypted field. However, accessing an encrypted database is logically the same as accessing an encrypted field—that's the basis of my argument. In either case we need authorization to access the data. The only difference you are claiming is using an HSM to store the private-key for the field. Why not use an HSM key to store the entire database? My argument remains, it's easier to reason about built-in database security (with HSM) versus a custom solution with HSM. Oct 10, 2019 at 18:13
  • 1
    @MikeOunsworth, the only comment I have is the "...does not add value to a server". Occasionally we see starts-ups burn through their cash, not find a buyer and collapse. When this happens, generally, the servers end up at auction or on eBay or similar -- there are people who buy those servers for the hard-drives, not for the server, but for the data they contain. in this case, using an HSM with TDE turned on in the database will save you from those ... well 'people' I suppose ... reselling the data on the dark web.
    – rip...
    Oct 12, 2019 at 19:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .