Securing Data using Linked and Parameterized Reports

Monday Aug 24th 2009 by Marcin Policht

Recent installments of our series have focused on SQL Server 2005 Express Edition's implementation of the Reporting Services component, discussing access control in particular. This article presents alternative methods that provide more flexibility than the built-in pre-defined roles do.

In the previous installment of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we continued our discussion of its implementation of Reporting Services, focusing in particular on access control. As we have demonstrated, it is possible to delegate specific system-wide and item-specific tasks to designated users or groups by employing a built-in mechanism based on a set of pre-defined roles. However, there are situations where this model does not provide a sufficient level of flexibility. In this article, we will present alternative methods that help address this shortcoming.

As we have mentioned earlier, Reporting Services facilitates the customization of published reports without modifying their original definitions (.rdl files) by creating linked reports. Restricting the range of published data can be accomplished by properly adjusting their security and parameters (displayed in the Parameters section on the Properties tab). Note that despite these modifications, linked reports still point to the same data source (although this can be altered as well, if desired), so, in essence, their functionality is similar to what is provided by database views.

In general, in order to present only a portion of a full dataset to a target user or group, you could create a linked report (referencing the original one, providing a full view of the data), store it into a folder whose access permissions are restricted to browsing, and configure its parameters to ensure that only records meeting specific criteria will be displayed. Obviously, this approach is predicated on the assumption that a set of parameters exist that allow you to filter out data that is supposed to remain hidden. Let's walk through an example that illustrates such an approach.

We will use one of the sample server management reports called Schema Table Extended Properties for this purpose. In its original form, it allows you to choose a target database (from the DatabaseName listbox) for which you want to display schemas and columns. After the selection is made, you can explore each schema, their tables and columns (down to the property level) in the drilldown fashion. Let's assume that we want to allow a specific user (for the sake of example, we will refer to it as AWBrowser) to view definitions of only those objects that reside in the AdventureWorks database.

To start, create a folder within the site hierarchy, which will serve as a container for our new linked report (we'll call it Linked Reports, but obviously the naming is entirely arbitrary). To accomplish this, launch an Internet Explorer while logged on as an account with Content Manager and System Administrator roles. Once you reach the Home page of SQL Server Reporting Services site, click on the New Folder button in the toolbar, type its name in the Name: textbox, and finalize your entry with the OK button. After the folder appears in the site hierarchy, use its Edit icon to display its properties and switch to the Security section. Click the Edit Item Security button in the toolbar to create a new role assignment, granting Browser privileges to our designated account (AWBrowser). With appropriate permissions in place, navigate to the Server Management Sample Reports folder and locate the General section within the Properties tab of the Schema Table Extended Properties report. Assuming you have sufficient privileges, you should find the Create Linked Report button there. After clicking on it, provide the new linked report name (let's call it AdventureWorks Schema Table Extended Properties) and use the Change Location button to point to the Linked Reports folder. Once you confirm your entries by clicking on the OK button, you will be redirected to the target location automatically. Switch to the Parameters section of the Properties tab and make sure that the Has Default checkbox is selected, the Default Value textbox contains AdventureWorks entry, as well as that Prompt User is unchecked. Click on the Apply button. Verify that AWBrowser is assigned the Browser role by reviewing the Security section. At this point, the new, more restrictive, linked report is ready to be viewed by our designated user.

While the solution described above is fairly straightforward, it introduces administrative overhead in situations where access to parameterized reports needs to be granted to a larger number of users (and might make your Reporting Services site cluttered and tough to navigate). In addition, it has a dependency on the report's parameters, which limits its flexibility considerably. These drawbacks are eliminated when using an alternative approach, which involves modifying the underlying data source and leveraging the built-in capability to control the output of a report based on the name of a user that generates it. More specifically, you can create an additional table providing a mapping between each username and a field (column) that will constitute the basis for filtering data that these users are supposed to be able to view. Such a table is referenced by a slightly modified query on which the report is based, which includes the WHERE clause matching the value of the username column and the parameter representing a current user. Finally, you have to alter this newly created dataset parameter to indicate that its value will be populated automatically by the operating system and (for this very reason) remove it from the list of explicit parameters that the report will expect its users to provide.

Let's demonstrate this method by using a report, whose creation we have presented in our article The Basic Functionality of Report Designer, leveraging a query included in the Sales and Marketing Scenario described on the MSDN site. Its output, based on the content of the AdventureWorks database, listed all of the individual customers (including their full name and address), grouped by State/Province and Country/Region. While in its original form, the resulting report displayed all data; our goal will be to filter it, such that its outcome will be geared towards individual regional managers, (i.e. it will include only records of their customers).

Start by creating an auxiliary table, which will contain the mapping between Reporting Services users and the country/region field stored in the database. To accomplish this, launch SQL Server Management Studio Express, log on with an account that has the db_owner role in the AdventureWorks database, and click on the New Query toolbar button. In the new window, type in the following statement (we leverage one of user-defined data types called dbo.Name here, already referenced in Person.CountryRegion table), and execute it:

USE AdventureWorks
CREATE TABLE dbo.UserMapping
	WindowsUser nvarchar(50) NOT NULL,
	CountryRegion dbo.Name NOT NULL

This will result in the creation of our mapping table. In order to illustrate its purpose, add a record consisting of a single pair of values to it (in our case, this will be ColinHay and Australia) designating the name of our manager from the "land down under" and the region he is responsible for.

Now open our sample Reporting Services project in Business Intelligence Development Studio. In the Designer window, switch to the Data tab, invoke the Add Table... command from its context sensitive menu, and select the newly created dbo.UserMapping. Once its representation appears on the Designer's surface, create a link between its CountryRegion field and its counterpart (i.e. Name) in the CountryRegion (Person) table. Note that this action will automatically update the T-SQL statement displayed at the bottom of the window (by adding INNER JOIN dbo.UserMapping ON Person.CountryRegion.Name = UserMapping.CountryRegion). Next, append a condition, which will filter the result set to include only those records where the value of UserMapping.WindowsUser matches the name of a user who generates the report. This is accomplished by adding the WHERE dbo.UserMapping.WindowsUser = @UserID clause (note that @UserID simply designates a parameter, which subsequently will be assigned a value provided by the operating system). Still on the Data tab, click on the ellipsis (...) button next to the Dataset listbox to launch its dialog box, switch to the Parameters tab, and modify the entry containing the @UserID Name by setting its value to =User!UserID. (This value represents the name of a Windows user in which the security context the report is executed). In order to prevent having this parameter listed as available for assignment once the report is published, remove it via the Report\Report Parameters... menu. At this point, you are ready to deploy it (using the Deploy option in the context sensitive menu of the report appearing in the Solution Expolorer window). Once our regional manager logs on and executes the newly published report, its results should include only records representing individual customers located in Australia.

In the next installment of our series covering various aspects of SQL Server 2005 Express Edition, we will look into other methods of securing Reporting Services installation.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site