Password protect front end and back end multiple user - how? Access2010

[SemiAuto40]

I am to the point of deploying my DB (Access2010) but I need to split the DB into FE and BE. I don't want to leave the back end vulnerable. Currently I have multiple users and passwords for each hashed and stored in a table. My DB is more protected now because of my login form and password scheme. It does not seem to make sense to have the backend so visible and accessible to fools. Every thing I read says split the database - but no one gives any info on how to secure it except for a single password. My DB (as stated above) has multiple passwords and access levels for each user. Isn't there a way to protect the backend? What is everyone doing?



Thanx for your replies.

[June7]

Review this discussion http://stackoverflow.com/questions/1...end-vs-backend

[tanvi]

There are two methods to protect the backend database:

1) I just put a simply little "autoexec" macro that runs in the back end..

I have that macro launch a msgbox command that says you don't have
permissions. The user clicks ok, and they are kicked out.


2) If you open the frontend and delete the table links, and then recreate them,
you'll be prompted for the backend password - that will get stored with the
link, and the user won't be asked again. You could do this on your copy of
the frontend, and then distribute the updated frontend to the users.

[SemiAuto40]

There are two methods to protect the backend database:

1) I just put a simply little "autoexec" macro that runs in the back end..

I have that macro launch a msgbox command that says you don't have
permissions. The user clicks ok, and they are kicked out.


2) If you open the frontend and delete the table links, and then recreate them,
you'll be prompted for the backend password - that will get stored with the
link, and the user won't be asked again. You could do this on your copy of
the frontend, and then distribute the updated frontend to the users.

What I am trying to do is maintain separate passwords for users on the front end and have the back end so that everyone can't look through it.

[Rawb]

Sorry for the late reply, but I've recently started looking at this issue myself. And what it comes down to is that it is EXTREMELY difficult to lock down an Access Database!

I've sketched together a rough idea of how to do it, but since I don't have a copy of 2010 at work yet (it's coming though!) I can't do any VBA programming/testing.

Basically, it works like this: You have your Front-end DB and two (count them TWO) Back-end DBs.

• For the Front-end DB, I strongly recommend using a freshly decompiled ACCDE - compiled ones tend to have readable code imbedded inside them, making some of the VBA Code accessible by opening the Front-end in a Text/Binary File editor. The only Tables you should include in this file are Temporary ones that ONLY include non-proprietary/confidential data for the same reason you should de-compile it (so no Sales/Purchase Orders Tables folks!).
• The FIRST Back-end DB should contain ONLY the Users Table. This Table should have an additional (encrypted) field containing the password used to access the SECOND Back-end DB (be sure to use the Cryptographic API to encrypt it, don't just "hide" the text behind a so-called PASSWORD datatype). Although the second DB's password is the same for each user, it will be encrypted using each user's password. Thus, you'll need to store it separately for each user.
• The SECOND Back-End DB should contain the remaining security-related Tables (Groups, Permissions, Memberships, etc.) as well as all your data Tables. This DB (the entire file) should be encrypted with the password hidden inside the FIRST Back-end DB.

Then it works like this:

• The user opens the Front-end DB and gets a Form asking for their username/password.
• Then the system attempts to decrypt the encrypted Field in the FIRST Back-end DB using the user-supplied password.
• If successful, then it uses VBA to create temporary links between the Front-end and SECOND Back-end DBs that last until the Front-end DB is closed.
• If unsuccessful, then no harm. The user gets an error message and has to try again.

[Perceptus]

I maintain a database that is split amongst almost a dozen different front ends. All the login is handled by the built in ms access accounts and groups.

Long story short though. This method of security only works as long as the user does not hold shift when starting the db. If they do then there is nothing you can do about it. I am currently considering on moving the database to Infopath or even making a .net website.

[Rawb]

Yeah, on anything before Access 2007, real security was a technical and literal impossibility. In fact, even WITH the new encryption capabilities of Access 2007+, effective security is still somewhat limited...

You can't salt passwords (because the salt itself would have to be unencrypted and accessible to the user - and thus, pointless) and users have direct, physical access to the back-end database. That means that even if the file itself is encrypted, all they need to do is copy it to a thumb drive and take it home to decrypt at their leisure. And with the advent of cloud computing and multiple core processors, it's actually possible to brute-force some of these encryption methods now (not easy or fast, but still possible).

[rankhornjp]

I maintain a database that is split amongst almost a dozen different front ends. All the login is handled by the built in ms access accounts and groups.

Long story short though. This method of security only works as long as the user does not hold shift when starting the db. If they do then there is nothing you can do about it. I am currently considering on moving the database to Infopath or even making a .net website.

Change the extension on the front-end to .accdr (forces it to run in run-time mode, disabling edit functions). I'm not saying this is fool-proof, but at least you know that MOST people won't know how to access it.

You can change the back-end as well...