Protect UDM with Dimension Data Security, Part 2

 Executive Summary:

As I explained in "Protect UDM with Dynamic Dimension Security" (July 2007, InstantDoc ID 95998), 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, go to http://www.sqlmag.com, enter InstantDoc ID 96763, and click the Download the Code zip file. 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:

1. Open the Dimension Security project in Business Intelligence Development Studio (BIDS) or in Visual Studio 2005.
2. 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.
3. Expand the Dimension drop-down list and select the Employee (attribute security defined) dimension.
4. Expand the Attribute drop-down list and select Employees (attribute security defined). The Allowed Member Set text box shows the Link- Member MDX expression.
5. To quickly test the Employee role for a given employee, replace Username in the allowed set expression that Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 96763) shows with the login ID of the desired employee (e.g., adventure-works\stephen0).
6. 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:

1. Open SSMS and connect to the SSAS instance.
2. Right-click the Dimension Security database and select New Query, MDX.
3. 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:

1. Use SSMS to open the Employee table in the AdventureWorksDW relational database.
2. Change the login ID of the desired employee, such as Stephen Jiang, to your Windows login ID.
3. Connect to the SSAS server and expand the Dimension Security database and Dimensions folder. Right-click the Employee dimension and select Process.
4. Make sure that the Processing Options column is set to Process Update. Click OK to process the Employee dimension.
5. Right-click the Adventure Works cube in the Dimension Security database and select Browse.
6. 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.

Continued on page 2.