Implementing Data Security in a Report Model
The report models introduced in SQL Server 2005 feature a number of ways to customize the data visible to different users and groups: perspectives, model item security, security filters, and opaque expressions. This article describes when and how to use each of these features.
PerspectivesA perspective is a pre-defined subset of a report model, typically tailored for a particular type of user or task. Perspectives are not securable, and do not actually protect data from unauthorized users. Instead, they allows users to self-select a model subset that will simplify their design-time experience for a given report. You can define a perspective in Model Designer by right-clicking on the root of the model tree and choosing New->Perspective.
Model Item Security
Model item security allows you to expose different entities, attributes, and roles in the model to different users and groups. This is analogous to table and column security in a database. You can enable and configure model item security in SQL Server Management Studio using the model properties dialog.
Report Builder downloads report models at design-time by calling GetUserModel on the report server web service. In response, the report server creates and returns an on-the-fly subset with only the items for which the current user has permission. This subset enables Report Builder to provide a design-time experience consistent with what will actually be allowed when the report is run. At run-time, the report server creates the same on-the-fly subset and discards any query elements that reference model items not present in the subset. Depending on what remains, the query may return fewer result columns, or it may return an error.
To accommodate the fact that the query may not return all requested data, Report Builder automatically includes visibility expressions in the report layout that cause the report to "shrink" gracefully and display only the columns for which data was returned. This enables a single report to serve many classes of users, with the data and the layout automatically adjusting based on the permissions of the current user.
Security FiltersSecurity filters allow you to enforce row-level security on your data. Configuring them takes some effort, but once set up they are quite powerful. You can define the filter attributes in Model Designer (choose New Filter instead of New Expression), then use SQL Server Management Studio to set permissions on them.
By default, all users with permission to view an entity can see all rows of that entity. This default changes to "no rows" if you add one or more filter attributes to the entity's SecurityFilters collection (implying that you wish to explicitly grant access instead). Alternatively, you can create your own default by assigning a filter attribute as the DefaultSecurityFilter. You control row visibility for individual users by granting them access to one or more filters in the SecurityFilters collection. Users will be able to see the union of all rows exposed by the security filters to which are they have permission. If a user does not have permission to any of the filters in the SecurityFilters collection, they will see only the rows exposed by the default security filter if one is defined; otherwise they will see no rows at all.
Generally speaking, there are two kinds of security filters: those that use the GetUserID function, and those that don't. Those that do are useful if you have actual usernames stored in the database. You can then define a security filter that shows or hides rows by comparing a particular field to the value of the GetUserID function. To do this, create a new filter attribute, drag the username field onto the filter, click the field label and choose "Edit as Formula", then replace EMPTY with GetUserID(). This effectively creates a "my data" filter. Be aware that if one or more joins are required to get from the entity whose row visibility you are restricting to the entity that contains the username field, such a security filter may have significant performance implications. If you are running off a data warehouse, consider copying the username information to each of the tables where you will be restricting row visibility, as part of the data loading process.
When using security filters, you will usually need to define an "all rows" filter to grant full access to admins or other high-privilege users. To do this, create a new filter attribute and leave the filter dialog empty: an empty filter will always return true.
[Important: Security filters did not work correctly in the original SQL Server 2005 release. If you want to use this feature, make sure you have SP1 or later installed.]
Opaque Expressions
Opaque expressions allow you to expose a calculated value without exposing either the inputs that contribute to it or the expression that combines them. Any expression attribute for which one or more inputs are not visible to the current user becomes an opaque expression for that user.
These are especially useful when you want to expose aggregate data without exposing the details. For example, you may want to give product managers access to sales totals by product, but not individual sales orders. To do this, enable model item security and remove permissions for product managers to the Sales Order entity. Then create an expression attribute in the Product entity called Total Sales that references the corresponding attribute on the forbidden entity. Give product managers access to this attribute, and they now have an opaque expression that lets them see sales totals by product, even though they cannot see individual sales orders.
These four features in SQL Server 2005 -- perspectives, model item security, security filters, and opaque expressions -- give you as a report model designer and administrator a great deal of flexibility in customizing the data visible to different users of your model.
Comments
- Anonymous
March 18, 2006
I just posted the first article on my blog. Here's the abstract:
The report models introduced... - Anonymous
March 18, 2006
Bob Meyers (the mastermind behind the RS 2005 Report Builder) tells us how to implement horizontal data... - Anonymous
March 20, 2006
Bob Meyers posted a really good write-up on various ways to secure your report model!
http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.asp... - Anonymous
April 25, 2006
FYI... The URL listed in one of the prior responses does not work for me. (http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.asp)
My question:
Is it possible to use Active Directory groups (or Organizational Units) to assign security filters? The original post outlines the method for retrieving the current user ID, but is there a way to determine whether or not the current user is in a particular group?
If not, I think this would be a good enhancement to include in the next Report Builder release. - Anonymous
April 25, 2006
Well... disregard the URL comment in my previous post. The URL was truncated in the preview (ends in .asp instead of .aspx), and when I added the 'x', I realized that it was the URL for this page anyway.
/sigh - Anonymous
February 25, 2008
Having some trouble getting Security Filters to work. I've added a filter to an entity and deployed the Report Model, but still see all data when running a report. Is there some other setting I'm missing?Thanks,Krip - Anonymous
March 06, 2009
I have some questions regarding report model security and folder namespaces. Perhaps you can help me out? Shouldn't I be able to prevent a user from viewing/accessing a model based on folder security settings on the report server? I'm having difficulty implementing this so that User_1 can only view the model for Client_A and User_2 the model for Client_B. Client_A and Client_B each have their own folder structure.I'm using Forms Authentication and right now, once a user is Authenticated, the user has carte blanche to the report models with the ability to see the models and access them.