Security..Is there another way? (RE-STATED)

[marianne]

FOR VERSION 2003 AND BELOW:

I have another thought above security. Ok, let us set aside ULS issue, http://www.access-programmers.co.uk/forums/showthread.php?t=176268

OK, so we know that Microsoft removed the deprecated User Level Security to their new version of MS Access 2007. This is because they have made the DATABASE PASSWORD more powerful with the new encryption.

This new encryption is still a code.

So my question now is can we improve or enhance the existing database password encryption by making an encryption code more powerful and apply it to the database. And using that code also in decrypting the encryption password inside our project.

It just like when there is still bug in the microsoft products, service packs are developed and provided for download to update the product that has a bug. I think microsoft may not make service pack for this database password encryption update for version 2003 and below since they are in the business of sales for new and improved product.

So in this case, we are to make our improvised more powerful encryption to be implemented to the mdb file.

Can we make that code? Does someone know how to do that?

 

[marianne]

no ideas out there?

 

[oumahexi]

I'm not sure if this is what you're lookig for Marianne, but, we have a situation where there are multiple outlets for our db, the user selected their location and that sets the ini file. In the startup initialiser we have the following piece of code:

strFileName = Nz(DLookup("INIFile", "qryCodeConfiguration", "Id=1"), "")
strReturn = GetPrivateProfileString32(strFileName, "Condition1", "Condition2", "")


I hope youfind this helpful.

 

[Banana]

One way to provide a custom protection of the data is to use an encryption algorithm.

Here's a link to a Visual Basic code for Blowfish which can be imported into a VBA project. You do need to provide a key, however, and if you don't want your users to log in by entering a password, then you will have the same problem as the built-in encryption has - key being stored in the file and therefore no better than putting your house key under the doormat.

 

[NigelShaw]

Hi,

i havent followed the thread that far as im sure there was a different one...

couldnt you store the key in a txt file and have the code check for it.

if the file is there, get the key, if the key is correct, get access
if the file is NOT there, bail

or am i reading this wrong? i assumed a VBA protection was being sought.



nigel

 

[The_Doc_Man]

The problem is, as always for trying to secure something, that in the absence of a smart-card or biometric type of login, you are stuck with passwords or trust. We can forget about trust because you are asking the question. You obviously DON'T trust your users.

If you cannot absolutely identify your users because you don't have biometric or physical-device security (like a smart-card) for login, then I would respectfully point out that adding layers to layers only increases the amount of time that you are going to spend getting through your own muck and mire, but at the end of the day, if someone gets hold of a password who should not have done so, your goose is still cooked.

I respectfully suggest you to consider that there is no potential return on investment to add yet another layer on what you already have.

Does your site use physical-token or biometrics? THEN you have something that might be helpful. But even so, there is more to consider because of potential sharing issues. I.e. decrypting data on the fly vs. decrypting the database vs. only decrypting what goes into the text boxes of a form vs. something else.

Basically, not trying to dampen your spirits, but I feel I must inject a dose of reality into the search. You are setting yourself up for a LOT of work that might be useless in the long run, because you are looking at the wrong source of trouble. If you can't trust your users, then you can't trust them with any number of passwords, either.

 

[marianne]

Banana, was that a direct answer to this post? if it is, how can apply it to the database file?

 

[Banana]

Well, to make it work you of course have to split the database so you only need to worry about encrypting the backend. In the front end, you would have the code to open the encrypted file and handling it as a binary stream decrypt it with the supplied key and re-create the unecrypted file for the front-end to use and when front-end closes, encrypt the backend file again and delete the original unecrypted file.

Alternatively, you can do it at table/row which neatly avoids the problem of the unecrypted file beingn exposed & exploited at a cost of much greater performance overhead due to more frequent encryption & decryption. To use any bound forms, you would have probably need to use a temporary table to store the plaintext and write a routine to commit the temporary table contents into the permanent (and encrypted) table.

