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 SIDis a binary string that uniquely identifies the User or Group. When a userlogs on, whether from the logon dialog in Microsoft Access or from code inVisual Basic (illustrated later in the article), the Microsoft Accessengine reads from the MSysAccounts table of the SYSTEM.MDA database. Thisdatabase is created only by Microsoft Access and a new (empty) one will becreated if the original copy is deleted.
NOTE: If the original SYSTEM.MDA is accidentally deleted, all the uniqueSIDs are lost. Therefore, all ability to gain access to protected databasesis also lost. Therefore, it is a good idea to back up both the database andthe 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) andthe 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 theengine. 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 onsecurity.
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 ofAdmin and "" respectively. Visual Basic version 3.0 will, without any code,send this key combination to the Microsoft Access engine by default. Thismeans that, even without the use of the Visual Basic security-relatedstatements, the Visual Basic program will gain admission to the database,if the user "Admin" of Group Admins has not had its password changed fromthe default of none ("").
Once logged on, the user's SID is retrieved. This SID is used for allsubsequent 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 permissionsgranted 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. TheVisual Basic program will gain entry to the database and have fullpermissions, seeming to ignore the Microsoft Access security mechanism ifeither 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 none ("").
This occurs because of the default behavior of both the Microsoft Accessengine and Visual Basic. The combined effect is to allow entry to thedatabase 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 fromVisual 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 twostatements are designed to provide a choice of SYSTEM.MDA files and logonentries to an Microsoft Access database, with security set by MicrosoftAccess. Following these two sections is a section that relates the twostatements to the behavior of the Microsoft Access engine with regard tosecurity.
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, aconstant is defined for this value:
Global Const DB_OPTIONINIPATH = 1
SetDataAccessOption sets the name and path of your application'sinitialization (.INI) file. The application's .INI file takes effect onlywhen SetDataAccessOption is used before the data access functionality isloaded and initialized. Once data access has been initialized, this settingcannot be changed without first exiting the application. The value is astring expression. For the DB_OPTIONINIPATH option, the value argumentcontains a string expression providing the path and name of yourapplication's initialization (.INI) file. Initialization files are usuallystored in a user's \WINDOWS directory, and have the same name as theexecutable file but with a .INI extension. Use this statement only if yourapplication's initialization file has a different name or is in a directoryother than the \WINDOWS directory.
The SetDataAccessOption statement is not needed when you run the VisualBasic project in the VB.EXE environment if the VB.INI file (in the \WINDOWSdirectory) contains the following lines:
NOTE: the actual location of the SYSTEM.MDA is not significant providedboth Microsoft Access and Visual Basic have an entry pointing to theSYSTEM.MDA they will share. The SetDataAccessOption statement is notrequired if the application .EXE file has its own .INI file in the \WINDOWSand 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 ofthe following line to the Microsoft Access database engine that is includedwith Visual Basic:
SetDefaultWorkspace "Admin" , ""
This statement has the effect of obtaining a valid SID and gaining entry toall 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 Accesssecurity, you must understand the Microsoft Access security mechanism.Here's a detailed explanation for the benefit of the Visual Basicprogrammer who has not used Microsoft Access extensively. There is ahierarchy of permissions in Microsoft Access. At the top level,there are Groups. Contained within a particular Group are Users. Togrant permissions selectively to particular Users, all permissions mustfirst 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 calledImplicit permissions. Implicit permissions take priority over Explicitpermissions.
You can use the Security menu to set permissions in Microsoft Access aftera database has been opened and the user has logged on. From the Securitymenu, choose Permissions to assign permissions on each object in thedatabase, which in Visual Basic means Table and Query objects only.
For example, if there was a Group in the Microsoft Access database namedAnalysts containing the Users Bob and Sue and you want to limit Bob toRead 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 thefollowing 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 case 1: ' Do nothing case 2: SetDefaultWorkspace "bob", "leftout" case 3: SetDataAccessOption 1, "E:\VB.INI" ' not in \WINDOWS directory case 4: SetDataAccessOption 1, "E:\VB.INI" ' not in \WINDOWS directory SetDefaultWorkspace "bob", "leftout" end select 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)End Sub
Here are several scenarios to illustrate the relationship between VisualBasic and Microsoft Access Security:
SCENARIO ONE: In this case, there is no reference to the location of theSYSTEM.MDA file. Windows and the Microsoft Access engine are unable to findthe .INI file with the [Options] section listed previously in this article.Therefore, the SYSTEM.MDA is ignored and Visual Basic defaults to itsdefault 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 theUser "Admin" in the Admins Group. Therefore, the following Visual Basicerror 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 Basicapplication attempting to bypass the logons in the SYSTEM.MDA file.
SCENARIO TWO: In this case, because you invoke the SetDefaultWorkspacestatement without having any pointer to the SYSTEM.MDA file, the VisualBasic Microsoft Access engine hunts for the SYSTEM.MDA file and, notfinding 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 aswould occur if the SYSTEM.MDA file was moved, renamed, or deleted.
SCENARIO THREE: In this case, you tell the Visual Basic Microsoft Accessengine where the SYSTEM.MDA file resides but don't supply a user andpassword combination. Therefore, again, Visual Basic supplies the only userand password combination it knows ("Admin", ""), which is no longer a validcombination because you added a password to the Admin User account. As aresult, 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 "ReadDefinitions" to allow the Visual Basic Microsoft Access engine to read,the Visual Basic application prints the first two fields in the firstrecord of the table named TestTbl.
If you repeated the four scenarios with the User Sue, all would be thesame. However, Sue could go further and modify the table structure and thedata as well. Remember, you first selected the Group analysts and revokedall permissions. Then you added back all permissions to Sue, but only ReadData 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 inthe SYSTEM.MDA when a database is created. As a result, the Admins groupwill always have permission to change the permissions on all objects inthat database. This permission cannot be taken away by anyone. Thispermission remains even when all permissions have been revoked from theAdmins Group, and it is not displayed in the Permissions dialog. This isanother reason to keep a backup and keep track of which SYSTEM.MDA was inuse when the database was created.
With OwnerAccess Option in a SQL Query
One last point of possible confusion revolves around the use of thefollowing 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 ;") db.Close End Sub
This code results in this error:
Invalid Database ID.
This is because OwnerAccess refers to the owner of the database. The owneris the creator of the database. In other words, OwnerAccess refers tothe owner's user and password combination (unique SID) that is stored inthe database (BD1.MDB in this case). However, the code does not contain thetwo statements needed to point to the SYSTEM.MDA file of a secureddatabase. Actually, in this case, only the SetDefaultWorkspace statement isessential 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 thedatabase owner to the engine, so the engine doesn't know the default nameand password combination (Admin, "") of the user is the database owner.Even if it turns out that the User Admin is the database owner, withouthaving read the SYSTEM.MDA file, the engine cannot verify that fact, soit gives the error.
Notes for Microsoft Access Version 2.0 users
Using the recently released Microsoft Jet 2.0/Visual Basic 3.0Compatibility Layer, Visual Basic can gain access to Microsoft Accessversion 2.0 databases. Below are some notes to help you convert a secureversion 1.1 database into Microsoft Access version 2.0 format.
If a version 1.x database is secured, it will remain secure whether youopen it with Microsoft Access version 1.x or 2.0. However, Microsoft Accessversion 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 workgroupfile (SYSTEM.MDA). If Microsoft Access version 2.0 is installed in thesame directory as version 1.x, the version 1.x SYSTEM.MDA file will berenamed SYSTEM1X.MDA.
To make changes to the security of a converted database, you must use aversion 2.0 SYSTEM.MDA that has identical groups and users (and identicalPIDs) as the original SYSTEM.MDA.
NOTE: PIDs (Personal IDs) in Microsoft Access version 2.0 are theequivalent 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 toall 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 databasein 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 below:
[Options] 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.