Help secure an Access 2007 database

This article explains how to help keep your Microsoft Office Access 2007 databases more secure. It explains the concepts that you must understand before you can use the Office Access 2007 security features properly and how to use the tools that Access provides for helping to secure a database.

What's new in Office Access 2007 security

Office Access 2007 provides an improved security model that helps to simplify the process of applying security to a database and of opening a database that has security enabled.

Note: Although the model and techniques discussed in this article enhance security, the most secure way to help protect your data is to store your tables on a server, such as a computer running Windows SharePoint Services 3.0, and to store your forms and reports on local computers or network shares. For information about using Access databases with Windows SharePoint Services 3.0, see the articles Export a table or query to a SharePoint site and Import from or link data to a SharePoint list.

The following is a list of what's new in Office Access 2007 security:

The ability to view data even when you don't want to enable any disabled Microsoft Visual Basic for Applications (VBA) code or components in a database. In Microsoft Office Access 2003, if you set your security level to High, you had to code-sign and trust a database before you could view data. In Office Access 2007, you can open databases and view data without having to decide whether you should enable a database.
Greater ease of use. If you place database files (in either the new Office Access 2007 file format or the earlier file formats) in a trusted location, such as a file folder or network share that you designate as secure, those files will open and run without displaying warning messages or asking you to enable any disabled content. Also, if you open databases from earlier versions of Access, such as .mdb or .mde files, in Office Access 2007, and those databases have been digitally signed and you have chosen to trust the publisher, those files will run without the need to make trust decisions. However, remember that VBA code in a signed database will not run until you trust the publisher, and it will not run if the digital signature becomes invalid. A signature becomes invalid when someone other than the person who signed it tampers with the content of a database. For more information about signing databases, see the section How security works with databases from earlier versions of Access opened in Office Access 2007.

If you are unsure of whether to trust a certificate, the article How to tell if a digital signature is trustworthy provides general information about checking the dates and other items in a certificate to ensure that it is valid.
The Trust Center. The Trust Center is a dialog box that provides a single location for setting and changing security settings for Access. You use the Trust Center to create or change trusted locations and to set security options for Office Access 2007. Those settings affect how new and existing databases behave when they are opened in that instance of Access. The Trust Center also contains logic for evaluating the components in a database and for determining whether the database is safe to open or whether the Trust Center should disable the database and let you decide to enable it. For information about using the Trust Center with Access, see Use an Office Access 2007 database in a trusted location later in this article.

For general information about using the Trust Center, see the article View my security and privacy settings in the Trust Center.
Fewer warning messages. Earlier versions of Access forced you to deal with a variety of alert messages — macro security and sandbox mode, just to name two. By default, if you open an Office Access 2007 database outside of a trusted location, you see a single tool called the Message Bar.

If you know that you can trust the database, you can use the Message Bar to enable any disabled components — action queries (queries that add, delete, or change data), macros, ActiveX controls, expressions (functions that evaluate to a single value), and VBA code — when you open a database that contains one or more of those components.
New ways to sign and distribute files created in the Office Access 2007 file format. In earlier versions of Access, you used the Visual Basic Editor to apply a security certificate to individual database components. In Office Access 2007, you package the database and then sign and distribute the package. If you extract a database from a signed package to a trusted location, the database runs without displaying the Message Bar. If you extract a database from a signed package to an untrusted location, but you have trusted the package certificate and the signature is valid, you don't need to make a trust decision. When you package and sign a database that is untrusted or that contains an invalid digital signature, you must use the Message Bar to trust the database each time you open it, unless you place it in a trusted location.
A stronger algorithm for encrypting databases in the Office Access 2007 file format that use the database password feature. Encrypting a database scrambles the data in your tables and helps prevent unwanted users from reading your data.
A new subclass of macro actions that run when a database is disabled. These safer macros also contain error-handling capabilities. You can also embed macros (even those that contain actions that Access disables) directly into any form, report, or control property that would logically work with a module of VBA code or a macro from an earlier version of Access.

For more information about macros, see the article Macro basics in Access 2007.

Finally, remember these rules as you proceed:

If you open the database in a trusted location, all components run without the need to make trust decisions.
If you package, sign, and deploy a database from an earlier version of Access (.mdb or .mde file), all components run without the need to make trust decisions if the database contains a valid digital signature from a trusted publisher, and you trust the certificate.
If you sign and deploy an untrusted database to an untrusted location, the Trust Center disables the database by default, and you must choose to enable the database each time you open it. For more information, see the section Enable disabled content when you open a database.

Office Access 2007 and user-level security

Office Access 2007 does not provide user-level security for databases that are created in the new file format (.accdb and .accde files). However, if you open a database from an earlier version of Access in Office Access 2007 and that database has user-level security applied, those settings will still function.

If you convert a database from an earlier version of Access with user-level security to the new file format, Access strips out all security settings automatically, and the rules for securing an .accdb or .accde file apply.

Finally, remember that all users can see all database objects at all times when you open databases that were created in Office Access 2007.

Office Access 2007 security architecture

To understand the Office Access 2007 security architecture, you need to remember that an Access database is not a file in the same sense as a Microsoft Office Excel 2007 workbook or a Microsoft Office Word 2007 document. Instead, an Access database is a set of objects — tables, forms, queries, macros, reports, and so on — that often depend on each other to function. For example, if you create a data entry form, you cannot enter or store data with that form unless you bind (link) the controls in the form to a table.

Several Access components can pose security risks, including action queries (queries that insert, delete, or change data), macros, expressions (functions that return a single value), and VBA code. To help make your data more secure, Office Access 2007 and the Trust Center perform a set of security checks whenever you open a database. The process works as follows:

When you open an .accdb or .accde file in Office Access 2007, Access submits the location of the database to the Trust Center. If the location is trusted, the database runs with full functionality. If you open a database from an earlier version of Access in Office Access 2007, Access submits the location and details about the digital signature applied to that database, if any.

The Trust Center checks that "evidence" to evaluate trust for the database and then informs Access how to open the database. Access either disables the database or opens it with full functionality.

Note: Remember that the settings you or your system administrator choose in the Trust Center control the trust decisions that occur when Access opens a database.

For more information about using the Trust Center, see the article View my security and privacy settings in the Trust Center.
If the Trust Center disables any content, the Message Bar appears when the database opens.

To enable any disabled content, you click Options and then choose options in the dialog box that appears. Access enables the disabled content, and the database reopens with full functionality. Otherwise, the disabled components will not work.
If you open a database that was created in the earlier file format (.mdb or .mde files), and that database is not signed and trusted, by default, Access disables any executable content.

Understand Disabled mode

When the Trust Center evaluates a database as untrusted, Office Access 2007 opens that database in Disabled mode — that is, it turns off all executable content. This is true for databases that were created in the new Office Access 2007 file format and for files created in earlier versions of Access.

Office Access 2007 disables the following components:

VBA code and any references in the VBA code, plus any unsafe expressions.
Unsafe actions in all macros. "Unsafe" actions are any actions that could allow a user to modify the database or gain access to resources outside the database. However, actions that Access disables can sometimes be considered "safe." For example, if you trust the person who created the database, you can trust any unsafe macro actions.
Several types of queries:
- Action Queries These add, update, and delete data.
- Data Definition Language (DDL) Queries These are used to create or alter objects in a database, such as tables and procedures.
- SQL Pass Through Queries These send commands directly to a database server that supports the Open Database Connectivity (ODBC) standard. Pass-through queries work with the tables on the server without involving the Access database engine.
ActiveX controls.

When a database opens, Access might attempt to load add-ins — programs that extend the functionality of either Access or the open database. You also might want to run wizards that create objects in the open database. When an add-in is loaded or a wizard starts, Access passes evidence to the Trust Center, which makes additional trust decisions and either enables or disables the object or action. Whenever the Trust Center disables a database and you disagree with that decision, you can almost always use the Message Bar to enable the content. Add-ins provide the exception to that rule. If, in the Trust Center (in the Add-ins pane), you select the Require Application Extensions to be Signed by Trusted Publisher check box, Access prompts you to enable the add-in, but that process does not involve the Message Bar. For information about using the Trust Center, see Use an Office Access 2007 database in a trusted location later in this article.

Top of Page

Use an Office Access 2007 database in a trusted location

When you place an Office Access 2007 database in a trusted location, all VBA code, macros, and safe expressions run when you open the database. You do not have to make trust decisions while the database opens.

The process of using an Office Access 2007 database in a trusted location follows these broad steps:

Use the Trust Center to find or create a trusted location.
Save, move, or copy an Office Access 2007 database to the trusted location.
Open and use the database.

The following sets of steps explain how to find or create a trusted location and then add a database to that location.

Start the Trust Center

Click the Microsoft Office Button , and then click Access Options.

Note: You do not need to open a database.

The Access Options dialog box appears.
Click Trust Center, and under Microsoft Office Access Trust Center, click Trust Center Settings.
Click Trusted Locations, and then do one of the following:
- Note the path of one or more trusted locations.
- Create a new trusted location. To do so, click Add new location, and then complete the options in the Microsoft Office Trusted Location dialog box.
  
  For more information about creating a trusted location, see the article Create, remove, or change a trusted location for your files.

