Article ID: 105990 - View products that this article applies to.
This article was previously published under Q105990
This article has been archived. It is offered "as is" and will no longer be updated.
Visual Basic version 3.0 includes the Microsoft Access database engine. Visual Basic contains the syntax to manipulate a Microsoft Access database in almost every way that Microsoft Access can. One major exception is in the area of security. Only Microsoft Access can set or modify security options (such as logon IDs and passwords for the system) and set or modify permissions on specific objects in a particular database.
Visual Basic version 3.0 does contain two statements (SetDataAccessOption and SetDefaultWorkspace) that allow a Visual Basic application to satisfy the security mechanism that Microsoft Access implements, and log on using Visual Basic code. By using these statements, you can gain the permissions granted to a particular user.
This article explains the mechanisms of Microsoft Access security that apply to Visual Basic version 3.0 and the Visual Basic programmer. The entire security capabilities of Microsoft Access are beyond the scope of this article.
For a complete description of the security capabilities of Microsoft Access, please refer to the following Knowledge Base article:
(http://support.microsoft.com/kb/122036/EN-US/ )WX1051: Security Wizard and White Paper App. Note 2.0
Microsoft Access security is implemented in two parts:
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 security.
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 DatabaseThe 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 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 security.
SetDataAccessOption Statement -- Syntax and BehaviorSetDataAccessOption has the following parameters:
SetDataAccessOption option, value option is a numeric value with only one legal value (1).
In the DATACONS.TXT file supplied at the root of the \VB directory, a constant is defined for this value:
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 BehaviorSetDefaultWorkspace 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 SecurityTo 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 permissions.
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:
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:
This code results in this error:
Invalid Database ID.
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 usersUsing 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 renamed SYSTEM1X.MDA.
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:
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.
Key Points to Remember