the Access Experts: Custom Database Solutions

About Us


The Secrets of Security

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.


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.


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.


    Copyright © 1998-2011 Wendell Bell & Associates Inc.  Last updated on August 2, 2011

    Email protected by MX Guarddog spam filtering