Getting started with security

[BruceM]

I am once again attempting to get a handle on user-level security in Access
(I am using Access 2003). I have obtained and attempted to comprehend the
MS Access Security FAQ, and have looked at several other guides to
implementing security. The one thing that has become apparent is that any
number of approaches may be valid. Some guides say to use the wizard; other
guides say to avoid them. Some guides say to start by opening any database;
other say to start by opening Access, but no database. I could go on, but
don't see any point to that.

Regarding the FAQ, Microsoft at times advocates approaches shunned by
others. For instance, the FAQ describes setting a database password, but
does not mention that it is a weak security step (which seems to be the
consensus elsewhere). Also, the FAQ metions that "a second bug has recently
emerged" that affects Access 95 and 97 security. This has never been
updated in the FAQ, which tends to make me skeptical about the rest of the
information, which for all I know is similarly outdated.

Bottom line is that I am bewildered by choices before I have completed step
1. Here's what I have so far:
1) Jack MacDonald's paper seems to be a thorough explanation, but is a
little hard to follow since theory and instructions are intermixed. Joan
Wilds's step-by-step document is helpful, as is Lynn Trapp's Ten Steps, for
a point-by-point summary, even though some details differ (which leads me to
the next observation).
2) I can either start by opening Access and no database, or Access and any
database.
3) I should create workgroups, and join them as needed for single sessions;
custom shortcuts can implement this for specific databases.
4) Permissions should be assigned per group; users will have permissions
based on the groups to which they belong.

The eventual plan is to have split databases and individual front ends.

Questions:
1) Should each user's computer have their own copy of the relevant
workgroup information files? I believe this to be so, in which case the
next three questions are relevant.
2) If so, does this file need to be updated for all users if, for instance,
a new user joins a group, or will the old file continue to work for a user
who is unaffected by the change?
3) Even if it is not necessary to update the file for all users, is it
nevertheless recommnded practice?
4) Is the default location for the system.mdw file as good a place as any
for new workgroup information files?
5) Can I rely on the security wizard in Access 2003? Some of the control
wizards continue to use DoMenuItem commands in VBA, so I am moderately
suspicious of the wizards in general.
6) Am I missing anything obvious so far, and is there something I have not
mentioned that will make the process easier?

 

[Lynn Trapp]

3) I should create workgroups, and join them as needed for single

sessions; custom shortcuts can implement this for specific databases.

The recommended practice is to join the default system.mdw file that ships
with Access and use custom shortcuts for all secure databases.

1) Should each user's computer have their own copy of the relevant
workgroup information files? I believe this to be so, in which case the
next three questions are relevant.

NO, there should only be one copy of the secure workgroup information file
and all users of a given database should link to it through the custom
shortcut. Doing what you suggest can lead to an administrative nightmare,
depending on the number of users you have.

2) If so, does this file need to be updated for all users if, for
instance, a new user joins a group, or will the old file continue to work
for a user who is unaffected by the change?

Obviously, I don't recommend that approach, but if you did you should update
all copies so that they match.

3) Even if it is not necessary to update the file for all users, is it
nevertheless recommnded practice?

See Above!

4) Is the default location for the system.mdw file as good a place as any
for new workgroup information files?

NO. Your secure.mdw file should be on a server accessable to all users. the
default system.mdw file is on each user's hard drive.

5) Can I rely on the security wizard in Access 2003? Some of the control
wizards continue to use DoMenuItem commands in VBA, so I am moderately
suspicious of the wizards in general.

From all that I know you can trust the security Wizard in 2003. It's
probably the best wizard for security so far.

6) Am I missing anything obvious so far, and is there something I have not
mentioned that will make the process easier?

Nothing sticks out. Ask more questions if you need.

 

[BruceM]

Thanks for the reply. I have interspersed a few more questions and comments
inline, but I think I am ready to jump back in with the answers you have
already provided. I expect more questions will arise as I proceed, but I
can get started with reasonable confidence now.

The recommended practice is to join the default system.mdw file that ships
with Access and use custom shortcuts for all secure databases.

I think I used terminology incorrectly. I didn't mean the user would join a
group, but rather that the user would go through a particular information
file to open a secured database. The custom shortcut would contain the path
to the appropriate information file, as I understand it. I don't know how
to describe this, but I realize I used incorrect terminology in referring to
it as joining a group, which from what I can tell is administered by a user
with sufficient permissions.

NO, there should only be one copy of the secure workgroup information file
and all users of a given database should link to it through the custom
shortcut. Doing what you suggest can lead to an administrative nightmare,
depending on the number of users you have.

