Database Encryption 8

[fischadler]

Hi,
I have been looking through the threads to find a solution for my problem, but although I found others that had the same problem, I found the answers inconclusive.
I have a password protected Access DB but as many people know this protection is next to useless.
I was thinking of using an XOR and base 64 based encryption method to encode the data before it is saved to the database and then decrypt it when displaying it in my app. The problem with that is that filtering and sorting of the recordsets won't work. I access the file everytime I need to read or write data. I don't use the "save the database to memory" approach firstly because I don't know how and secondly because there might be multiple users on a network accessing the same DB file simultaneously.
Basically I simply need to make the database unreadable unless accessed through my app. I'de rather not use whole file encryption because:
1) some databases are quite large and would make loading the app a pain
2) a database could be copied after the file is decrypted at startup and before it is re-encrypted at the end of the app
3) if the program crashes the data would remain un-encrypted and available
4) I'm not sure about this but it seems there is danger of losing all the data if somehow the encryption/decryption process is interrupted.

Any ideas?

-Fischadler

 

[shakespeare5677]

If you can manage the sorting manually, then you can get away with encryping the string before filtering by it, allowing you to use your program to encrypt before saving. This would allow you to keep the program very fast because the complicated encryption stuff happens only once in a while on read/write. I'd say just use a couple of well built collections of classes to handle the records, build in the encryption/decription on save/write. This way every record coming in will be automatically decrypted, stored decrypted (allowing for a nice little sorting procedure by the parent class/collection) and encrypted on save. This, of course, won't work if you have or planning to have any data controls bound directly to the DB.
-Max

 

[strongm]

>XOR

XOR is hardly encryption

 

[fischadler]

Max,
I have no data controls bound directly to the database. I'm not sure I understand your solution. Suppose I have a database with a text field (named MyField) containing the encrypted data for example "#$%^&*&^%" which, for the sake of the example, would translate to "Hello World". Now, suppose I run the query:
SELECT MyField FROM MyTable WHERE MyField LIKE "*Hello*";
The SQL query will not return the record I need. Unless there is a way to load an unencrypted version of the database into memory and run the query on that. I tried creating a hidden unencrypted copy of the database on the HD and then have my application use that and finally encrypting the data back to the original database. It took me about 9 minutes to decrypt just the text and memo fields for a 20MB DB. That is too much for a program that is opened and closed several times a day. There is another problem when there are multiple users accessing the same main database because each one would have his/her own unencrypted copy running on their PC. But I'll worry about one thing at a time.

Strongm,
Any half decent hacker would be able to reverse engineer my application and find the key used for the XOR "encryption" and then write his/her app to decrypt the data. I'm not sure it is that easy to decrypt an XOR encrypted string without knowing the key. It might be that the first lesson they get when they go to hacking school is about decyphering XORs without a password but I'm not concerened about that. What really concerns me is that someone bypasses the MS Access password protection (which seems to be very easy to do) and has access to easily legible and exportable data.

-Fischadler

 

[tsdragon]

I think what Max meant is you could do something like:

EncStr = Encrypt("Hello")
SQL = "SELECT MyField FROM MyTable WHERE MyField LIKE '*" & EncStr & "*'"

Of course, something like that will only work where the "encryption" algorithm you are using does not shift the location or order of the bytes of data, which means it would be pretty much useless as an encryption algorithm.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[strongm]

> I'm not sure it is that easy to decrypt an XOR encrypted string without knowing the key

You may not be sure, but I am (except in the particualr case where the XOR key is longer than the data to be encrypted, which almost gives you a one-time pad)

 

[bjd4jc]

If you are that concerned about people not exporting your data then use a SQL Server.

 

[fischadler]

