Protect UDM with Dimension Data Security, Part 2

SQL Server 2005 Analysis Services (SSAS) offers a robust role-based security model for restricting access to Unified Dimensional Model (UDM) objects and data. UDM dimension data security protects dimension members and the data associated with them. My previous article explained the fundamentals of dimension data security. In this article, I explain how to harness the power of MDX expressions to implement dynamic dimension security. I discuss two practical approaches for meeting advanced requirements for securing UDM data. First, I present a solution in which the security policies are stored in a factless fact table. Next, I explain how to leverage SSAS stored procedures to integrate UDM with an external security service.

Related: Protect UDM with Dimension Data Security (Part 1)

My sample Dimension Security project demonstrates how you can set up advanced dimension data security. To obtain this project, click the "Download the Code" file at the top of the page. Before deploying the SSAS project, you need to create a new table called FactSecurityFilter in the AdventureWorksDW relational database and populate it with data by executing the FactlessFactTable.sql script found in the zip file.

Understanding Dynamic Dimension Security

As I explained in my previous article, you implement dimension data security by defining allowed and denied sets. An allowed set denies access to all attribute members except a set of allowed members. Conversely, a denied set lets you see all attribute members except a set of denied members. A user can access only the cube space defined by the attribute members the user is allowed to see. From an enduser perspective, unauthorized members and their data simply don't exist in the cube.

Basic dimension data security limitations. Basic dimension data security lets you define allowed and denied sets by selecting attribute members at design time. However, most real-life security requirements would outgrow basic dimension data security.

First, basic dimension security might require multiple database roles. For example, a common security requirement is to let a manager see only the sales data of the employees reporting directly or indirectly to him or her. If basic dimension data security were the only option, you'd have no choice except to set up a database role for each manager. Granted, SSAS 2005 significantly improves dimension data security, to ensure that UDM scales well with many roles and large dimensions. (For more information about these improvements, see the Learning Path: Improvements to dimension security in Analysis Services 2005.) However, as the number of database roles increases, so will your maintenance effort.

In addition, the members of allowed and denied sets might be unknown at design time. For example, suppose you need to obtain the authorization policies from an external security service. UDM supports dynamic dimension security to address such advanced security requirements.

Implementing dynamic dimension data expressions. The term dynamic emphasizes the fact that the server resolves the allowed and denied sets at runtime. You can use the Dimension Data Designer's Advanced tab to implement expression-based allowed and denied sets. Use the MDX Username function to make the expression user-specific. This function returns the Windows identity of the user in the format DomainNameUserName. For example, if Stephen logs in to the adventure-works domain as stephen0, Username would return adventureworksstephen0. Because you can use expressions with dynamic dimension security, you might need only a single database role.

The Employee role in my sample Dimension Security project demonstrates dynamic dimension security. Its allowed set expression restricts each manager to see the sales data of his or her direct and indirect subordinates based on the Employees parent-child dimension.

The StrToMember MDX function returns the Login ID member associated with the interactive user. Thus, if Stephen Jiang queries the cube, the StrToMember function returns the [Employee].[Login ID].&[adventureworks stephen0] member. Next, the Exists function finds the corresponding member in the Employee Name hierarchy. Assuming again that Stephen is the interactive user, Exists returns [Employee].[Employee Name].&[272].

Dimension data security for the parent-child dimension must be defined on the parent key (i.e., the attribute that defines the parent-child hierarchy) rather than on the dimension key. I use the Link-ToMember function to find the corresponding member in the [Employee]. [Employees] parent key attribute. Since the Exists function returns a set, I use the Item(0) function to return the first member of the set (there should be only one since an employee has a single login ID). Also, note that the expression doesn't specifically request the employee's subordinates (e.g., by using the MDX Descendants function), because granting access to a member in a parent-child hierarchy automatically grants access to its descendants.

Testing the Employee role. Follow these steps to test the Employee role:

