So lets say you want to outsource your database to a third party and be able to query on it on their server

but you want to "encode" the salary column of the users on their server, aka hide the real value from that third party, in a way that you can still query it on that and decode the result

for example one approach is making salaries between 100-200 map to 20 and 200-300 map to 10

the problem with this approach is that when i want to get someone with the salary of 230, the where clause changes to where salary=10, and this might map to many people and the result wont be correct, or where salary>120 turns into where salary=20 or salary=10

so basically i have to have a one to one mapping function that is still quaryable on the server and be able to decode the result

so how will you change the salary values in a way that i can still put a meaningful thing in the where clause that corresponds to that, and be able to decode the result?

obviously just adding something to it is not good, because this way they can still know who has the biggest paycheck, you have to change it in a way that they cant get any info from it, or just adding the numbers together wont work since there might be collusion

  • How secure do you want that to be? Just enough to stop "script kiddies" / "wannabes", or some provably cryptographically secure guarantees? Related: stackoverflow.com/q/25558793 – rwong Dec 31 '18 at 14:06
  • @rwong just enough to script kiddies, nothing serious – OneAndOnly Dec 31 '18 at 14:20
  • As usual, implementing your own cryptographic idea could be error-prone and suffer from a false sense of security. The SQL engine of your choice may have an overview page which discusses various privacy-preserving and encryption choices as well as their trade-offs. Have you considered using whatever is offered by the SQL engine, instead of rolling out your own? – rwong Dec 31 '18 at 14:20
  • 1
    PGP encrypt the data and keep the key on your pc, not the server – Ewan Jan 1 '19 at 20:10

Forgive me an oblique approach at an answer to this question from a software design perspective.

The third party's access to your data is controlled in a number of ways

  • Databases implement access control via users and passwords.
  • Servers also have access control via username/password
  • When you connect to your database or server you can implement transport level encryption to prevent the data or passwords being snooped
  • Servers can implement whole disk encryption or databases can encrypt the entire database to prevent users directly reading the data off the hard disc bypassing the OS.

So overall there should be no need to encrypt a particular bit of data to prevent casual leakage.

However, there are some security scenarios you need to consider

  • The server or disk drive falls into the hands of someone willing to spend the time and processor power to crack the whole disc/database encryption.

The physical security of your server is something you simply have to trust the third party with. Presumably you have a contract and insurance etc to deal with things like theft, disposal of drives etc.

If your data is so sensitive that it can't be entrusted to the third party at all, then your only option is never to send it. Store your salary data somewhere else with the UserId from the main database.

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.