Restricting DBA from accessing SQL DB objects

Question

Restricting DBA from accessing SQL DB objects

MuskaanGoyal

SSC Veteran

Points: 277
More actions
June 24, 2011 at 4:20 am

#238483

Hi,
I have a requirement to restrict everyone (including DBA) from accessing user defined sql server database objects (like tables/function/views/stored procedures triggers etc). No one should be able to select/modify/delete/drop/disable these database objects from any sql client. These should only be accessible from application. I searched on net and found following:
1. logon triggers : this is a server level trigger which blocks the user (you can modify to block dba well) to logon on the database server. But the same server may have other application database as well and using this user wont be able to login to other db as well. so this is out of scope.
2. DENY permission: as per my R&D results, we cant apply deny permission on sa / dba. let me know if this is possible and how.
3. DB level trigger: we can create database level triggers and roll back the action if any of above mentioned event occur. but DBA can disable the trigger can can access db objects after that.
4. sp_addextendedproperty: using this stored procedure, we can hide user defined tables in SSMSE. but no control over command line. The DBA can login through sql prompt and can execute commands to view table structure and other objects.
Does anyone have any idea on how to restrict any user (especially DBA) from accessing sql server database objects.
PS: I am using SQL Server 2005/2008.
Thanks,
Bhawna

Viewing 15 posts - 1 through 15 (of 36 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

why would you want to restrict the DBA? surely you are just making their job very difficult to do and if possible end up in a situaiton where the dba is locked out.

winash SSChampion Points: 11446 More actions · Answer 2

Have you looked at the separation of duties framework at http://sqlserversod.codeplex.com/?

Might not be exactly what you are looking for but it seems to be a better way of managing access.

And to reiterate what steveb mentioned - why would you want to restrict everyone but the application from accessing DB ojects?

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

When you say DBA, do you mean a login that is member of the sysadmin role?

There is no way of blocking out a sysadmin from SQL Server. A sysadmin can simply use a DAC connection to bypass all your "security features" implemented by logon triggers etc.

If your DB contains sensitive data that you don't want a sysadmin to be able to read then you need to encrypt it, and hide the encryption keys from the dba.

Lowell SSC Guru Points: 323518 More actions · Answer 4

it goes back to the "who can you trust" issue.

if you cannot trust the people with the keys to the database, there's basically three options: remove the keys, remove the people or remove the database.

You've covered all the bases pretty well about looking into logon triggers and other options, but you completely skipped over taking rights away, which is where you should have started with.

if someone has sysadmin rights and shouldn't, take those rights away. give them a role that better suits the biz rules you need instead.

Create an DDL and/or DML trace audit so that you can identify(and then reprimand or fire) changes that should not have occurred.

If you cannot trust your sysadmins, fire them and get some new ones.

yeah i know, you can't fire anyone, nor take away those rights, and you already KNOW that's the right thing to do, but it needed to be mentioned.

I think your only option is audits (maybe even using even notifications) and identify the people who wander out of their allowed access.

if you don't want to use SQL because you want your app to be sooper sekret, what are your options? not using SQL server at all, and doing everything in a zipped, encrypted binary file when not in memory? like a SQLLite database or something?

at some point, you have to trust someone(even if it's just yourself)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MuskaanGoyal SSC Veteran Points: 277 More actions · Answer 5

I have already applied cell level encryption. But I don't want DBA to see the definition or modify any object. We are blocking DBA access for the security of the product.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

bhawna.jain (6/24/2011)
I have already applied cell level encryption. But I don't want DBA to see the definition or modify any object. We are blocking DBA access for the security of the product.

As far as I know SQL Server does not provides such a feature in a way that both allows the DBA to do her/his job and also block her/him from touching objects.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

steveb. SSC-Forever Points: 46733 More actions · Answer 7

bhawna.jain (6/24/2011)
I have already applied cell level encryption. But I don't want DBA to see the definition or modify any object. We are blocking DBA access for the security of the product.

how do you expect the DBA to do their job then?

what happens in a DR situation

or if a index is neede?

sturner SSC-Insane Points: 22411 More actions · Answer 8

If you have so little faith or trust in your DBA then you should find one you can trust. Trying to implement the philosophy you are proposing is silly and negates the reason for having a sysadmin role.

I doubt you will ever have luck holding on to a qualified DBA under such circumstances.

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323518 More actions · Answer 9

reminds me of some of the other threads , like where the poster wants to encrypt the definition of his tables, because, to him, that might reveal a trade secret or something.

or where they want to remove all foreign keys so no one can reverse engineer their logic and make their own reports.....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steveb. SSC-Forever Points: 46733 More actions · Answer 10

yeah these threads pop up every now and again, and i feel that the real solution to protecting your IP would be to desgin the application to use complied DLLs (or similar) to carry out all the important work, as I don't feel this sort of logic belongs in a stored proc..

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

bhawna.jain (6/24/2011)
I have already applied cell level encryption. But I don't want DBA to see the definition or modify any object. We are blocking DBA access for the security of the product.

I agree with Lowell, Sturner, and the others. If you can't trust the DBA, then get one you can. If it's that important, spend the money to build a separate server, write the code to make it 100% self maintaining, and don't give the DBA a login for it.

Good luck on that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

Sounds like a software vendor worried that someone may look at their database design and stored procedure.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 13

Michael Valentine Jones (6/24/2011)
Sounds like a software vendor worried that someone may look at their database design and stored procedure.

Agreed. If that's the case best approach may be to sell whatever it does as a service so customer will only have access to the front-end 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Oliiii SSCertifiable Points: 5328 More actions · Answer 14

We've had the same question here a few month ago.

A provider asked us how they could hide all their design from us (dba), the short answer is they can't.

A dba motivated enough will see all your secrets.

As the other said, if you want to protect some IP then shove that piece in the application code.

If you want to protect your data from a DBA then encrypt it in the application.

The more you try to hide things the harder the life of your DBA, which might simply start recommending your soft be replaced by something else (exactly what happened here).