A while ago we were asked to deliver a SharePoint platform with calculation tools that needs a secure structure to make different tools available to a group of authorized people (internal and external users).

The goal was to share calculation tools (Excel documents with different calculation sheets) that can be shared with people from within or outside the organisation. The client wanted to have control over who can access the calculation tools and with which rights (read, write, execute,…) This is done by making SharePoint Lists available to a group of people where the calculation tools can be downloaded from and worked with according to the rights that the user has who did the download.

To achieve this, we had to make sure that:

  • we could define the rights (read / write / execute / …) for working with the tools for the different users
  • we could restrict the use of the tools for the different users

For the first requirement, we came up with Azure Rights Managment Service which is accessable by internal and external users of the client.

Azure Rights Management Service

Azure Rights Management (Azure RMS) can protect company’s sensitive information in all these scenarios. It uses encryption, identity, and authorization policies to help secure files and email, and it works across multiple devices – phones, tablets, and PCs. Information can be protected both within your organization and outside your organization because that protection remains with the data, even when it leaves your organization’s boundaries. As an example, employees might email a document to a partner company, or they save a document to their cloud drive. At the moment that the file is accessed, the – RMS enabled – client makes contact with Azure RMS to check which rights the user has when working with the file. The persistent protection that Azure RMS provides not only helps to secure your company data, but might also be legally mandated for compliance, legal discovery requirements, or simply good information management practices.

picture 1

For the second requirement, an external webservice is created that checks whether the user has the right to use the tool. This is achieved through a macro that is executed at the time that the tool is opened. It is even RMS that checks whether macro’s can be executed. If the user does not have the rights (from RMS) to execute a macro, the external webservice that checks whether the user can work with the tool isn’t even called which disables the user the use of the tool.

Overview

In the picture 2 we have SharePoint Online that makes the SharePoint List with calculation tools (design tools) available (1) and which are shared with internal and external users. If the user can access the SharePoint List, he makes a download of the calculation tool he needs. The calculation tools are protected by an RM template that resides on Azure Rights Management Service. If the user opens the calculation tools (which is an RMS protected Excel file), he will be asked to authenticate himself (2) with his O365 account. If the authorization went fine, Azure RMS will be called to find out which rights the user has for the Azure RM template (3) with which the calculation tool is protected.

The different rights are

  • VIEW Interpreted by most applications as allowed to present the data on the screen
  • EDIT Interpreted by most applications as allowed to modify content in the document and save it
  • DOCEDIT Interpreted by most applications as allowed to modify the content of the document.
  • EXTRACT Interpreted by most applications as allowed to copy the content to the clipboard or otherwise extract the content in unencrypted form.
  • VIEWRIGHTSDATA Interpreted by applications as allowed to view the policy on the document.
  • EDITRIGHTSDATA Interpreted by applications as allowed to modify the policy on the document.
  • OBJMODEL Interpreted by most applications as allowed to access the document programmatically; for example, by using macros.
  • EXPORT Interpreted by most applications as allowed to save the file in unencrypted form. For example, this right allows you to save in a different file format that does not support protection.
  • PRINT Interpreted by most applications as allowed to print the document.
  • OWNER User has all rights on the document, including the ability to remove protection.
  • FORWARD Interpreted by most applications as allowed to forward an email message, and to add recipients to the To and Cc lines.
  • REPLY Interpreted by most applications as allowed to select reply to an email message, without allowing changes in the To or Cc lines.
  • REPLYALL Interpreted by most applications as allowed to reply to all recipients of an email message, but doesn’t allow the user to add recipients to the To or Cc lines

At the moment that the calculation tool starts, an initial macro is activated which will call a custom external webservice that will supplementary check whether the authenticated user may use the calculation tool (4). This is an extra security that the client wanted to make sure that they can deny access to the calculation tool to users at any time (5)

Now, accessing macro’s in the calculation tool is a right that needs to be activated in the RM template. So, if this right isn’t activated, the macro can’t be called and the custom webservice can’t grant access to use the calculation tool which in fact already enables RMS to deny using the tool.

picture 2

Integration with SharePoint

In SharePoint, two lists are created:

  • A SharePoint list with the set of calculation tools that can be used by the users. Each calculation tool resides in a SharePoint Document Library
  • A SharePoint list with the mapping between a calculation tool and the internal or external user that can access the tool
Update Rights Management policy

The first SharePoint list is used by an external Azure App that retrieves from SharePoint a unique list of users and the tools that the user has access to. On the basis of this list, Azure RMS templates are updated with the correct rights that users have when working with the tool. Each tool is protected with a specific Azure RMS template. So, when a user opens a tool, the RMS template  with which the tool is protected – will be checked through the Azure Rights Management Service. If the user was previously added to the RMS template – by the Azure App – and has the correct rights, the user can continue working with the tool after he is authorized by the external webservice.

To enable this, a power shell script is created :

We authenticate to the Azure RMS Service environment and add some right definitions with the New-AadrmRightDefinition command. To create the right definitions, we need the several users and the rights we will give them. In our example we give them all View and Programmatic rights.

Then we retrieve the RM templates that needs to be updated en add the Right Definitions to the templates with the SetAadrmTemplateProperty command.

Finally we process and remove the PowerShell script with a ProcessStart activity with the windows power shell application.

 The created PowerShell file looks like this :

Check Access right

The second SharePoint list will be used by the external web service that will be called by a macro in the tool that the user wants to work with. If there is a correct mapping of the user and the tool, the user can continue working with the tool.

This solution made it possible for our customer to share calculation tools to internal and external users without loosing control over who has which rights when working with the calculation tools and the possibility to restrict the access to users who can or can’t work with the calculation tool.

Jim De Donder

Analyst Developer @ Spikes