16.1. Access Security Model Overview

With so many methods to set up security in Access, you can easily become overwhelmed deciding which method to choose. This section provides a synopsis of Access security methods, with suggestions for when to use one method over another and when to combine methods. This section also covers the relative difficulty of using these methods.

There are two common scenarios used when setting up Access databases:

• Standalone databases which contain all of the objects necessary to maintain the desired data, including tables, forms, reports, modules, and so on.

• Linked databases in which one database typically contains all user-interface components, such as forms, reports, and modules, with links to tables in another database, which contains all of the tables and data. The database containing the user-interface components and links is called the Front end. The database containing the tables and the data is called the Back end.

There are seven security methods that can be used in Access. You may apply any or all of these security methods to a single database. Additionally, all of the security methods can be applied to a standalone, a front-end, or a back-end database.

• Encoding: Encoding a database encrypts the data in the file such that the data cannot easily be read using a file explorer/browser tool (for example, Windows Notepad). This type of security is useful when the folder that contains the database file can be opened by anyone with access to the system but you need to restrict reviewing the data contained in the database to authorized users only. This method does not require a password to view the data using Access and is very easy to implement. You should probably combine this method with shared-level security or user-level security.

• Shared-level security: Shared-level security is established by setting a password on a database. When a database has a password, the user is required to enter the password before they can open it. This type of security works well in small workgroups where it is not necessary to know who opened the database or restrict users from altering any objects in the database. If your users are not allowed to share a common password, this method will not be satisfactory. You can combine this method with encoding and user-level security.

• User-level security: User-level security is established when you attach a workgroup information file (MDW) file to the database. User-level security permits you to set permissions for each object in the database at a single user or at a user group level. This method can be useful when you have a number of users using the database and each user needs different types of access (permission) to the database. Types of access include data deleting, updating, or inserting, each of which can be authorized or not independently depending on the user. Additionally, queries, forms, and reports can be made available to specific users depending on their needs. This method can be combined with encoding and shared-level security, and requires more maintenance and planning than other methods and is therefore more time consuming to implement and maintain.

• Compile: Compiling the database produces an Access compiled database (type MDE) file. When a database is compiled, the code is compiled and compacted and the user readable code is removed from the database. Additionally, forms, reports, pages, or modules cannot be modified, nor can they be exported to another database.This method can be used to protect your intellectual property rights as well as prevent users from changing forms, reports, pages, or modules. This does not secure the data in database tables. This method does not secure the data in an Access database. You can set up shared-level security or user-level security to require a password. This method is most commonly used on a front-end database.

  NOTE

  If your database has shared-level security or user-level security when you make an MDE, you will not be able to remove the security from the MDE.

• Set project password: If you have written code that you do want others to be able to view or change, you can set a password on the project for your database using the Visual Basic Editor. This will only prevent users from viewing or changing code. It does not prevent them from changing form, report, or page layouts. This method does not secure the data in an Access database. You can set up shared-level security or user-level security to require a password. This is most commonly used on a front-end database.

Figure 16-1 shows the shared-level security and user-level security methods as they apply to the linked database scenario. The encoding and compile methods, shown in Figure 16-1, could be applied to these databases—though you will probably not compile a back-end database because it usually has no code.

Figure 16.1. Figure 16-1

Options (B) and (C) in Figure 16-1 are the same as when they are applied to a single database as described above. Options (F) and (G) are described here because of the difference in the way the methods apply to a back-end database.

• Shared-level security: Like the single database scenario, you set shared-level security by setting a password on the database. Setting a password on a back-end database will require that a password be entered before the database can be opened. The password for the front-end database does not have to be the same as the password for the back-end database. In order to establish a link to a table in a back-end database that has a password, the password must be entered at the time the link is created.

  This method is fairly easy to implement, but has some problems. The main problem is that the password to the back-end database is stored in one of the Access database system tables in the front-end database. An experienced user can determine the password to the backend database. For this reason, you should also have a password for the front-end database. Of course, having a password on the front-end will not prevent users who know that password from determining the password to the back-end database. However, it will prevent persons who are not authorized to use either the front-end or the back-end from easily obtaining the passwords.

  NOTE

  If you change the password on the back-end database, you must recreate the links in a front-end database. The linked table manager will not be able to relink the tables.

• User-level security: Using this method is also like the single database scenario. It has the same level of difficulty as setting user-level security on a single database. You should keep in mind that if you are going to use a workgroup information file (MDW) on the back end, it must be the same file used on the front end. This is because Access can only use one MDW file at a time. Also, when you set up permissions to secure data, you will apply those permissions to the back-end database, not the front-end one.

The rest of this chapter covers each of these security methods in more detail. You will see how to set up security on your database as well as some other problems you might encounter.