Security and Deployment configuration issues

[Ayat]

Hello,

I have followed the very informative postings here on security and deploying a multi user access application and have started doing that myself. A lot has worked but there are a few things that are not working out right and I just wanted to get some clarification on that. Hopefully this will benefit others in my situation as well.

1. First, I put security into the database by creating multiple users some with Admin and some with user priviledges.

2. I created a different workgroup file (product.mdw)

3. I then split the db to front and backend. I used UNC to nk tables from FE to BE

4. I put the BE on a shared network folder along with product.mdw. The reason is I am still developing the app and may also need to tweak the user permission settings, so thought of putting teh .mdw file on a server rather than putting it on each users machine for now. I am then using a shortcut on each user's machine that references this .mdw file on the server

5. I also have the FE on the server for now and created a shortcut on each user's desktop (I am doing this for now so that I donot need to put the FE on each users machine every time I change something. this is temporary for a few weeks till I finish development. After that I plan to put the FE on each user's machine)

The problem I am facing is that after putting the shortcut in a user's machine, if the user even wants to open any local Access db on their machine (not my app), it is now asking for a UID and passwd.

Now, when I put the shortcut on the user's desktop (to repeat, I referenced the FE, and .mdw file on the network folder in the shortcuts Target settting), should I have done anything else on the user's machine. After just putting the shortcut on the desktop, the user could click on it and go directly into my database without any UID and passwd though I have security in my db. The I went to the user's Access install and changed the workgroup file to the one on the network folder (through Tools - Security -Workgroup Info ...). Was that necessary because I had alsready put in all the necesary parameters in the shortcut's Target setting?

I guess I am missing someting about how the .mdw file associates with Access. When you define a .mdw file in the Tools - Security - Workgroup ,..) setting, does that associate with a particular DB or for any db that this Access install brings up? If so, then how does one get aorund the problem of deployment to user's desktop where the users could be having their own local Access DB's that they use without a password but need to use a password to access a DB on the network. I cannot imagine that because I require the user to use a password for my db on the network means that I have to tell them that they need to use a password for any DB they use in their machine from now on.

Please let me know where I am going wrong. Thank you

 

[The_Doc_Man]

This is one of the common misunderstandings about Access Workgroups, and I don't think it is your fault for missing it. This problem is really a side-effect of U.S. government regulations about how security must be handled in order to be eligible for use in government settings. Not that Access is so secure that the goverment trusts it for much. But it does make a nice front end for many other DB products, so it is (barely) tolerated.

The thing is, you have to join a workgroup to use Access security. But you can't normally join the workgroup once Access has already started, because of the way your current workgroup is determined.

See, what the WRKGADM program (or whatever its silly name is) does when you join a workgroup is it writes a REGISTRY entry telling all other MS Office programs on THAT ONE COMPUTER what workgroup you are using at the moment. But there is only one "slot" for that entry and it must be loaded before you open any .MDB file. You have to do it that way 'cause Access only looks up that name once. If you go back and reset the workgroup file name, it is too late for the open copy of Access to notice it.

So your users, if they DON'T join the workgroup, use the "SYSTEM" workgroup on their machines. And by default, they are the ADMIN account in that workgroup. So if you have anyone who can get into your DB without joining the workgroup, that means you didn't block the account named "ADMIN" adequately. That's step one.

I always create another account for administration, make it a member of the ADMINS group (note the "S"), and then remove ADMINS group from the ADMIN (no "S") account. I also make individual user-role groups for each role, after which I remove nearly all permissions from the default USERS group. Or is that "USER" - oh, well, whatever it is, I allow ADMIN to be a user but not an ADMINS group member. Since I gutted the USERS group, coming in as ADMIN doesn't do much good.

OK, so you block your ADMIN account in the target DB. So now your users want to use another DB. To do so, they have to join a different workgroup, such as the default SYSTEM workgroup on their own machines. If your users don't go back afterwards, their registry entry still points to the workgroup with the extra security, not the "default" workgroup of SYSTEM.WKG created by installing Office. Which means that the UID/PWD combo they must use now comes from a DB unrelated to the one holding their non-shared data i.e. the one they try to change to. And that is 'cause the registry says so.

If you can distribute an icon for your DB and everyone uses the same drive letter for that share drive, you can look up "Command Line" in Access Help and find the option that lets you add the correct workgroup file in the command line. Which happens to be in the "properties" dialog box that comes up when you right-click the icon. You can edit that displayed command line to include the .WKG specifier, which makes Access ignore the registry and use the command-line setting instead. Then your users must be given a copy of the modified icon that points to the right places. Because of this, it is not merely a good idea to have the WKG file in the same folder as the MDB file, it is perhaps one of the few ways to retain your sanity. Otherwise, you have such a maintenance nightmare as to cause you to drool incessantly and murmur to yourself.

 

[Laurentech]

Keep your secured mdw file on the server. On the user's desktops, make an icon pointing to the FE on the user's machine, and include the /wrkgrp stuff pointing to the mdw on the server in the command line.

I have done some quick testing in the past, and found that if you map a drive on the user's machine to the shared network folder and link your tables to that instead of linking to a UNC address the response is much faster. I went from over a minute to display a form to just a few seconds.

Larry