SQL Server 2008 data protection

views:

answers:

SQL Server 2008 data protection

I have a client-server application where a .NET client accesses all the data and stored procedures in a SQL Server 2008 database.

Is there any way to protect all this data so that only the users I create and authorize can access this specific database? Especially the user 'sa' comes to mind. I don't like him to access all my data.

+3 A:

You can't block sa or another system administrator, as access to the full system is integral to their role. You could try encrypting the data so it's meaningless outside your application, although it might complicate any future reporting needs.

CodeByMoonlight 2009-09-17 13:43:57

Encryption isn't really an option as then I'd have to adapt the whole application (I guess). Also I'm sure there's a performance penalty. Still thanks for the answer.

Marc 2009-09-17 15:02:48

+6 A:

You cannot prevent the system admin from accessing data, nor should you.

However, no one except the designated dba should have the password for the sa account. If sa doesn't have a password or if lots of people have the password, change that now. If the application accesses through sa, change that immediately.

Other than that way you can best limit access is remove access to all other accounts from the tables and views (including select access) and only allow exec access through the stored procs. That way anyone except the designated admin can only do waht the stored procs do and nothing else. You cannot do this however if you have used any dynamic sql either inthe application or the stored procs which is one reason why dynamic sql is a poor idea.

HLGEM 2009-09-17 13:44:23

so if a user has no SELECT access on a certain table, but has EXEC access on a stored procedure that does a SELECT from that same table, the user can see the data?

KM 2009-09-17 14:00:32

Yes, but only via the stored procedure.

CodeByMoonlight 2009-09-17 14:02:08

Yes, you are right.

Raj More 2009-09-17 14:03:09

+1: Good answer. It may also be worth noting that you can in fact use dynamic SQL with such an implementation, provided you "sign" the stored procedure to resolve the issue of a broken ownership chain.

John Sansom 2009-09-17 14:17:47

Alright thanks for this answer and the comments. Still I'm a bit disappointed that like this I can't rollout my application with the guarantee that competitors won't have access to the data directly (to write converter apps or so). Thanks anyway.

Marc 2009-09-17 15:01:26

You have to remember, it might be your app, but it is their data. We convert data and send to our competitors all the time and they do the same to us as clients move back and forth. In general your competitors won't have sa access and the client would usually ask for and pay for a data conversion. Clients need to be able to fix things when things go wrong with their data. If you don't want them to have direct access to thier data, then use a business model where you host thier data and only your dbas have direct access to the tables.

HLGEM 2009-09-17 15:14:04

I wanted to add that we have found it is more likely you will be able to win a client back if you cooperate with their needs when they move to another vendor. If you behave badly at that point, your chances of getting the customer back ever become very close to zero.

HLGEM 2009-09-17 15:15:30

HLGEM, I fully agree with you. Still it was a requirement I got and try to solve. I'm just astonished that this cannot be achieved. What about SQL servers containing sensitive financial and medical data? Does the DBA always have full access? I'd like to be able to allow him to create and restore backups, but then that should be it. It seems by the way that this is possible in Oracle through schema definitions. Anyway thanks again for your answer and comments.

Marc 2009-09-17 15:21:20

If you have sensitive finacial and medical data, you encrypt it. We encrypt all SSNs and tax ids in our systems for instance.DBas do much more than create and restore backups. They are the first line of defense when something goes wrong, when there are deadlocks or users unable to log in or loys of other things. Or data that has gotten thoroughly messed up and needs fixing. It can take too much time to do a restore just to fix the data in one table. Some of those fixes happen because of bugs your company put there. They can't do their jobs without full access to the database.

HLGEM 2009-09-17 17:00:56

ansaurus

tags:

views:

answers:

SQL Server 2008 data protection

related questions