locked
Major security hole - csv export RRS feed

  • Question

  • Using VS2008 and SQL Server 2008R2.

    We have found that .csv export shows hidden data that the user normally wouldn't be able to see with the onscreen report.

    All of our users have roles that get assigned when they login to the system. Some users are internal departmental users and they're allowed to see national aggregate data rows in a tablix. Our external users are only allowed to see data for their particular organisation, and they're not allowed to see national aggregate data. So based on these roles we have implemented switch statements in the tablix to hide/show rows based on their roles.

    This works fine, with external users having the rows hidden in the onscreen report. But when they export to csv the hidden data shows up! We now have to jump through hoops at the backend and modify our sprocs to deal with this. Have we overlooked something? Is it possible to get a handle on the csv export process? It shouldn't be this hard. Why is it like this? Does anyone know what's going on?

    tia

    rob

    Wednesday, November 2, 2011 10:48 PM

Answers

  • Completely agree.. you're right  - it's inconsistent and confusing.

    The only thing to do is submit wishes via Connect (http://connect.microsoft.com/sqlserver)... so they can be reviewed by the SQL server team. I don't work for Microsoft so I don't know what the process is for getting features included/changing existing design.

    I would say though that it makes sense to configure and control data security via the data sources themselves, rather than a renderer.

     

     

     


    Josh Ash
    • Marked as answer by Lola Wang Tuesday, November 8, 2011 5:50 AM
    Thursday, November 3, 2011 10:21 PM

All replies

  • The behaviour you are seeing is by design - you cannot conditionally hide using expression in data renderers.

    The solution to this is to ensure you remove rows at the data source level - not by hiding fields/rows in the report.

    This obviously requires a major redesign in a lot of cases, but in my opinion it's the best way to deal with it.  It also requires Kerberos to be enabled so you know at the database level which user is running the report.

     

     

     

     


    Josh Ash
    Thursday, November 3, 2011 12:02 AM
  • Thanks - appreciate the answer.

    All I would say is the export to Excel and other formats seems to not export the hidden data, so you have inconsistent behaviour in your design. Secondly, wouldn't it be better if you added a true/false property to the tablix control so report designers could choose whether they wanted the hidden data to show up or not? That would make it much more flexible. Surely it wouldn't be too hard to do that :).

     

     

    Thursday, November 3, 2011 10:12 PM
  • Completely agree.. you're right  - it's inconsistent and confusing.

    The only thing to do is submit wishes via Connect (http://connect.microsoft.com/sqlserver)... so they can be reviewed by the SQL server team. I don't work for Microsoft so I don't know what the process is for getting features included/changing existing design.

    I would say though that it makes sense to configure and control data security via the data sources themselves, rather than a renderer.

     

     

     


    Josh Ash
    • Marked as answer by Lola Wang Tuesday, November 8, 2011 5:50 AM
    Thursday, November 3, 2011 10:21 PM