Frontend - Backend Databases Security question

[isetea]

Hi,
I'm wondering about the following.

If I would have a backend table with the data kept in tables and a frontend database with forms and reports and queries etc. and linked tables to the backend db, what would be the right way to secure both.

In my situation I have salary information in some of the tables, which should not be wide open to the holy world. I also need to restrict the access of some queries and forms in the frontend database to different users. That means I need to set up a secured frontend database with different usergroups having different object permissions. So in that frontend database the Users Group and the Admin User do not have any permissions any more. Thats why I created a shortcut where I kept the database and workgroup information in the target. Every user needs to open the database via the shortcut.

Unfortunately I was not able to secure both the backend and the frontend database, because the linked tables in the frontend db are only pointing to a database not to a shortcut. Because the backend database is secured as well (no permission to Admin and Users any more) it can't be opened directly - only by shortcut.

Am I right that there is obviously no possibility to have a secured backend and frontend db via the Jet database engine and Access?

I hope somebody can answer my questions. At least I know that I can't do anything else and I did not something wrong

 

[TastyWheat]

I'd like to know the answer to this as well, but I also have a suggestion (which I have a feeling will be debunked).

If you secure the backend that should make the tables safe. I'm assuming that if the backend is secure you can't mess with the tables without proper permissions (even when using a frontend). If this is the case I wouldn't really worry about securing the frontend. Most people wouldn't intentionally try to mess with the code or the form designs. Even if someone does mess with it they can't do anything they wouldn't normally be allowed to do (because their permissions wouldn't have changed). You can still remove the design options so they don't accidentally do anything.

Again this is just an idea. I haven't tested this out myself.

 

[isetea]

Hi TastyWheat,
I was aware of you suggestion already because I have read through McDonalds Document about "How I Use Microsoft Access User-level Security". I can only recommend that document to everybody who wants to secure an won database. Together with the Microsoft Access FAQ most of the questions are answered.
But, unfortunately not mine. [Or it's because English is not my mother language and I didn't understand everything correct ]

Still looking for somebody who can answer my question below...

 

[Adeptus]

If you assign permissions on the backend using the same workgroup (MDW) file, doesn't it use the same user credentials for both?

 

[TastyWheat]

My idea worked, sort of. I secured my backend and I was able to read but not edit the data on the frontend.

However, my grasp of user-level security is still very lacking. In my case I either get asked for a password all the time (even when using databases I didn't secure) or I never get asked for a password (and I'm automatically allowed or denied access).

 

[KeithG]

Here is what I always do in your situation.

First I create a table with the Windows user ID's of the people who need access to the restricted table. Then I create a procedure to retrieve the username of the person currently logged into windows and put it in the Open event of my start up form. Once I have the username I query the table I made of usernames that can access the data to see if the username is in the table. If the user name is in the table I call a procedure to link the restricted table to the FE. If the username is not in the table I call a procedure to make sure that the restricted table is not linked to the FE.

 

[TastyWheat]

KeithG's method should add a little extra security, but it sounds like a bit of "reinventing the wheel". You should be able to emulate all of the security measures of the build in user-level security, but that would require a lot of coding. And just because someone opens a database with a certain login doesn't mean they are that person. In other words, not everybody remembers/bothers to logout.

Here's the way I ended up doing it. I can't say it's the best or only way but it worked for me.

Make the frontend and the backend. Run the security wizard on the backend to create a new workgroup file. Once that's done open your frontend with the workgroup you just created (it's best to make a shortcut for this). Then run the security wizard on the frontend, but instead of creating a new workgroup modify the one that's open (make sure it's not the default workgroup). When I did this the program kind of stalled, but I closed and reopened the database and it was secured. So just give it a minute or two if nothing happens. From this point you should not be able to open either end without joining the correct workgroup and supplying the right username and password.

Ask me if any of that is unclear, but I just now figured all of this out so I might not have answers to everything.

 

[Adeptus]

In my case I either get asked for a password all the time (even when using databases I didn't secure) or I never get asked for a password (and I'm automatically allowed or denied access).

After you have it secure, make a shortcut to the database, and specify the workgroup file in the command string. (Look up "access commandline")

Then, in Workgroup Administrator, set your workgroup to the default.

That way, you can open unsecured databases without prompting, can open secured databases via the shortcut, with prompting, and still can't open secured databases if you bypass the shortcut.

 

[TastyWheat]

I figured that part out already, but better it was answered and not just left hanging there.