Share via

B2TR changes to UDFs in Excel Services

  • Article

In previous posts, I described how UDFs work and what they are useful for. One thing I did not discuss was the ability of a UDF to know under which user it’s actually being executed. The main reason for not discussing this before is that this ability was still a bit hokey during B2 and did not work in a predictable manner. With B2TR we added features to the UDF infrastructure to make this ability both safer and more consistent.

 

What is it good for

Since B2 (where UDFs were first available for users), it has always been possible to use the .NET principal object to figure out what user caused the UDF to run. The return value from the following call would return the user name – something a UDF can then use to figure out what action it needs to take:

 

string userName = System.Thread.CurrentPrincipal.Identity.Name;

 

Uses for this are wide and varied as uses for UDFs. One possible way to take advantage of this is to have user-based caches (see the post about state in UDFs for more information). Another is to use the name to figure out information about the user (from who the manager of this user is to what their salary is).

Another interesting thing that can be done is impersonating the user and then using their credentials to take action in the organization (think about accessing a database or OLAP server as the user and getting information that is relevant to that user without resorting to writing your own mechanisms for authentication). Impersonation in UDFs will be discussed in a future post.

So what’s the problem?

The problem with using this information in a UDF before B2TR is that Excel Services was just too diligent in all the caching that it does. As you will recall, one of the great features in Excel Services is its ability to cache information across users and thus reducing the amount of processing time and waiting that users have to go through. Well, it turns out that workbooks that contain UDF results are  also cached and reused among users. This in turn means that if Joe was the first person to load a workbook that uses the GetMySalary() function everything seemed just great and dandy. However, when Michael asked for the that workbook, he may end up seeing Joes salary. Not a good thing.

When Excel Services looks at a workbook, if it thinks it can share the information between users, it will do so. Until B2TR, there was no way of telling Excel Services: “Hey! This UDF returns personal information and thus workbooks that use it should never be shared.”

There have, however, been workarounds for this. Setting the volatile function lifetime setting in Excel Services to be zero would have guaranteed that each new user coming in would cause a recalculation of the workbook (as long as GetMySalary() was marked as Volatile) each and every time it is requested, thus giving the correct information.

This approach has numerous disadvantages. It overloads the meaning of volatility – something may return personal data and not be volatile. Furthermore, it can potentially greatly degrade the server performance since no caching of workbooks will occur – a new workbook will be calculated even when the same user asks for it.

The solution

For this reason, we introduced a new property to the UdfMethod attribute called ReturnsPersonalInformation. When set to true, it tells Excel Services that it should never share workbooks that use this UDF between users. The same user accessing a workbook will still get the benefits of sharing. Workbooks that can be shared across users, will continue being sharable. However, when this property is present, the same actual workbook will never be shared between users.

A method that returns personal information will look like this:

[UdfMethod(ReturnsPersonalInformation=true)]

public double MySalary()
{

     // ...

}

The other thing we did was protect the administrators and developers of accidentally making mistakes (as far as we can, that is). If a method is not explicitly marked as returning personal information, Excel Services will go ahead and nuke the principal off the thread, replacing it with an “empty” principal. Meaning that a UDF method which is not decorated as returning personal information will get an empty string when it tries to access System.Thread.CurrentPrincipal.Identity.Name.

This is not to say that the protection is perfect. Determined developers will find other ways of getting the current user name. But at least the supported way of getting at that information will trip the developer and show her that something needs to be fixed.

Comments

  • Anonymous
    September 10, 2006
    A few months ago, I mentioned that around the time we fully locked down the user interface in preparation...
  • Anonymous
    May 13, 2008
    Hi All, We are using excel services for reporting and have issues using UDF's in delegation mode. The UDF calls MOSS web service to pull the data based on the user credentials. The UDF code threw Unable to  cast object of type 'Micorsoft.Office.Excel.Server.CalculationServer.WorkOnBehalfIdentity' to type 'System.Security.Principal.WindowsIdentity' and internet search suggested to change the mode to delegation. After switing to Delegation excel services throws "Access Denied" or "An error has occured" errors. Any help to fix this will be appreciated thanks S.Saba