February 8, 2010 at 1:43 am
Hi,
I am developing a database whose data must be accessed only through stored procedures.
All selects, inserts, updates or deletes are made trough stored procedures. Nobody should be able to view a table or access the data from a table, even a user has sa role. The tables should be completly hidden (the structure and the data) from any user (even if the database is backup-ed and then restored).
Thanks,
ioani
February 8, 2010 at 2:25 am
You can use SQL server 2005 native column encryption methods to encrypt/decrpyt the data .
Until someone knows the keys even if this is restored in another server its nearly impossible to decrypt the data.
These links are very helpful.
http://blogs.technet.com/keithcombs/archive/2005/11/24/sql-server-2005-data-encryption.aspx
http://www.mssqltips.com/tip.asp?tip=928
You can write stored pocs/views to insert/decrypt the data. These definition for sp and views can again be encrypted so that no one can see how its done.
SQL 2008 has an added advantage of encrypting the whole database in case you happen to be on 2008.
Coming to your 2nd point that not even sa can view. I guess you cannot prevent this. anybody having sysadmin srvrole has the supreme power on SQL. Yes if you donot want admin guys who usually have domain admin to get into sql you can remove builtin\administrators or remove the sysadmin role for
builtin\administrators in SQL. Before this you must ensure you know sa password or you have atleast one account in SQL having sysadmin role.
February 8, 2010 at 3:28 am
SQL 2008 (you may consider upgrading) offers the "transparent data encryption", which protects the data "at rest":
http://www.mssqltips.com/tip.asp?tip=1514
The users in the db should have no other rights than the "EXEC" rights on the stored procs. You may explore the "application role" feature.
February 8, 2010 at 4:02 am
There is no way to hide the tables from a login that belongs to systadmin server role. While I agree with the post that said that if you’ll encrypt each column in each table using a key that is based on phrase that is known to the application only, then the DBA won’t be able to read the data, I wouldn’t do that. The first reason is that you are going to do table scan for every select statement that you’ll use. Another thing is that this won’t hide the tables’ structure. Another problem is that someone still needs to create the keys. That someone would be the DBA (I don’t think that you’ll find a DBA that will let someone else run a script on the production server without checking it first).
I once worked in a company that had a database that they didn’t the DBA to be able to look at it (it contained the salaries for all the employees). We installed a server that had only this database. The head of the HR modified the SA password. We didn’t have any user that we could login to this server. We created maintenance plains for this server. Showed the HR how to make sure that everything is in order. From time to time she asked for our help. At that time we worked from her office. She logged in to the server and watched us work and made sure that we don’t check the employees salaries. While I agree that this approach still has some problems, I can’t think of another way to block the possibility that a DBA will look at sensitive data.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2010 at 4:43 am
as stated earlier if you remove builtin\administrator you will be able to restrict the server guys. But there is a loophole . anyone member of local admin group in the server can start the server in single user mode and will have sysadmin role.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply