September 14, 2008 at 7:06 am
Hi All
There must be many scenarios where there is a need to prevent the dba from viewing the data - eg medical, criminal, psychiatric records etc etc.
As far as I can see, only two approaches - 'encrypt bypassphrase' and encryption of the data on the client - meet this need.
Am I on the right path here?
I've found some excellent 'How to ' articles on here about encrytion but none of them seems to address the very first question - "what am I trying to protect against?"
TIA
pg
September 14, 2008 at 7:49 am
How much do you trust your dba ?
Don't you have deonthology rules ?
Implement SOx or HIPAA guidelines, so that every (high privilege ) access needs to be justified by a request, approved and signed off by superiors .
I would leave the encryption only as a last line of defense, because of its impact to the system (performance, data io and indexes ).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 14, 2008 at 8:21 am
Thanks for this reply but it's not really appropriate. The system we're developing is similar to say a small system for an HR dept - details of salaries, sickness records, disciplinary records, suitablity for promotion etc etc where some of the individuals concerned may well be known to the dba personally. The goal is to keep this kind of data confidential, with visibility restricted to individual users, and where opening the table in SSMS does not immediately reveal the sensitive data, and likewise simply backing up and restoring to another machine.
Conversely the system won't be exposed on the internet, so protection against determined, skilled hacking attempts isn't an issue . . .it's more along the lines of a bored dba thinking 'I wonder what so-and-so earns' or 'Why did she get that promotion?' which in my experience realistically demands a more saintly level of restraint than 'Is my dba going to sell the company to our competitors?'
Or to put it another way, when our clients ask about confidentiallity, we would like to be able to say to our clients 'Your sensitive data is encrypted' rather than 'You must trust your dba not to look'
pg
September 14, 2008 at 8:24 am
Not only that, the passphrase will need to be in code, or be entered by people. How many people can keep a shared passphrase secret?
If you think about it, not much data needs to be secret. It's small parts of the data. So you could think about encrypting those.
Preventing the DBA from seeing information is hard. You can secure with different schemas, prevent them from seeing some data, but it won't necessarily be 100%. You might be better off auditing all sysadmin access into databases with Profiler. Should be relatively little of it.
September 14, 2008 at 11:26 am
There must be many scenarios where there is a need to prevent the dba from viewing the data - eg medical, criminal, psychiatric records etc etc.
. Isn't this a some what miss-statement in that somewhere authorized users are authorized using a criteria which they must meet - why not apply that criteria to whomever is hired as the DBA?. Seems to me that you are singling out a position (DBA) and attributing to that position a false attribute, that is anyone in the DBA position will miss use the data. Why wouldn't a HR data entry clerk be suspect as well, or for that matter the HR Manager?
September 14, 2008 at 12:02 pm
Well, for that very reason, the data would not be entered by an HR data entry clerk.
I don't want to get too involved in the details of the scenario, but imagine you have some sort of psychiatric or counselling service, within a larger organisation. The counsellors, as we'll call them, are not technical, and rely for computer expertise, backups etc on the dba of the umbrella organisation. However, they need to be able to promise confidentiallity to the people whom they see. They enter the interview notes into the system themselves. Furthermore, we need to be able to send them a CD with the system on with reaonably straightforward installation requirements.
Steve I take the point about the the security of the passphrase, but this is equivalent to the key of the filing cabinet; that is, you don't blame the manufacturer of the filing cabinet if someone loses the key - likewise, as I said, I'd rather be able as an ISV to say 'your interview notes are encrypted - so don't tell anyone the passphrase' rather than 'you just have to trust the dba of the umbrella organisation'
pg
September 14, 2008 at 12:52 pm
I've seen some demo stuff using the approach that every doctor had his own ssl certificate used for encryption of e.g. diagnose varchar columns, so only the one being granted use for the certificate could access the data.
So the doctor can only see his own patients unless he's also being granted a certificate from another doctor.
Only certrain critical fields would be encrypted.
No indexing can be done on encrypted columns, to these columns were no candidates for search arguments, without a big price.
I haven't seen it used in a prod environment, because that is not my branche 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 14, 2008 at 1:19 pm
Fair point on the filing cabinet, but I think computer systems are expected to have better security. Also, the physical nature of notes tends to mean that you can protect them better since they only have to be secured in one location.
If you're building this system, I'd look at encrypting the notes with a symmetric key, and then encrypting that with certificates, separate one for each doctor. You can grant permissions to the certificates to logins, so that will get you shared security if you need it. Can revoke that fairly easy as well.
You need some strong auditing here, because the DBA could still grant himself access, but this will appear to be better.
Notes are likely relatively low volume of reads, so the decryption shouldn't stress things too much. Honestly this type of system is what I wish MS had provided a sample/case study on since it's a bit complicated, and it would be good to see some samples for how it can be implemented.
If you decide to build this, I've love to get a writeup on some details of how to manage the security. Shouldn't compromise what you've written and it would help others.
September 14, 2008 at 3:19 pm
This might be a situation where encryption of specific data is required. And by encryption I mean - encryption handled by the app, externally to SQL Server. If the info is that criticial and that sensitive, then no - the DBA should not have access to it. That being said - we're talking about encrypting only the most sensitive aspects of this (in healthcare, say - symptoms and treatment), beause encrypting everything will essentially make the system unusable. Sorting/indexing/retrieving stuff/range comparisons become almost impossible with everything encrypted.
There is a certain amount of trust that is needed to the DBA, in order for the application to keep operating. But there's no reason that some data couldn't be protected against even them.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 15, 2008 at 12:15 am
Here are some urls I've used just to investigate some scenarios:
[url]http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx"> http://blogs.msdn.com/lcris/archive/2006/10/19/sql-server-2005-an-example-for-how-to-use-counter-signatures.aspx%5B/url%5D
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
http://www.lakesidesql.com/articles/?p=15
And I think even BOL has some nice examples.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 15, 2008 at 3:11 am
Did you consider something like Ingrian (http://www.ingrian.com/) ? The way it works is that ETL passes whatever columns you want encrypted and it sends them on to the database encrypted. Only users authorized to see the unencrypted results get a client plugin to be able to read it. The DBA will only see a column of 32 character hex gibberish but would still be able to do his/her thing. Since the Ingrian app has its own server and set of encrypting keys, there's no practical way for the DBA to accidentally or even maliciously attempt to get at the real values.
September 16, 2008 at 3:21 am
Thanks guys for all of your suggestions - I knew if I posted on here I'd get some good advice!
pg
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply