SSRS How-To: Create Reports by User to Protect Sensitive Data

In the past year, many of my webinar attendees wanted to know how to create reports that automatically morph based on the user viewing them. Take, for example, a personnel roster. When clerks in the mailroom view the roster, they see employees' office numbers, but not their personal information such as salaries or home addresses. However, when mid-level HR managers look at the same report, they see virtually everything. Another example is having reports morph to accommodate the user's native language. In this case, the report's column headings, annotations, and other text (perhaps even the data itself) would be displayed in the appropriate language.

SQL Server Reporting Services (SSRS) makes automatic customization fairly easy. It gives you two key elements about the user viewing a report -- the user's ID (User.UserID) and language (User.Language) -- that you can use to implement custom functionality. The User.UserID value is derived from the user's Windows login to the domain or local system. The User.Language value is extracted from the user's browser language settings by the report processor.

Both the report processor and SQL Server can gate access to sensitive data because each one has its own security paradigm. SSRS security settings can also play a role by limiting the visibility of folders or individual reports to specific login accounts or report roles. I won't discuss how to configure these rights but rather the strategies you can use to leverage them. I'll then show you how to use the UserID constant to control what is displayed in a report. (For information about how to use the Language constant, see the sidebar "Morphing Reports to Accommodate the User's Native Language.")

Strategies for Protecting Sensitive Information

There are countless ways to protect data. Here are two techniques that you can use to prevent data from being seen by those who shouldn't see it:

Keep in mind that these techniques are meaningless if someone can physically access your server when no one is watching. (I can have a server's hard drive out in under a minute or plug in a USB key and upload a keylogger in 10 seconds.) Never let anyone get access to your servers -- not unless they're fully trusted. These techniques are also meaningless if a sensitive report is printed and left in the trash for the janitor to view on his or her coffee break.

Also keep in mind that data needs to be protected not only after it's recorded in a database but before as well. High-rights users shouldn't remain logged in when they leave for lunch or go down the hall to get a cup of tea.

The One-Size-Fits-All Approach

The one-size-fits-all approach involves creating a report that shows the maximum amount of information -- a report that only the person with the highest rights should be able to see. Once the report is built, you write code that determines whether or not to show data in the report to a specific user based on the UserID constant, which contains the domain-qualified name of that user.

A question I'm commonly asked about this approach concerns whether users should be authorized to launch the report in the first place. That's up to you. If your SSRS authentication roles and rights schemas are configured correctly, a report's name might be visible in the Report Manager catalog or the Reports virtual directory, or the user might be able to enter the report URL directly, but that doesn't mean the report processor will be permitted to run it. In other words, even though some users are authorized to run the report, they might not be able to. Consider that the user credentials just might be those passed through to SQL Server (or the target data source) to gain access to the underlying data objects. If the report uses Windows' Security Support Provider Interface (SSPI) authentication and those credentials have been granted access to the objects referenced in the report's data sets, the report has a good chance of running. In some cases, users might have rights to run a report but not the rights to the underlying data, so the report won't run. To prevent Trojan horse attacks, you shouldn't use Windows authentication credentials in a report's data sources.

To demonstrate managing report object visibility based on the UserID constant, I created the HideThisColumnFrom function. This function tests for specific users, returning True or False. When True is returned, the columns or individual cells are hidden from the user. When False is returned, the user has access to the columns or individual cells. You need to insert this function into each report in which you want to hide data from certain users.

Listing 1 shows an example of how to use the HideThisColumnFrom function. It's hard-coded to permit me (billva) and Alice Farkle to have access to the hidden parts of the report. All other users don't have access to the hidden content.

Function HideThisColumnFrom(strUserID As String) as Boolean  Select Case strUserID    Case "SBSDOMbillva" : Return False    Case "SBSDOMAliceFarke" : Return False    Case Else : Return True  End SelectEnd Function

The main advantage of the one-size-fits-all approach is that the report is easier maintain. Although you have to set the visibility properties for each report element (e.g., column, text box), once they're configured, you only have to revisit the HideThisColumnFrom function when you need to add or remove users from the permitted users list.

The hard-coded list approach is simple to implement, but it's not exactly ideal because it requires you to revisit, retest, and redeploy the HideThisColumnFrom function each time the list changes. Unless you're being paid by the hour or need a lot of busywork, this approach probably should be replaced with a self-maintaining data-driven scheme, such as one of the following:

Here's how the second approach works:

1. You implement a report that uses parameter-driven user authentication. In this case, in addition to using the UserID constant, the report captures the name of the employee from a filtered drop-down list of known employees. This can be generated through DLL-invoked calls to AD or a database query that returns valid login names and their associated rights.

2. Using the UserID constant as a cascading parameter, the report prompts the user for a password.

3. Using the user-provided password as another cascading parameter, the report tests the validity of that password by checking it against an encrypted employee-password table in the database.

4. If there's a match, a "rights" value is returned, which you can use to grant varying degrees of access to the user.

How to Hide Content

As I mentioned previously, with the one-size-fits-all approach, you need to set the visibility properties for each report element in the report. For the most part, you can use these properties to hide report columns, values, data regions, data visualizations, and other report elements. The report processor knows how to execute expressions on nearly every property of every report element, so let's look at some ways to programmatically hide report elements.

The Tablix column hierarchy exposes a Boolean Hidden property that can be set to True to hide a selected column. You can set this property by right-clicking a Tablix column and choosing Column Visibility, as shown in Figure 1.

Figure 1: Setting Tablix column visibility

For this example, I simply set the Column Visibility property to a Boolean expression that calls the HideThisColumnFrom function:

=Code.HideThisColumnFrom(User!UserID) 

The text box in each Tablix cell also exposes the Boolean Hidden property, as do all of the other report data regions (e.g., chart, list) and other report elements (e.g., rectangle, line). Note that when you choose to conditionally expose data by expanding data groups, this simply toggles the underlying Hidden property on the selected groups of rows.

How to Customize and Obscure Values

Another approach to limiting what's visible to users is to modify a text box's value or modify how that value is displayed. You can clear the value of a text box's Value property or replace it with an alternative value. You can even set the value's color and the background color to the same color, obscuring the value.

For example, suppose that a company's mailroom clerks are responsible for coordinating company birthday parties. Although they need to know the month and day of the employees' birthdates, they shouldn't be allowed to see the year. That detail is confidential information that only HR should be able to see. Leveraging the HideThisColumnFrom function, you can set the "Date of Birth" column's Format property to the following code expression:

=iif(Code.HideThisColumnFrom(User!UserID),"MMM dd", "d") 

(Although this command wraps here, you'd enter it all on one line.)

How to Hide Subreports

Subreports expose a global Hidden property that you can use to programmatically show or hide them. You can also use a report element's Action property to show different versions of a subreport to different users. Specifically, you set the Action property to an expression that resolves into a path to the appropriate subreport.

One of the shortcomings of the current version of the Report Definition Language (RDL) is that the report element's Action property doesn't support a way to programmatically enable or disable the invocation of a subreport. However, you can change the path so that it leads to a benign subreport that doesn't show sensitive data. Another approach is to hide the "action button" -- the report element that has the Action property programmed to show the sensitive subreport.

To avoid having someone stumble upon or unearth a sensitive subreport, you can hide the parameter prompt and not set a default parameter. This way, the subreport can be invoked by an Action click-through but not directly from Report Manager. Any attempt to launch the subreport on its own results in a query failure that complains about a missing parameter.

Keep Sensitive Data Private

We all have to deal with sensitive data. Whether it's the name and financials of an organization that your company is about to acquire or the results of an employee's drug test, it's important to keep some data private. In the United States, federal and state laws also dictate what can and can't be made public. Posting a report about someone's medical history on a bulletin board can lead to some pretty serious repercussions. Sending out a URL to the same report is just as bad if it's viewed by someone who has no right to see it. You can avoid this type of problem with SSRS reports by carefully applying the techniques I showed you to hide or obscure sensitive data.