Exploring Microsoft Access Security

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Frank C. Rice
Microsoft Corporation

July 2002

Applies to:
   Microsoft® Access 2002

Summary: This article explores the different ways to implement various levels of protection for your Microsoft Access 2002 databases. (19 printed pages)

Contents

Introduction
Access Security Overview
About Access User-Level Security
Manually Set a Database Password
Set a Database Password Programmatically
Secure an Access Database with the Security Wizard
Programmatically Add and Remove Users and Groups
Set Permissions Programmatically
Conclusion

Introduction

In earlier versions of Microsoft® Access (prior to Microsoft Access 2000), security was sometimes looked at as too difficult for anyone to understand and use. There were a number of steps that the user had to navigate, in order, and to forget a step or to get the sequence wrong could have disastrous effects. With the advent of the Security Wizard in Microsoft Access for Microsoft Windows® 95 and the improvements made to the Security Wizard in Microsoft Access 2000, implementing security in Access has become much easier. However, even with this additional assistance, it is extremely important that you understand your options and are aware of the process used to protect the data and objects in your database. Failure to do so could, at a minimum, result in unsecured data or, in an extreme case, lock you out of your database.

There are a number of ways that you can protect an Access database and the objects it contains. In this article, we will examine some of the ways that you can protect the objects that comprise your database, to include the data, the objects that it contains such as forms and reports, and the code, which can constitute a considerable investment of intellectual property in your database. The techniques discussed in this article apply only to Microsoft Access database (.mdb) files.

Access Security Overview

The following sections provide information on the various ways to protect an Access database.

Encrypt or Decrypt the Database

The simplest (and least secure) method of protection is to encrypt the database. Encrypting a database compacts the database file and makes it indecipherable by some utility programs such as a word processor. Encrypting an unsecured database does not secure your database because anybody can open the database and gain full access to all objects in the database. For more information on securing your database, see the section Secure an Access Database Using the Security Wizard later in this article.

Encryption can be used to keep casual users from accessing the information in your database when you transmit a database electronically, or when you store it on floppy disk, tape, or compact disc. However, Jet (the database engine used by Access) uses a very weak method of encryption and should never be used to protect sensitive data. The Encrypt/Decrypt Database command is available from the Security submenu on the Tools menu. Decryption of a database reverses the encryption.

Use a Custom Interface

Another protection schema that is relatively simple to implement (and like encryption, does not securely protect the objects and sensitive data in your database) is to use a custom interface instead of the standard Access interface. By choosing Startup from the Tools menu, you can specify a custom startup form, custom menus, and even a custom title and icon. You can also choose to conceal the Database window, which hides the objects from less technical users of your application. The features of the Startup dialog box are also available programmatically. For information on how to set start up options from the Startup dialog box, see "About startup options" in Access Help. For more information on how to set start-up options programmatically, see "Set Startup Properties and Options in Code" in Access Help from the Microsoft Visual Basic® Editor.

Set a Database Password

You can also set a password on the database, which requires users to enter a password in order to gain access to the data and database objects.

**Note   **Using passwords to secure the database or objects in the database is also known as share-level security.

You can't assign permissions to users or groups with this option, so anyone with the password has unrestricted access to all Access data and database objects. The Set Database Password command is available from the Security submenu on the Tools menu.

User-Level Security

In addition to share-level security, you can also use user-level security, which provides the most restrictive access and gives you the most control over the database and objects it contains. Because this is part of the recommended method to protect a database (when used in conjunction with the file-level and share-level security provided by the operating system), we will discuss user-level security in more detail later in this article.

Likewise, we will discuss options to secure the Visual Basic for Applications (VBA) code contained in a database.

**Caution   **User-level security, when used by itself, primarily secures the code and objects in your database so that users can’t accidentally modify or change them. If you don’t want users to have unauthorized access to the code in a form, report, or module, you must convert the .mdb file into an MDE file (discussed in more detail later in this article). The only way to prevent users from modifying the queries, macros, or data access pages in your database is to put the database file (or data access pages) on a protected file share. Additionally, you can't give a malicious person the rights to modify data without them also having the ability to easily break through security and modify the design. To provide this functionality, you should use a server-based database product such as Microsoft SQL Server™.