Place a database in a trusted location

Use your favorite technique for moving or copying a database file to a trusted location. For example, you can use Windows Explorer to copy or move the file, or you can open the file in Access and save it to the trusted location.

Open a database in a trusted location

Use your favorite technique for opening a file. For example, you can locate and double-click the file in Windows Explorer, or, if Access is running, you can click the Microsoft Office Button to locate and open the file.

Top of Page

Package, sign, and distribute an Office Access 2007 database

Office Access 2007 makes it easy and fast to sign and distribute a database. When you create an .accdb file or .accde file, you can package the file, apply a digital signature to the package, and then distribute the signed package to other users. The Package and Sign tool places the database in an Access Deployment (.accdc) file, signs the file, and then places the signed package at a location that you determine. Users can then extract the database from the package and work directly in the database (not in the package file).

Remember these facts as you proceed:

Packaging a database and signing the package is a way to convey trust. When you package and sign a database, your digital signature confirms that the database has not been altered after you created the package.
After the database is extracted from the package, there is no longer a connection between the signed package and the extracted database.
You can use the Package and Sign tool only with databases saved in an Office Access 2007 file format. Access 2007 also provides tools to sign and distribute databases that were created in an earlier file format. You must use the digital signature tool that is appropriate for the database file format that you are using.
You can add only one database to a package.
The process digitally signs a package that contains your entire database, not just macros or modules.
The process compresses the package file to help reduce download times.
You can extract databases from package files that are located on Windows SharePoint Services 3.0 servers.

The steps in the following sections explain how to create a signed package file and how to extract and use the database from a signed package file.

Create a signed package

Open the database that you want to package and sign.
Click the Microsoft Office Button , click Publish, and then click Package and Sign.

The Select Certificate dialog box appears.
Select a digital certificate and then click OK.

The Create Microsoft Office Access Signed Package dialog box appears.
In the Save in list, select a location for your signed database package.
Enter a name for the signed package in the File name box, and then click Create.

Access creates the .accdc file and places it in the location that you chose.

Extract and use a signed package

Click the Microsoft Office Button , and then click Open.

The Open dialog box appears.
Select Microsoft Office Access Signed Packages (*.accdc) as the file type.
Use the Look in list to locate the folder that contains your .accdc file, select the file, and then click Open.
Do one of the following:
- If you chose to trust the security certificate that was used to sign the deployment package, the Extract Database To dialog box appears. Go to the next step.
- If you have not yet chosen to trust the security certificate, the following message appears.
  
  If you trust the database, click Open. If you trust any certificate from that provider, click Trust all From Publisher. The Extract Database To dialog box appears.
  
  Note: If you use a self-signed certificate to sign a database package and then click Trust all from publisher when you open that package, packages signed by using your self-signed certificates will always be trusted.
Optionally, in the Save in list, select a location for the extracted database and then, in the File name box, enter a different name for the extracted database.

Tip: If you extract the database to a trusted location, its contents will be automatically enabled whenever you open it. If you choose a non-trusted location, some database content may be disabled by default.
Click OK.

If you are unsure of whether to trust a certificate, the article How to tell if a digital signature is trustworthy provides general information about checking the dates and other items in a certificate to help ensure that it is valid.

Top of Page

Enable disabled content when you open a database

By default, Access disables all executable content in a database unless you either trust the database or place the database in a trusted location. When you open a database, Access disables the content and displays the Message Bar.

The Message Bar

Unlike Access 2003, Office Access 2007 does not display a set of modal dialog boxes (dialog boxes that require you to make a decision before you can do anything else) when you open a database. However, if you want Office Access 2007 to revert to that earlier behavior, you can add a registry key and display an older modal dialog box. Steps at the end of this section explain how to change the registry key.

Regardless of how Access behaves when it opens a database, if that database comes from a reliable publisher, you can choose to enable the executable components in the file.

Trust a database

On the Message Bar, click Options.

The Microsoft Office Security Options dialog box appears.
Select Enable this content and then click OK.

If you do not see the Message Bar

On the Database Tools tab, in the Show/Hide group, click Message Bar.

Important: When you follow these steps, Access enables all disabled content, including potentially malicious code, until you close the database. If malicious code harms your data or your computer, Access cannot undo that damage.

Close a database

Click the Microsoft Office Button , and then click Close Database.

When you reopen the database, the Message Bar appears again. At this point, you can close the Message Bar by leaving the disabled content in its disabled state or by hiding the bar. Either action has the same effect — all disabled content remains disabled.

