Section 15.2. Macros and Security | Access 2007[c] The Missing Manual

15.2. Macros and Security

In recent years , the people at Microsoft have become paranoid about security. They've clamped down in Office programs like Access in a bid to lock out evil virus writers. And although these changes make Access a safer place to be, they also make it a bit inconvenient to use certain types of macros.

15.2.1. Unsafe Macro Actions

Access distinguishes between two types of macros: those that are always harmless, no matter how they're used, and those that have the potential for abuse. The OpenTable macro's harmless. It could open a table you don't want to see, but it can't cause any real mischief. On the other hand, the PrintOut macro isn't as innocent. In the wrong hands, it could send 400 copies of your data to the printer in 80-point font. Similarly, DeleteObject could wreak real havoc in your database, and RunApp definitely isn't safeit could launch the latest spy ware or install a computer virus.

POWER USERS' CLINIC
Dealing with Macro Errors

You'll face two types of errors with macros. First, there are the errors you make when you design your macro. Using single-step debugging, you can track these down and fix them. Second, there are errors that occur when the macro's being used in the wrong context. Perhaps the data you need isn't in the current record, or the form you're trying to use isn't open. You can't avoid this sort of error by changing the macro, but you can tell Access what to do about it.

Ordinarily, Access halts your macro as soon as an error occurs. If you want to take a different approach, then start your macro with the OnError action. The OnError action chooses one of three error handling options, depending on the Go To argument. Set it to Fail, and you get the standard behavior. Set it to Next, and Access skips over a troublecausing action and runs the next action in the list. And if you set it to Macro Name , Access jumps down the list until it finds a specific macro. (You tell it what macro to go to, using the Macro Name argument.) You'll learn how macro names work in Section 15.4.

OnError is an unusual action, because its effects last throughout your macro (or at least until the next time you use OnError). In a long, complex macro, you can call OnError several times. But be careful not to use error handling options that may cause additional problems. In many macros, one action depends on the next, so it's best to halt the entire macro at the first sign of trouble.

When you create a macro, the drop-down list of actions shows only actions that are 100 percent harmless. These actions are known as safe actions. Of course, there are valid reasons to use potentially unsafe macros. Maybe you really do want to print out a report, delete an object, or run another program. In that case, you need to use potentially unsafe macro actionsones that Access doesn't trust quite so readily.

Note: As long as you're the one in control of your database, you know it doesn't contain devious code and other trickery . There's no good reason to stay away from potentially unsafe macro actions. However, if someone's just sent you a database in an email message, or if you've downloaded a database from the Web, you may not be so sure. For that reason, Access automatically disables the unsafe macros in a database, unless you tell it otherwise . You'll learn more about how this works in Section 15.2.2.

To see the full list of macro actions, including those that Access considers unsafe, create a new macro (or open an existing one), and then choose Macro Tools Design Show/Hide Show All Actions. Now the drop-down list of actions includes several more possibilities. When you choose an unsafe action while building a macro, Access lets you know with a warning icon (Figure 15-8).

Note: Access has no concept of what actions may be more or less dangerous. Instead, it simply distinguishes between safe and unsafe.

Figure 15-8. The triangular exclamation mark highlights the actions that Access may refuse to run. In this case, the PrintOut action's the problem.

UP TO SPEED
The Actions that Access Doesn't Trust

Here's the lineup of the most common unsafe actions:

