SQL dba role with limited access/permissions.

Question

SQL dba role with limited access/permissions.

sKreetz!

Mr or Mrs. 500

Points: 596
More actions
November 17, 2014 at 7:47 am

#291974

I would like to allow SQL dba's to be able to do their normal jobs administering the database as well as some security, but there's a twist. The ceo of our company does now want the dba's to be able to see sensitive employee information. That part has been taken care of with user security permissions, BUT the dba's aren't able to carry out certain tasks with out our vp logging in as sa and doing it for them. There has to be another way to do this without having our vp log in or granting a dba full permissions.
It pretty much boils down to.....
Giving the dba's full permission to do everything BUT modifying/changing their own and/or other user security roles to be able to access employee information.
Thanks!

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Encrypt the sensitive data in the application, If it's sensitive, it should be encrypted anyway, if it's encrypted by the app and the DBAs don't have access to where the keys are stored, that satisfies the requirements.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sKreetz! Mr or Mrs. 500 Points: 596 More actions · Answer 2

Thanks for the quick reply GilaMonster!

Does this also apply to dba's changing their own or other dba permissions to access this information? I would like the dba's to be able to do everything, including creating new users and granting permissions, except for granting permissions to see this data. Only sa should be able to see this data.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

sKreetz! (11/17/2014)
Thanks for the quick reply GilaMonster!
Does this also apply to dba's changing their own or other dba permissions to access this information? I would like the dba's to be able to do everything, including creating new users and granting permissions, except for granting permissions to see this data. Only sa should be able to see this data.

If the data is encrypted in the application, then the DBAs will not be able to access the sensitive data unless you give them the key used to encrypt the data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

sKreetz! (11/17/2014)
Does this also apply to dba's changing their own or other dba permissions to access this information?

If the data is encrypted outside of the database (in the app) and the encryption keys are not stored in the DB, then even a database sysadmin can't see it. The point it, the data is encrypted before it arrives in the DB and only decrypted by the application. The DBAs can have sysadmin in the database, the data is still encrypted and the keys are not in the DB.

Yes, it requires application changes, it's not a magic switch.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125600 More actions · Answer 5

There are several fixed server and database roles that grant persmission to a user to perform various admin type operations without being a member of full SYSADMIN. For example: securityadmin, db_ssisoperator, db_backupoperator.

http://technet.microsoft.com/en-us/library/ms175892(v=SQL.105).aspx

http://technet.microsoft.com/en-us/library/ms189612(v=SQL.105).aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

However - http://dbakapil.blogspot.com/2010/07/potential-security-threat-of-using.html

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125600 More actions · Answer 7

GilaMonster (11/17/2014)
However - http://dbakapil.blogspot.com/2010/07/potential-security-threat-of-using.html

Yes, if one of the fixed roles don't fit the use case exactly, then creating a database role with only the specific granular permissions that each "database operator" needs is perhaps the best way.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho