I have a use case where I need to encrypt a piece of PII information in a database, which then can be decrypted and accessed by multiple user roles in an application (e.g the user it belongs to, customer service, engineering, etc.. ).

What is an industry standard strategy for these sort of things?

UPDATE: PLEASE READ BEFORE POSTING: In addition to posting feedback for my idea below(which I really appreciate) please also propose a strategy. I believe it would be very helpful to anyone trying to encrypt any shared resource nowadays.

Here is what I have in mind at the moment which will hopefully describe what I refer to better:

Full Disclosure: The information below is also meant to get an opinion or a peer review on my current strategy I came up with

As part of a defense-in-layers strategy my criteria is to have at least 3 factors to the encryption/decryption strategy that way if any 2 pieces are compromised the data can't still be decrypted.

The 3 factors: The Software accessing the information, A DB with encrypted data(DB1), A DB with the encryption keys(DB2).

The Encryption Strategy:

  1. Data gets inputted into the software
  2. A, let's say, 128bit Key is generated
  3. Data gets encrypted with that key
  4. Encrypted Data is stored into the DB (presented as DB1 in the 3 factors above)
  5. The 128b key gets encrypted with a Master Key hardcoded in the software
  6. Encrypted 128b key gets stored into the other DB (presented as DB2 in the 3 factors above)

The Decryption Strategy is the reverse..

The point is that the software holds the master key(Factor 1), DB1 has the encrypted data(Factor 2) and DB2 holds the encrypted encryption keys(yay for word repetition..also Factor 3) which can only be decrypted with the master key.

In my head if any of these get exposed, it's useless information that doesn't reveal the encrypted PII.

Btw this assumes solid encryption algorithms such as AES (..most likely AES only given that DES, 3DES and Blowfish are deprecated).

Update i realize i didn't make myself clear on the point of where these 3 factors will rezide. All 3 will be in different hosting environments on 3 different servers We're talking about a large scale strategy(which could work small scale as well) where DBs are inherently separate hardware entities. In my mind that was the assumption to begin with.

Update 2 A lot of the feedback here rightfully suggests that the software is the achile's heel here. This is my big canandrum too. I can't think of any software strategy where if the software is compromised the data is not at risk, given that the software always has an open connection into the DB and contains all the business logic to view/handle that data. Even implementing mydiamo in the software, that still means the data gets compromised if someone takes control of the software.


6 Answers 6


Someone who breaks into your server will make off with the software and the databases. Having all of your factors, they will then be able to run the decryption strategy in reverse to recover all the PII.

In multi-factor authentication, additional factors don't count if they would be stolen at the same time as an existing factor. For example, a hardware token is a different factor from a password, because a key logger (the typical way to steal the password), being software, wouldn't be able to steal the hardware token at the same time.

  • Maybe i didn't make myself clear, every factor is hosted in a different env/server ...not all on the same server. We're talking about a large scale infrastructure design here
    – dwkd
    Dec 16, 2016 at 4:40
  • Well, if we're talking about large scale infrastructure, I'm sure it will work. Dec 16, 2016 at 4:45
  • 1
    Ok i hope that is not sarcasm and i really appreciate the input and pointing that out. Any suggestions on a different approach? Many thanks
    – dwkd
    Dec 16, 2016 at 4:48
  • Your answer intrigues me everytime i read it..and i read it 10 times now.. I wish you'd have provided a way to design the software side that would prevent what you're saying. I can't think of any software strategy where if the software is compromised the data is not at risk, given that the software always has an open connection into the DB..this is my big canandrum. Even implementing mydiamo in the softwarr, that still means that it gets compromised if someone takes control of the software. I literally don't see any difference
    – dwkd
    Dec 24, 2016 at 21:43

If I have correctly understood your question, you are trying to harden the data against an attacker that could take a partial control on your datacenter. Do not forget that once one part of a datacenter is compromised the full zone administratively accessible from there is assumed to be compromised. That means that it is not enough to segregate the information on 3 different servers, but that 3 servers should be in 3 different zones with strict firewalling between them to ensure that only the required information should be able to be exchanged. Or at least, the secured database and the normal database should not be in the same zone.

In particular, the application should only be able to get the encryption/decryption key through a web service and by presenting the credentials proving that it does that on behalf of an authorized user. That means that you will have to set up a strong authentication and authorization system and thoroughly examine all the security implications. And you should also set up code audits to control that the application does not keep that key longer than required

What I mean is that it is necessary to use a strong encryption system, but it is only the visible part of what should be done to actually increase security. The essential part will not be only technical but will involve organization and a precise definition of what priviledges are granted to whom and what server is allowed to communicate with what other one. You should also consider how backups are done because they are an essential part in a secure system because they must be robust but not too easily accessible. IMHO you should also anlyze the value of the data and the risks that it is exposed to. Because setting up a secure system will not come without a cost.

TL/DR: there are no evident flaws in what you describe, but what is lacking is the definition of the segregation between the different parts, because the devil will hide in such details.

  • This situation i'm describing is very common in the medical field where, for example you want to encrypt someone's medical record but at the same time you need to give access to that record to other folks such as consulting physicians, nurses etc
    – dwkd
    Dec 20, 2016 at 17:54

I know you have mentioned that you have the two databases segregated, however they obviously communicate somehow, so I would consider them both at risk if your network is compromised, which means they could be considered as just 1 factor.

Having a physical token separate is still a good idea, obviously, but my message is that using 2 databases may not be much better than using 1.

  • Thanks for the feedback. I am also looking for examples on actual strategies as I've been unable to find any 'industry standard' on this
    – dwkd
    Dec 20, 2016 at 16:06

Maybe one way to improve the security is not hardcode the 'master key' in the software, rather fetch in run time from yet another server. At least that way the attacker can't just walk away with software and DBs, but either has to create a memory dump to get the master key or even attack the key server.


If what you are doing is a large scale project as you mentioned, my recommendation is to go with commercial solutions that address just that.

You know the usual advice: don't roll your own when it comes to cryptography/encryption. There are many ways that your strategy can go wrong. Better to check out a tested solution and there are some that I think would fit exactly your requirements.

Simply put, you want a solution that is going to have column level encryption, providing different encryption/decryption keys for each column, and giving you an ability to define access control policies per column.

The solution should also help you store and manage your keys in a separate server (preferably HSM) and ensure a secure transmission of the keys between the servers.

As a disclosure, I have worked as a DB encryption consultant for quite some time, but again this puts me in a position to give you an "I-ve-been-there-done-that" advice.

I have consulted for many clients who have very tight security and performance requirements. I'm not sure which DBMS you are using, but if you are using open source DB, you may find mydiamo to have all you're looking for. If you are using commercial DBMS, this company or this have pretty well tested solutions.

  • I've just had a look on the linked sites. I'm pretty sure that they all use excellent (and easy to use) technical solution. But no address the organizational question while it is essential in security because it is far beyond a turnkey solution... Dec 21, 2016 at 7:14
  • ... If all is needed is compliance with a standard, or a protection in case of problem (I used a serious solution), just keep on. If you really need to protect data, you must address the organizational question, be you using a commercial solution or not. Dec 21, 2016 at 7:20
  • mydiamo looks great and suitable for my needs no doubt. Leaving the organizational part for a different topic, i was hoping in the spirit of stackoverflow to have an answer with an actual strategy that anyone can reference to in the future.
    – dwkd
    Dec 22, 2016 at 18:08


A rather common practice in such contexts is to have the "master key" of your scenario to reside in a hardware security module, so it never leaves it and it can decrypt the data-specific keys when required.

As described currently, your software seems to be a single point of failure - an exploit in your application would have access to the master key and also likely read access to both databases. If you truly want a 'defense in layers', then you would need to ensure that the system that has access to all data is unable to decrypt it, and the system that is able to decrypt data cannot access all the data.

  • are HSMs commonly found and can be requested in the cloud world? i'm just wondering in case one does not have access to the data center
    – dwkd
    Dec 22, 2016 at 18:09
  • @dwkd I don't believe that a HSM would be feasible in the cloud world, since their whole reason of existence pretty much relies of having exclusive control over a secure piece of hardware. That security is achieved in part by a design that makes certain sensitive operations impossible to be done remotely, they have be done on site by physically inserting certain key components (kept off-site by separate people, etc etc) - having some cloud system that implements the exact same API wouldn't provide you with a comparable risk reduction; it may perform part of those functions but it's different.
    – Peteris
    Dec 23, 2016 at 1:52

You must log in to answer this question.

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