November 13, 2009 at 2:53 am
Hi All,
Is there any way to prevent the DBA's (having SA role) from reading the data.
The database is a kind of sensitive data, so the DBA's should not be able to view the data in tables, but they will have all other privilleges to administrator the SQL Server
subban
November 13, 2009 at 2:56 am
Encrypt the sensitive data and then store it. And decrypt while reading in application.
-Vikas Bindra
November 13, 2009 at 3:02 am
NO, this is not the one required here.
I can't encrypt the entire data in the database which will require the changes to application code as well.
I am talkin abt segregation of duties where a DBA cannot read the data but he can administor the system
subban
November 13, 2009 at 3:17 am
Encryption (with the keys stored outside the DB) is about the only way to do this. If a DBA has sysadmin permissions then, by definition, he can do absolutely anything within the databases. sysadmin bypasses all security checks so there's no way to DENY to someone with sysadmin permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2009 at 3:26 am
I don't think there is a way to prevent a sysAdmin to fire select statements. If there is one then I would also like to know.
The only way I would apply this is by encrypting the data (even if it require changes in application), as the DBA might have to have a look at the data for administration purpose.
-Vikas Bindra
November 13, 2009 at 3:30 am
sysadmin bypasses all security checks so there's no way to DENY to someone with sysadmin permissions
That is what I had thought. Thanks Gail for the conformation.
-Vikas Bindra
November 13, 2009 at 3:40 am
As you can't stop your DBA's from viewing the data, then you may want to consider auditing what they do (using profiler). Then you can at least ask them why they needed to execute a given SELECT statement on the data.
...or why they turned the profile trace off 🙂
November 13, 2009 at 3:44 am
How about if assign the 'CONTROL SERVER' permission to the DBA's and "Deny Select" on all user tables in the database?
This will allow the DBA to do all Administration task but cannot see the data.
subban
November 13, 2009 at 3:48 am
Another option would be: get DBA's you trust.
The DBA job is mostly "underestimated" in terms of the requirements a person in such a position need to meet.
And one of those requirements is: to be absolute trustworthy.
Anybody with SA privileges (like a DBA) basically has the power to (worst case) shut down the business.
Btw: that's one of the reasons why I don't like the concept of outsourcing business crucial or fincancial or HR databases...
November 13, 2009 at 4:13 am
Subhash-63067 (11/13/2009)
How about if assign the 'CONTROL SERVER' permission to the DBA's and "Deny Select" on all user tables in the database?
That'll work initially, however as someone with control server permissions, they have impersonate rights to all logins, including sa (to which nothing can be denied). They could just execute as sa, remove the deny permissions then revert back to their own login. Or create a new login, make it db_owner of the db in question and use that to read the tables.
Control server permissions means just that. They have control over the server. Denying select rights would stop someone who knows their way around SQL for maybe a couple minutes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2009 at 11:33 am
lmu92 (11/13/2009)
Another option would be: get DBA's you trust.The DBA job is mostly "underestimated" in terms of the requirements a person in such a position need to meet.
And one of those requirements is: to be absolute trustworthy.
Anybody with SA privileges (like a DBA) basically has the power to (worst case) shut down the business.
Btw: that's one of the reasons why I don't like the concept of outsourcing business crucial or fincancial or HR databases...
Ya beat me to that one. If you can't trust a DBA and you can't audit him/her, then you're in deep Kimichie. Do the required background checks and hire someone you can trust with the life of the company. Full up bondable bacground checks are expensive but worth it if you want to do "secret" business and you need the DBA to help/maintiain the servers/databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2009 at 12:34 pm
Encryption is the only safe way to do this. Anyone who tells you otherwise is just trying to sneak past you, or doesn't know what they're talking about.
Even if you create a login that only has permission to back up databases and validate backups, there's nothing that stops the person from restoring one of those backups to a server where they have sa permissions. Then, all unencrypted data belongs to them.
That will also bypass trace audits.
If you can't trust your DBAs with the data, for whatever reason, then you need to encrypt it. It's as simple as that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 13, 2009 at 12:45 pm
GSquared (11/13/2009)
If you can't trust your DBAs with the data, for whatever reason, then you need to encrypt it. It's as simple as that.
It's nothing more than that. But nothing less either.
November 13, 2009 at 1:50 pm
lmu92 (11/13/2009)
GSquared (11/13/2009)
If you can't trust your DBAs with the data, for whatever reason, then you need to encrypt it. It's as simple as that.It's nothing more than that. But nothing less either.
I tend to agree.
As a simpler question, what do you consider sensitive data? In some cases sensitive data is required by law(HIPAA), by contract(PCI), or by internal policy to be encrypted. I have experienced a few people that viewed ALL their data as sensitive and that in almost all cases is nothing short of paranoia. Most companies have less the 1% of their data that is truly sensitive, either from a business perspective or a compliance perspective. Data that is sensitive SHOULD be encrypted and the keys tightly controlled, if possible you should have different keys in DEV than you do in Prod, so that your devs can't read the data either.. And being from a dev background I don't like to not be able to see data but I also don't want to be on the hook for it any more than I absolutely have to..
As a side note, most people don't encrypt email, but how much sensitive business is conducted over email both inside and outside your company? I would venture to say, a lot..
If you just don't want your DBA to see the data that is one thing, barring encryption you can't, PERIOD. If you don't trust your DBA you have much larger issues to deal with.
CEWII
November 13, 2009 at 2:19 pm
There's nothing wrong with keeping people you trust away from data they don't need, and shouldn't have. Even if you have DBAs that you trust implicitly and that are actually worthy of that trust, there's nothing that makes it so the next guy you hire can't actually be trusted, or that makes it so someone otherwise trustworthy runs into a situation that renders them untrustworthy (desperation can make people do things they would otherwise never consider).
That doesn't even take into account backup tapes getting lost in a taxi, or whatever.
You can have trustworthy people create issues with unprotected data, even without going into Hollywood-worthy plot twists (the DBA's family is at home being held at gunpoint by members of the Jamaican Maffia, and they've injected him with a disease that only they can cure, which will kill him in less than 50 years unless he does what they say, and will give him ocassional bouts of sniffles, so that they can have a scene where his nose almost drips onto the laser sensor beams).
So, even if you do trust your DBAs, you should still consider securing your data. At least the parts like SSNs, credit card numbers, payroll data, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply