Problems with Access Security on Network 1

[AcquisitiveOne]

Okay this one has stumped me for a while now. We have a split access database on a Novell network. (The data tables are on the network in and mdb file. The forms, queries, macs are distrubuted to users C: drive in order to speed up the database. The Distrubuted Forms,queries, and macs are linked to the Main Data Tables.) We keep our workgroup .mdw file also on the network for everyone in our workgroup to look at. For some reason when we set permissions in the .mdw file they do not affect other users computers. For example if we allow a certain user to be able to only view data for some reason the changes won't take effect. With his login on one computer he might have full permissions. With his login on a different computer he might have no permissions. With his login in another computer he might have the permissions we set. Could it be that are workgroup .mdw needs to be distributed to the C:\ of the users computers also? Can you not put the .mdw file on the network???? (Even though changes to .mdw would need to be downloaded to individual computers for changes to take effect.) Could it be the Novell Network??? Please help any ideas would be helpful. I have tried many things running out of ideas. Its like the computers are all looking at different .mdw files but I know they are not. They are all viewing the same file on the Network. I checked and double checked. Please Post any ideas. THX

 

[RickSpr]

I agree, it looks like they are all looking at different .mdw files. When you say "they are all viewing the same file on the network" it makes me think that you think that having the .mdw file in the folder makes it active for those users. Nope. They have to "join" that workgroup file to make it active, or they have to use an icon that specifies that workgroup file.

But the fact that they can access your database despite being attached to the wrong workgroup tells me you didn't set up your workgroup file right anyway. You probably made a copy of system.mdw and put your user and group accounts in that, right? Well, you can't ever get a secure environment that way, because anybody with an unmodified copy of system.mdw has Admins and Users groups that have full permissions, and they have an Admin user account that has no password. When they logon, they come in as administrators.

To secure a database, you have a create a totally new workgroup using the Workgroup Administrator, and specify a unique Workgroup ID number for it. Then add your user and group accounts to that, designating at least one user account as belonging to Admins. Make sure you use the same PIDs as you did the first time. Then you have to remove the Admin user from the Admins group, and remove any permissions from the Users group (unless you want to leave some permissions for all users). Finally, give the Admin user a nonblank password and test.

You shouldn't have to re-install the permissions, because those are stored in your database file. But you do need to delete the copy of system.mdw in the network folder.

Your users can then join your workgroup file using the Workgroup Administrator. Because your workgroup file now has its own Workgroup ID, any databases other than yours won't be available while they're attached to your workgroup. This is often an annoyance for users. You can make their lives easier by creating a desktop (or Start Menu) shortcut for them that launches Access with your database, and specifies a workgroup file to be used. Create the shortcut with this command line:
"C:\Program Files\Microsoft Office\Office\msaccess.exe" "path to your database" /wrkgrp "path to your .mdw file"
The path to msaccess.exe might have to be changed depending on where Access is installed on each user's machine. The database and .mdw path can be UNC paths so they're constant for everybody.

Don't feel bad about getting the security setup wrong; it's tricky. In fact, I'm working with another Tek-Tips poster with exactly the same problem right now. Rick Sprague

 

[fredk]

Rick - I hope you have this marked for email notification - I have one question for your regarding your above post (which is the clearest I have read on this issue) - I cannot delete the mdw file on the network because many users of access have files on the drive and I cannot disrupt their access -

If I simply add a shortcut using the path of the file and path for mdw file - can I leave the system.mdw alone - ? Also, if I approach it this way (and I remove admin from the admin group and change the users security to read only - Does that not allow anyone else to change the database? (because everyone other than who I set up would be assigned a user and only have read permissions)

Will that secure the database? Thanks for your help!!!!

 

[lameid]

Rick is mostly right. Although you can't simply get away with using the same PID's for the users and different PID for the workgroup. While I suspect that this will retain the user permissions, the only way to change the OWNER of the DATABASE is to log on as a new owner and import everything into a new file. At least that's true in Access 97, I'd be interested to here about a difference in later versions as I will eventually move up. Because owners have Administrative permission, the file owner can seize ownership of the other objects and set permissions defeating security.

The fundamental key to understanding access security is that it is the PID in the workgroup that controls permission and not the authenication in the workgroup file.
Second most important thing is that the PID for the Users Group and the Admin User are common to all MDW files. Never have any permission assigned to either one of thes unless the intent is to give everone the same permission except for the people using the MDW (perhaps all users have the same permissions and only the Developer has permission to modify the objects).

If you assigned users to the users group in an attempt to limit access to just your users you need to create a new group and assign your users to that and in turn give that group the same permissions you originally assigned the users group (be sure to take care of database ownership first).

FYI: Remember that the PIDs control access and not authenication? Copy your MDW to your hard drive. Change a password. Join the copy of the MDW. Log on. Note that your old password works and not the new one as the MDW's do control authenication. Scary isn't it.

What I do is to create one master MDW with all the groups in it. Then I create an MDW for each group and place it in it's own directory. Each group MDW has the Admin user in the group with no password. I then assign the groups permissions so that users have to be using the appropriate workgroup. I restrict access to the workgroups via network security. Then I use the same method Rick suggested for users to join the appropriate workgroup. This forces authenication via the network which is a little more trustworthy. I bet your thinking "but you said never to assign permission to the admin user you do not want everyone to have." Technically I did not. I only assinged a group and made the admin a member of the group. The PID of the Group has access not the PID of the admin in this case. I hoped this helped and answered your question.

 

[fredk]

Iameid - Thanks for your response and the good information - Funny this is that I thought I marked the thread but did not - I was reviewing another thread and saw that you replied that you answered the security question- Only to find out it was my question you answered.

Thanks again for the good info!

 

[Rhonin]

To All,
Perhaps the most important thing to remember about setting up a secure Access .mdw is making sure that if the designer leaves, following administrator guru's will be able to get into it in the event of corruption. I just went through this with the new company I now work for. The original developer was 1.5 light years gone and the app blew up. it turned out to have bigger issues once the backups were in place and getting around the security took a good bit of work.

Anyway, it works even if I don't like it! (.mdw)

Rhonin
"seppuku.... it's a thought."

 

[hunterlpr]

I have a similar problem - I think - my problem is that everyone joins the workgroup located on the network and it works fine. The problem is I need a station set up that is not on the network just on the hard drive. However, when I copy the workgroup and program to a disk and then copy them both to the hard drive I get a read only message or I get not allowed access. I believe it has to do with the workgroup and need to know how to switch this database - since it is a copy and is in a remote location just to the systems workgroup..since there won't be much technical support or need for much security except for a simple password. PLEASE HELP! Thanks