Deleting an object . Clearly, a dangerous move.
Printing an object . Because who knows how much paper you'll need?
Copying an object . A devious attacker could use this to create a macro that fills up your database.
Saving an object . This action may seem fairly innocent, but it could easily be combined with other actions to create a macro that changes a database object, and then saves a tampered version.
Copying your database file . After all, this action could overwrite a copy that you've already made, or replace another important file. Exporting data's considered just as risky.
Maximizing, minimizing, or moving a window . Perhaps Microsoft was being a little too conservative when it decided not to trust these actions, which let you reposition forms and other windows. In any case, people don't use these actions too often in Access 2007, because they don't apply to tabbed windows , only the less commonly used free-floating windows (Section 1.3.6).
Running SQL . As you learned in Section 6.2.3, SQL is the language that underlies Access queries. You can use raw SQL commands to perform just about any task in your database, from deleting a dozen records to creating a new table.
Running VB code . Although this action doesn't appear with an exclamation icon, Access treats all VB code as unsafe. You'll learn more in Chapter 16.
Sending arbitrary keystrokes . The SendKeys action lets you send a stream of keystrokes to the currently active window. You can do just about anything, and that's the problem. Respectable Access users avoid SendKeys anyway, because it's a bit buggy . (Weird problems occur if you click with the mouse while the macro runs, and end up directing the keystrokes to a different window from the one you intended.)

Some actions may be considered unsafe, depending on what arguments you use with them:

Quitting Access . Access lets you perform a normal Quit action, which prompts the person using the database to save changes, discard them, or cancel the exit request. However, you can configure the Quit action to close immediately without prompting you to save anything (or to close immediately, and save all outstanding changes). If you use either of these options, then Access treats the action as unsafe.
Sending an email . This action's considered unsafe if you don't let the macro user confirm or cancel the message before it's sent.

15.2.2. How Access Handles Unsafe Macros

You've learned the difference between safe and unsafe macro actions, but you haven't considered what Access does when it comes face to face with a risky action. Previous versions of Access pop up a stream of warning messages. Access 2007 handles the challenge on its own, by quietly disabling the unsafe macros whenever you open a database file.

As you've no doubt noticed by now, when you open a database, Access shows a security message, as shown in Figure 15-9. This message warns you that Access has switched off any potentially risky parts of your database.

Note: The message bar can be hidden. If you think Access has disabled some macros, but you don't see the message bar, then choose Database Tools

Show/Hide

Message Bar.

Figure 15-9. The message bar gives you an ominous alert. To switch the unsafe macros back on in this database, click Options and then, in the window that appears (shown in Figure 15-10), select "Enable this content". The permission you're granting lasts only as long as the database is open, so you'll need to click Enable Content every time you open the database. You could also click the "Open the Trust Center" link (shown in Figure 15-10) to adjust the security settings for a more permanent solution.

All this fuss about safe and unsafe macros might seem a little unnecessary, considering you can enable all your macros and get back to normal with a quick click of the Enable Content button. However, life isn't always that easy. Here's why:

Although you may not be bothered by a few unnecessary warning messages, other people won't be as trusting. They'll see the security warning and think twice, which means they won't be able to use all your database's features. Or they might not understand the question, or realize they need to click Enable Content.
In corporate environments, the system administrator can configure Access so it doesn't show the security warning at all. Your macros are quietly disabled, and the person using the database won't understand why certain features don't work.
Clicking Enable Content for the one thousandth time gets annoying. Really.

Figure 15-10. When you click Options (as shown in Figure 15-9), Access explains the problem with this slightly intimidating Message box. Choose "Enable this content", and then click OK, and you're good to go.

15.2.3. The Trust Center

So what if you don't want to face the message bar every time you open a database? Access gives you three options to make it easier to work with databases that contain unsafe macros:

You can lower the Access security settings so that unsafe macros are allowed . This approach isn't recommended, because it allows any code in your database. If you accidentally open a database that contains troublemaking code, you have no protection.
You can tell Access to trust the database files in certain folders on your computer (or on other computers) . This way's the most convenient way to go.
You can tell Access to trust databases that have been created by a trusted publisher . This option's the most secure, but in order to set it up, you need to pay another company to get a security certificate. For that reason, only big companies with money to burn use this option.

All these actions take place in the same window: the Trust Center (Figure 15-11). To get to it, in the Microsoft Office Security Options dialog box (Figure 15-10), click the "Open the Trust Center" link. Or, use the following more roundabout approach:

Choose Office button Access Options .
Click the Trust Center Settings button .