Finally, you need to figure out how to store your encryption key safely or even better don't store at all by having your users giving the pieces it need to re-create the key as NigelShaw & I already described.


I do understand you really want to protect the data, but you need to be very mindful of the simple fact that's farther more easier to fool yoourself thinking it secured than it is to actually secure it, so if you really want to work, you have to commit yourself to learn how to do this correctly and believe me, I'm so totally with The_Doc_Man; it's much more work than going to your boss and HR director and knocking some sense into your employees who is (ab)using the database.

 

[marianne]

yes. I also agree with The_Doc_Man that is it a personnel issue. However, though I believe that it is a personnel issue that such penalty would probably include termination, but on the other hand, I was thinking more of the data. Even though such penalty would be imposed but such data would already be gone. Though I know that security would be more robust in SQL, but I would like to stick to Access first on what could be my possible maximum security that I could implement. I read one article about vPPN. It is an add-in created in access application by the member of one forum as discussed there as additional security measure. And it is now free for download. Unfortunately it is for version 2007 only as discussed there. That was intended in lieu for ULS.

With the procedure that you gave me Banana, to be honest, though I get it through reading it but somehow, I am vague with the implementation. Can you show me the way?

 

[Banana]

I'm sure The_Doc_Man did mention making use of audit trail to log all the data changes so if it gets destroyed you can roll back the changes. I'd say it'd be easier to set it up and spent an hour or two to roll back the changes should the need arise than it is to go through the hassle of rolling out your own security mechanism, hoping that nobody circumvent it, and annoying the hell out of others when the application crawls to snail's speed due to the extra overhead.

Furthermore, it's fairly well-documented. Here's one, another, as well this one.


With the specifics about the procedure, if you've not worked on low-level file handling and working with bytes, then you need to learn that first before you can even think about encrypting with the blowfish algorithm. See if this get you started.

 

[marianne]

Sub Command1_Click ()
Const DB_ENCRYPT = 2
Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"

'** Enter the following two lines as one, single line:
CompactDatabase "C:\VB\TESTING.MDB", "C:\VB\NEWTEST.MDB",
DB_LANG_GENERAL, DB_ENCRYPT
End Sub

----------------------------------------------------------------

can somebody explain what are those constants for there?

 

[Banana]

Where did you find that code? It almost looks to me like it's VBScript because I don't think those two constants are needed for the CompactDatabase method inside VBA but VBScript would definitely need it as it doesn't supports enums.

If you look in VBA editor's help files (not Access's help), it gives you a good explanation of what each arguments does, and in that case, it's basically saying "compact that database at so and so filepath to the new filepath, and save it with General sorting order and encrypt it."

Same thing in VBA but without the constants:

DBEngine.CompactDatabase "C:\VB\TESTING.MDB", "C:\VB\NEWTEST.MDB", dbLangGeneral, dbEncrypt

 

[The_Doc_Man]

Another comment on the idea of encrypting/decrypting on the fly:

If you decrypt the encrypted DB and make a legit update, then if someone else at the same time does the same thing, one of you will lose because when you re-encrypt, one of you will overwrite the other.

Your problem will always devolve into this hard, cold fact: Access is a great program for a small business. If the business gets large enough that you lose the ability to trust your employees, you need to establish a far more robust environment. Without that environment, your framework will always be on a shaky foundation.

I am in the process of implementing my own security. I will be turning my security paradigms around completely by forcing the users through a switchboard interface and locking things down based on who the network says they are. But we know that with some reliability because our network security guys have locked down the login process. When we ask for Environ("USERNAME"), we know we can trust that because our network admin guys made it trustworthy. So what I do now is look you up in my list of known users. If I don't know you, you become an automatic guest. But the forms read your status and if you aren't allowed to touch anything (level 1), the forms lock all the controls to become read-only. If I know you and you are banned (level 0), the forms do a DoCmd.QUIT. If you are allowed at level 2, you can touch any form that involves data related to you personally. If you are allowed at level 3, you can touch anything and can even minimize the switchboard.