Set a Module Password

You can protect all of the standard modules and class modules (such as the code contained in the forms and reports) from users accidentally modifying or viewing the VBA code by protecting it with a password. Once you set a password, you only need to enter this password once per session to view or modify the code in the Visual Basic Editor. The password is required not only to view and edit, but also to cut, copy, paste, export, or delete any module. You should be aware, however, that protecting your code in this manner doesn't prevent you or other users from running the code or others from viewing the code by using third-party utilities such as hex editors. To fully protect your code, you must convert the .mdb file to an MDE file.

To set a password for the modules in your project:

1. Choose the Properties command for the project from the Tools menu in the Visual Basic Editor.
2. In the Project Properties dialog box, click the Protection tab.
3. Select the Lock project for viewing check box and type a password.
4. Retype the password in the Confirm password box, and then click OK.

Use an MDE File

You can totally secure the code in Access from unauthorized access by converting the database file as an MDE file. When you convert an .mdb file to an MDE file, Access compiles all modules, removes all editable source code, and compacts the destination database. The original .mdb file isn't touched. The VBA code in the new database will continue to run, but it cannot be viewed or edited. Your Access database will continue to function normally and you can still update data and run reports. Specifically, saving your Access database as an MDE file prevents the following actions:

• Viewing, modifying, or creating forms, reports, or modules in Design view.
• Adding, deleting, or changing references to object libraries or databases.
• Changing code using the properties or methods of the Access or VBA object models—an MDE file contains no editable code.
• Importing or exporting forms, reports, or modules. However, tables, queries, data access pages, and macros can be imported from or exported to non-MDE databases.

To convert an .mdb file to an MDE file:

1. Close the database.

2. Click Database Utilities on the Tools menu.

3. Click Make MDE File.

4. In the Database to Save as MDE dialog box, locate the .mdb file, and click Make MDE.

   **Note   **Databases created in Access 2002 use the default Access 2000 file format. Databases in the Access 2000 file format can only be converted to an MDE file from within Access 2000. To convert an .mdb file created in the default Access 2000 format to an MDE file from within Access 2002, you must first convert the file to the Access 2002 file format. You can do this by first opening the database exclusively (see the steps in the section Manually Set a Database Password later in this article to open a database exclusively). Next, point to Database Utilities on the Tools menu, point to Convert Database, and then click To Access 2002 File Format. The database can now be converted to an MDE file.

More information on the requirements for converting an .mdb file to an MDE file can be found in "Secure Visual Basic for Applications code in Access databases" in Access Help.

Now let's look at user-level security in a little more detail.

About Access User-Level Security

Access uses the Microsoft Jet database engine to store and retrieve objects in the database. The Jet database engine uses a workgroup-based security model (also called user-level security) to determine who can open a database and to secure the objects contained in the database. User-level security is always on for any Access database, regardless whether you have explicitly secured the database or not. As we will see in the following paragraphs, you change the default level of security in Access by manipulating the permissions and memberships of user and group accounts.

Whenever you start Access, the Jet database engine looks for a workgroup information file (named system.mdw by default, although it can be named anything with the .mdw extension). The workgroup information file holds group and user information, including passwords, which determines who can open the database and the privileges they have on the objects in the database. The permissions to individual objects are stored in the database. So, for example, you could give the users of one group permissions on certain tables but not others, and another group permission to view a report but not to modify the design of the report.

The workgroup information file contains built-in groups (Admins and Users) and a generic user account (Admin) with administrative (unlimited) privileges on the database and the objects it contains. You can also add new groups and users by using menu commands (the Security submenu on the Tools menu) or through VBA code.

**Note   **When you install Access, the setup program automatically creates a workgroup information file that is identified by the name and organization information you specify. Because this information is often easy to determine, it's possible for unauthorized users to create another version of this workgroup information file and consequently assume the irrevocable permissions of an administrator account (a member of the Admins group) in the workgroup defined by that workgroup information file. To prevent this, you should create a new workgroup information file, and specify a unique workgroup ID (WID). Then, only someone who knows the WID will be able to create a copy of the workgroup information file. Creation of a new workgroup information file by using the User-level Security Wizard is discussed later in this article.

