181
CHAPTER 8
■ ■ ■
Securing a Pivot Table
I
f other people have access to your pivot table, you may want to disable some of the
features, address privacy concerns, monitor access to protected data sources, or prevent
users from making some changes. Some pivot table security settings require programming,
and are discussed in Chapter 13. Other settings can be made manually, and are explained
here.
In Excel, you can protect a file with a password when saving it. You can also password
protect a workbook’s structure and windows, as well as its worksheet contents. The Microsoft
Office 2003 Editions Security Whitepaper (www.microsoft.com/technet/prodtechnol/office/
office2003/operate/o3secdet.mspx) discusses security threats that exist and security
technologies available in Excel, and other Office programs. The whitepaper describes
security threats such as exposure of confidential data, viruses, and malicious code. It out-
lines the security features available in Office, including digital signatures, file protection,
personal data removal, Information Rights Management, and creating security settings.
As with other Excel security features, a knowledgeable user can circumvent most
PivotTable protection by using tools, such as password crackers, that are readily available
on the Internet. Applying Excel security features will deter novice users, and can help pre-
vent accidental errors or deletions, but may not thwart a determined malicious attack.
8.1. Using a Password-Protected Data Source
Problem
Your pivot table is based on an external data source that is password protected. When you
open the Excel file and refresh the pivot table, a Login dialog box appears, in which you have
to enter your login name and password. You want to use the pivot table without entering
the password every time.
Solution
You can change the PivotTable options to store the password with the pivot table:
6293ch08.qxd 2/1/06 2:28 PM Page 181

Figure 8-1. View or edit strings in the Edit OLE DB Query dialog box.
1. Right-click a cell in the pivot table, and choose Table Options.
2. Under External data options, add a checkmark to Save password, then click OK.
■
Caution
If you save the password with the pivot table, it will be visible in the pivot table’s connection
string, so security concerns may deter you from using this option. See the Notes section of this problem for
information on viewing the connection string.
Notes
To view the pivot table’s connection string, you can use programming, or, if the pivot
table was created from an OLE DB data source, the connection string may be visible in
the Edit OLE DB Query dialog box.
Follow these steps to open the Edit OLE DB Query dialog box:
1. Select a cell in the pivot table.
2. Choose Data ➤ Import External Data ➤ Edit Query.
3. In the Edit OLE DB Query dialog box (see Figure 8-1), the connection string, com-
mand type, and command text can be viewed and edited.
8.1
■
USING A PASSWORD-PROTECTED DATA SOURCE 182
6293ch08.qxd 2/1/06 2:28 PM Page 182
8.3
■
USING A DATA SOURCE: ACCESS DATABASE WITH USER-LEVEL SECURITY 183
Figure 8-2. Choose the option to rebuild the cube when the report is opened.
8.2. Using a Data Source: No Prompt for Password with OLAP Cube
Problem
Your pivot table is based on an offline OLAP cube that you created using the OLAP cube
wizard from a secured database. To protect the confidential data in the cube, you want to
prompt the user to enter a password in order to use the cube. Currently, the cube is not

password protected, and users can select the cube from another Excel workbook and view
the current data in the OLAP cube. The database password isn’t required until users try to
refresh the pivot table.
Solution
When you create an OLAP cube in Excel using the OLAP cube wizard, you can’t set a pass-
word for the offline OLAP cube. You could protect the cube file by storing it in a secured
network folder. Or, in Step 3 of the OLAP cube wizard, choose to rebuild the cube when
the report is opened instead of saving a cube file (see Figure 8-2).
8.3. Using a Data Source: Access Database with User-Level Security
Problem
Your Access database is protected by user-level security, so you can assign users to groups,
and control who has access to the database objects and data. Securing the database creates
a workgroup file, with an .mdw extension, where each user is identified by a unique iden-
tification code.
6293ch08.qxd 2/1/06 2:28 PM Page 183
You are trying to create a pivot table based on a table in the Access database, but you
get the error message “You do not have the necessary permissions to use the 'C:\Data\
SalesDataNew.mdb' object. Have your system administrator or the person who created
this object establish the appropriate permissions for you.” You have full permissions to
the database, and entered a valid user name and password, but still get the error.
Solution
You need to configure a data source so it uses the correct workgroup file. By default, the
data source will use the system.mdw workgroup file that’s shipped with Access.
To create a data source for the secured Access file, follow these steps:
1. Choose Data ➤ PivotTable and PivotChart Report.
2. Select External data source, and click the Next button.
3. Click the Get Data button.
4. In the Choose Data Source dialog box, select New Data Source and click OK.
5. Type a name for the secured data source, for example, Secured Sales Database.
6. From the dropdown list of drivers, select the Microsoft Access driver.

7. Click the Connect button.
8. In the ODBC Microsoft Access Setup dialog box, click the Select button.
9. Select the secured database and click OK.
10. In the System Database section, select Database, and click the System Database
button.
11. Select the workgroup file for the secured database, and click OK.
12. Click OK to close the ODBC Microsoft Access Setup dialog box.
13. Click OK to close the error message “Not a valid account name or password.”
14. In the Login dialog box, enter a valid login name and password, and click OK.
15. Click OK to close the Create New Data Source dialog box.
8.3
■
USING A DATA SOURCE: ACCESS DATABASE WITH USER-LEVEL SECURITY184
6293ch08.qxd 2/1/06 2:28 PM Page 184
8.4
■
PROTECTION: PREVENTING CHANGES TO A PIVOT TABLE 185
16. Click OK to close the Choose Data Source dialog box.
17. In the Query Wizard, or Microsoft Query, create your query, then complete the
PivotTable and PivotChart Wizard.
How It Works
To open an Access file that has user-level security, you need to use the workgroup file
for that database. This file, with an .mdw extension, is created when user-level security is
added to the database, and contains the valid user ID and password information. The
previous technique enters the workgroup file information into the DNS file, and that DNS
file is used as the data source for the database.
8.4. Protection: Preventing Changes to a Pivot Table
Problem
You want to prevent users from making any changes to the pivot table. They should be
able to view the pivot table, but not change the selected items, type over any of the field

names, or rearrange the layout. However, you want users to be able to make changes to
data and formulas in other areas of the worksheet.
Solution
If you protect the worksheet without enabling pivot table use, or if you share the workbook,
users won’t be able to modify the pivot table.
Protecting the Worksheet
When protecting a worksheet, prepare the sheet first by unlocking cells where changes
can be made. Then, turn on the worksheet protection.
To prepare the sheet, follow these steps:
1. Select any cells in which users will be allowed to make changes.
2. Choose Format ➤ Cells.
3. On the Protection tab, remove the checkmark from Locked. If some selected cells
are currently locked, and others are unlocked, the check box will contain a gray
checkmark. Click once in the check box to add a black checkmark, then click again
to clear the check box (see Figure 8-3).
6293ch08.qxd 2/1/06 2:28 PM Page 185