How Visual Basic 3.0 Handles Security Set by Microsoft Access

ID: Q105990

The information in this article applies to:

Microsoft Visual Basic Standard and Professional Editions for Windows, version 3.0

SUMMARY

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 articles:

Q122036 : WX1051: Security Wizard and White Paper App. Note 2.0

MORE INFORMATION

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 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 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 none ("").

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 security.

SetDataAccessOption Statement -- Syntax and Behavior

SetDataAccessOption has the following parameters:


   SetDataAccessOption option, value

   option is a numeric value with only one legal value (1).

For example:


   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:

[Options]
SystemDB=T:\ACCESS\SYSTEM.MDA
UtilityDB=T:\ACCESS\UTILITY.MDA

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 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:

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
      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 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 ;")
      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 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 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:

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 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.

Keywords : kbcode kbVBp300 APrgDataAcc
Version : 3.0
Platform : WINDOWS
Issue type : kbhowto