Parameter security when exporting to Excel

Parameter security
In our environment we use an ASP.NET web portal to offer reports, which are all made in Reporting Services 2005. The security with regard to who may open which reports and who may select which parameters is set in this portal, based on the NTUserName of the user (Active Directory). The portal compares this NTUsername with an employee table that holds more information about the user, for example his job and cost centre.

Who may select what?
To decide who may select what (parameters), we again look at the NTUserName of the user. Stored procedures now fill the parameter box on the report portal with values.

This seems to work fine until you start working with drillthrough reports. Say I make a report (A) with a link to a drillthrough report (B). A sends a number of parameters along to B, to show only the data that is relevant.

As soon as I export A to Excel, the hyperlinks to B stay intact. From Excel I can click the link and I land on report B as expected. The problem now is that all parameters show in the url. With a bit of skill, the user can now change the url and get to see data that is not meant for his eyes.

There are several possible solutions for this problem:

1. In the query of your report you include something about security, combined with a security table
2. On the database (not advised)
3. Pass encrypted values in the link

In our case we chose solution 3. I will spare you the details of why we chose it.

Explanation with a simple example
Report A has a parameter: GeselecteerdeJaar
Report B has a parameter GeselecteerdeJaar and Parameters SecurityPmt/ SecurityPmt2
Goal: if the user changes the year in the URL, the report must not show any data.

In the hyperlink from A-> B we set this in the parameter mapping:
Left side GeselecteerdeJaar, Right side: =Parameters!GeselecteerdeJaar.Value
Left side SecurityPmt, Right side: =Parameters!GeselecteerdeJaar.Value * 2

So we say that the SecurityPmt in Report B is the selected year * 2. If we choose 2008 as the year, it sends 4016 along as the security parameter.

Next we have to set up a check on Report B.
We set a default value for SecurityPmt2
=IIF(Parameters! SecurityPmt.Value / 2 = Parameters!GeselecteerdeJaar.Value,1,0).

Here we say: divide the security parameter by 2. The result must be equal to the selected year. If this is true, I get value 1, otherwise I get value 0.

Finally we protect the data region by changing the visibility:

=IIF(Parameters! SecurityPmt2.Value=1,False,True)

If the value of the parameter is 1, it must NOT become HIDDEN. If it is 0, it must.

So this is a good method to add extra security when you pass parameters on. The example I give with *2 is of course not enough. Find a nice formula for it that nobody can guess, and send that along.