SSRS in SharePoint integrated mode and report model security - security hole?

Question

Here is my scenario: I've got SSRS 2008 R2 running in integrated mode with SharePoint 2010. Everything works as expected in that I can create data sources, models (either through studio or via the "generate model" option on the ECB for a datasource in a report library). My datasource is using a dedicated SQL account with the stored credentials option. My model specifically hides a couple of columns so that my report builder 3.0 users don't have access to them (consider these sensitive columns).

What I've discovered though is that while a user cannot edit my model or the data source (they only have read access to them), they can (from another report library where they have contribute permissions) create a new report model (from SharEPoint) and simply past the url to the report data source I setup for them in my library. Once they do this, they end up with a model which does not have any of the hidden columns I setup in the locked-down model. This model also does not have any of the model item security settings I have applied - it's a brand new model only using the data source.

So here is my question: Microsoft implies that report model item security can be used to prevent users from accessing certain entities in the model (hence the term "report model item security"). But if users can simply create there own model using the SharePoint UI from a report library of their own creation and point to the datasource (.rsds file) that they have to have read access to, isn't this a bit of a security flaw?

Answer 1

"My datasource is using a dedicated SQL account" [...] "data source (they only have read access to them". The user has read access to the datasource. That means the user can do anything he wants with the data provided by that datasource - no matter what models you set up.

There is no security flaw, but a flaw in your design. Once you use a dedicated account to access an external database, you only have the security of that account. You would need to use impersonation to pass the users' credentials to the database so he is only allowed to see what he ought to see. This of course would open another can of worms as you don't want to have all your SharePoint users as database users. This leaves you only with option three: giving the database the ability to look up users in some kind of security lookup database, this of course would be a custom solution.

Right now you are using a "security by obscurity" approach. You give the users one model with certain "permissions" or hidden columns, but the underlying datasource gives the users all data (even the hidden stuff). What stops the user from using the datasource for which they have read access anyways? Nothing...