If you want to test the MDX query in SQL Server Management Studio (SSMS) to see the allowed set for a given employee, you can follow these steps:

Follow these steps if you want to test the Username function in the role:

As expected, the report shows data only for Stephen Jiang and the AdventureWorks employees who report to him. For example, Amy Alberts isn't shown on the report because she's at the same level as Stephen in the Employees hierarchy. You might be surprised to see that the report shows Stephen's managers (Brian Welcker and Ken Sanchez). This is because enabling a member in a parent-child hierarchy automatically grants access to the member's ancestors. Consider enabling Visual Total on the Advanced tab (Dimension Data tab in the Cube Browser) if you want the ancestors' totals to be contributed only by the allowed descendants.

As you can see, MDX expressions and the Username function give you a lot of flexibility to secure UDM data. Now let's discuss two more advanced scenarios for dynamic dimension security.

Importing Security Policies into a Factless Fact Table

Suppose that the AdventureWorks online transaction processing (OLTP) application has complex authorization rules that dictate which resellers an employee is authorized to see. Your task is to propagate the same rules to UDM. One option is to replicate the security infrastructure from the source application. However, doing so will result in duplication of the management effort necessary to maintain the security policies in two places. Instead, consider importing only the authorized members into a fact table that acts as a security filter. This would require importing a dataset that contains the authorized resellers per employee. The necessary steps to use this approach include implementing the fact table, implementing the measure group, implementing the database role, and testing the database role.

Implementing the fact table. A new fact table (FactSecurityFilter) is required to store the authorized resellers for each employee, as Figure 2 shows. Its schema is simple. The table has two columns that store the identifiers (primary keys) of employees and authorized resellers. For example, if Stephen Jiang has a primary key of 272 in the source database and he is authorized to see resellers Associated Bikes (primary key 7) and Finer Mart (primary key 38), the fact table will have the following rows.

