Securing Models
On a report server, report models are used as data sources for both creating and using ad hoc reports. You can secure report models in three ways: through the report server folder namespace, through model item security, and through database security.
Because security for report models is multi-layered, a user who can view a model in the folder hierarchy might encounter other restrictions that impose limits on how that model is used at design time and run time. The ability to use a model as a report data source depends on the following factors:
Role-based security on a model (that is, the ability to view a model in the report server folder hierarchy).
Role-based security on the report that uses the model as a data source. If a user cannot access the report, he or she might not be able view the data that the model provides (in Reporting Services, data from a model is viewable only in reports; third-party applications can expose model data in other ways).
Security on items within the model.
Database security at the view, table, or column level.
Securing a Report Model in the Folder Namespace
As with all items that are stored on a report server, you can define item-level role assignments that determine whether a user can view or manage a report model.
Users who have permission to view a model can see it in the report server folder hierarchy, read a limited amount of information about the model in the General properties page (for example, when it was created or modified), and query the model by clicking through links in any ad hoc report that uses the model as a data source.
Users who have permission to manage a model can delete, rename, and update the model. Typically, model management tasks also require the ability to publish new models, but the ability to do that is actually conveyed through role assignments on folders, where the folder role assignment determines whether users can add items to it.
Users who have permission to view a published model cannot open it directly to view its contents or download it to the file system. At run time, all interaction with the report model is through the report that uses it.
Securing Items in a Model
Model item security allows you to control access to specific parts of a model. To configure model item security, use SQL Server Management Studio. After you enable model item security, you can create role assignments on specific nodes in the model namespace. For more information, see Model Item Security Page (Report Manager).
A report model namespace is represented as a hierarchical structure that includes a root node, entities, model roles, and fields. It also includes folders and perspectives that you can use to organize (but not secure) model items. When you view the model in Management Studio, you can browse the hierarchical structure and specify role assignments at different levels. You can specify role assignments on the root node of a report model to control access to the entire model, or on parts of a model to vary access permissions on selected branches. As with report server folder namespace security, the model namespace supports inherited security for items lower in the tree structure.
Model item security is off by default. When model item security is not enabled, all permissions for viewing the data that the model represents are determined through role assignments on the model and report in the report server folder hierarchy.
Model item security is transparent to the user. If a user does not have access to a particular branch of the model hierarchy, that portion of the model is not presented to the user in the report. It cannot be used for data exploration, nor can it return data in a report. With model item security, the report server modifies the query that is sent to the data source to exclude any portion of the model that is off limits to the user.
Securing Models Through Database Security
Database security provides the third layer of security in a model-driven report. If you restrict access to tables or columns, the database will return an access denied error for all unauthorized access. If you include in your model any tables or columns that are subject to database security, a database error message will be returned if a user accesses a model item that maps to a table or column that he or she is not authorized to view. While database security at the table or column level is necessary in some scenarios, it is import to consider how it affects ad hoc report navigation. A user who gets a database error message while navigating a report must retrace his or her steps to get back to the portion of the model to which he or she has access.
Note
If you are relying on database security to ensure that only specific users are allowed to read particular tables or column values, be sure that you configure the data source to use Windows integrated security.
Securable Model Items
Use Management Studio to secure these parts of a model:
root node
folders
entities
model roles (where the term "role" refers to the relationship between entities)
fields
You cannot secure perspectives as whole, but you can secure the model items within the perspective. Security is inherited based on the model item's security. For example, if the model item can only be accessed by administrators within the model, then the model item can only be accessed by administrators when it appears in the perspective.
Report model security is separate from security you define on the report server folder hierarchy and at the system level. The root node of a model is not accessed or secured through the folder hierarchy.
Hiding Model Items
As an alternative to restricting access through role assignments, you can use the Hidden property to prevent users from seeing portions of a model. If you do not want any users to see a model item, change the Hidden property for the item to true in Model Designer. Hiding an item does not remove it from model calculations or relationships. For example, if you hide a field that is used in an expression, the field is still used in the expression even if users cannot see it. Hiding an item hides it for all users. If you want to vary visibility and access by user or group, use role assignments rather than the Hidden property to secure the item.
How to Secure Items in a Report Model
You can secure items in a model from Report Manager. To secure model items, the report model must be deployed on the report server.
In Report Manager, Navigate to the folder that contains the model.
Hover your mouse over the name of the model, then click the arrow to open the menu and select Security.
In the Model Properties page, click Model Item Security.
Select the Secure individual model items independently of this model checkbox.
Select the root node. A role assignment is required on the root node.
Click Assign read permission to the following users and groups.
Type the list of users or groups seperated by a semi-colon ';'.
Click Apply.
Navigate to the next entity, relationship, field, or folder that you want to secure. Repeat steps 6 through 8.