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.
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.