Disable content

On the Message Bar, click Options.

The Microsoft Office Security Options dialog box appears.
Select Help protect me from unknown content (recommended) and then click OK.

Access disables all potentially dangerous components.

Hide the Message Bar

Without making a trust decision, click the Close button (X) in the upper corner of the Message Bar.

The Message Bar closes.

Show the Message Bar

On the Database Tools tab, in the Show/Hide group, click Message Bar. You can also close and reopen the database to show the Message Bar.

Add the registry key to display modal dialog boxes

Caution Incorrectly editing the registry may severely damage your operating system, requiring you to reinstall it. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For the most recent information about using and protecting your computer's registry, see Microsoft Windows Help.

In Microsoft Windows, click the Start button, and then click Run.
In the Open box, type regedit and then press ENTER.

The Registry Editor starts.
Expand the HKEY_CURRENT_USER folder and navigate to the following registry key:

Software\Microsoft\Office\12.0\Access\Security
In the right pane of the Registry Editor, right-click the blank area, point to New, and click DWORD Value. A new, blank DWORD value appears.
Type the following name for the value: ModalTrustDecisionOnly.
Double-click the new value.

The Edit DWORD Value dialog box appears.
In the Value Data field, change the 0 value to 1, and then click OK.
Close the Registry Editor.

Now when you open a database that contains unsafe content, you see a series of dialog boxes instead of the Message Bar. To revert to the original behavior, repeat these steps and change the 1 value to 0.

Top of Page

Use a database password to encrypt an Office Access 2007 database

The encryption tool in Office Access 2007 combines and improves on two older tools — encoding and database passwords. When you use a database password to encrypt a database, you make all data unreadable by other tools, and you force users to enter a password to use the database. The encryption applied in Office Access 2007 uses a stronger algorithm than was used in earlier versions of Access.

Encrypt by using a database password

Open the database that you want to encrypt in Exclusive mode.

Open the database in Exclusive mode
- Click the Microsoft Office Button , and then click Open.
- In the Open dialog box, browse to the file that you want to open, and then select the file.
- Click the arrow next to the Open button, and then click Open Exclusive.
On the Database Tools tab, in the Database Tools group, click Encrypt with Password.

The Set Database Password dialog box appears.
Type your password in the Password box, and then type it again in the Verify field.
Notes:
- Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.
- It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.
Click OK.

Decrypt and open a database

Open the encrypted database as you typically open any other database.

The Password Required dialog box appears.
Type your password in the Enter database password box, and then click OK.

Remove a password

On the Database Tools tab, in the Database Tools group, click Decrypt Database.

The Unset Database Password dialog box appears.
Type your password in the Password box, and then click OK.

Top of Page

How security works with databases from earlier versions of Access opened in Office Access 2007

When you open a database that was created in an earlier version of Access, any security features applied to that database still work. For example, if you applied user-level security to a database, the feature works in Office Access 2007.

By default, Access opens all older untrusted databases in Disabled mode and keeps them in that state. You can choose to enable any disabled content each time you open the older database, or you can apply a digital signature by using a certificate from a trusted publisher, or you can place the database in a trusted location.

Important: The steps in this section do not apply to databases that use one of the new file formats.

For databases earlier than Office Access 2007, you can apply a digital signature to the components in the database. A digital signature confirms that any macros, code modules, and other executable components in the database originated with the signer and that no one has altered them since the database was signed.

To apply a signature to your database, you first need a digital certificate. If you create databases for commercial distribution, you must obtain a certificate from a commercial certificate authority (CA). Certificate authorities do background checks to verify that the people who create content (such as databases) are reputable.

To learn more about certification authorities that offer services for Microsoft products, refer to the See Also section.

If you want to use a database for personal or limited workgroup scenarios, Microsoft Office Professional 2007 provides a tool for creating a self-signed certificate. The steps in the following sections explain how to install and use a tool called SelfCert.exe to create a self-signed certificate.

Create a self-signed certificate

In Microsoft Windows, click the Start button, point to All Programs, point to Microsoft Office, point to Microsoft Office Tools, and then click Digital Certificate for VBA Projects.

-or-

Browse to the folder that contains your Office Professional 2007 program files. The default folder is Drive:\Program Files\Microsoft Office\Office12. In that folder, locate and double-click SelfCert.exe.

The Create Digital Certificate dialog box appears.
In the Your certificate's name box, type a name for the new test certificate.
Click OK twice.

Note: If you don't see the Digital Certificate for VBA Projects command, or you can't find SelfCert.exe, you might need to install SelfCert.

