Secure a PowerPivot Workbook on SharePoint
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
One of the advantages of sharing information on SharePoint is the ability to control access to documents and operations. This topic explains the permissions and features for securing your PowerPivot workbooks on SharePoint.
This topic contains the following sections:
Securing against information disclosure
Create Secure Locations for PowerPivot Workbooks
Grant Permissions
Use Workflows and Alerts to Manage the Publication of Workbooks that Contain Sensitive Information
Securing against information disclosure
There are two ways in which sensitive data in a PowerPivot workbook could be inadvertently disclosed to others: thumbnail images that provide document preview in PowerPivot Gallery, and view permissions on a workbook that enable access to its embedded data.
Preview images in PowerPivot Gallery
PowerPivot Gallery includes document preview that shows thumbnail images of PowerPivot workbooks and Reporting Services reports that are based on PowerPivot workbooks in the same library.
Note
You must have Read permissions on a document to view its preview in PowerPivot Gallery.
One case where this behavior results in potential information disclosure is when you use one document (a PowerPivot workbook) as an external data source, and another document (either a report or another workbook) to visualize that data. In this scenario, if you restrict permission on the workbook that provides the data, but allow View access on the document that visualizes the data, then preview images of the data visualization document will reveal the source data even though the user does not have View permissions on the data source document.
To ensure that preview images do not inadvertently disclose sensitive data, use the same permissions on both documents. Alternatively, if preview is not required, consider using a standard SharePoint library rather than PowerPivot Gallery to store the documents. You can secure each document independently, without risk of information disclosure through preview.
Hidden data in a workbook is available for data re-use
Before you publish a PowerPivot workbook to SharePoint, review the data so that you fully understand what you are publishing. Colleagues who use your published workbook as a data source for their own workbooks and reports will have full access to the data that is embedded in your workbook. When you use a PowerPivot workbook as an external data source, all data that you added to the PowerPivot window is accessible, even if it is not visible in Excel PivotTables and PivotCharts. For example, filters that you use to exclude information in a PowerPivot table or PivotTable do not apply when you or someone else uses that same PowerPivot data to create a Reporting Services report or as a data source to import into a second workbook.
To protect a workbook that contains sensitive data, be sure that you publish the workbook to a library that is only accessible by authorized users. In SharePoint, permissions are set on documents, and not on information within a document. Individuals who have permission to publish PowerPivot workbooks make their data available to anyone who has view access to the document.
Create Secure Locations for PowerPivot Workbooks
Creating a secure destination for your PowerPivot workbooks requires that you work with your site administrator. A site administrator can help you secure PowerPivot data by:
Providing sites and libraries that are secured at different levels for different groups. You can choose different publishing locations based on the sensitivity of the data in the workbook.
Setting up controls that determine how documents are published and used. For example, a site or list administrator with Manage Lists or Full Control permission can create workflows to require approval before a document is published. Similarly, Contributors who publish workbooks can create alerts to find out whether other people with the same permissions changed and replaced the document. For more information, see Use Workflows and Alerts in this topic.
Storing workbooks in a PowerPivot Gallery is recommended for its preview and document creation features that make it easy to create reports or other workbooks based on PowerPivot data in the gallery.
You can secure PowerPivot Gallery using the same approaches you use to secure any other library in SharePoint. As with any site or list, you can replace inherited permissions with custom permissions to grant document access to specific groups or individuals. You can create multiple PowerPivot Gallery libraries and then vary the storage and security policies for each one.
For more information about how to create and use PowerPivot Gallery, see Manage PowerPivot Gallery in SQL Server Books Online on the Microsoft web site. For more information on how to secure your sites and libraries in SharePoint 2010, see Plan security for sites and content on the TechNet web site.
Grant Permissions
SharePoint permissions determine who can publish or view workbooks on your SharePoint site. Although SharePoint provides multiple permission levels, just the following two permissions are used for PowerPivot workbook access:
Permission |
Description |
|---|---|
View Only |
People with View Only permissions of a workbook and a workbook-used-as-data source can open PowerPivot workbooks in a browser, download a snapshot of the workbook, or reload the workbook from its current location. Note To see data in a workbook or in a snapshot of the PowerPivot Gallery, you must have the same permissions on the workbook and the workbook-used-as-data source. If you do not have the same permissions on both (view-only or better), then you do not see a snapshot. |
Contribute |
People with Contribute permissions can use Library Tools to add, edit, or download documents on a site. Contribute permissions also allow you to change or manage views, email a link, subscribe to feed, and use some Connect & Export options. |
You must be a site administrator to view or grant permissions.
To grant permissions to view or publish a PowerPivot workbook
In the site, click Site Actions.
Click Site Permissions.
Select the checkbox for the site collection Members group.
On the ribbon, click Grant Permissions.
Enter the Windows domain user or group accounts who should have permission to add or remove documents.
Do not use e-mail addresses or distribution groups unless the application is configured for Claims authentication.
Click OK.
Select the checkbox for the site collection Visitors group.
On the ribbon, click Grant Permissions.
Enter the Windows domain user or group accounts who should have permission to view documents. As before, do not use e-mail addresses or distribution group if the application is configured for classic authentication.
Click OK.
Use Workflows and Alerts to Manage the Publication of Workbooks that Contain Sensitive Information
You must be a site or list administrator to create workflows. You must be a Contributor to create alerts.
To create an alert on changes to an existing PowerPivot workbook
Right-click the workbook in the library and select Alert Me.
In Send Alerts for These Changes, specify the criteria for which you want to receive an alert.
Click OK.
Note
If Alert Me is not available, the feature is not configured. Ask your SharePoint administrator to configure outgoing e-mail settings for the web application that hosts your site.