Figure 15-11. The Macro Settings section lets you decide how Access reacts to unsafe macros. You can choose to enable or disable unsafe macros, and you can choose whether or not Access should notify you when it disables something.

There are six sections in the Trust Center:

Trusted Publishers lets you tell Access to trust databases that are digitally signed by certain people. In order to use this feature, your company needs to buy a digital certificate from a company like VeriSign (www.verisign.com). Then, when you open a signed database, Access contacts the company that issued the certificate, and checks that it's valid. If it is, everything's kosher, the database is trusted, and all unsafe macros are allowed. Digital certificates are outside the scope of this book.

Note: If you dig around long enough, you'll discover that Microsoft has a tool (named makecert.exe ) for generating your own digital certificates. However, this tool's for testing purposes only, as the certificates it generates doesn't work on anyone else's computer. Bewaresome Access books and Web sites may lead you astray.
Trusted Locations lets you pick out the places on your hard drive where you store your databases. That way, Access knows to trust your database files, but not anyone else's. You'll learn how to set up a trusted location in the following section.
Add-ins lets you adjust whether Access add-ins (mini programs that extend the features in Access) should be supported even if they weren't created by a supported publisher. Ordinarily, all add-ins are allowed. (After all, if you don't trust a specific add-in, don't install it!) People use this setting only in corporate environments where they need to lock down Access severely to prevent any chance of a problem.
Macro Settings lets you configure how Access deals with macros. You can make it more rigorous (so that no macros are allowed, unless they're from a trusted publisher), or less (so that all macros are allowed, no matter what they might do). By far the best choice is to leave this option at the standard setting: "Disable all macros with notification."
Message Bar lets you set whether Access shows the message bar when it blocks unsafe macros in an untrusted database.
Privacy Options lets you tweak a few options that aren't related to macros at all. You can choose whether Access checks the Web for updated Help content, and whether it sends troubleshooting information to Microsoft when a problem occurs (so that Microsoft can spot bugs and learn how to improve Access in the future). If you're paranoid about Internet spies, then you may want to disable some of these options. Most of the time, these settings are only for conspiracy theorists.

15.2.4. Setting Up a Trusted Location

Wouldn't it be nice to have a way to distinguish between your databases, which contain perfectly harmless code, and other databases? Access 2007 adds a new feature to make this easy. It lets you designate a specific folder on your hard drive as a trusted location. If you open a database in this location, then Access automatically trust it and allows unsafe macros.

Note: Of course, it's still up to you to make sure that you don't put potentially dangerous databases in the trusted location. If you do, then you won't have any protection when you open it. However, this compromise is reasonable, because most Access fans are already in the habit of putting their databases in a separate folder.

Here's how you can set up a new trusted location:

Open the Trust Center window .

If you're not there already, follow the steps in Section 15.2.3.
Choose the Trusted Locations section .

You see a window that lists all the trusted locations (Figure 15-12). Initially, you just see one trusted location: the ACCWIZ folder that Access uses to store its wizard.
Make sure the "Disable all Trusted Locations" options isn't set .

If it is, you need to switch it off before you can use the trusted locations feature.
If you want to trust a folder on your company or home network, then choose "Allow trusted locations on my network" .

This setting's a bit riskier, because a network location's out of your control. A hacker could sneak a virus-laden database into that location without your noticing. However, if you're reasonably certain that the network's secure (and the other people who use the folder aren't likely to download databases from the Web and place them there), you don't need to worry.

Figure 15-12. In this example, a new trusted location has been added for the FunkyAccessFiles subfolder in the hard drive's My Documents section.

Click "Add new location" .

Access asks you to fill in a few pieces of information (Figure 15-13).

Figure 15-13. To configure a trusted location, you need to specify the path (click Browse to hunt it down). You can also choose whether or not subfolders of this folder should be trusted, and you can fill in an optional description that appears in the list of trusted locations.

Click OK to add the location to the list .

You can configure the location or remove it at any time by selecting it in the list, and then using the clear-as-a-bell Remove and Modify buttons .