Code sign a database

Note: Remember that these steps apply only when you are using databases in Office Access 2007 that use one of the earlier database file formats, such as an .mdb file. To sign newer databases, see the section Package, sign, and distribute an Office Access 2007 database.

Open the database that you want to sign.
On the Database Tools tab, in the Macro group, click Visual Basic to start the Visual Basic Editor.

Keyboard shortcut Press ALT+F11.
In the Project Explorer window, select the database or Visual Basic for Applications (VBA) project that you want to sign.
On the Tools menu, click Digital Signature.

The Digital Signature dialog box appears.
Click Choose to select your test certificate.

The Select Certificate dialog box appears.
Select the certificate that you want to apply.

If you followed the steps in the previous section, select the certificate that you created by using SelfCert.
Click OK to close the Self Certificate dialog box, and click OK again to close the Digital Signature dialog box.

Tips for signing earlier version databases

If you want to prevent users of your solution from accidentally modifying your VBA project and invalidating your signature, lock the VBA project before signing it.

Note: Locking your VBA project doesn't prevent another user from replacing the digital signature with another signature. Corporate administrators might re-sign templates and add-ins so that they can control exactly what users may run on their computers.
When you digitally sign a VBA project, consider obtaining a timestamp so that others can verify your signature even after the certificate used for the signature has expired. See Microsoft Office Online for more information about VBA security and timestamps.

Note: Remember that these steps apply only when you are using databases in Office Access 2007 that were created in earlier versions of Access. To sign newer databases, see the section Package, sign, and distribute an Office Access 2007 database.

Install SelfCert.exe

Start your Office Professional 2007 Setup CD or other installation media.
In Setup, click Add or Remove Features, and then click Continue.

Note: If you work in an environment in which Office Professional 2007 is installed on individual computers by IT administrators rather than by CD, follow these steps:
1. In Microsoft Windows, click the Start button, and then click Control Panel.
2. Double-click Add or Remove Programs.
3. Select 2007 Microsoft Office system, and then click Change.
  
  Setup starts.
4. Click Add or remove features, and then click Continue.
5. Continue with the following steps.
Expand the Microsoft Office and Office Shared Features nodes by clicking the plus signs (+) next to them.
Click Digital Certificate for VBA Projects.
Click Run from My Computer.
Click Continue to install the component.
Click Close after the installation finishes, and then go back to the first set of steps in this section.

Top of Page

Run unsafe expressions (disable sandbox mode)

When you add an expression to a database and you then trust the database or place it in a trusted location, Access runs that expression in an operating environment called sandbox mode. Access does this for databases that were created in either Office Access 2007 or earlier Access file formats. Access enables sandbox mode by default, and the mode always disables unsafe expressions, even after you trust a database. For more information about the expressions that sandbox mode disables, see the Microsoft Office Online article About Microsoft Jet Expression Service sandbox mode.

If you trust a database and you want to run an expression that sandbox mode disables, you can run that expression by changing a registry key and disabling sandbox mode. Remember that you must first trust a database to follow these steps.

The following drawing shows the decision process that you follow to run unsafe expressions.

The decision process for enabling or disabling sandbox mode

If you are not familiar with the registry, or you are not comfortable with changing registry keys, contact someone who is, or consider converting the database from the earlier version of Access to the Office Access 2007 file format. Also, you must have administrator permissions on the computer to change the registry values.

Change the registry key

Important: Following these steps allows unsafe expressions to run in all instances of Access for all users on the computer.

In Microsoft Windows, click the Start button, and then click Run.
In the Open box, type regedit and then press ENTER.

The Registry Editor starts.
Expand the HKEY_LOCAL_MACHINE folder and navigate to the following registry key:

\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines
In the right pane of the registry editor, double-click the SandboxMode value.

The Edit DWORD Value dialog box appears.
In the Value Data field, change the value from 3 to 2, and then click OK.
Close the Registry Editor.

Important Remember that if you do not first trust the database, Access disables any unsafe expressions regardless of whether you change this registry setting.

Top of Page

1 comment:

AnonymousApril 8, 2022 at 10:17 AM
Microsoft Office Tutorials: Help Secure An Access 2007 Database >>>>> Download Now

>>>>> Download Full

Microsoft Office Tutorials: Help Secure An Access 2007 Database >>>>> Download LINK

>>>>> Download Now

Microsoft Office Tutorials: Help Secure An Access 2007 Database >>>>> Download Full

>>>>> Download LINK B7

Microsoft Office Tutorials

Sunday, October 7, 2018