Somebody asked whether it’s a good idea to encrypt database columns in a web application. My answer: “It probably won’t help much.” Why?
Figure out your threat model
Best practice: figure out your threat model before you spend time and money securing your system. If you build complex security measures without a clear idea of your threat model, you’ll trick yourself into a false sense of safety.
And, encrypting columns in a DBMS is a complex security measure.
What is your threat model? What attacks will you get? By whom? How will attacks damage you?
Your most likely outsider attack comes from cybercriminals breaking into your web servers to steal your users’ information to commit identity theft (Equifax), blackmail (Ashley Madison), or espionage (US Government human resources database).
If you encrypt some columns in your DBMS and your web users need to be able to view and update those columns, your web servers will have to know the encryption and decryption keys. If a criminal pwns your web server, he will have your keys. Therefore he will have access to the encrypted columns of your dbms. And, he’ll have a big signpost saying LOOK! Here’s the secret stuff!
There are plenty of other imaginable outsider attacks, of course. Somebody could break through your firewall and hit your database directly. Somebody could get into a cache and grab cached sensitive data. Somebody could guess your web app’s administrator password. Or, steal a bulk upload file.
Your proposed design imagines an insider attack. People who already have DBMS access credentials must be prevented from seeing certain columns in certain tables. What will they do with that information? You didn’t say. What’s the threat?
Stopping short of encryption, you can do these things to keep your insiders from violating your users’ confidentiality.
- Get the sensitive data out of your system entirely. For example, if you’re handling credit cards, work with stripe.com or braintree.com. They’ll hold your secrets for you, and they have excellent cybersecurity teams.
- Sort out whether you can trust your insiders. Investigate prospective employees, etc.
- Establish clear security policies. For example, “We never look at the
credit_cardtable unless we have a specific need to do so.” If you’re handling health care data in the US, you already have HIPAA guidelines. Get your insiders to understand and agree to your guidelines.
- Sack insiders who violate these policies intentionally.
- Build mechanisms to help enforce policies. Issue each insider his or her own username/password pair to access the DBMS. Use selective GRANT operations at the table and column level to allow and disallow viewing of data. For example,
GRANT SELECT (name, address) ON person TO username@'%';
username see the
address columns, but not the
taxpayer_id column in the
person table. Read this. https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-column-privileges
Wise security measures
Spend your time and money on good firewalls protecting your DBMS machines. Study up on OWASP and follow those practices. Spend time and money running penetration tests of your web app and fixing the problems. Spend them on vetting and training your insiders. These things slow down attackers more effectively than the imagined magic bullet of encrypted columns.
There’s the old joke about the two guys and the bear.
Adam: Uh oh, I don’t know if we can run faster than this bear.
Bill: I just have to run faster than you.
That’s a good way to handle security for your small web site. Make it hard enough to crack that the bad guys will attack somebody else. If you’re running a big web site with a large number of sensitive records (I’m looking at you, Equifax) this isn’t good enough.