The FactSecurityFilter fact table doesn't have any numeric facts (hence the term "factless" fact table). Note also that FactSecurityFilter doesn't keep an historical record of the security policies (i.e., it doesn't join the DimDate table). The assumption is that the last security policy prevails. For example, if Stephen is authorized to see a reseller in the current period, he will be granted access to that reseller even though he might not have been authorized in the previous period.

The SQL script FactlessFactTable.sql lets you create and populate the FactSecurityFilter fact table. An extraction, transformation, and loading (ETL) task could refresh the FactSecurityFilter fact table on a regular basis (e.g., as part of the data import process).

Implementing the measure group. Next, you need to map the Fact-SecurityFilter table to a UDM measure group as you would any other fact table, by following these general steps:

At this point, the Cube Designer should have created two dimension relationships (Reseller and Employee) to the Security Filter measure group. With the Security Filter measure group in place, you're ready to set up a database role.

Testing the database role. To quickly test the new role, replace the Username function with a member. For instance, use the following expression to test the database role for Stephen Jiang:

Exists([Reseller].[Reseller Name].MEMBERS,StrToMember("[Employee].[LoginID].&[adventure-worksstephen0]"),"Security Filter")

Follow these steps to test the Factless role:

Figure 5 shows the report results for Stephen Jiang. The report shows only 45 resellers (out of 702 total), because Stephen Jiang is associated with these resellers only in FactSecurityFilter.

In SSMS, you can use the following MDX query to verify the expression against the Dimension Security database:

select [Measures].[Reseller Sales-Sales Amount] on 0,Exists([Reseller].[ResellerName].[Reseller Name].MEMBERS,[Employee].[Employee Name].&[272],"Security Filter") on 1from [Adventure Works]

At this point, you might be concerned with the performance implications of the factless fact table approach. After all, thousands of authorized members could exist for each user, which might cause the fact table to increase considerably in size. In case you're wondering whether the SSAS storage engine can evaluate large allowed sets efficiently, I did a performance study for a real-life UDM with several million rows in the fact table; my study showed that the server was capable of constructing the allowed set in several seconds!

Excellent performance is the most important advantage of the factless fact table approach. Because the server applies security when each user connects, minimizing the time to initialize dimension data is essential. You might find other ways to further boost performance based on your requirements. For example, if an employee has access to all resellers, don't import all members into the fact table. Instead, consider adding a new attribute to the Employee dimension that flags power users. Then, shortcut your allowed set expression to bypass the Exists function and return all members.

Using SSAS Stored Procedures to Externalize Security

What if the factless fact table approach isn't an option? For example, if you need to secure several dimensions, importing security policies into a fact table might be counterproductive. Or, you might have no other choice but to obtain the allowed set from an external security service at runtime. SSAS stored procedures can help in these situations. Although SSAS stored procedures might not be as efficient as the factless fact table approach, they give you more flexibility. To illustrate this benefit, let's create an SSAS stored procedure to return the authorized resellers.

Implementing SSAS stored procedures. An SSAS stored procedure is nothing more than a method in a .NET assembly that's deployed to the SSAS server. Web Listing 3 shows the GetSecurityFilter stored procedure from the Extensibility VB.NET class library project.

GetSecurityFilter returns a Microsoft.Analysis-Services.AdomdServer.Set object for the allowed set. For the sake of simplicity, GetSecurityFilter queries the AdventureWorksDW relational database to obtain the authorized resellers for the interactive user. Next, GetSecurityFilter enumerates through the dataset and constructs the allowed set.

StrToSet vs. SetBuilder. Two options exist for creating an MDX set in an SSAS stored procedure. The first option should be familiar to readers experienced with SSAS 2000. It uses the StrToSet function to convert a comma-delimited string of tuples to an MDX set. The second option is new with SSAS 2005. It uses the Microsoft.AnalysisServices .AdomdServer.SetBuilder object to construct the set. SetBuilder far outperforms StrToSet. Table 1 shows performance statistics gathered from a reallife project that compared both options.

As the number of rows increases, SetBuilder's performance advantage becomes clear. Note that 40,000 records took 272 seconds to convert to a set with StrToSet and 144 seconds with SetBuilder (for a 47 percent performance increase). However, even with SetBuilder the SSAS stored procedure approach is much slower than using a factless fact table.

Note that the server invokes the security stored procedure repeatedly when initializing a user session, once for each attribute hierarchy in the secured dimension. To avoid repeated calls to the external security service, cache the allowed and denied sets per user (e.g., by using the .NET HttpRuntime.Cache object for in-memory caching.

Setting up and testing the database role. To set up and test a database role that uses the GetSecurity- Filter stored procedure, follow these steps:

Extensibility.GetSecurityFilter
(Username)

What if the stored procedure discovers that the user has rights to see all members? From a performance standpoint, letting the server bypass the set expression makes sense. Unfortunately, no method exists for shortcutting the MDX expression (e.g., by returning a null set from the stored procedure). Instead of returning a set with all members, consider assigning power users to a privileged database role. Or, if possible, add an attribute that flags these users and use the IIF function in the set expression to check the flag and bypass the stored procedure call.

You can use the following MDX query in SSMS to test the GetSecurityFilter stored procedure:

SELECT [Measures].[Reseller Sales-Sales Amount] ON 0,Extensibility.GetSecurityFilter(â??adventure-worksstephen0â??) ON 1FROM [Adventure Works]

As with the factless fact table approach, the query should return only the resellers that Stephen Jiang is authorized to see.

Secure UDM Data

Dynamic dimension security lets you construct allowed and denied sets programmatically and apply user-specific security policies at runtime. The cornerstone of dynamic data security is the Username function, which returns the interactive user's Windows identity. If you need to obtain the security policies from an external system, use the factless fact table approach for maximum performance. Using an SSAS stored procedure lets you meet most demanding integration requirements.