Replacement for Workgroup administrator

Question

Since MSAccess 2010 no longer supports system.mdw, is there a good example of a system of coding out there that I can use to replace it? I am interested in group permissions for forms and tables (read only vs. readwrite) in my distributed .mde.  I have a user table, and my thought was to create a user level number for each user.  If the user level is 1, readwrite.  If the user level is 2, read only.

BUT, tt would not be a blanket statement...even though user level is 1, not ALL forms/queries/tables would be readwrite...Just some.

Would I have to write code for every object's on open property? Or could I have a table of objects (forms, tables, queries) and somehow cross reference it?

I doubt I am reinventing a wheel here. There must be something out there already. Any guidance would be greatly appreciated.

Thank you, and Happy New Year!

Answer 1

I have to agree with the others. Any solution is going to require code. A lot of the code can be boiler plate or reference functions that can be passed parameters. So applying the code can a simple matter of copy and paste.

For the complex set of rules you seem to require, you will have to create a matrix of permissions in a table. But, if that level of permissions are required, I wouldn't use Access.

Answer 2

> Is the only way to do this to call code from each and every on open property of every object?

All my Active Directory code needs is a single line of code in each form_open event. Assuming you have 100 forms, the copy/paste will likely take about 300 seconds. Is that really worth searching for a better way?

If you say Yes, then I would suggest you study the WithEvents keyword and Form objects. Not sure there is a solution there, but that's where I would be looking.

Answer 3

As Scott Diamond says in the article at the link I posted, "….it should give you enough to expand on for more complex needs".  So it's down to you to build on his basic methodology.  The code he cites in the article relies on the 5th column of the login combo box to determine the user's access level, so is very limited.  To vary a user's Access levels to different objects, and conversely an object's to different users you could model the many to many relationship type between users and objects in a table with a composite key of UserID and ObjectName columns or similar, and a non-key column AccessLevel.  When a form is opened the code would look up the Accesslevel value in the row in this table for the current user, obtained from the login form, and the current form and by means of a Select Case statement, assign the values to the relevant properties.  Bear in mind, though that the AllowEdits property is a very blunt instrument , and locks all editable controls in the form, including things like unbound navigational combo boxes.  I would not use it, but would tag the bound controls and set their Locked and Enabled properties in a loop through the form's Controls collection.

I'm really just thinking aloud here.  I've never done any of this, and never would.  If I wanted security I would not be using Access.  Whatever you do it will never be a truly secure environment.  The sort of measures described in Scott Diamond's article are very easily subverted by a moderately knowledgeable user.  User and group security was dropped because it just wasn't secure.

Answer 4

Thanks Ken.

It looks from this example like I have to code each and every form's on open property.

Is the only way to do this to call code from each and every on open property of every object?

I am trying to figure out an  efficient way to define and evaluate each object...

For example...

tblObjectRights:

  ObjectName (text)

  UserGroup (1=ReadWrite; 2=ReadOnly) (long integer) (linked to tblUsers)

  What Is Allowed (1=ReadWrite; 2=ReadOnly) (long integer)

See, even though an object's UserGroup may be 2 (readonly), this Particular Object may allow 1 (readwrite)

Then perhaps there an example of an efficient way to use this in a Public Function that is called from each object's onopen event.

Again, I feel the wheel has already been invented and just looking for a treasure map...

Answer 5

Take a look at Tom van Stiphout's Access security blog at:

http://www.accesssecurityblog.com

Two methods are described, using VBA or Active Directory.