SSRS 2005 Parameter Based Security

Question

I work for company A. Company A has a sister company B. Both companies A and B use the same ERP database. I have created an SSRS 2005 report that can be used by both companies. It has a CompanyID parameter that determines whether to display data for company A or company B.

For most reports this will be OK, but for company sensitive information (such as payroll), this will be an issue since anybody at company A can change the CompanyID parameter to company B's ID, and visa versa.

My initial idea to handle this was to create a linked report for each company in their own respective folders, A and B, where security on folder A only allowed company A users and folder B security only allowed B users. Then I would add a default CompanyID parameter to each linked report and hide the parameters from the user. So far so good. The problem with this is that you can still change the parameter values using the URL query string. For example, a user at company A could change the report url from:

http://server/ReportServer/ReportViewer.aspx?/Payroll/A&rs:Command=Render

to:

http://server/ReportServer/ReportViewer.aspx?/Payroll/A&rs:Command=Render&CompanyID=B

Now they have completely bypassed the hidden default parameter.

What is a good approach to solve this? I would like to share reports between both companies if possible.

Update: We also have company specific ASP.NET intranets that already restrict access based on company via AD domain. I suppose I could use the ReportViewer control on an intranet page to apply the appropriate parameters at run time. I could probably incorporate this logic into a generic report page that could be used for any report, right? (Please excuse my ignorance, I'm a total SSRS n00b)

Answer 1

What is your security apparatus here? It seems to me a solid and secure solution would be to drive the access to data based on the user account. How is the report data gathered? Is it SELECT directly in your data set? Are you calling procedures? selecting against a view?

EDIT: Since you are selecting against a VIEW which unions the respective company data, if you grant rights to the views to the users or roles which have access you may be able to create a scenario where the data is returned to the user based on their rights.

Answer 2

You could use Expression Based Connection Strings in order to not have to use linked reports, but it still means passing information as a parameter which will get exposed in the GET request.

There's no getting around that you have to distinguish for whom the report is going to be displayed/run for.