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.

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 DomainName\UserName. For example, if Stephen logs in to the adventure-works domain as stephen0, Username would return adventureworks\stephen0. 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:

Open the Dimension Security project in Business Intelligence Development Studio (BIDS) or in Visual Studio 2005.
In Solution Explorer, expand the Roles folder and double-click the Employee role to open it in the Roles Designer. Switch to the Dimension Data tab.
Expand the Dimension drop-down list and select the Employee (attribute security defined) dimension.
Expand the Attribute drop-down list and select Employees (attribute security defined). The Allowed Member Set text box shows the Link- Member MDX expression.
To quickly test the Employee role for a given employee, replace Username in the allowed set expression that Web Listing 1 shows with the login ID of the desired employee (e.g., adventure-works\stephen0).
Deploy the Dimension Security SSAS project; right-click the Dimension Security project node in the Solution Explorer window and select Deploy.

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:

Open SSMS and connect to the SSAS instance.
Right-click the Dimension Security database and select New Query, MDX.
Web Listing 2 provides an example MDX query that returns the subordinates of Stephen Jiang, whose Windows login is adventure-works\stephen0. Enter this query in the MDX query pane and execute it by clicking the Exclamation toolbar button or pressing Ctrl+E.

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

Use SSMS to open the Employee table in the AdventureWorksDW relational database.
Change the login ID of the desired employee, such as Stephen Jiang, to your Windows login ID.
Connect to the SSAS server and expand the Dimension Security database and Dimensions folder. Right-click the Employee dimension and select Process.
Make sure that the Processing Options column is set to Process Update. Click OK to process the Employee dimension.
Right-click the Adventure Works cube in the Dimension Security database and select Browse.
Click the Change User toolbar button and select the Employee role. Finally, create a report similar to the one that Figure 1 shows by dragging the Employees hierarchy from the Employee dimension on columns and the Reseller Sales-Sales Amount measure from the Measures, Internet Sales display folder on data.

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.
Figure 2: The FactSecurityFilter fact table stores the authorized members

EmployeeKey	ResellerKey
272	7
272	38

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:

In BIDS, add the fact table to the data source view (DSV).
Create a new Security Filter measure group that uses FactSecurityFilter as a source, as Figure 3 shows.
A measure group must have at least one measure; therefore, leave the system-generated Security Filter Count measure. Don't set the Visible property of the measure to False in an attempt to hide the Security Filter measure group from the end user. If you do so, the MDX expression for the allowed set won't work (reported as a bug).
Select the Dimension Usage tab in the Cube Designer to verify the dimension relationships to the Security Filter measure group.

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.

Right-click the Roles folder in the Solution Explorer window in BIDS and select New Role.
In the Solution Explorer window, rename the new role in place to Factless.
In the Role Designer, switch to the Cubes tab and grant the Factless role read access to the Adventure Works cube.
In the Dimension Data tab, select the Reseller cube dimension and the Reseller Name attribute.
Enter the MDX expression for the allowed member set, as Figure 4 shows. This expression uses the Exists function to select the associated resellers for the interactive users over the Security Filter measure group. Note that you don't have to use the attribute hierarchies that map to the keys in the fact table. For example, I use the Login ID attribute hierarchy in the expression instead of Employee Name.

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\].\[Login
ID\].&\[adventure-works\stephen0\]"),
"Security Filter")

Follow these steps to test the Factless role:

1. Deploy the project.

2. Right-click the Adventure Works cube and select Browse.

3. Click the Change User toolbar button in the Cube Browser and select the Factless role.

4. Create a report that has Reseller Name on rows and Reseller Sales-Sales Amount measure on data.

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.

Figure 5: Resellers report results

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\].\[Reseller
Name\].\[Reseller Name\].MEMBERS,
\[Employee\].\[Employee Name\].&\[272\],
"Security Filter") on 1
from \[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:

Add a project reference to the Extensibility assembly in your SSAS project. To do so, rightclick the Assemblies folder and select New Assembly Reference.
In the Add Reference dialog, click the Projects tab and select the Extensibility project. Click the Add button to add the Extensibility project to the Selected Projects and Components pane, and click OK.
In Solution Explorer, select the Extensibility assembly and change its Permission Set property to External Access.
Create a new database role called External. Grant the role read access to the Adventure Works cube. Use the following allowed MDX expression for the allowed set of the Reseller Name attribute:

Extensibility.GetSecurityFilter
(Username)

5. Deploy the project and test the External role as you tested the Factless role.

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-works\stephen0â??) ON 1
FROM \[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.

Comments

Plain text