Oh, if you try to bypass the switchboard and run a form directly, the code that defines your access level says "Banned" and the form quits the database again. Since most of my tables are heavily encoded, you don't see much useful data. The forms do most of the translation work.

No, it isn't perfect, and someone who is a determined hacker would probably be able to break through. But once I split FE/BE and convert the "public" FE to an MDE, I'll stop a lot of interference.

No matter what you try to do, remember that if you can't trust the method of identifying your users, you are putting your effort in the wrong place from the get-go.

 

[marianne]

http://www.everythingaccess.com/tutorials.asp?ID=Changing-the-encryption-type-in-Access-2007

I found this on the internet and it has encryption manager for 2007 version. when I tried it on 2003. it still say encryption change success. it changes the encryption code.

I somewhat like it. but I want to build my own encryption type that cannot be found anywhere in the net and then apply my encryption thru like the encryption manager. I really dont know how to code how encryption manager does it.

 

[marianne]

cant the forum help me with my need?

 

[Banana]

Marianne,

I'm not sure why the freeware you linked reported success on 2003, but from the comments in the same link:

5. genesis says...
23 July 2009

can we improve the database password encryption of access 2003 also? or encryptionmanager for version 2003!

kindly email me at xxx

6. Wayne Phillips says...
23 July 2009


Genesis,

Unfortunately, it is not possible to increase the encryption strength in Access 2003.

7.genesis says...
24 July 2009

why not? may I know why?

8. Wayne Phillips says...
24 July 2009

Genesis, it is because Access 2003 (and prior versions) use a fixed implementation of the RC4-32 algorithm (an extremely weak implementation of it, actually). Only starting with Access 2007 did they move to using the Windows Crypto API to implement the encryption - that is why it is now customizable to some extent.

Regard,
Wayne

With regards to assistance, what I really, really want to see from you is put some effort in it. Write up some code and post with *specific* questions where you are stuck. People are more likely to respond to "How do I call this function" or "What's the variables I should be using" than "Help me write code". Alternatively if you do not have time to invest into this, consider hiring a professional help.

 

[marianne]

Three things.

1. I know how to write code on create a function for encryption and decryption
2. I know how to set database password thru vba
3. BUT I do not know how to combine numbers 1 and 2 and then apply to the database or maybe I am not sure of it.

that is why I ask how to read this one:
-----------------------------------------------------------------
Sub Command1_Click ()
Const DB_ENCRYPT = 2
Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"

'** Enter the following two lines as one, single line:
CompactDatabase "C:\VB\TESTING.MDB", "C:\VB\NEWTEST.MDB",
DB_LANG_GENERAL, DB_ENCRYPT
End Sub

-----------------------------------------------------------------

because I wanted to know how DB_ENCRYPT works. maybe I can replace the DB_ENCRYPT with the function I created for my customized encryption.

Am I on the right track Banana?

 

[Banana]

Short answer, no.

As Wayne explained, the built-in encryption for 2003 and earlier is pretty much fixed so we can't use it at all, short of reverse engineering the Access (which may not be permitted and even if it were, you can expect several thing to break).

As stated earlier, you basically have two options:

1) Encrypt the whole file using custom routine. An example is split the database, and in the backend, encrypt it using the Blowfish code I linked to earlier then when users log in, decrypt the file.

2) Instead of encrypting the file itself, encrypt the records, which means you would need to decrypt & encrypt each record on the fly each time you needed to read a record. One way to accomplish this is to write a custom function that calls the Blowfish decryption:

SELECT Decrypt(ColumnOne, Key), Decrypt(ColumnTwo, Key), Decrypt(ColumnThree, Key), Decrypt(ColumnFour, Key)
FROM aTable;

then use either unbound form or temporary table to hold the plaintext then encrypt the data when the user is done with the record and click the 'Save' button so it gets written back to the backend encrypted.

First is simple to set up but carries a risk of having a plaintext backend as long a user is logged in that can be copied. Second is more complicated and while more secure, will be resource intensive due to the need for decrypt/encrypt every records.

