AVOIDING COMMON PITFALLS FOUND IN ACCESS SECURITY

The following sections discuss a handful of issues that developers new to security might often encounter. Some of the topics covered in this section include planning security, running permissions on queries, securing attached tables, and dealing with life after the Security Wizard.

Planning Security

You should plan your security model at the same time you plan your database design. Planning ahead and implementing security along the way gives you less chance for leaving security holes in your application. Often when a project gets big and hundreds of objects are created, there's a great possibility that an object or two might be overlooked and not be secured properly if you wait until the last minute to secure the database.

One technique that's a good practice to follow is setting up all your users and groups first, before creating any objects. Then use the default permissions for new objects settings for each object.

Creating Objects with Default Accounts

It can't be stressed enough that any objects—including database objects—created with the default user Admin account are never secure. Furthermore, you should always create a brand-new workgroup information file before starting that new project that must be secured. It's of the utmost importance that your Admins group SID is unique—that's what makes security work. (Recall that the only way to create a new SID for the Admins group is to use the Workgroup Administrator.) For more information about how to create a new workgroup information file, see “Creating a Workgroup Information File” earlier in this chapter.

After the new workgroup information file is created, you can proceed to create a new developer account, add that account to the Admins group, log on with the new account, and create the application—of course, making sure that you remove the Admin account from the Admins group.

Securing Linked Tables in a Multiuser Environment

Often, Access developers find it beneficial to create a multiple-database system. Splitting the code from the data allows the code to run locally on each machine and the data to reside on a server. This situation gives the developer performance gains and makes it easier to manage updates. But how do you set up security in this type of situation?

One suggestion is to totally lock up the data database. The only things that should be in the data database are tables and system relationships. Remove all permissions from all users and groups. Also remember to encrypt the database. Because the database will be up on a file server where many people have access to it, you need to make sure that they can't read the database if they copy it to their local machine; encryption will solve this problem. Because data will be added, deleted, and modified, you need to leave the network operating system permissions set to Read, Write, and Update on the data file.

Now that the operating system will allow writes to the file, you need to set up Access permissions so that the code database can manipulate the data database. Next, create links to each table, and then remove all permissions to all users and groups for those linked table objects. Any time you need to access the data through a form or a report, base the form's or report's record source to a query with the Run Permissions property set to Owner's. This ensures the most protection of your sensitive data in the data database and keeps people from looking at the tables through the code database, except through the mechanisms (forms and reports) that you provide.

On a similar note, for ease of user administration, it's suggested that you keep your workgroup information file (System.mdw) on the file server with your data database. This is especially important if you're doing a lot of user administration, such as creating users and groups or moving users between groups. This is also very helpful if you let users change their own passwords.

Running with Owner's Permissions

Perhaps the most complex mechanisms built into the Access security model is the query property Run Permissions. If you can master the concept of the WITH OWNERACCESS OPTION SQL clause for queries, you'll have no problem implementing the most advanced features of Access security.

The purpose for the Owner access option is to let people with no permissions for a table look at a limited set via a query. Typically, you need to have at least Read Data permission to view data from a table. The problem arises when you don't want to allow viewing of the full table. For example, you might have an Employees table with a salary field. You might want to use this table to create a phone book listing for a form by showing the FirstName, LastName, and PhoneExt fields in a query. To execute the query, you must have Read Data on the table, thus the dilemma. You need to give partial access to the table. Note that this is the functional equivalent of SQL Server column-level security.

Recall the two users from earlier in the chapter, Ryan the manager and Jennifer the employee. As a member of the Managers group, Ryan might have full access to update the Employees table. You don't, however, want to give Jennifer access to the table because she shouldn't be able to view everybody else's salary. On the other hand, that's your only Employees table and you've created a query that returns employee names and phone extensions from that table.

What Access lets you do in this situation is create the table and remove all the permissions from the Employees and Users groups. Then, if the developer made the table and the query, that person can set the Run Permissions property to Owner's. Doing this grants the user executing the query access to the base table for just that query. By setting the query's permissions to Read Design and Read Data only for the Employees group, all employees can look at the phone listing without having access to the base table.

It's suggested that you use this method on all linked tables when you're using a two-database (code plus data) system.

All queries with their Run Permissions property set to Owner's will end with OWNERACCESS OPTION in their SQL clause.

To view the Run Permissions property of a query, follow these steps:

1.	Open the query in Design view.
2.	From the View menu choose Properties.
3.	Double-click in the upper half of the query design grid so that the property sheet displays the query properties. The Run Permissions property is a toggle field six rows down the property sheet.

Using Security in a Replication Environment

You need to be aware of several issues when dealing with security in a replication environment. First, database passwords can't be used on a replicated database. If you want to implement security, full user-level security must be used.

The workgroup information file (System.mdw) should never be replicated. Some serious repercussions can result if a workgroup information file has a conflict during synchronization.

Distributing Secured Applications with the Microsoft Office Developer

The biggest thing to remember when you're going to distribute an application is that the /runtime command-line argument is no substitution for full user-level security.

If you rely only on the runtime environment (that is, no Design mode or database container) for protection of your application, it can be broken into with any copy of a full version of Access. Remember, Access can't compile its applications into executables to protect code and data.

Distributing Secured Applications by Creating an .mde File

One feature as of Access 97 is the capability to create an .mde file for distribution. If your database contains VBA code, saving your database as an .mde file compiles all modules, removes all editable source code, and compacts the destination database.

Because the editable code is removed, memory is saved, thus increasing performance.

To save a file as an .mde file, choose Database Utilities from the Tools menu. Then choose Make MDE File. You're presented with the Database to Save As MDE dialog. Because this dialog is one of the common file dialogs, simply locate the file and then click Make MDE. When creating the MDE for a ADP, the extension will be ADE.