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:
- Rights-limited queries. Most shops I work with either have or are working toward a locked-down security strategy. That is, all database tables are rights-restricted and accessible to only a few DBAs. Queries are executed through stored procedures and views that have been exposed to specific roles or application logins. This line of defense keeps adventurous Microsoft Access or Microsoft Excel users from opening a connection to the SQL Server instance and exploring the tables and their contents.
- Rights-limited reports. The first approach that usually comes to mind when trying to show information based on users' rights is to create individual reports -- one for each class of user. Although you could do that, some users might be able to find report URLs and get access to the reports anyway. (One way to find the URLs is by looking in the browser history of a high-rights user.) Plus, maintaining parallel reports means at least twice the amount of work because you need to create individual reports and keep each report's content, layout, and underlying expressions up-to-date. In contrast, the approach I'm going to demonstrate tries to keep things simple -- use a single report for everyone, regardless of rank or position.
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 "SBSDOM\billva" : Return False
Case "SBSDOM\AliceFarke" : Return False
Case Else : Return True
End Select
End 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:
- Query Active Directory (AD) to determine the rights associated with a specific user. This would require coding and deploying a protected managed-code DLL. Because the HideThisColumnFrom function would need to access AD through the Microsoft .NET Framework, it would have to run with elevated rights.
- Create your own database table of permitted users and their associated rights.
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.
