Back
Excel

Excel Services part 8: Controlling and protecting spreadsheets

To this point in my discussion of Excel Services, I have written primarily about the user-facing part of Excel Services – all the things customers can do with Excel Web Access and Excel Web Services in order to execute and interact with workbooks on the server.  In the next two posts, I plan to cover some of the security aspects of Excel – how customers who deploy Excel Services can “lock down†and protect key spreadsheets.

In my overview of Excel Services, I mentioned that a request that we frequently hear from customers is the ability to limit access to spreadsheets either for regulatory and audit concerns or to protect proprietary information in spreadsheets.  To address this requirement, one of the main things that we’ve done (in addition to allowing users to execute and view spreadsheets on the server) is extended the Windows SharePoint Services (SharePoint) architecture with a new “rightâ€, which we call a the “View Item†right.

Before I get into exactly what the View Item right is, let me give a bit of background on what SharePoint is, and how it relates to Excel Services.  As I’ve mentioned in previous posts, Excel Services is built as part of the SharePoint products and technologies platform.  For the context of this conversation on the View Item right, consider SharePoint as a document store on the server – users can save and version files, administrators can control access permissions, etc., all via any browser  (Note, SharePoint does *a lot* more than this – in addition to being a document store, SharePoint provides many more features which you can read about here).
 
Currently, SharePoint administrators can give users “Reader†rights (look at content), “Contributor†rights (look at, change, and add to content), or “Administrator†rights (full control).  One way to think of this is similar to a regular file system and the file access rights that can be set (e.g. read only, read/write etc.)

With the View Item right that we are adding, customers can lock down spreadsheets that have been published to SharePoint (this right is specific to SharePoint document libraries and does not work with workbooks stored in UNC shares or generic HTTP locations) such that users can open the spreadsheets using Excel Services, interact with the workbooks, and see the execution results, but can’t download a copy of the spreadsheet, or access any areas that were not published as viewable on the server.  This hides any proprietary information contained within the book – specific formulas, the proprietary model, the external data connections, and hidden elements of the book – all of these things become inaccessible to users.

Let’s look at some examples of how View Item can be used in an Excel Services solution.  Imagine a workbook that takes several inputs, and then calculates discount rates for a large retailer.  The discount rate for any specific distributor is dependant on many factors – what quantity of product is purchased, the time of year, and the number of previous transactions for a given distributor – and of course, this discount rate formula is carefully guarded by the retailer since it determines the profit made on each transaction.  With Excel Services, this retailer can now allow distributors View Item right to the workbook containing this sensitive model, without having to worry that they will actually be able to download or see the model.

The View Item right affects how both Excel Web Access and the Excel Web Services allow access to a workbook.  Let’s look at the specific elements that are affected:

1. Which portions of the workbook can be accessed by a user:  When a user only has the View Item right, they can only see the portions of the workbook that have been marked as viewable on the server during the publish process. 


(Click to enlarge)

View Item right prevents users from seeing ranges that were not marked as viewable during publish

 

2. Which portions of the workbook can be opened in Excel:  While users with the Reader right can always open the original workbook in Excel if they want to see the model/formulas/data connections/etc., users with the View Item right can only open a snapshot of the original workbook in Excel.  A snapshot is much like what you would get with a copy/paste values and formatting, so that the user can see the numbers, but none of the proprietary information behind those numbers (formulas, connections, etc.), since that information is not contained in the snapshot.  And, of course, they can only see the numbers for the portions of the workbook that were marked as visible on the server.


Workbook contains formulas and other proprietary information

 


Snapshot contains only the numerical values and formatting

 

These examples focus on accessing the spreadsheet through the browsing using Excel Web Access (the browser).  Similarly, if an application accesses the spreadsheet through Excel Web Services, the View Item right is enforced.  For example, issuing a “GetRangeA1†call to a range that has not been marked as viewable will result in an exception, as will “GetWorkbookâ€.

That sums up how users can lock down spreadsheets to protect proprietary information/ensure everyone is looking at the latest sanctioned version using the new View Item right within SharePoint.  Next, more about some of the security functionality that we’ve built into Excel Services – how Excel Services decides whether or not to execute a workbook, how it connects to external data sources, and how it integrates with some of the other security features in SharePoint like versioning, IRM, and document approval.