Microsoft Jet databases can be secured in one of two ways: share-level security or user-level security. For share-level security, the database is secured with a password. Anyone attempting to open the database must specify the correct database password. For user-level security, each user is given a user name and password to open the database.
The first step in securing a Microsoft Jet database is to change the password for the Admin user, if using user-level security, or changing the database password if using share-level security. When changing a password for a user or database, you must supply both the existing and new passwords. When changing the database or Admin user's password for the first time, use an empty string ("") as the existing password.
The following code shows how to enable user-level security by setting the password for the Admin user to "password."
DAO
Sub DAOChangePassword()
Dim wks As Workspace
Dim usr As DAO.User
' Open the workspace, specifying the system database to use
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "")
' Change the password for the user Admin
wks.Users("Admin").NewPassword "", "password"
End Sub
ADOX
Sub ADOChangePassword()
Dim cat As New ADOX.Catalog
' Open the catalog, specifying the system database to use
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Change the password for the user Admin
cat.Users("Admin").ChangePassword "", "password"
End Sub
DAO and ADOX both have a method on the User object to change the user's password. The method takes the user's current password and the new password as parameters. In DAO this method is called NewPassword, while in ADOX it is called ChangePassword.
Note The Microsoft Jet Provider will not error on the line of code that opens the catalog if the system database specified is incorrect. However, it will error when attempting to change the password or perform any other security-related operations with the following error if the system database was not correctly specified: "The operation requested by the application is not supported by the provider."
The following code shows how to change the database password for enabling security at the share level.
DAO
Sub DAOChangeDatabasePassword()
' Make sure there isn't already a file with the
' name of the compacted database.
If Dir(".\NewNorthWind.mdb") <> "" Then _
Kill ".\NewNorthWind.mdb"
' Basic compact - creating new database named newnwind
DBEngine.CompactDatabase ".\NorthWind.mdb", _
".\NewNorthWind.mdb", , , ";pwd=password;"
' Delete the original database
Kill ".\NorthWind.mdb"
' Rename the file back to the original name
Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"
End Sub
JRO
Sub JROChangeDatabasePassword()
Dim je As New JRO.JetEngine
' Make sure there isn't already a file with the
' name of the compacted database.
If Dir(".\NewNorthWind.mdb") <> "" Then _
Kill ".\NewNorthWind.mdb"
' Compact the database specifying the new database password
je.CompactDatabase "Data Source=.\NorthWind.mdb;", _
"Data Source=.\NewNorthWind.mdb;" & _
"Jet OLEDB:Database Password=password"
' Delete the original database
Kill ".\NorthWind.mdb"
' Rename the file back to the original name
Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"
End Sub
Note JRO, not ADOX, is used to change a database password at share level.
Both DAO and JRO allow you to change the database password when compacting the database. The syntax is slightly different: In DAO specify ";pwd=password;" in the Password parameter of CompactDatabase. In JRO specify the provider-specific "Jet OLEDB:Database Password=password" in the destination connection parameter of CompactDatabase.
Alternatively, the DAO code could be rewritten to use the NewPassword method of the Database object:
Sub DAOChangeDatabasePassword2()
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb", True)
db.NewPassword "", "password"
db.Close
End Sub
A similar mechanism is not currently available in JRO or ADOX. You must use the CompactDatabase method in order to change the database password.
A User object represents a user account that has specific access permissions, while a Group object represents a group of user accounts that have common access permissions. Creating users and groups allows you to easily control and maintain users' access to the database and objects within the database.
The following code example shows how to create a new user.
DAO
Sub DAOCreateUser()
Dim wks As DAO.Workspace
' Open a workspace
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
' Create the user and append it to the Users collection
wks.Users.Append wks.CreateUser("MyUser", "xNewUser", "password")
End Sub
ADOX
Sub ADOCreateUser()
Dim cat As New ADOX.Catalog
' Open the catalog, specifying the system database to use
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW;" & _
"User Id=Admin;Password=password;"
' Create the new user and append it to the users collection
cat.Users.Append "MyUser", "password"
End Sub
Unlike with DAO, with ADOX you do not have to create a User object before adding the user to the database with the Append method. With ADOX you can create a new user simply by passing the name and password to the Append method of the Users collection.
When creating a user or group in DAO you must provide a value for the PID parameter or property. This parameter is not required when creating a new user or group in ADOX because the Microsoft Jet Provider automatically generates PID values. This PID value is not retrievable using ADOX or DAO.
This presents problems if the workgroup information file, such as system.mdw, becomes corrupted or destroyed. In order to re-create users and groups and restore permissions to your databases and their objects, you must know the PID values. Microsoft Jet combines the user or group name and the PID value to create the identifier, called the SID, that it uses to control the permissions of databases and objects. By re-creating a user or group with its original name and PID value, Microsoft Jet will create a SID value identical to the original value, so all permissions set originally using that SID will work again. Because you cannot set the PID value using ADOX, the Microsoft Jet Provider effectively creates a new SID that does not have any database or object permissions set for it.
There are two methods for working around this problem if you choose not to explicitly set the PID values. One is to make frequent backups of the workgroup information file, and restore the file from the backup when necessary. This is usually the most efficient way to avoid the problem. The second method is to re-create all of the permissions in the database to work with the new users and groups that you will create. This is usually difficult to accomplish efficiently. Both methods do not require you to keep track of the PID values for users and groups.
Another way to avoid this problem is to use the new SQL Data Definition Language (DDL) security commands available for Microsoft Jet 4.0. The CREATE USER command, as implemented by Microsoft Jet 4.0, accepts a PID parameter for a new user. Likewise, the CREATE GROUP command accepts a PID parameter for a new group. The following code example demonstrates how to create a user and specify a PID value using ADO and DDL.
ADO
Sub ADOCreateUser2()
Dim cmd As New ADODB.Command
' Create the Command
cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW;" & _
"User Id=Admin;Password=password;"
' Execute the DDL security command
cmd.CommandText = "CREATE USER MyUser MyPW MyPID"
cmd.Execute
End Sub
Adding users to a group makes maintaining permissions easier. Because users within a group inherit the permissions of the group, you can set permissions once and have it apply to an entire group of users. For example, you can assign update permissions for the Salary table to all managers by simply granting the Managers group update permission.
The following code example demonstrates how to create a new group and add an existing user to that group.
DAO
Sub DAOAddUserToNewGroup()
Dim wks As DAO.Workspace
' Open the workspace
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
' Create a new group
wks.Groups.Append wks.CreateGroup("MyGroup", "xMyGroup")
' Add the user to the new group
wks.Users("MyUser").Groups.Append _
wks.Users("MyUser").CreateGroup("MyGroup")
End Sub
ADOX
Sub ADOAddUserToNewGroup()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Create a new group
cat.Groups.Append "MyGroup"
' Add the user to the new group
cat.Users("MyUser").Groups.Append "MyGroup"
End Sub
Both DAO and ADOX have a Groups collection on the Users object that can be used to add the user to a group as well as to determine what groups the user belongs to. However, note that with DAO you must create a new Group object using the User object's CreateGroup method before appending the Group object to the User object's Groups collection. With ADOX it is neither necessary nor valid to create a new Group object; just append the name of the group to the User object's Groups collection.
Note Before using this technique to create a Group, see the comments about setting PID values in the section "Creating Users and Groups."
By setting permissions you can control a user's access to an object. For example, you can allow one user to read an object's contents, but not change them. Permissions can be set for a specific user or an entire group of users. When permissions are set for a group, every user in that group inherits those permissions.
In the following example the user created in the section "Creating Users and Groups" is granted permissions to read, insert, update, and delete data.
DAO
Sub DAOSetUserObjectPermissions()
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim doc As DAO.Document
' Open the database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Set permissions for MyUser on the Customers table
Set doc = db.Containers("Tables").Documents("Customers")
doc.UserName = "MyUser"
doc.Permissions = dbSecRetrieveData Or dbSecInsertData _
Or dbSecReplaceData Or dbSecDeleteData
End Sub
ADOX
Sub ADOSetUserObjectPermissions()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Set permissions for MyUser on the Customers table
cat.Users("MyUser").SetPermissions "Customers", adPermObjTable, _
adAccessSet, adRightRead Or adRightInsert Or adRightUpdate _
Or adRightDelete
End Sub
The process for setting permissions with ADOX is essentially the inverse of the DAO process. With DAO you first select the object and then indicate the user for whom to set permissions. With ADOX, you first select the user and then specify the object on which to set permissions.
In addition, with DAO you set a series of properties in order to set permissions on an object. In the previous example you set the UserName property followed by the Permissions property. With ADOX, a single method, SetPermissions, is used to set permissions on an object. The SetPermissions method has parameters that map to the properties used in DAO.
With the DAO Permissions property, which maps to the Rights parameter of the ADOX SetPermissions method, you supply a constant or combination of constants that represent the permissions to set. The following table shows how the DAO Security constants map to the ADOX Rights constants.
| DAO | ADOX |
| dbSecNoAccess | adRightNone |
| dbSecFullAccess | adRightFull |
| dbSecDelete | adRightDrop |
| dbSecReadSec | adRightReadPermissions |
| dbSecWriteSec | adRightWritePermissions |
| dbSecWriteOwner | adRightWriteOwner |
| dbSecCreate | adRightCreate |
| dbSecReadDef | adRightReadDesign |
| dbSecWriteDef | adRightWriteDesign |
| dbSecRetrieveData | adRightRead |
| dbSecInsertData | adRightInsert |
| dbSecReplaceData | adRightUpdate |
| dbSecDeleteData | adRightDelete |
| dbSecDBAdmin | adRightFull |
| dbSecDBCreate | adRightCreate |
| dbSecDBExclusive | adRightExclusive |
| dbSecDBOpen | adRightRead |
As shown in the preceding table, DAO has specific security constants for setting permissions on a database. These constants are used with the Databases container or a database object. In the following listings, you can see how to use both DAO and ADOX to set permissions for a user on a database object.
DAO
Sub DAOSetDatabasePermissions()
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim doc As DAO.Document
' Open the database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Set permissions for MyUser on the current database
Set doc = db.Containers("Databases").Documents("MSysDB")
doc.UserName = "MyUser"
doc.Permissions = dbSecDBExclusive
End Sub
ADOX
Sub ADOSetDatabasePermissions()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Set permissions for MyUser on the current database
cat.Users("MyUser").SetPermissions "", adPermObjDatabase, _
adAccessSet, adRightExclusive
End Sub
Setting permissions for a database differs slightly from other objects. When using DAO, you must specify "MSysDb" as the article name when you want to specify permissions for the current database. To do the equivalent in ADOX, specify an empty string ("") as the name of the database.
In addition to granting permissions to a user on specific objects you may also want to specify permissions for a class/container of objects such as Tables. When specifying permissions on a container, you can indicate whether new objects of that class created by the user should inherit those permissions by default.
DAO
Sub DAOSetUserContainerPermissions()
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim ctr As DAO.Container
' Open the database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Set permissions for MyUser on the Tables Container
Set ctr = db.Containers("Tables")
ctr.UserName = "MyUser"
ctr.Inherit = True
ctr.Permissions = dbSecRetrieveData Or dbSecInsertData _
Or dbSecReplaceData Or dbSecDeleteData
End Sub
ADOX
Sub ADOSetUserContainerPermissions()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Set permissions for MyUser on the Tables Container
cat.Users("MyUser").SetPermissions Null, adPermObjTable, _
adAccessSet, adRightRead Or adRightInsert Or adRightUpdate _
Or adRightDelete, adInheritNone
End Sub
With DAO the Container object was used to specify permissions for a class of objects. With ADOX setting the Name parameter of the SetPermissions object to Null sets permissions for the class of objects specified by the ObjectType parameter. The InheritType parameter of the ADOX SetPermissions method indicates whether new objects should inherit the permissions. This is equivalent to setting the DAO Inherit property. In the ADOX example the InheritType parameter must be set to adInheritNone because it is setting the permissions for an existing object instead of a new object.
Use the Containers and Documents collections in DAO to set permissions on Access-specific objects, such as Forms, Reports, and Macros. Although ADOX supports setting permissions for Access-specific objects, the Microsoft Jet 4.0 Provider doesn't properly map the ADOX permissions flags to Microsoft Jet permissions flags. If you need to set permissions for Access-specific objects, you must use DAO until this problem is corrected.
The database, and every object in the database, has an owner. By default, the owner is the user that created that object. The object owner has special privileges for that object in that he or she can always assign or revoke permissions for that object.
The following listings demonstrate how to get the user name of the object owner.
DAO
Sub DAOGetObjectOwner()
Dim db As DAO.Database
Dim wks As DAO.Workspace
' Open the database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Print the owner of the Customers table
Debug.Print db.Containers("Tables").Documents("Customers").Owner
End Sub
ADOX
Sub ADOGetObjectOwner()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Print the owner of the Customers table
Debug.Print cat.GetObjectOwner("Customers", adPermObjTable)
End Sub
With DAO you use the Owner property of an Document or Container object to retrieve the user name of the object owner. With ADOX you use the GetObjectOwner method of a Catalog object. This method takes the object's name and type as parameters.