Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have a large SQL SERVER database having hundreds of tables,views,Procedures and Functions containing sensitive data. I have to protect it at cost from tampering. It's a offline windows application. So I think the risk is less (tell me if I'm wrong). But I want to protect data even if someone managed to access database server physically. I know the naming of the table may give the intruder about much more idea about the data .
for eg: Naming like "Tbl_User" ,"SalesMaster" etc. I heard some companies use names like
TBL001 ,TBL002 etc. and column names like "Field1" , "Field2" etc. So that the Data Structure can not Identify by its name. So please share your thought on this problem
Posted
Comments
Sergey Alexandrovich Kryukov 24-Sep-14 2:45am    
Risk of what is less? why?
—SA
Jackson K T 24-Sep-14 3:02am    
I thought risk is less than online application because data is only locally available. So if u want to steal you have be inside the office
Sergey Alexandrovich Kryukov 24-Sep-14 3:20am    
Such considerations are simply incorrect. You cannot compare apples with oranges.

You only publish a database on some network, if you need collaboration of at least few people on it. In this case, not using the network is not an option — it should be at least the LAN connecting those people. And if you need only one person to work on a database, there is no a need to give a network access to it, so, again, no options.

—SA
Jackson K T 24-Sep-14 3:28am    
okay my bad... I consider the users on the LAN network risk less because they have little or no interest in computing.
Sergey Alexandrovich Kryukov 24-Sep-14 3:56am    
This is, again, about the illusion of safety, something which can compromise safety...
—SA

Jackson K T asked:
Could you suggest something practical on this issue?
Yes and no.

Yes, because you probably strongly underestimate the modern security-related technology; that's why you discussed so very naive suggestion someone gave you. (It's so good that you took it with some critical thinking and asked your question.) From that standpoint, it's possible to greatly improve your awareness, which could potentially result in a very reasonable solution.

No, because each security schema has its cost. And I don't know neither your potential vulnerabilities no the price you are ready to pay.

So, what to do? Let me give you some idea, just for example. First of all, you can use some authentication with strong passwords, so only the authorized users would get access to one or another part of the database, read-write or read-only, on different levels. But I don't believe that database servers are well equipped in this respect. It's very usual approach to hide a database in a separate tier completely closed from any direct access by any human operator, except some people connected directly to the host of the database server. A separate tier can provide access to the database through its own API and secure transport. For simplification of out discussion, let's assume that this tier is a Web application/site/service, then yet another tier would be just the browser.

Let's see. On the Web tier, you can introduce as strong authentication as possible. Passwords are not stored anywhere, because it is never needed, because you can use cryptographic hash function of the password, store only the hash and compare hash with hash for authentication. The authentication itself could be eavesdropped on the network, but this is prevented by using HTTPS and SSL, instead of HTTP. Also, there is such thing as two-side certificates, both server and client-side. If your organization is so small that you can hand and receive the certificate files in personal contact with each co-worked, you can even use self-signed certificate, including even the certificate needed for SSL.

Finally, it does not have to be exposed to Internet. Network technologies allows us to limit all the operations physically to some narrower network, so, for example, it could be accessible only from some office, and still use all the Web technologies (which is sometimes referred as to Intranet). But even this would be an overkill — many serious organizations do trust Internet. Don't look too seriously at those cases when important government offices got leaks. They probably use a lot of legacy and their systems might be overly complicated. If you do it all from scratch using only the modern technology, the risk is way lower.

This is only the example of the approach. You probably need to understand some detail. Please see:
http://en.wikipedia.org/wiki/HTTP_Secure[^],
http://en.wikipedia.org/wiki/Transport_Layer_Security[^],
http://en.wikipedia.org/wiki/Public-key_cryptography[^],
http://en.wikipedia.org/wiki/Public_key_infrastructure[^],
http://en.wikipedia.org/wiki/Certificate_authority[^],
http://en.wikipedia.org/wiki/Cryptographic_hash_function[^].

See also some of my past answers:
i already encrypt my password but when i log in it gives me an error. how can decrypte it[^],
Decryption of Encrypted Password[^],
storing password value int sql server with secure way[^].

—SA
 
Share this answer
 
v3
Comments
Jackson K T 26-Sep-14 0:09am    
Thank you Sergey Alexandrovich ...
Sergey Alexandrovich Kryukov 26-Sep-14 0:55am    
You are very welcome.
Good luck, call again.
—SA
You have to weigh up the risks vs the cost of maintaining the software.

If you call the tables Tbl1 Tbl2 etc. then you would need to somewhere have a list of what those tables 'are'.

If that is a list on a piece of paper taped to the wall, or a document stored on the machine, then you've wasted your time!

As I said, it's a case of working out the risks vs cost in your particular environment.

If the data is worth stealing, then any potential thief with access to the database can probably make sense of it - Table74.Field21 with a value of "Justin" and Table74.Field22 with a value of "Beiber" would lead me to be able to decode that Table74 is the Customer table and Field21/22 are First Name and Last Name.

So it really depends on whether the data are decipherable as much as the column names - and how much effort a potential thief would be prepared to put in to get that data.

Of course you could encrypt the data too...
 
Share this answer
 
Comments
Jackson K T 24-Sep-14 3:13am    
Encryption was the first thing in my mind but I use SQL SERVER 2008 R2. It provides Transparent Data Encryption (TDE) but only available with Enterprise edition or higher . Another option is column level encryption but I fear it may hit performance . Is there any work around ? Like custom encryption algorithms ?. I use .Net Winforms for Front End
_Maxxx_ 24-Sep-14 8:21am    
Encryption will hit performance - by definition it is doing more work - but generally not too significantly (of course it depends on the encryption!)

And with databases you need to be able to de-crypt - and as a developer you need to be able to do so relatively simply or maintaining data can be a pain - and if you can , then can someone else gaining access to the server?

You could just encrypt some data (if not all of it is sensitive) - but again if you think someone might be able to access the server, you'd need to ensure that decryption was hard enough that any snooper isn't going to be able to get at your data.

But users will have to.

And users will have an application that decrypts the data!

that is probably your weak spot!
Jackson K T 26-Sep-14 0:09am    
Thank you _Maxxx_
In a way, those "cryptic" names like "Field1" , "Field2" is such a bad idea that it increases the risk in certain aspect, not reduces it. To understand it, please read: http://en.wikipedia.org/wiki/Security_through_obscurity[^].

Got an idea?

[EDIT]

By the way, the word "abstracting" has a very different meaning. :-)

—SA
 
Share this answer
 
v2
Comments
Jackson K T 24-Sep-14 3:19am    
yea. :) Thanks
Jackson K T 24-Sep-14 3:21am    
could you suggest something practical on this issue ?
Sergey Alexandrovich Kryukov 24-Sep-14 3:52am    
Yes and no. :-) Please see Solution 3 I've just written.
—SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900