How Does Access Security work? (written for access 2000)
MsAccess is not a database
In order to understand Access security it is a good thing to understand that there is a difference between Access the program and Access the database. Usually nobody makes a distinction between the two, but doing so makes things easier to understand. The program (Msaccess.exe) has no security what so ever. However, you need the program to create databases and it will enable you to secure a database. In order to do this, it uses a separate database with a different extension (.mdw instead of .mdb)
So Access security always consists of two databases. One that contains all the objects that you have created (tables, forms etc, I will refer to it as the Objectdatabase) and one that contains information on all the users that can Access the Objectdatabase and all the rights that they have on the objects in the Objectdatabase. This is the workgroup file or workgroup database. To make a distinction between the two, Microsoft has changed the extension of the security database to mdw (instead of mdb).
There are several names for the workgroup file in use: workgroup file, workgroup database, mdw file or mdw database, they all mean the same thing.
Access security is always on (but not active), even if you don’t see it.
By default, when you start MsAccess.exe no mdw file is used (You can open Access without a database, try it). However when you create a new database or open a existing one, the default mdw file will be used to check the security rules (if any). This default mdw file is standard system.mdw which comes installed with Access/Office. It is located on the c drive (or whatever drive MsOffice/MsAccess has been installed on). If you delete the system.mdw and start MsAccess and open or create a database, a new system.mdw will be created. In other words: An Access database (*.mdb) needs an .mdw database.
In this mechanism Msaccess.exe is a member of a workgroup file. As said standard this is system.mdw but you could change this to a different one. There is a program named wrkgadm.exe to change your membership (I’ll get into that later on), and in later versions this program is integrated in access.exe.
Why do I say that security is always on but not active? Because you have to activate it by setting the password of the Administrator account to something else than blank. By default every user is Administrator (Access is built this way) A blank password means that security is not active and there are no questions asked.
! It is essential to understand that without special security you or anybody that uses the same computer logs in as administrator. Usually this is not what you want (if so please stop reading) so we’ll have to prevent people from logging in as administrator.
Below there are several steps listed that you’ll have to follow, but first some more explanation.
The role of the MDW file
As mentioned the workgroup file contains information on the rights on objects in one (or more ) database(s).
You can control which mdw file is being used. There are two ways to do this:
1. Tell the program to join a certain workgroup file (instead of the default system.mdw).
2. Tell Access to open a certain database with a certain workgroup file
In the first case all your databases will be using the same mdw file. If you have many users they al must be able to Access this mdw file. It would not be very practical to use the system.mdw on your hard drive for this, but if stored on shared folder on an accessible server it would be fine. I would not advise you for this strategy however, unless you have only one group of not to many users that will be using not to many secured databases. This because this one mdw file will hold all security information for these people and databases. You probably can understand that in larger organizations with several groups of people and several databases it is better for each group to have their own mdw file (hence the name workgroup).
The 2nd approach uses shortcuts. Instead of having a shortcut simply point to the database, you also have it say were your Access program is and which mdw file is to be used. This is an example:
