Learn about Microsoft Access security

Brief introduction

In earlier versions of Microsoft®access (prior to Microsoft Access 2000), knowledge about security was sometimes considered impossible for anyone to master and apply. You need to perform a number of steps sequentially, which can have disastrous consequences if you omit a step or reverse the sequence. With the advent of the security Wizard of Microsoft Access for Microsoft Windows®95, and the continuous improvement of security Wizard in Microsoft Access 2000, the ACCE Implementation of security in SS has become very simple. However, even with these help, you must be aware of your own security options and the actions to protect data and objects in your database. Otherwise, the light will bring data security risks, the heavy will lock you in your own database. There are many ways to protect your Access database and the data it contains. In this article, we discuss ways to protect the objects that make up a database (including data), objects that contain elements such as forms and reports, and code, which may be the most valuable part of the database. The security techniques discussed in this article apply only to Microsoft Access database (. mdb) files.

Knowledge about protecting sensitive data that you need to know

You should know that there are many tools and third-party utilities that can be used to detect passwords for any type of database, as well as user names and passwords from any workgroup information file (the workgroup information file is described in detail later in this article). If you need to protect sensitive data from unauthorized access, the best security measure is to use file-level security and file-sharing security provided by your computer's operating system. File-level security involves setting permissions on the data file. File share security involves restricting access to data file storage locations. An example of file share security is to set user permissions on the folder where the data files are stored (on the local computer or on a network server). To do this, you can split the data into multiple files, set user permissions on those files, and then place the files in a protected file space. You can then link to these files from an Access database that has security settings.

Access Security Overview

The following sections describe several ways to protect an Access database.

Encrypt or decrypt a database

The simplest (and least secure) protection method is to encrypt the database. Encrypting a database is compressing the database files so that some utilities (such as word processors) cannot interpret them. Encrypting a database that does not have a security setting does not guarantee the security of the database because anyone can open the database and fully access all objects in the database. For more information about security settings for a database, see Using the security Wizard to set up secure Access databases later in this article. Encryption prevents other users from accidentally accessing information in the database when the database is transmitted electronically or stored on a floppy disk, tape, or disc. However, the encryption method used by Jet (the database engine used by Access) is so weak that it must not be used to protect sensitive data. The Encrypt/Decrypt Database command is located in the Security submenu of the Tools menu. Decrypting a database is a inverse of the encryption process.

Using a custom interface

Another relatively simple protection scheme is to use a custom interface instead of the Access standard interface. As with encryption, it does not protect the security of objects and sensitive data in the database. By selecting the startup option on the Tools menu, you can specify a custom startup form, menu, or even a custom title and icon. You can also choose to cancel the Database window to hide these objects from application users who lack the appropriate technology. The features of the Startup dialog box can also be programmatically implemented. For more information about how to set startup options from the Startup dialog box, see "About startup Options" in Access help. For more information about how to set startup options programmatically, see Access help Microsoft Visual basic® Editor's options for setting startup options and encoding.

Set the database password

You can set a password on the database, which requires users to enter a password when accessing data and database objects. Note: Using a password to secure a database or its objects is also known as shared-level security. You cannot use this option to assign permissions to users or groups, so anyone with a password has unrestricted access to all Access data and database objects. The Set Database Password command is located in the Security submenu of the Tools menu.

User-level security

In addition to shared-level security, you can use user-level security, which provides the strictest access restrictions that allow you to maximize control over the database and the objects it contains. This is part of our recommended database protection (when used in conjunction with file-level and shared-level security provided by the operating system), so we'll cover user-level security in detail later in this article. Similarly, we will discuss various ways to protect the Visual Basic for Applications (VBA) code contained in the database. Warning: User-level security (when used alone) is primarily used to protect code and objects in the database, so that users do not accidentally modify or change them. If you do not want users to illegally access code in a form, report, or module, you must convert the. mdb file to an MDE file (described in more detail later in this article). The only way to prevent users from modifying queries, macros, or data access pages in a database is to place the database files in a protected file-sharing area. In addition, it is not possible in Access to both allow users to modify the data in a table, while preventing it from modifying the design of a table or deleting a table. To provide such a feature, you need to use a server-based database product, such as Microsoft SQL Server™.

Set Module password

Use a password to protect all standard modules and class modules, such as those contained in forms and reports, lest users accidentally modify or view the VBA code. After you set a password, you only need to enter the password once per session to view or modify the code in the Visual Basic editor. In addition to viewing and editing, you need a password when you cut, copy, paste, export, or delete any module. It should be clear, however, that protecting your code with this method does not prevent you or other users from running the code, nor does it prevent other users from using Third-party utilities, such as the 16-in editor, to view the code. To fully protect your code, you must convert the. mdb file to an MDE file. To set a password for a module in a project:

1.	Select the Properties command from the Tools menu of the Visual Basic editor for this item.
2.	In the Project Properties dialog box, click the Protection tab.
3.	Select the Lock project when viewing check box and type the password.
4.	In the Confirm Password box, retype the password, and then click OK.