The Admins group cannot be deleted, and its members have administrative rights that can't be revoked. You can remove the privileges from the Admins group from the menu or through code, but any member of the Admins group can add the privileges back. In addition, there must always be at least one member of the Admins group to administer the database. For any unsecured database, the Admins group always contains the default Admin user account, which is also the account that everyone is logged on to by default.

All users must belong to the default Users group, regardless of any other groups that they belong to. You can create user accounts in VBA without adding the new user account to the Users group. However, if you don't also add that user account to the Admins group, the user won't be able to successfully start Access because many of the tables that Access uses internally to administer the database are mapped to the permissions of the Users group.

Secure an Access database by using the following steps:

1. Add the new user account to the Admins group. This user will have Administration rights to the objects in the database.
2. Remove the default Admin user account from the Admins group.
3. Remove permissions from the Admin user and the Users group.
4. Assign permissions to any custom groups that you create.

Setting a password on the default Admin user account activates the logon dialog box so that users will be prompted for a user name and password whenever they start Access. If you never set a password on the Admin account, users are automatically logged on as the Admin user with no password and the logon dialog box doesn't appear.

Permissions to the objects in a database can be either explicit (assigned directly to a user account), or implicit (derived from the group the user belongs to) or a combination of both. Access uses the "least restrictive" rule regarding permissions where user permissions consist of the sum of their explicit and implicit permissions. For example, if the account for User A has restrictive permissions and User A belongs to one group with restrictive permissions and another group with administrative (all) permissions, then User A has administrative permissions. For this reason, it is generally not a good idea to assign explicit permissions to a user account. Creating groups with different permissions and then assigning users to the group with the permissions appropriate for that user will result in fewer database administration headaches.

In the following sections, we'll demonstrate how to implement protection for your database and the objects it contains by using the user interface, as well as programmatically. First, we'll look at a couple of different ways to set a database password.

Manually Set a Database Password

You can set a password on your database, which requires users to enter a password to gain access to your database. However, once a user logs in, they have unrestricted access to your data and the objects in the database.

**Note   **Before you set a database password, it is recommended that you make a backup copy of the database and store it in a secure place.

Before you can set a database password, you need to have the database opened exclusively. To open the database exclusively:

1. Close the database, if it is opened.
2. Reopen the database by clicking Open on the File menu.
3. In the Open dialog box, locate the database, click the arrow next to the Open button, and then click Open Exclusive.

To set a database password manually:

1. On the Tools menu, point to Security, and then click Set Database Password.

2. In the Password box, type your password.

   **Note   **Passwords are case-sensitive.

3. In the Verify box, confirm your password by typing the password again, and then click OK.

The database password is now set. The next time you or any other user opens the database, a dialog box will be displayed that requests a password. Now let's look at setting a database password programmatically.

Set a Database Password Programmatically

You can programmatically set, change, or remove a database password by using theALTER DATABASEkeyword with the following syntax:

ALTER DATABASE PASSWORD NewPassword OldPassword

In this statement, the passwords are represented as String values delimited by square brackets ([]) with the following exceptions.

To set the database password for the first time, you use theALTER DATABASEstatement with the NULL keyword as theOldPasswordparameter. To remove a database password, you use theALTER DATABASEstatement with the NULL keyword as theNewPasswordparameter. The keyword NULL should not appear in square brackets in either of these cases.

Before using the following procedure, you may need to set a reference to the Microsoft ADO Ext 2.5 for DDL and Security library, if it’s not set already:

1. In the Visual Basic Editor, on the Tools menu, point to References. The References dialog box appears.
2. Select the Microsoft ADO Ext 2.5 for DDL and Security check box.

Look at the following code to set a password for the first time:

Private Function CreateDBPassword(ByVal Password As String, _
        ByVal Path As String) As Boolean
    Dim objConn as ADODB.Connection
    Dim strAlterPassword as String
    On Error GoTo CreateDBPassword_Err
    ' Create the SQL string to initialize a database password.
    strAlterPassword = "ALTER DATABASE PASSWORD [Your Password] NULL;"

    ' Open the unsecured database.
    Set objConn = New ADODB.Connection
    With objConn
        .Mode = adModeShareExclusive
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
            "Source=[Your Path];" 

     ' Execute the SQL statement to secure the database.
     .Execute (strAlterPassword)
    End With

    ' Clean up objects.
    objConn.Close
    Set objConn = Nothing
    
    ' Return true if successful.
    CreateDBPassword = True

