MS Access User And Group Permissions - Changes Not Propagating - Multiple Users MDE Front End - Network Back End

1
votes

The setup:

  • X users each using an instance of a MS Access front end MDE which connects to a single back end MDB.
  • User-Level access/permission controls in place (e.g. Users, Trusted-Users, Admins).
  • All changes are made via the Tool->Security->etc. MS Access menu options.
  • All security is stored in a single secure.mdw file, to which all MDB instances connect.

The problem:

  • When modifying a particular group's permissions (e.g. Trusted-Users) - say, from being allowed to modify queries, to not be able to modify queries - the change works on the MDB instance the change was made (as expected), but not on the other instances (i.e. MDB instances on the other PCs). Basically, after the change is made, if we then go to one of the other instances and log in as an Admin, we see that the permissions for the group (Trusted-Users) have not changed.

  • Interestingly (back on the first MDB instance), if we move a user out of one group and into another group, then move to another MDB instance, we now see this change HAS propagated to all other instances - thereby indicating they do point to the same MDW file.

Questions:

  • Is it the case that modifying a group's permissions on one MDB instance does NOT propagate to ALL MDB instances (even though they point to a single MDB file)?

  • If yes, does this mean we must then re-install EVERY MDB instance after making a change to a group's permissions?

  • Or instead, is our approach incorrect? Should we modify group permissions in a different way?

If you need any further information let me know.

Many thanks.

1
securityms-accesspermissions
When testing, did you make sure the "other" users have closed Access and restarted the app ? - Patrick Honorez
Seconded, @iDevlop. It sounds like there needs to be a "push" of sorts to get the other users to inherit the new permissions. Restarting their instance of Access may help. If it does not, may I suggest creating a "dummy" user which you change groups of during updates? It's not the best practise but if it resolves the bug... - StuckAtWork
@iDevelop, yes the other users had closed their instance. Thanks. - 41st

1 Answers

1
votes

"All security is stored in a single secure.mdw file, to which all MDB instances connect."

The workgroup information file (MDW) stores only the definitions of users and groups. Database object permissions are stored in the database which contains those objects. And the database object permissions reference the users and groups defined in the MDW.

So the permissions for your queries are stored in the MDE files. Changing permissions in one MDE has no effect on the other MDE copies.

You can do one of the following:

  • distribute updated MDE version to your users
  • execute DDL statements (GRANT/REVOKE) by administrative user in each MDE

"Interestingly (back on the first MDB instance), if we move a user out of one group and into another group, then move to another MDB instance, we now see this change HAS propagated to all other instances - thereby indicating they do point to the same MDW file."

In that case, you changed the definition of that user. Her permissions will now be those granted to her group membership in the current MDE.