Permissions for Databases

As with other objects, the database itself has its own unique permissions. Permissions for the database itself are set by modifying the Document object in the Container object named “Databases.” While this may seem to be counterintuitive at first, it does make sense when you remember that all permission settings work at the Containers and Documents level. To work the database itself into this scheme, Microsoft Jet defines a database Document object called MSysDb. By setting the Permissions property of this document, you are in effect setting the permissions for the database itself. The following permissions are defined for this document.

As an example, assume that you don’t want user Hannah to have permission to open a database, but you want to allow user Jayne to open it exclusively. The following code accomplishes this. Note that user accounts named Hannah and Jayne must exist in order for this code to run. In this example, strDbPath is the path to the database, and strWorkgroup is the path to the workgroup information file:

Dim doc As Document
Dim dbs As Database
Dim strUser As String

DBEngine.SystemDb = strWorkgroup
Set dbs = OpenDatabase(strDbPath)
Set doc = dbs.Containers!Databases!MSysDB

With doc
	' Remove Hannah's permission to open the database.
	strUser = "Hannah"
	.UserName = strUser
	.Permissions = .Permissions And _
		Not dbSecDBOpen

	' Give Jayne the ability to open the database exclusively.
	strUser = "Jayne"
	.UserName = strUser
	.Permissions = .Permissions Or _
		dbSecDBExclusive Or dbSecDBOpen
End With

Preventing Users from Creating Databases

You can also use Microsoft Jet to prevent users from creating databases. Because this permission is applied to users and not to a specific database, the permission is stored in the Container object named “Databases” in the workgroup information file. By default, anyone in the Admins group or the Users group has this permission, which is represented by the constant dbSecDBCreate.

It may not be necessary to set this permission in most circumstances. However, removing this permission is the only way you can prevent users from owning a database. For example, you can remove this permission if you’re concerned that users might create a new database and import the objects in your database into the new database, which they own. Since you can’t revoke ownership of a database, you can remove database creation permissions to prevent users from creating a new database in the first place.

To reference the path to a workgroup information file in your code, you use the SystemDB property. The following code denies a user or group the permission to create databases:

Sub RemoveCreatePerms(strWorkgroup As String, strUser As String)
	Dim dbs As Database
	Dim ctr As Container
	
	' Set SystemDB property to name of workgroup information file.
	DBEngine.SystemDB = strWorkgroup
	' Open workgroup information file.
	Set dbs = OpenDatabase(strWorkgroup)
	Set ctr = dbs.Containers!Databases
	With ctr
		' Revoke database creation permissions for user or group
		' in workgroup information file.
		.UserName = strUser
		.Permissions = .Permissions And Not dbSecDBCreate
	End With
End Sub

Database Passwords

In some cases, the only security a developer needs is the ability to prevent unauthorized users from opening the database; after the user has the database open, no other security is needed. To meet this need, Microsoft Jet 3.0 and 3.5 enable you to password-protect the database itself.

The database password is stored in the database header pages of the database itself. Setting a database password has no effect on the workgroup information file.

Caution If you lose the password to your database, you can never open the database again! Make sure you have adequate safeguards: Always have hard copies of your database passwords stored in a secure place.

Setting the Database Password

The database password can be set either through the Microsoft Access user interface using the Set Database Password command (Tools menu, Security submenu), or through DAO with the NewPassword method. When using either method, the database must be opened exclusively.

The following code opens the Orders database and assigns the password “topsecret” to it. In this example, strDbPath is the path to the database:

Dim dbs As Database

' Open database in exclusive mode.
Set dbs = OpenDatabase(strDbPath, True)
' Assign new password.
dbs.NewPassword "", "topsecret"
dbs.Close

Opening a Password-Protected Database

After a database password has been set, all attempts to open that database fail unless the correct password is given. To open a password-protected database using DAO, you specify the password as the connect argument of the OpenDatabase method. For example, the following code opens the password-protected Orders database. In this example, strDbPath is the path to the database:

Dim dbs As Database

' Open database with password parameter.
Set dbs = OpenDatabase(strDbPath, _
	False, False, ";pwd=topsecret")

Changing the Database Password

To change the database password, you must supply both the existing and new passwords to the NewPassword method. The following example changes the password of the Orders database from “topsecret” to “ultrasecret”:

Dim dbs As Database
Dim strDbPath As String

Set dbs = OpenDatabase(strDbPath, True, _
	False, ";pwd=topsecret")
dbs.NewPassword "topsecret", "ultrasecret"

You can remove the database password by using the NewPassword method to set the password to a zero-length string (""), or through the Microsoft Access user interface by using the Unset Database Password command (Tools menu, Security submenu).

Note This new capability increases the chance that generic OpenDatabase method code will stop working. Always check to see if your attempt to open a database has succeeded. Given that your code may encounter a password-protected database, it should have the flexibility to handle such cases.

Preventing Users from Setting a Database Password

In an unsecured environment, any user can set a database password. This can create problems for your application because you and other users won’t be able to open it. You can prevent users from doing this by removing Administer permissions from the Database object.

If you want to make it impossible for users to set a database password, follow the steps to secure a database using the User-Level Security Wizard in the “Securing Your Database with Microsoft Access” section earlier in this chapter. This will prevent all users who aren’t members of the Admins group from setting the database password.

If, however, you just want to make it difficult for users to set a database password, you don’t need to fully implement security. Simply remove Administer permissions from the Database object for the Admin user, the Users group, and the Admins group. All users who try to set the database password will fail and receive a permission denied error: Before they could set the password, they would first have to explicitly grant themselves permission to do so. For many applications, that is security enough.