Security

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.

Changing a Password

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.

Creating Users and Groups

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.

Setting PID values

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 recreate 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 recreating 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. Since 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 recreate 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 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 a User to a Group

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

Setting Permissions

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 example below, 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 example above, 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 table below 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 table above, 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 document 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 since 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.

Determining an Object's Owner

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