In fact, I'd be more inclined to just use MySQL as a backend with a MDE front-end configured with ULS to deny any permissions upon the table & queries objects.

 

[The_Doc_Man]

marianne, you are possibly asking for something that is just not in the cards for Access.

Up until a couple of years ago, I used to teach Windows security to new Win System Administrators. What you seek to do is certainly possible on SOME combination of software, but I strongly doubt just how much you can do if you want to stay within Access.

Implementing ULS? Good idea but MS abandoned it because of its headaches.

Implementing your own security inside the DB? Possible but you have to work hard to do it. And you cannot let users bypass any part of your security.

You must remember that Access is a database for a small business. The reason it does what it does so well is because machines have gotten a pot-load faster since Access v2.0 first came out. (I used that...). On a 33 MHz 486 chip with a 3600 rpm disk, response was pretty good - until you started to get into some big recordsets. Then you could almost watch it try to work in real-time, it was so slow. But when the machine is now over 1.5 Ghz and the disks are 7200 rpm or faster, that tends to gloss over some of the slowness inherent in the Access environment. Not to mention that Jet 4.0 as a DB engine is muchly improved over the Jet we saw in AcV2.

My point is, the original paradigm for Access was a SMALL BUSINESS environment with small machines and not that much data. Folks who try to push the envelope often find it constrained enough that they get frustrated in what they suddently want to do but cannot do. All because Access is based on a small shop.

If you really want serious security, you do far better to use Access as a front-end only and use an SQL-based backend product for the DB engine. There is no simple answer when trying to make a simple program do not-so-simple functions.

I'll offer this one suggestion: If you have someone in your group who knows enough about Visual Basic or Visual C (any flavor) or some other similar language, enough to create a meaningful DLL file, you could have that person do a compiled encryption rather than relying on Access VBA (which is interpreted). Or you could explore a library to see if you can find the interfaces to an encryption and decryption routine for a compiled DLL file, to which you need to make a reference. Otherwise, object browser won't show you anything inside the DLL file.

I sincerely hope, Marianne, that you understand the serious difficulty of the question you are asking. We have difficulty in answering you with anything better than we have already discussed because to be honest, very few of us try to take Access this far. You now find yourself on the "leading, bleeding edge" of technology, and now you are finding out why it is named to include "bleeding." If you go any farther out than this, you are likely to meet Rod Serling.

 

[DJkarl]

Three things.

1. I know how to write code on create a function for encryption and decryption
2. I know how to set database password thru vba
3. BUT I do not know how to combine numbers 1 and 2 and then apply to the database or maybe I am not sure of it.

that is why I ask how to read this one:
-----------------------------------------------------------------
Sub Command1_Click ()
Const DB_ENCRYPT = 2
Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"

'** Enter the following two lines as one, single line:
CompactDatabase "C:\VB\TESTING.MDB", "C:\VB\NEWTEST.MDB",
DB_LANG_GENERAL, DB_ENCRYPT
End Sub

-----------------------------------------------------------------

because I wanted to know how DB_ENCRYPT works. maybe I can replace the DB_ENCRYPT with the function I created for my customized encryption.

Am I on the right track Banana?

http://support.microsoft.com/kb/104875

The first sentence on the MS kb article sums up database encryption.

Database encryption has nothing to do with security.

The DB_ENCRYPT constant just tells the CompactDatabase function to use the RSA encryption algorithm to encrypt the output database.

http://en.wikipedia.org/wiki/RSA

This link will give you information on how the RSA encryption algorithm works.

You seem to be looking for an easy answer and as Doc Man pointed out you are venturing into territory where few have dared to tread. I'm sure someone somewhere has probably done something similar to what you are asking, but for the same reasons you indicated they did not post their solution online for security pruposes.

I would heed Doc_Man's and Banana's advice on this, trying to create your own encryption schema to protect MS access data seems tedious at best and futile at worst.