sql website, encrypt each row with different key.
Hi I have been asked to build a simple site. Problem is the want to hold patient data on that site. Each user will be able to add details about thier patient.
The database is encrypted and has always encrypted on colums. To be extra safe I would like to create a key for each user then use this to encrypt their rows with that key. So if ever there was an attach on the site each users info is encrypted different.
Please advise best way to do this.
The database is encrypted and has always encrypted on colums. To be extra safe I would like to create a key for each user then use this to encrypt their rows with that key. So if ever there was an attach on the site each users info is encrypted different.
Please advise best way to do this.
gr8gonzo
Just use a hash of the patient's name or ID as part of your encryption key. Just make sure it's something that won't change much or at all. Otherwise if that changes then you need to decrypt with the old key and re-encrypt with the new key.
ASKER
So you would do it in code on the web server? the only thing that worries me with this is then if they want to search on a cloumn you would have to decrypt every row into a temp datatable then search. Is there a way to do it sql side?
That's correct. It's painful, but that's what you were asking for, so...
If you do it all within SQL, then if the DB is compromised then the keys/mechanisms are all compromised, too.
That said, I would usually not encrypt the individual rows. I would just employ encryption at rest on the storage level, and if needed, additionally encrypt anything that didn't need to be searchable.
If you do it all within SQL, then if the DB is compromised then the keys/mechanisms are all compromised, too.
That said, I would usually not encrypt the individual rows. I would just employ encryption at rest on the storage level, and if needed, additionally encrypt anything that didn't need to be searchable.
You can encrypt the data with pgp and keep the searchable columns in plain text.
That is only actually useful if you do not have the keys. And still breakable given enough time.
Truthfully, i believe you are overthinking this. Even always encrypt is most likely plain useless. As long as the web server does not know the data is encrypted, it makes no difference to a remote attacker.
Adding encryption all over the place is not how you achieve better security. That is rather how you loose focus of the actual issues. Start with stating your requirements.
That is only actually useful if you do not have the keys. And still breakable given enough time.
Truthfully, i believe you are overthinking this. Even always encrypt is most likely plain useless. As long as the web server does not know the data is encrypted, it makes no difference to a remote attacker.
Adding encryption all over the place is not how you achieve better security. That is rather how you loose focus of the actual issues. Start with stating your requirements.
ASKER
Hi Both, thanks. That's what worries me the sql server is on the webserver so if someone gets in no matter what I do, the data is compromised.
As users will be entering their patient data I want it to be as secure as possible. Even if I encrypt users rows using userId as key its still all going to be seen if someone hacks the server.
What way would you recommend me make it as secure as possible knowing its all on the same server?
As users will be entering their patient data I want it to be as secure as possible. Even if I encrypt users rows using userId as key its still all going to be seen if someone hacks the server.
What way would you recommend me make it as secure as possible knowing its all on the same server?
You can't ultimately protect against everything. There is no such thing as perfect security. Security is the art of deterrence. If you are collecting data that is worth breaking through 5 walls for, then you put at LEAST 6 walls up.
For example, if you are worried about someone breaking in and stealing the physical server, then encryption at rest can help mitigate that attack. But if your application itself is vulnerable to SQL injection, for example, then it doesn't matter what encryption you use because the injection will allow a hacker to access all the data by abusing a legitimate channel.
So first and foremost, your application needs to be secure. Perform penetration tests to make sure everything is patched. That is your front line and if it's not secure, then it doesn't matter what else you do.
For example, if you are worried about someone breaking in and stealing the physical server, then encryption at rest can help mitigate that attack. But if your application itself is vulnerable to SQL injection, for example, then it doesn't matter what encryption you use because the injection will allow a hacker to access all the data by abusing a legitimate channel.
So first and foremost, your application needs to be secure. Perform penetration tests to make sure everything is patched. That is your front line and if it's not secure, then it doesn't matter what else you do.
The problem would be exactly the same should the data be located on a different server. As long as the web server can access the unencrypted data, if the server is compromised, the data will be trivialy available without the encryption even going in his way. Actually, he would not even notice you encrypted anything.
Such encryption would only protect you if someone breaks into premices, and steals the server hard drive.
There is no way past the fact that you cannot expect to be able to both decrypt on the fly and expect a compromised server will not leak. Any one pretending the contrary is eather incompetent or a con artist.
What you can actually do is give the end users the decryption key ( in real life, let them generate the key themselves ), that you would not ever know. If the user looses his key, the data would be lost. and even that would not prevent brute forcing the key. It just might require a few centuries on commodity hardware, assuming the encryption algorythm is not breached.
no encryption level will ever let you past the above.
I am very sorry but your requirements simply do not add up. But i am pretty sure many vendors sell such "solutions". And that is simply a scam.
Such encryption would only protect you if someone breaks into premices, and steals the server hard drive.
There is no way past the fact that you cannot expect to be able to both decrypt on the fly and expect a compromised server will not leak. Any one pretending the contrary is eather incompetent or a con artist.
What you can actually do is give the end users the decryption key ( in real life, let them generate the key themselves ), that you would not ever know. If the user looses his key, the data would be lost. and even that would not prevent brute forcing the key. It just might require a few centuries on commodity hardware, assuming the encryption algorythm is not breached.
no encryption level will ever let you past the above.
I am very sorry but your requirements simply do not add up. But i am pretty sure many vendors sell such "solutions". And that is simply a scam.
A real world security would require making your server as impenetrable as possible. Which means use a decent firewall, properly configured reverse proxies, and forget about m$.
A military grade security would have the client encrypt the data themselves using a key that none of your servers would ever know, send parts of the data through different channels ( at lest ond of them not being network based ), on different datacenters, which would run different oses, soft, and hardware, and also different stuff on the reverse proxies and actual servers, add a few IDS, combine well known and custom encryption algorythms, ...
In your case, i believe a single reasonably well configured datacenter, and end to end encryption would be quite decent. If your server is expected to have access to unencrypted data, forget enf to end and focus on securing your premises.
A military grade security would have the client encrypt the data themselves using a key that none of your servers would ever know, send parts of the data through different channels ( at lest ond of them not being network based ), on different datacenters, which would run different oses, soft, and hardware, and also different stuff on the reverse proxies and actual servers, add a few IDS, combine well known and custom encryption algorythms, ...
In your case, i believe a single reasonably well configured datacenter, and end to end encryption would be quite decent. If your server is expected to have access to unencrypted data, forget enf to end and focus on securing your premises.
ASKER
hi thanks again to both of you. the server is held in a data centre where both sql and iis are and they have assured me they have the best barracuda firewalls.
it’s just going to be a fairly simple website but as it stores patients data just want to make sure it’s secure as possible. the website only allows https with a godaddy ssl.
so do you think using always encrypted is enough? I will run some sql injection penetration test too once completed.
it’s just going to be a fairly simple website but as it stores patients data just want to make sure it’s secure as possible. the website only allows https with a godaddy ssl.
so do you think using always encrypted is enough? I will run some sql injection penetration test too once completed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.