Yesterday I discussed one of the main security mechanisms for spreadsheets saved to SharePoint - the View Item right - that guarantees users will only be able to view a spreadsheet on the server and not download it to the client.  Today I will cover how we ensure that the spreadsheets that users are viewing are the “right” spreadsheets – specifically, how to control which users can author spreadsheets that will be run on the server, and which versions of those spreadsheets will be available for users to view.

Controlling Who Can Publish Spreadsheets to Excel Services
The first step in controlling who can save spreadsheets to the server is controlling where the server will load spreadsheets from. The administrator does this by maintaining a list of directory paths, or “Trusted Locations.”  Excel Services checks this list before opening any spreadsheet and will not load and execute spreadsheets unless it comes from a “Trusted Location”.  Using SharePoint rights (for spreadsheets stored in SharePoint document libraries) or simple file system rights (for arbitrary UNC paths), the administrator can control who can save spreadsheets into these locations.  Effectively, this allows the administrator to control which users have access to save spreadsheets that will be executed by Excel Services. 

As an example, on a company intranet, all employees could have the rights to save spreadsheets (and other files) to various sites within a portal.  However, an administrator could designate one trusted location within that portal where only a select few users could save spreadsheets that would be loaded and executed by Excel Services.  In turn, the users browsing these spreadsheets are guaranteed that they are viewing sanctioned copies of spreadsheets.

Controlling the Publishing Process for Spreadsheets on Excel Services
When spreadsheets are stored in SharePoint document libraries, we can provide many more features for controlling the process of authoring and publishing spreadsheets, thereby guaranteeing not only that the right versions of the spreadsheet are made available to users, but that the spreadsheets have gone through proper review and approval cycles.  Additionally, we can provide an audit log that tracks who accessed which spreadsheet and when.  This is very useful in the context of compliance for example.  Let’s look at this in a bit more detail.

  1. Versioning - The new release of SharePoint has a robust check-in/check-out and versioning mechanism, allowing for major and minor version numbering, as well as security specifically for old versions of spreadsheets (and other documents).  Additionally, SharePoint has built-in functionality around retention and expiration of documents so that old versions of spreadsheets are automatically retained and then destroyed in order to meet compliance requirements.

Versioning settings in SharePoint

  1. Document approval - Document approval within SharePoint allows an administrator to setup a document library so that when a spreadsheet author saves a new version of a spreadsheet in the library, it is not immediately available to other users to view.  Instead, the spreadsheet needs to be reviewed (by an administrator or appointee such as the financial analyst in charge of the library) and can be either approved or rejected. Only once approved does the spreadsheet become available for everyone else that has rights to view it. This approval can be as simple as the administrator monitoring and changing a flag on the spreadsheet in the document library, or can be a custom workflow that sends emails to a group of approvers in order to assure that the spreadsheet meets any number of internal requirements prior to it's approval.

Document approval settings in SharePoint

3. Auditing - Finally, the new version of SharePoint allows administrators to audit key events within document libraries.  While we have not implemented auditing within spreadsheets themselves, events such as Open, Create, Modify, and Delete, of spreadsheets are all logged to a centralized audit log, and there are several built-in reports to analyze that log, as well as mechanisms to generate custom Excel reports.

Auditing settings in SharePoint

That wraps up my discussion of controlling and protecting spreadsheets.  As you can see, with Trusted Locations and core SharePoint document management features, Excel Services can help customers meet scenarios where it is important that only the "sanctioned" spreadsheet be shared among multiple people, and with the View Item right, can also help to ensure that the people viewing the spreadsheet are always accessing that single, “sanctioned” spreadsheet.

Next week I will talk in more detail about Data Connection Libraries and how Excel Services accesses external data.