Microsoft Access security is implemented in two parts:
- Each user and group has a unique security ID (SID) code.
- That SID code is stored in the database along with the associated
permissions for that SID.
The next two sections give the details.
Each User and Group Has Unique Security ID (SID)
In Microsoft Access, each User and Group has a security ID (SID). The SID
is a binary string that uniquely identifies the User or Group. When a user
logs on, whether from the logon dialog in Microsoft Access or from code in
Visual Basic (illustrated later in the article), the Microsoft Access
engine reads from the MSysAccounts table of the SYSTEM.MDA database. This
database is created only by Microsoft Access and a new (empty) one will be
created if the original copy is deleted.
NOTE: If the original SYSTEM.MDA is accidentally deleted, all the unique
SIDs are lost. Therefore, all ability to gain access to protected databases
is also lost. Therefore, it is a good idea to back up both the database and
the SYSTEM.MDA file in place when the permissions were set on the database.
When logging on, the user supplies the user name (not case-sensitive) and
the password (case-sensitive). If the user name and password are correct,
the SID of the user is retrieved and saved in a structure internal to the
engine. The password is used only to validate the user. From this point on,
once the user becomes a validated user, the password has no effect on
Here is a key point that pertains to Visual Basic's behavior. By default,
the Microsoft Access engine attempts to validate the user and password of
Admin and "" respectively. Visual Basic version 3.0 will, without any code,
send this key combination to the Microsoft Access engine by default. This
means that, even without the use of the Visual Basic security-related
statements, the Visual Basic program will gain admission to the database,
if the user "Admin" of Group Admins has not had its password changed from
the default of none ("").
Once logged on, the user's SID is retrieved. This SID is used for all
subsequent operations within the Microsoft Access engine.
The SID Is Stored in the SYSTEM.MDA Database
The SID is stored in the database itself. Therefore, all permissions
granted to a particular User or Group are also stored in the database,
associated with the unique SID.
This brings up another key point pertaining to Visual Basic's behavior. The
Visual Basic program will gain entry to the database and have full
permissions, seeming to ignore the Microsoft Access security mechanism if
either of the following is true:
- The Visual Basic programmer has not taken the location of the SYSTEM.MDA
database into account in the program code.
- The User "Admin" has not had its password altered from the default of
This occurs because of the default behavior of both the Microsoft Access
engine and Visual Basic. The combined effect is to allow entry to the
database and its objects by the Visual Basic code.
The list of the object types in Microsoft Access are: Table, Query, Form,
Report, Macro, and Module. Of these, only the first two are accessible from
Visual Basic code, so the others can be omitted from this explanation.
The following two sections explain each of the two Visual Basic security-
related statements (SetDataAccessOption and SetDefaultWorkspace). The two
statements are designed to provide a choice of SYSTEM.MDA files and logon
entries to an Microsoft Access database, with security set by Microsoft
Access. Following these two sections is a section that relates the two
statements to the behavior of the Microsoft Access engine with regard to
SetDataAccessOption Statement -- Syntax and Behavior
SetDataAccessOption has the following parameters:
SetDataAccessOption option, value
option is a numeric value with only one legal value (1).
SetDataAccessOption 1, "E:\VBPROJ\MY.INI"
In the DATACONS.TXT file supplied at the root of the \VB directory, a
constant is defined for this value:
Global Const DB_OPTIONINIPATH = 1
SetDataAccessOption sets the name and path of your application's
initialization (.INI) file. The application's .INI file takes effect only
when SetDataAccessOption is used before the data access functionality is
loaded and initialized. Once data access has been initialized, this setting
cannot be changed without first exiting the application. The value is a
string expression. For the DB_OPTIONINIPATH option, the value argument
contains a string expression providing the path and name of your
application's initialization (.INI) file. Initialization files are usually
stored in a user's \WINDOWS directory, and have the same name as the
executable file but with a .INI extension. Use this statement only if your
application's initialization file has a different name or is in a directory
other than the \WINDOWS directory.
The SetDataAccessOption statement is not needed when you run the Visual
Basic project in the VB.EXE environment if the VB.INI file (in the \WINDOWS
directory) contains the following lines:
NOTE: the actual location of the SYSTEM.MDA is not significant provided
both Microsoft Access and Visual Basic have an entry pointing to the
SYSTEM.MDA they will share. The SetDataAccessOption statement is not
required if the application .EXE file has its own .INI file in the \WINDOWS
and the .EXE and .INI files share the same name.
SetDefaultWorkspace Statement -- Syntax and Behavior
SetDefaultWorkspace has the following parameters:
SetDefaultWorkspace username, password
If this statement is left out, Visual Basic will send the equivalent of
the following line to the Microsoft Access database engine that is included
with Visual Basic:
SetDefaultWorkspace "Admin" , ""
This statement has the effect of obtaining a valid SID and gaining entry to
all the Table and Query objects in the database.
Relationship Between Visual Basic and Microsoft Access Security
To understand the relationship between Visual Basic and Microsoft Access
security, you must understand the Microsoft Access security mechanism.
Here's a detailed explanation for the benefit of the Visual Basic
programmer who has not used Microsoft Access extensively. There is a
hierarchy of permissions in Microsoft Access. At the top level,
there are Groups. Contained within a particular Group are Users. To
grant permissions selectively to particular Users, all permissions must
first be deselected or removed from the Users' Group. Then and only then,
can permissions be granted or revoked for individual Users.
Permissions listed for an individual User are called Explicit permissions.
Permissions set for the Group containing the User account are called
Implicit permissions. Implicit permissions take priority over Explicit
You can use the Security menu to set permissions in Microsoft Access after
a database has been opened and the user has logged on. From the Security
menu, choose Permissions to assign permissions on each object in the
database, which in Visual Basic means Table and Query objects only.
For example, if there was a Group in the Microsoft Access database named
Analysts containing the Users Bob and Sue and you want to limit Bob to
Read Data only and grant Sue Full Permissions, follow these steps:
- Log on to Microsoft Access as a User in the Admins Group. For example,
enter Admin or Fred.
- From the Security menu, choose Permissions (ALT S P).
- Table objects are the default type. Select the name of the table you
want to set permissions on. For example, select TestTbl.
- Set the option in the User/Group frame to Groups. Then click the combo
box list down and click Analysts to select that Group.
- Clear all check boxes to revoke all permissions for the entire Group.
- Change the List option button back to Users and select Bob. Clear the
check boxes for all of Bob's permissions.
- Select Sue from the list, and check the Full Permissions check box.
- Click the Assign button to apply the changes to the table.
At this point, assume you have a Visual Basic program containing the
following code in the form load event:
Sub Form_Load ()
Dim db As database
Dim ds As dynaset
Dim scenario as integer
scenario = 'insert a value between 1 and 4 here
select case scenario
' Do nothing
SetDefaultWorkspace "bob", "leftout"
SetDataAccessOption 1, "E:\VB.INI" ' not in \WINDOWS directory
SetDataAccessOption 1, "E:\VB.INI" ' not in \WINDOWS directory
SetDefaultWorkspace "bob", "leftout"
Set db = OpenDatabase("E:\DATACON\BASES\ACCESS11\ASAMPLE.MDB") ' point 1
Set ds = db.CreateDynaset("TestTbl") ' point 2
autoredraw = True ' to make Print statement persist on the form
Print ds(0), ds(1)
Here are several scenarios to illustrate the relationship between Visual
Basic and Microsoft Access Security:
SCENARIO ONE: In this case, there is no reference to the location of the
SYSTEM.MDA file. Windows and the Microsoft Access engine are unable to find
the .INI file with the [Options] section listed previously in this article.
Therefore, the SYSTEM.MDA is ignored and Visual Basic defaults to its
default user and password combination ("Admin", ""). However, previously,
the default password for the User Admin was changed to something other than
"". In addition, all permissions were revoked for the Group Admins and the
User "Admin" in the Admins Group. Therefore, the following Visual Basic
error occurs at point 2:
Couldn't read; no read permission for table or query 'f))'
You have closed the back door to Visual Basic and any Visual Basic
application attempting to bypass the logons in the SYSTEM.MDA file.
SCENARIO TWO: In this case, because you invoke the SetDefaultWorkspace
statement without having any pointer to the SYSTEM.MDA file, the Visual
Basic Microsoft Access engine hunts for the SYSTEM.MDA file and, not
finding it, gives the following error at point 0 in the code:
Couldn't find file 'SYSTEM.MDA'
NOTE: The errors that occur in both Scenarios one and two are the same as
would occur if the SYSTEM.MDA file was moved, renamed, or deleted.
SCENARIO THREE: In this case, you tell the Visual Basic Microsoft Access
engine where the SYSTEM.MDA file resides but don't supply a user and
password combination. Therefore, again, Visual Basic supplies the only user
and password combination it knows ("Admin", ""), which is no longer a valid
combination because you added a password to the Admin User account. As a
result, Visual Basic gives the following error at point 1 in the code:
Not a valid account or password.
SCENARIO FOUR: In this case, you supply both parameters correctly.
Therefore, because you gave Bob "Read Data" permission as well as "Read
Definitions" to allow the Visual Basic Microsoft Access engine to read,
the Visual Basic application prints the first two fields in the first
record of the table named TestTbl.
If you repeated the four scenarios with the User Sue, all would be the
same. However, Sue could go further and modify the table structure and the
data as well. Remember, you first selected the Group analysts and revoked
all permissions. Then you added back all permissions to Sue, but only Read
Data and Read Definitions were added back to Bob.
NOTE: The Admins Group has special significance with regard to security.
This applies to any User in that Group. The Admins group's SID is stored in
the SYSTEM.MDA when a database is created. As a result, the Admins group
will always have permission to change the permissions on all objects in
that database. This permission cannot be taken away by anyone. This
permission remains even when all permissions have been revoked from the
Admins Group, and it is not displayed in the Permissions dialog. This is
another reason to keep a backup and keep track of which SYSTEM.MDA was in
use when the database was created.
With OwnerAccess Option in a SQL Query
One last point of possible confusion revolves around the use of the
following phrase in a SQL query:
... With OwnerAccess Option
For example, look at this code:
Sub Form_Load ()
Dim db As Database
Dim qd As querydef
Set db = OpenDatabase("C:\ACCESS\DB1.MDB")
' Enter the following two lines of code as one, single line:
Set qd = db.CreateQueryDef("myQD", "select * from [TableDetails]
with owneraccess option ;")
This code results in this error:
This is because OwnerAccess refers to the owner of the database. The owner
is the creator of the database. In other words, OwnerAccess refers to
the owner's user and password combination (unique SID) that is stored in
the database (BD1.MDB in this case). However, the code does not contain the
two statements needed to point to the SYSTEM.MDA file of a secured
database. Actually, in this case, only the SetDefaultWorkspace statement is
essential if the compiled .EXE file's .INI file containing a valid
[Options] section, is in the \WINDOWS directory.
The code uses the backdoor. It has not supplied the unique SID of the
database owner to the engine, so the engine doesn't know the default name
and password combination (Admin, "") of the user is the database owner.
Even if it turns out that the User Admin is the database owner, without
having read the SYSTEM.MDA file, the engine cannot verify that fact, so
it gives the error.
Notes for Microsoft Access Version 2.0 users
Using the recently released Microsoft Jet 2.0/Visual Basic 3.0
Compatibility Layer, Visual Basic can gain access to Microsoft Access
version 2.0 databases. Below are some notes to help you convert a secure
version 1.1 database into Microsoft Access version 2.0 format.
If a version 1.x database is secured, it will remain secure whether you
open it with Microsoft Access version 1.x or 2.0. However, Microsoft Access
version 2.0 cannot be used to change or add permissions in the database,
even by the administrator, until the database is converted to version 2.0.
When you install Microsoft Access version 2.0, it creates its own workgroup
file (SYSTEM.MDA). If Microsoft Access version 2.0 is installed in the
same directory as version 1.x, the version 1.x SYSTEM.MDA file will be
To make changes to the security of a converted database, you must use a
version 2.0 SYSTEM.MDA that has identical groups and users (and identical
PIDs) as the original SYSTEM.MDA.
NOTE: PIDs (Personal IDs) in Microsoft Access version 2.0 are the
equivalent of PINs (Personal ID Numbers) in version 1.x
To Create a Secure Workgroup:
- Use the 2.0 Workgroup Administrator tool to create a new workgroup.
This is a Version 2.0 SYSTEM.MDA file.
- Re-create all the users and group accounts using the same names and PID
numbers that were used in Microsoft Access version 1.x.
To Convert a Secure 1.x Database to 2.0 Format:
NOTE: In a secure workgroup, only users with Modify Design permissions to
all of the objects can convert a version 1.x format to version 2.0 format.
Also, you must assign Modify Design permissions to the version 1.x database
in Microsoft Access version 1.x using the version 1.x workgroup.
- Make sure no one is using the version 1.x database.
- Log on to Microsoft Access 2.0 as a member of the Admins group who is
not the Admin user.
- From the File menu, choose the Convert Database command.
- Select the version 1.x database you want to convert. You will be
prompted for the version 2.0 database name.
NOTE: The Convert Database command will force you to choose a new name
for the database. This lets you keep a backup copy of your version 1.x
database, as once you have converted a database from version 1.x to
version 2.0 you CANNOT convert it back to version 1.x.
- Have your users join the new version 2.0 workgroup (SYSTEM.MDA) by
using the Workgroup Administrator tool.
NOTE: You can also accomplish this by modifying the MSACC20.INI file
in your Windows directory. In the [Options] section of the file,
change the SystemDB entry to point to the version 2.0 SYSTEM.MDA
file. The [Options] section of the file will be similar to the example
SystemDB=<microsoft access path>\SYSTEM.MDA
Key Points to Remember
- Only Microsoft Access can create and modify the SYSTEM.MDA file.
- The SYSTEM.MDA file contains the unique SID used in a database with
permissions to sort out who is who for the Microsoft Access engine to
enforce those permissions. The SID is obtained by supplying the
Microsoft Access engine with a valid user and password combination,
from which it obtains the unique SID that the engine stores in memory
to enforce security on an open database.
- Both Microsoft Access and Visual Basic need to be pointed to the
location off the SYSTEM.MDA file in order to gain entry to databases
that have security and permissions implemented.
- There is a back door available to the Visual Basic application program
if the password for the default User in the Admins group (named Admin)
is not changed from the default none ("").
- If the phrase "With OwnerAccess Option" is used in the SQL query of a
CreateQueryDef, CreateDynaset, or CreateSnapshot method, a pointer to
the SYSTEM.MDA file must exist. Even if you are using the back door
(the default user and password combination of Admin and "") and don't
seem to need the SYSTEM.MDA, when you use "With OwnerAccess Option" in
a SQL query, the engine must see the SYSTEM.MDA file to match the SID of
the owner (creator) of the database to the user who logged on.
- The valid logon user and password combinations are stored in the
SYSTEM.MDA file but the permissions are stored in the database (.MDB
file) itself. A unique key (the SID) is extracted from the SYSTEM.MDA
by using a valid user and password combination, supplied to the
Microsoft Access engine by the logon dialog in Microsoft Access or
by the code in Visual Basic.