How to protect a database from the Server Administ

问题:

We have a requirement from a client to protect the database our application uses, even from their local administrators (Auditors just gave them that requirement).

In their requirement, protecting the data means that the Sql Server admin cannot read, nor modify sensitive data stored in tables.

We could do that with Encryption in Sql Server 2005, but that would interfere with our third party ORM, and it has other cons, like indexing, etc.

In Sql Server 2008 we could use TDE, but I understand that this solution doesn't protect against a user with Sql Server admin rights to query the database.

Is there any best practice or known solution to this problem?

This problem could be similar to the one of having an application hosted by a host provider, and you want to protect the data from the host admins.

We can use Sql Server 2005 or 2008.

回答1:

This has been asked a lot in the last few weeks. The answers usually boil down to:

(

a) If you don't control the application you are doomed to trust the DBA

or

b) If you do control the application you can encrypt everything with a key only known to the application, and decrypt on the way out. It'll hurt performance a bit (or a lot) though, that's why TDE exists. A variant of this to prevent tampering is to use a cryptographic hash of the values in the column, checking them upon application access.

)

and

c) Do extensive auditing, so you can control what are your admins doing.

回答2:

Auditors always ask for this, like they ask for other things that can never be done.

What you need to do is put it into risk-mitigation terms and show what controls you do have (tracking when users are elevated to administrators, what they did and that they were de-elevated afterwards) instead of in absolutes.

I once had a boss ask for total system redundancy without defining what he meant or how much he was willing to pay and sacrifice.

回答3:

I might have salary information in my tables, and I don't want my trusted dba's to see. Faced with the same problem we have narrowed are options to:

1- Encrypt outside SQLServer, before inserts and updates and decrypt after selects. ie: Using .net encryption. Downside: You loose some indexing and searching capabilities, cannot use like and betweens.

2- Use third party tools (at io level) that block crud to the database unless a password is provided. ie: www.Blockkk.com Downside: You will need to trust a third party tool installed in your server. It might not keep up with SQL Server patches, etc...

3- Use an Auditing Solution that will keep track of selects, inserts, deletes, etc... And will notify (by email or event log)if violations occurred. A sample violation could be a dba running a select on your Salaries table. then fire the dba and change everyone salaries.

回答4:

I think the right solution would be to only allow trusted people be DBA's. It is implicit in being DBA, that you have full access, so in my opinion, your auditor should demand that you have procedures for restricting who has DBA access. That way you work with the system through processes in stead of working aginst the system (ie. sql server). To have person you don't trust be DBA would be nuts...

回答5:

If you don't want any people in the admin group on the server to be able to access the database, then remove the "BUILTIN\Administrators" user on the server.

However, make sure you have another user that is a sysadmin on the server!

回答6:

another way i heard that a company has implemented but i haven't seen it is: there's a government body which issues kind of timestamped certificate. each db change is sent to async queue and is timestamped with this certificate and is stored off site. this way noone can delete anything without breaking the timestamp chain.

i don't know how exactly this works on a deeper level.