This is the quote I saw by following a link from a site to which I linked
from your site:
"We ... recommend that the security file is copied to each workstation, as
we discovered that corruption of the security file was a common situation
with large numbers of users homed on a single security file located on the
server."
The article went on to suggest that the appropriate file could be copied to
each user's computer by way of a logon script. In my situation I think it
unlikely there will be more than 20 users for a database; in most cases the
number will be considerably less, and more than two or three concurrent
users would probably be uncommon. Even if corruption of the security file
is a "common situation" with a "large number" of users, "common" and "large"
are imprecise terms, so it is difficult to judge the relevance of the
suggestion to my situation.
I don't want to try administering a batch of separate security information
files, so will avoid doing so if using a single (backed-up) file is a
reasonable approach, which it seems to be.
Do I understand that I could use the same security information file for
several databases, or would I do better to create a different information
file if the composition of the groups changes from one database to another?

 

[Lynn Trapp]

This is the quote I saw by following a link from a site to which I linked

from your site:
"We ... recommend that the security file is copied to each workstation, as
we discovered that corruption of the security file was a common situation
with large numbers of users homed on a single security file located on the
server."

I'm not sure what article that came from, but I have never seen a large
degree of corruption to a .mdw file when it was housed on a single server.
My previous company had hundreds of databases linked to the same .mdw file
and I only remember 2 occasions of corruption in the .mdw file in over 7
years. The corruption is easily fixed.

Do I understand that I could use the same security information file for
several databases, or would I do better to create a different information
file if the composition of the groups changes from one database to
another?

Yes, you can use the same .mdw file for as many databases as you want. The
..mdw file stores the authentication information for groups and users. The
permissions are stored in the .mdb file, so the stress on the .mdw file is
minimal.

 

[BruceM]

This is the link (which I discovered by way of a link on Jack MacDonald's
site):
http://www.access-experts.com/default.aspx?selection=TutorialSecurity&sm=18
Perhaps I misunderstood the comments there.

Thanks for clearing up the questions about using a single mdw file. I will
go ahead and do it that way in confidence that it is a reasonable approach.
It has taken a while for me to wrap my mind around the concept of the
security file storing group and user authentication information, while the
actual permissions are stored in the mdb file.

 

[Joan Wild]

If I may add to Lynn's response, you don't really want to have each user
with their own copy of the mdw. Their password is stored in it, and you'll
overwrite their password if/when you copy an updated mdw to their computer.
I too have rarely seen corruption in a mdw that wasn't recoverable (you are
backing it up, right).

 

[Lynn Trapp]

This is the link (which I discovered by way of a link on Jack MacDonald's

site):
http://www.access-experts.com/default.aspx?selection=TutorialSecurity&sm=18
Perhaps I misunderstood the comments there.

Thanks for clearing up the questions about using a single mdw file. I
will go ahead and do it that way in confidence that it is a reasonable
approach. It has taken a while for me to wrap my mind around the concept
of the security file storing group and user authentication information,
while the actual permissions are stored in the mdb file.

I've never seen that site before, but it looks quite accessible. They do
advertise a product called DBLauncher that might be helpful, but I still
don't know where they get their information about corrupt .mdw files. That
does not accord with my first hand experience in working with hundreds of
Access databases hitting the same .mdw file.

Holler back if you have further questions.

 

[BruceM]

I just realized that the link to Jack MacDonald's site that I referenced
earlier in this thread was from your site, not from Lynn's. I'm just trying
to be as accurate as is possible, even if the earlier inaccuracy is probably
not all that significant.
Thanks for pointing out about the password being stored in the mdw file. I
had overlooked that. I back up regularly, sometimes several times a day
when working on a project.

 

[BruceM]

There's definitely some good stuff ont he site. My best guess regarding
copies of the mdw file is that the idea is to have users set their passwords
using the file on the server, then to copy it each day to each individual
computer, so that passwords are not lost, but I am comfortable with the
general consensus that corruption of the mdw file on the server is not a big
concern, and that even if it occurs recovery needs not be a big deal.

 

[Joan Wild]

I'm just trying to be as accurate as is possible, even if
the earlier inaccuracy is probably not all that significant.

While we do hammer pretty hard about following every step and in order,
there are some subtle changes in order that won't matter. However, most
people get security wrong, and it's because they skip (what turns out to be)
an important step, or do things in the wrong order.

It sounds to me that you have a good handle on how security works. Once you
have it down and understand it, you'll recognise that these don't matter.

As far as using a single secure mdw or copying to the users, if you
understand where/when it matters then either method is OK - there is no
'right' answer. If you lock down the database so that users can't get to
the 'change password' dialog, or you don't provide them with a method to
change their password, then copying the secure mdw to their local computer
won't matter.