User Security in Access seems to be one of the topics that mystify people learning
Access, yet it is an important element in deploying multi-user systems, and relies
on some fairly simple concepts. It seems
the devil must be in the details, which we won’t try to cover in this tutorial, but
simply point you to places where you can explore them. So
on to the fundamental concepts.
Fundamentals
The first concept is that of a User. It
identifies the person who has opened the current database – by default it is Admin and
no password is required, so no login prompt occurs. When
the Admin UserID
is given a password, the login prompt always appears. Additional
UserIDs can be created using the Tools/Security/User
and Group Accounts menu. To do
so requires entering a unique SecurityID in addition to the UserID. When
a new UserID is created, the password is blank by default. Passwords
are set using the same dialog box, but can only be set when logged in as that user. UserIDs
with Administrative rights can clear a password for any UserID.
The second concept is that of a Group. By
default there are two GroupIDs, one called Admins,
and one called Users. All
users must always belong to the latter group, while by default, Admin also
belongs to the group Admins. In
addition, new GroupIDs can be created by any user belonging to Admins and
is done using the same dialog box as for users, but on a different tab. Note
that a group does not have a password associated with it. The
concept of groups allows users to be classified in various ways, which is very useful
when working with the next concept.
The third concept is that of Permissions. Access
allows any user who has administrative permissions to change the permissions associated
with that object in the database for any user or group. Objects
have different permissions depending on the type of object, i.e. table, query, form,
report or macro. Note that with Access
2000 and later versions, modules do not have permissions, and can be run and viewed
by anyone. For that reason, a password
is provided for opening a VBA project. Use
the Tools/Security/User and Group
Permissions menu to administer the permissions for a database. By
default, the Admins and Users groups
and the Admin user have all permissions.
The fourth concept is that of Ownership. Each
database object, and the database itself, have an owner user or group account. The
ownership of an object can be changed using the Tools/Security/User
and Group Permissions menu and the second tab of the dialog box, but the owner
of a database cannot be changed. The
concept of ownership is important for two reasons. The
owner of an object can always change the permissions for an object, even if that user
or group doesn’t currently have administrative permissions. In
addition, queries can be set to be run with owner permissions rather than with their
assigned permissions.
Implementation
User Security in Access is implemented in two separate files; one is the
security file, often referred to as the system.mdw or
workgroup file, and the other is the current database. When
Access is installed, it creates an initial security file with the system.mdw name. In
that file, user and group names and security IDs and passwords are stored. Security
IDs and passwords are encrypted in order to provide a reasonable degree of protection
from unauthorized users. The path for
the security file is stored in the registry, and can be modified by the user. In
Access 2002 this can be done by using the Tools/Security/Workgroup
Administrator, while previous versions required the use of a separate program
referred to as the MS Access Workgroup Administrator. That
facility can be used to create a new security file, or it can be used point to a different
security file. Also note that by using
the /wrkgrp switch on the command line starting
Access, you can use a security file other than the default one pointed to in the registry.
While user and group information is stored in the security or workgroup file, permissions
and ownership is stored in the current database. Thus
it is important to keep a database associated with the security file it was created
with. If you do not, you will likely
discover that users have permissions you didn’t intend for them to have. In
addition, you may discover that the database owner is <Unknown>. Note
that when a database is created, its owner is the user that the current session of
Access is running under. If no login
prompt was received, then the owner will be Admin, which means that any person running
the unmodified default security file will be able to administer permissions and ownership.
Why use Security?
There are many reasons for having security active. One
of the most common we encounter is a need to identify who made the most recent changes. If
that is the sole reason for using security, the function CurrentUser( ) gives you
the user name, and it can be stored in a field in each table. The
use of passwords (other than for the Admin user)
can typically be ignored, and permissions for objects don’t need to be changed.
In some scenarios, there is work division between various users, and it is required
that only certain persons perform certain functions. In
those cases, you may want everyone to have a password, and you may want to remove
most permissions for most users. A related
situation is where some people can see some data, and other people can see other data. The
same strategy can be employed in this case.
Finally, you may want to completely lock down a system so that users cannot make any
design changes, and can only add, edit and delete data. In
such cases, you will want to create a custom security file, you may want to create
custom code to manipulate the security information, and you will want to carefully
set only the necessary permissions.
Practical Aspects
Administration of security can be a rather onerous task if you are dealing with
a large number of users, and there is a significant change in staff over time. For
that reason, we normally recommend setting permissions based on groups rather than
individual users. In addition, while
some developers advocate having a separate security file for each database, our experience
suggests that one file typically requires less administrative effort, especially if
the same person uses several databases. We
do however 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. Some
organizations use a batch file during boot-up to copy this file to the local hard
drive – our DBLauncher product does this as a matter of course any time the security
file is updated.
We have also discovered that people have real challenges when they try to completely
lock down a system. There is a fairly
complex set of steps that need to be taken to accomplish a complete lockdown, and
developers typically miss one or more steps in the process. There
are references below to documents that describe exactly what needs to be done if you
really need to lock down a database.
Further Information
This brief set of notes only covers the major points of security. If
you are implementing security for an Access database, be sure to read the following
documents available from the Microsoft Knowledge Base support site:
207793
- Security FAQ Available in Download Center
235961
- Security Manager Add-In Available in Download Center
254372
– Overview of How to Secure a Microsoft Access Database
305541
- Understanding the Role of Workgroup Information Files in Access Security
In addition there are a number of other documents available from
the support site that may be of interest depending on your situation.
209639
– How to Set Security for Modifying Data in a Query Dynaset
209207
– How to Use Command-Line Switches in Microsoft Access
207891
– Can’t Use /Pwd Option with Password-Protected Database
304315
– How to Simulate Column-Level Security in Microsoft Access
325261
– WebCast: The Security Manager Add-In for Access 97/2000
282357
– Error Messages When You Convert .mdw file to Access 2002
278329
– How Do I run the Workgroup Administrator in Access 2002
286327
– Deploying DAP on the Internet or Your Intranet
203887
– How to Clear the Last User Name from the Logon Dialog Box
288960
– Upgrading Secured Databases to 2000/2002
223447
– Can’t Secure Individual Modules in Access 2000/2002
209871
– How to Create a Password Protected Form or Report
Finally you may want to consult one of the books on Access for
more details on the inner workings of User Security, and there is some useful information
available from the Help files.