Tsdragon,
The algorythm would not work properly when decrypting part of the string. :-(
bjd4jc,
Installing SQL Server on every customer's computer is next to impossible.
So basically you're saying that there is no data safe way to use MS Access.
Strongm,
What would you suggest?

-Fischadler

 

[strongm]

DES, AES ...

 

[bjd4jc]

I guess I made an assumption based on the following comment:

>>I don't know how and secondly because there might be multiple users on a network accessing the same DB file simultaneously.

I assumed that you were working in a networked environment where each user didn't need their own copy and thus one instance of SQL Server or MSDE (which is free) would have worked fine. Sorry about that...

 

[strongm]

(I'm only proposing DES/AES as dramatically stronger encryption methods than XOR, not as a solution on securing Access)

 

[fredericofonseca]

Without know exactly how your system works in terms of .MDB usage and on why you are so worried with someone copying your data it is difficult to give you a optimal solution.

But bear in mind the following.


Any user that has access to the tables, either directly through a query, or indirectly through your application can copy all data that is made accessible through those, and there is nothing you can do to prevent this.

If you are just interested in preventing unauthorized access to the database, e.g. outside the organization that owns the data contained on it, then you need to force your "customers" to use a OS that implements proper security access on to folders/directories, or convert your .mdbs to SQL Server or MSDE as mentioned before.

folder security is available on Windows machines using the NTFS security model, and if dealing with XP you need to force this to be visible. With XP you would also need to prevent the option it has not to ask for a user ID when the computer is turned on.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bjd4jc]

>>not as a solution on securing Access

strongm-

just for grins on Friday, what is you solution for securing Access?

 

[shakespeare5677]

Fischadler, tsdragon's right and that is exactly what I meant to say: if you're gonna query for a string, encrypt the string first and then query for it. If you do it with a class handling all the db access, then you should have no worries about saving, retrieving or sorting since the object will handle it all for you. Good luck.
-Max

 

[strongm]

Well, if I felt that Access security wasn't up to my requirements I'd probably look at another solution such as MSDE. However, this seems to have been discounted. In which case

http://www.governmentsecurity.org/articles/IsDatabaseSecurityanOxymoron.php

might be of some use

 

[fischadler]

Frederico,
There are various reasons why I need to prevent people from easily opening the data base and see the contents. One example would be when different people access the same database but the application restricts what they can do according to their user name and password. Now imagine if someone opened the database and went to the "Users" table and looked at the password field.
Another reason would be that I want people to make use of the data in the DB only through my application.
Of course if someone takes the database home and has enough time and will, will eventually be able to get the data they need. I can't do much about that, but at least I can deter the common office user.

Shakespear,
Due to the nature of the way strings are encrypted in the XOR method, the text is encrypted according to its position. Therefore encrypting the word "world" on it's own would give a different result the the encrypted "world" in the phrase "hello world". Strongm knows what I mean. It could also be that I am simply not understanding your suggestion to the full so please bear with me, I am not an expert programmer!

Strongm,
Maybe the fact that MSDE is free might make that option closer to a possibility. The main problems I see are:
1) I am not familiar MSDE
2) I presume that might require installation of extra items on each work station as compared to simply running the setup created by "package and deployment wizard".


I am awarding each one of you a star because I feel that all of you have made an effort to put me on the right tracks. Thanks to you all!

-Fischadler

 

[fredericofonseca]

MSDS is SQL Server, with some limitations, one of which you will not notice as it is the same as Access e.g. file limit of 2 GB per DB, increased to 4 GB on the new version of MSDE.

The other is the number of CONCURRENT QUERIES which is limited to 5. More than 5 and performance degrades. Note that this is queries not connections.


The other major difference is that it required a Windows machine to hold the DB, while the Access .mdb files can be kept in any fileserver, such as Netware or Linux/unix.


In terms of installation on the workstations, the software required can be added as part of your standard installation package, you just need to add the extra items to it. No more than what you would need to do with any ActiveX control you may use in your application.

One of the main benefits if that once you have your application changed to use MSDE you can then use a wider range of programming technics not available with Access, and if you sell you application to a customer that already has SQL Server or MSDE then you don´t even need to install that bit, as you can use their current installation.

So do download it from

http://www.microsoft.com

and try it out.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fischadler]

Frederico,
Thanks for the very useful information! As you correctly said, none of the limitations of MSDE should bother me because the applications I make are never used by more than 5 persons simultaneously and all my "customers" use Microsoft based networks.
Please don't laugh at me cos I'm very green when it comes to database technologies. Can an MSDE database be accessed using MS Access as an interface (just to setup tables and fields the first time)? Does it have password protection and is it safer than Access? Is there some special connection technique for MSDE? I use DAO - yes I know it's old and many told me to upgrade to ADO but I'm the kind of person that says "if it ain't broken, done't fix it" and DAO is good enough for me.
I'll look for an MSDE tutorial on the Google.

Thanks!

-Fischadler

 

[strongm]

>Can an MSDE database be accessed using MS Access as an interface

Yes

> Does it have password protection

Yes

>is it safer than Access?

Yes

>Is there some special connection technique for MSDE? I use DAO

You can continue to use DAO if you really want, but ADO is a better choice with MSDE/SQL Server

 

[fischadler]

Thanks, strongm. I just noticed a folder called MSDE inside my MS Office installation CD.

-Fischadler