Share via

Secure reports, parameters and User!UserID

  • Article

I have seen enough questions on how to protect parameters passed to reports to warrant a blog entry.

 

Let’s say if you want to integrate a report containing HR data in your application. Let’s assume the data in the HR database is keyed by the EmployeeID field.

Your application will figure out the value of EmployeeID for the current user, and you could pass EmployeeID as a report parameter that’ll drive its queries.

 

The problem with this approach is that your users could change the parameter and pass in another EmployeeID, which leads to unwanted information disclosure. You can try to hide the parameter, so it will not show up in the report viewer toolbar, or not show the toolbar at all, but parameter values will still show up in the URLs generated in the report.

Then you may start thinking about making the values random – so they’re harder to guess, or encrypt their values, etc, etc…

The truth is, you should never make security decisions based on report parameters. Report parameters are eminently spoof-able, hiding them will not work for sophisticated users, and custom-made encryption are seldom strong enough to withstand a serious hacker.

Instead of using parameters, base your security decisions on User!UserID. UserID is populated as the result of the authentication mechanism so it is inherently more reliable than parameters.

In the example above, you can add code in your report that retrieves the EmployeeID based on UserID.

 

Note:

If you can’t establish a relationship between your user’s Windows accounts and your report data, you can implement a custom authentication extension as described here. The value for UserID will then be determined by your custom auth extension.

Comments

  • Anonymous
    July 20, 2004
    Another solution is to simply retrieve your reports via the webservice. This is the solution we have implemented in our internet-based application. The Reporting Server has to be installed on your database server (or at least it did before SP1) so you shouldn't be exposing your database server to the public internet anyway. This way our app server makes requests to back to the reporting server passing our formsauthenticated userid as the first parameter to the reports. It is the only server with access to the reporting server so the reports are safe from web service meddlers.

    We implemented this solution back in March before SP1 and before the article you mention on using FormsAuth in reporting services. The downside is that you don't get the spiffy web interface - all our reports are returned as PDF.

    Just another way to skin the cat. :-)

  • Anonymous
    July 21, 2004
    This will work well for non-interactive reports, such as PDF or TIFF. However, when you deal with interactive formats, this is currently more dificult than it should be because the reports may contain links to the report server, such as the toolbar, drill-through parameters, images, charts, etc. There are ways to go around the images mentioned in the documentation for RenderStream, but it's not smooth sailing. We are working on making this separation easier in the upcoming version of SQL Server and Visual Studio.

  • Anonymous
    July 27, 2004
    The downside to using User!UserID on your reports is that you cannot set up subscriptions to them. The User!UserID information is not available at run-time and Report Manager blocks you from creating a subscription.

    I would have preferred this method but have chosen to pass the user as a parameter. We are creating a tool to generate linked reports for each user in a separate folders (and setting proper permissions).

  • Anonymous
    August 10, 2004
    Mike Lyncheski makes an excellent point.

    We also want to restrict access to data based on UserID for logged-in web users, but equally important to us is subscribing users to the same reports.

    Do you have any suggestions as to how this could and should be done? We cannot adopt Mike's suggestion as we have too many reports and too many users - it would be a maintenance nightmare I think!

  • Anonymous
    August 10, 2004
    That is correct, subscriptions will not be available for reports that refer to UserID.
    We are looking at ways to eliminate this requirement in future versions, but for now having multiple reports protected by report server permissions is the only secure way I can recommend. To alleviate the management complexity, perhaps you may not have to create linked reports for each user, if you can group your users by their permissions. In that case, you can create a linked report per group.

  • Anonymous
    April 26, 2005
    After taking a customer query regarding securing report parameters I found this blog entry from Tudor...

  • Anonymous
    April 25, 2006
    [This will be a list of resources I point people to all the time....just putting 'em all in one place]...

  • Anonymous
    May 08, 2007
    Hoping you may be able to help with a problem I'm having with SSRS parameters.... My report has 2 parameters - the User Id used to login to the application and the Department(s) within the organisation. Based upon the User's role, the user may have access to data for one or many Departments. Thus, the first parameter needs to be set in code based upon the User's login, however, the range of the second parameter (i.e. the range of Departments that the user can access) is controlled by the value of the first parameter. The second parameter is to appear as a drop-down of Departments to which the User has access. The report is to be produced for the selected Department. Are you able to advise how to restrict the range of values for the second parameter based upon the value of the first parameter?

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2185409-parameters