| Microsoft Office 2000/Visual Basic Programmer's Guide | |
As mentioned earlier, User and Group objects and their passwords are not stored in your database. They are stored in a Microsoft Jet database known as the workgroup information file, which is also referred to as the system database (Figure 18.3). The default name of this database is System.mdw. Although it's structurally no different from the databases you create, it does contain several system tables that the engine uses to store security information. You can't make changes to the contents of those tables by opening a workgroup information file directly; the information stored in them is encrypted and protected. To define user accounts and security groups, you must use the User and Group objects in ADO code, or use the Access security user interface.
The workgroup information file stores information about each user and group in a workgroup. This information includes which users belong to which groups, encrypted passwords, and the SID for each user and group. When you use Microsoft Jet commands that affect user or group objects or their passwords, the engine reads and writes to the workgroup information file.
Figure 18.3 The Workgroup Information File
You can use the same workgroup information file for more than one database, in which case the user, group, and password information remains the same across the databases. When a user logs on to Microsoft Jet, the engine looks in the workgroup information file for the user's name and password in order to authenticate the user. From then on, Microsoft Jet uses only the SID that it found for that user.
Keep in mind that when you use the Workgroup Administrator to specify ("join") a workgroup information file, the Workgroup Administrator writes this information to the Windows registry, and that workgroup information file's user and group accounts are used by default until you (or your code) specify another workgroup information file. If you perform operations on other databases while that workgroup information file is current, you may see unexpected results. For this reason, you may want to create a separate workgroup information file for each database that you secure, and then have users open the secured database by using a Windows shortcut that specifies the correct workgroup information file with the Access /wrkgrp startup command-line option. For more information about how to do this, see "Access Startup Command-Line Options" later in this chapter. The User-Level Security wizard also provides the option of creating such a shortcut for you.
Note In versions of Microsoft Jet prior to 3.0, the initialization file (usually MSAccess.ini or MSAcc20.ini) is used to store the path and name of the workgroup information file. In Microsoft Jet versions 3.0, 3.5, and 4.0, the path and name of the workgroup information file are stored in the Windows registry. In all versions, the SystemDB registry entry is used to specify the path to the workgroup information file. In Access 2000, the path and name of the default workgroup information file is stored in the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Access\Jet\4.0\Engines subkey of the Windows registry.
You can set or return the path for the current location of the workgroup information file when you are opening a database from ADO code by using the Jet OLEDB:System Database provider-specific property of the Microsoft OLE DB provider for Jet in the ADO Properties collection of the Connection object. For example, the following code fragment specifies the path to the workgroup information file to use before opening an Access database in ADO code.
Dim cnnOpen As ADODB.Connection
Set cnnOpen = New ADODB.Connection
With cnnOpen
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDB:System Database") = "c:\MyFolder\MySystem.mdw"
.Open "c:\MyFolder\MyDatabase.mdb", "MyUser", "MyPassword"
End With
Opening a database by using the Access user interface automatically establishes an ADO Connection object for the current database. Typing the following line of code in the Immediate window in Access and pressing ENTER returns the path to the workgroup information file in use for the current database.
? CurrentProject.Connection.Properties("Jet OLEDB:System Database").Value
Similarly, the DBEngine property of the Access Application object will create an instance of DAO even if the DAO type library isn't referenced. This allows you to return the path to the workgroup information file for the current database to the Immediate window in Access by using the DAO SystemDB property as shown in the following line of code.
? Application.DBEngine.SystemDB
Note Once a database is opened, the Jet OLEDB:System Database and SystemDB properties become read-only. You can't change the path to the workgroup information file for the current database after it is opened.
You can start Access with the following command-line options, which configure the security environment:
Note A user profile that you use to start Access from the command line is not the same thing as a user profile that's defined for logging on to the operating system. An Access user profile applies only to Access, and only when you start Access from the command line. A user profile defined for the operating system applies to every application on the system, and is used to maintain system data for individual users.
These startup command-line options are particularly useful when you are creating a Windows shortcut to open a secured database. For example, instead of using the Workgroup Administrator to specify a workgroup information file before opening a secured database, you can create a shortcut that uses the /wrkgrp option to specify the file when the database is opened.
Note that you must include the full path to the Access .exe file, as well as to the database. For example, the following command line starts Access and opens the Northwind sample database by using the MySystem.mdw workgroup information file:
"C:\Program Files\Microsoft Office\Office\MSAccess.exe" "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" /wrkgrp "C:\Windows\System\MySystem.mdw"