| Author |
Topic |
|
kevinhtet
Starting Member
7 Posts |
 Posted - 2007-10-12 : 14:11:21
|
| Hi,I am not a SQL Admin. I am just a software consultant and there is one question i can't answer. One of my clients is hosting SQL 2005 with over 250GB of data(text and media). There are about 20 users sharing the server with separate database. Each account has a table with very very sensitive data(text and media). We are worried about internal security about accessing the data. Is there any way or option, where only account user will have access to this data and not SQL Admin? Thanks.Kevinp.s. SQL Data is being accessed by asp.net in web browser and vb.net program. server is colo server. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 14:15:36
|
| No. You will need to encrypt the data if you want to hide it from the sysadmin.The DBA needs sysadmin access in order to perform maintenance type work, such as backups and index defragmentation. Without sysadmin, the DBA can not do their job and therefore the system would not be protected or tuned.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
kevinhtet
Starting Member
7 Posts |
Posted - 2007-10-12 : 14:51:41
|
| How would the process work? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 14:57:25
|
| What process?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
kevinhtet
Starting Member
7 Posts |
Posted - 2007-10-12 : 15:13:14
|
| encryption process. Does SQL have built-in Function or third partt software? I am sorry if i am asking you stupid question, i just don't have any knowlegde with SQL DB Structure. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 15:18:41
|
| In order to encrypt the data, you'd have to modify the application. Is the company willing to do that?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
kevinhtet
Starting Member
7 Posts |
Posted - 2007-10-12 : 15:25:59
|
| Yes, if you they have to but the question is will they be able to access and view the encripted data on another machine? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 15:30:11
|
| You would only be able to view the data via the application, which will know how to decrypt the data since it knows how to encrypt it. You will never be able to view the data in a readable format using SQL client tools such as SSMS. If you view the data in SSMS, it will be encrypted and therefore not readable.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
kevinhtet
Starting Member
7 Posts |
Posted - 2007-10-12 : 15:49:49
|
| So you are saying that, we need to installed an application on local machine to see the encripted data? Is it possible to have a plug-in for the IE browser to see the encripted data and other data on the same browser? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 15:51:46
|
| No, what I am saying is that whatever application was developed for the customers to use would need to be rewritten to encrypt the data going into the database and decrypt the data coming out of the database.I don't know what you are getting at as far as browsers go, but this all needs to be handled by the application.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
kevinhtet
Starting Member
7 Posts |
Posted - 2007-10-12 : 16:02:38
|
| application is webbase written in ASP.NET not .exe |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 16:21:16
|
| That doesn't matter. It's still an application. Whoever is responsible for the ASP.NET code will need to work on this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
kevinhtet
Starting Member
7 Posts |
Posted - 2007-10-12 : 16:26:31
|
| Thank you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 17:45:54
|
| Note that in the event of database corruption the DBA will have little chance of rescuing the data if it is encrypted.I obviously don't know the nature of the sensitive data, but you would be better to find a way to trust the DBA (legal-handcuffs, whatever), rather than put effort into preventing the DBAs working with the data in its raw state.(There are other reasons for encrypting, even if you trust your DBAs, of course - such as theft of your data. But the same issue applies of what level of recoverability you will have in the event of a disaster).Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 17:50:56
|
quote:
Originally posted by Kristen
Note that in the event of database corruption the DBA will have little chance of rescuing the data if it is encrypted.
Why?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 18:12:37
|
| Here's what I was thinking:Assuming no backups and all that jazz ...... so in extremis a DBA would be trying to retrieve whatever they can.Then copying data out of the database row-by-row to try to recover parts of a corrupted table. And any JOINed data. A bit like this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How+I+Rescue+Data+from+Corrupted+DatabasesExcept that if it is all encrypted you can't doSELECT TOP 100 * FROM MyTableto see the data, whether it looks "reasonable", and what it might JOIN with.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 19:41:08
|
| That's why you'd just be recovering the data that you could in all tables. Then you'd use the application to view the data to determine if your data is good. Not all of the data should be encrypted anyway, so the DBA would be able to see some stuff.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Security Question