CreateDBPassword_Err:
    Msgbox Err.Number & ":" & Err.Description
    CreateDBPassword = False 
End Function

This procedure accepts the user’s password and the path to the .mdb file. First, we declare a variable that represents the connection to our unsecured database and a String variable to contain the SQL statement that we use to change the password. Next, we setstrAlterPasswordto the Jet SQL statement that uses theALTER DATABASE PASSWORDkeyword. Notice that because we aren't replacing a password, the second argument is set to NULL. Next, we open a connection to the database. To set a password, the database must be opened exclusively, so we set the Mode property. Then we execute the SQL statement. You would typically run this procedure from one database to set the password in a separate unsecured database. The function returns True if all goes well.

Suppose that you want to change the password of a database that has already been secured. First, you need to log on to the database using the old password and then make the change. The following procedure illustrates this technique.

Before using the following procedure, you may need to set a reference to the Microsoft ADO Ext 2.5 for DDL and Security library, if it’s not set already:

1. In the Visual Basic Editor, on the Tools menu, point to References. The References dialog box appears.
2. Select the Microsoft ADO Ext 2.5 for DDL and Security check box.

Looking at the procedure:

Private Function ChangeDBPassword(ByVal OldPassword As String, _
        ByVal NewPassword As String, ByVal Path As String) As Boolean
    Dim objConn as ADODB.Connection
    Dim strAlterPassword as String

    On Error GoTo ChangeDBPassword_Err

    ' Create the SQL string to change the database password.
    strAlterPassword = "ALTER DATABASE PASSWORD [Your NewPassword] [Your OldPassword];"

    ' Open the secured database.
    Set objConn = New ADODB.Connection
    With objConn
        .Mode = adModeShareExclusive
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Jet OLEDB:Database Password") = "OldPassword"
        .Open "Data Source=[Your Path];" 

        ' Execute the SQL statement to change the password.
        .Execute (strAlterPassword)
    End With

    ' Clean up objects.
    objConn.Close
    Set objConn = Nothing 

    ChangeDBPassword = True

ChangeDBPassword_Err:
    Msgbox Err.Number & ":" & Err.Description
    ChangeDBPassword = False 
End Function

This procedure is similar to the previous subroutine except that to log on to the secured database, we need to use the old password before we change it. We do this by setting the Database Password attribute of the Connection object for the database we will change. This is one of the extended properties of the Connection object, so we use the special syntax illustrated above. To remove a password from the secure database, you also could use this procedure and substitute the NULL keyword for the first argument of theALTER DATABASEstatement.

Now let's look at implementing protection that is more robust than setting a password on the database.

Secure an Access Database with the Security Wizard

You can use the Security Wizard to secure your database by using the following steps:

1. Open the database that you want to secure.

2. On the Tools menu, point to Security, and then click User-Level Security Wizard. The Security Wizard dialog box is displayed.

3. Since we want to create a new workgroup information file, make sure that option is selected and click Next.

4. In the second screen of the Security Wizard dialog box (see Figure 1), type a new name for your workgroup information or accept the default name. Type a workgroup ID (WID). The WID uniquely identifies the Admins group for this workgroup file. Next, type your name and company name in the appropriate text boxes. Although these entries are optional, it is a good idea to fill them in because only someone who knows this information will be able to re-create the workgroup information file.

   

   Figure 1. Second screen of the Security Wizard dialog box

5. You can also choose whether this workgroup information file will be used as the default file for all of your databases or just for this particular database. Click the I want to create a shortcut to open my secured database option and click Next.

6. In the next screen (see Figure 2), you can select which objects you want the wizard to secure. By default, the wizard will secure all existing objects and all new objects that you create. Click Next.

   

   Figure 2. Third screen of the Security Wizard dialog box

7. In the next screen (see Figure 3), you can choose optional security groups with specific permissions. It is highly recommended that you use groups to manage the access privileges of your users instead of grant individual users specific permissions. Choose any additional group accounts and click Next.

   

   Figure 3. Fourth screen of the Security Wizard dialog box

8. In this screen (see Figure 4), you can set specific permissions for the Users group. Since all users are automatically members of the Users group, any permissions you choose here will apply to all users, so it is a good idea to limit the privileges for this group or leave the default of no permissions. Click Next.

   

   Figure 4. Fifth screen of the Security Wizard dialog box

9. In the next screen (see Figure 5), you can add users to your workgroup information file. You also choose a password and Personal ID (PID) for each user. Access uses the PID in combination with the account name to uniquely identify a user for a workgroup. Add users and click Next.

   **Note   **The personal identifier (PID) is not a password. It is encrypted, along with the name, to create a unique system identifier (SID) that identifies the user.

   

   Figure 5. Sixth screen of the Security Wizard dialog box

10. In this screen (see Figure 6), you add users to the groups in your workgroup file. Make your selections and click Next.

    

    Figure 6. Seventh screen of the Security Wizard dialog box

11. In the final screen, choose a name for the backup copy of the unsecured database. It is a good idea to safeguard this copy of your original database in the event that the secured version of the database is lost or corrupted. Click Finish. The original database is saved with a .bak extension.

    **Caution   **When you have created the workgroup information file, the Security Wizard creates a report that contains all of the information that you need to re-create the workgroup information file. It is important that you safeguard this information so that you can re-create the file if the original file is ever lost or corrupted.

12. Once you click Finish, the Security Wizard encrypts the database and requires that you reopen the secured database by using the new workgroup information file.

Now let's look at different ways to work with users and groups programmatically.

Programmatically Add and Remove Users and Groups

Once you have a secure database, you are likely to need to work with users and groups. The following sections demonstrate some of these techniques.

Before you use the procedures in the following sections, you may need to set a reference to the Microsoft ADO Ext 2.5 for DDL and Security library, if it’s not set already:

1. In the Visual Basic Editor, on the Tools menu, point to References. The References dialog box appears.
2. Select the Microsoft ADO Ext 2.5 for DDL and Security check box.

Add and Remove Users

The following procedure creates a new user account and then appends it to the default Users group in the workgroup information file that is used for the current database.

**Note   **The following examples are designed to be used within Access after you log on as a member of the Admins group and open a database. In the following procedure, you should make sure that the workgroup information file doesn't already contain a user with the name specified in

strUser

. For example, you could do this by calling the

DeleteUser

subroutine first.

Looking at the code:

Private Function AddUser(ByVal strUser As String, _
        ByVal strPID As String, _
        Optional ByVal strPwd As String) As Boolean
    Dim catDB As ADOX.Catalog

    On Error GoTo AddUser_Err

    ' Instantiate the Catalog object.
    Set catDB = New ADOX.Catalog
    With catDB
        ' Open Catalog object by using connection to the current
        ' database.
        .ActiveConnection = CurrentProject.Connection
        ' Create new user account.
        .Users.Append strUser, strPwd, strPID
        ' Append new user account to default Users group.
        .Groups("Users").Users.Append strUser
    End With

    ' Close Catalog object.
    Set catDB = Nothing

    AddUser = True

AddUser_Err:
    Msgbox Err.Number & ":" & Err.Description
    AddUser = False 
End Function

The procedure first declares a variable for the Catalog object and then instantiates the object.

**Note   **A Catalog object is a container for all of the objects in an Access database file.

The procedure then opens a connection to the current database and appends the new user to the Users collection of the Catalog object by using the arguments from the calling procedure. The new user is then appended to the default Users group. The Users collection contains all of the users for the database as defined in the workgroup information file.

To remove an existing user, you can use the following procedure:

Private Function DeleteUser(ByVal strUser As String) As Boolean
    Dim catDB As ADOX.Catalog

    On Error GoTo DeleteUser
    ' Instantiate the Catalog object.
    Set catDB = New ADOX.Catalog
    With catDB
        ' Open Catalog object in current database.
        .ActiveConnection = CurrentProject.Connection
        ' Delete strUser.
        .Users.Delete strUser
    End With

    ' Close Catalog object.
    Set catDB = Nothing

    DeleteUser = True

DeleteUser_Err:
    Msgbox Err.Number & ":" & Err.Description
    DeleteUser = False
End Function

This procedure is similar to the previous procedure except that we use the Delete method of the Catalog object to remove the user specified in the strUserString argument.

Add and Remove Groups

The procedure to add groups is similar to that used to add users.

Private Function AddGroup(ByVal strGroup As String, _
        ByVal strPID As String) As Boolean
    Dim catDB As ADOX.Catalog

    On Error GoTo AddGroup_Err

    Set catDB = New ADOX.Catalog
    With catDB
        ' Open Catalog object in the current database.
        .ActiveConnection = CurrentProject.Connection
        ' Create new group.
        .Groups.Append strGroup, strPID
    End With

    ' Close Catalog object.
    Set catDB = Nothing

    AddGroup = True

AddGroup_Err:
    Msgbox Err.Number & ":" & Err.Description
    AddGroup = False
End Function

The procedure instantiates the Catalog object and then opens a connection to the current database. Next, it appends the new group to the Groups collection of the Catalog object by using the arguments from the calling procedure.

To remove an existing group, you can use the following procedure:

Private Function DeleteGroup(ByVal strGroup As String) As Boolean
    Dim catDB As ADOX.Catalog

    On Error GoTo DeleteGroup_Err

    Set catDB = New ADOX.Catalog
    With catDB
        ' Open Catalog object in the current database.
        .ActiveConnection = CurrentProject.Connection
        ' Delete strGroup.
        .Groups.Delete strGroup
    End With

    ' Close Catalog object.
    Set catDB = Nothing

    DeleteGroup = True

DeleteGroup_Err:
    Msgbox Err.Number & ":" & Err.Description
    DeleteGroup = False
End Function

This procedure is similar to the previous procedure except that we use the Delete method of the Catalog object to remove the group specified in the strGroupString argument.

Now let's look at how to set permissions on database objects programmatically.

Set Permissions Programmatically

To set permissions for the various objects in the database, you can use the SetPermissions method of either the Group or User objects. In the following procedure, we first revoke all permissions for the group and then grant the group specified permissions. This ensures that the group has only the permissions we want them to have:

Private Function SetGroupPermissions(ByVal strGroup As String, _
        ByVal strTable As String, ByVal strObjectType As String, _
        ByVal strAction As String, _
        ByVal strRevokeEnum As String) As Boolean
    Dim catDB As ADOX.Catalog

    On Error GoTo SetGroupPermissions_Err

    Set catDB = New ADOX.Catalog
    With catDB
        ' Open Catalog object in the current database.
        .ActiveConnection = CurrentProject.Connection
        ' Revoke all permissions for the group.
        .Groups(strGroup).SetPermissions tblTable, _
            strObjectType, strAction, strRevokeEnum
            
        ' Grant specific permissions for the group.
        .Groups(strGroup).SetPermissions tblTable, _
            strObjectType, strAction, _ 
            adRightRead Or adRightInsert Or adRightUpdate
    End With

    ' Close Catalog object.
    Set catDB = Nothing

    SetGroupPermissions = True

SetGroupPermissions_Err:
    Msgbox Err.Number & ":" & Err.Description
    SetGroupPermissions = False 
End Function

After opening a Catalog object in the current database, we use the SetPermissions method of the Groups collection to revoke all permissions for the group on the Employees table. The first argument is the name of the table, and the second argument indicates the type of object, in this case, a table. The third argument specifies the type of action to perform when setting permissions, and the fourth argument is a single permissions constant which specifies that the group is to have no permissions. Now that we have revoked all permissions for the group for theEmployeestable, we can grant just the permissions we want.

The first three arguments for the next statement are the same as the previous statement. The fourth argument is a value created by combining different combinations of permissions constants by using the Or operator. In this case, we are granting read, insert, and update permissions on the table.

To set permissions on all new objects of the type specified, such as tables in the example above, change the first argument of the statement used to grant permissions to the NULL keyword. For example:

...
catDB.Groups(strGroup).SetPermissions NULL, adPermObjTable
...

Conclusion

In this article, we examined the different ways to implement various levels of protection for your Access databases. We looked at both share-level and user-level security. We also looked at how to implement security by using the Access Security Wizard and at how to implement security programmatically.