The explosion of viruses and hacker attacks in recent years has pushed security concerns to the forefront of development and application design. Responding to the need for security tools, SQL Server 2005 Analysis Services (SSAS) offers a robust role-based security model for restricting access to Unified Dimensional Model (UDM) objects and data.
Related: Protect UDM with Dimension Data Security, Part 2
You can leverage UDM dimension data security to protect dimension members and the data associated with them. First, you need to know the fundamentals of dimension data security, which I explain here. In a future article, I'll discuss two practical approaches for implementing dimension data security: a factless fact table and integrating with an external security service.
Setting Up Basic Dimension Data Security
Similar to other Microsoft and home-grown solutions, the UDM security model leverages Windows security. The user is authenticated based on her Windows account and authorized according to the security policies the administrator has set up. To simplify security management, the UDM administrator can group Windows users and groups into database roles. Next, the administrator assigns role permissions to restrict the cube space the user is authorized to access.
While UDM allows you to control access all the way down to the cube cells, most real-life security requirements are less granular. Typically, you'll need to secure access to dimension members and data associated with these members. Dimension data security allows you to do just that.
My sample Dimension Security project demonstrates how you can set up basic dimension data security. You can obtain the Dimension Security project by clicking the Download the Code link at the top of the page. You'll need the AdventureWorksDW database to process the Dimension Security project's Adventure Works cube. You can install the AdventureWorksDW database from the SQL Server 2005 setup program. In the Feature Selection step of the Setup wizard, click Advanced. Expand the Sample Databases folder and select the AdventureWorksDW database. Alternatively, you can obtain the database by downloading and installing it from SQL Server 2005 Samples and Sample Databases (listed in the Related Resources box).
The simplest approach to securing dimension data is to explicitly select which dimension members a given role is permitted to see. For example, the Adventure Works cube has Reseller and Geography dimensions. Let's create a role whose members will have access to Australian resellers only. Although this example might not have any practical application, it demonstrates several important aspects of how dimension data security works.
1. Open the Dimension Security project in Business Intelligence Development Studio (BIDS) or in Visual Studio 2005. Right-click the Roles folder and choose New Role to open the Role Designer. For the purposes of this demo, we won't assign members to this role. In real life, use the Membership tab to assign Windows users and groups to the role.
2. By default, UDM prevents the members of the role from accessing cubes in the containing SSAS project (database). Switch to the Cubes tab, click the Access column drop-down arrow, and select Read to grant the role access to the Adventure Works cube.
3. By default, UDM grants the new role access to all dimensions in the database. Verify this by going to the Dimension tab. You can control dimension access at the cube or database level (recall that a dimension can be shared among cubes) by using the Select Dimension Set drop-down box.
4. Switch to the Dimension Data tab, which is where you'll set up dimension data security. As on the Dimension tab, you can set rights at the database or cube level. Let's scope the dimension data security at the cube level.
5. Click the Dimension drop-down arrow and select the Reseller dimension under the Adventure Works cube, as Figure 1 shows.
6. In UDM, a dimension is a container of attribute hierarchies. For example, the Reseller dimension contains many attribute hierarchies, including the Country-Region hierarchy. The Basics tab allows you to secure dimension members explicitly by using one of two approaches—pessimistic or optimistic. With the pessimistic approach, you deny everything except a set of allowed members called an allowed set. The optimistic approach is the opposite—you use it to allow all members except a set of denied members (a denied set). For more information about allowed and denied sets, see "Introduction to Dimension Security in Analysis Services 2005" (listed in the Related Resources box).
For the purposes of this demo, take the pessimistic approach and deny all members except Australia. Expand the Attribute Hierarchy drop-down list, and select the Country-Region attribute hierarchy (as Figure 2 shows). Select the Deselect all members option to deny all members by default. Select the Australia member.
Note: A cube can have many dimensions and attributes, and it can be difficult to remember which ones are secured. But fear not. Once you've made a change to the dimension data security, the Cube Designer appends (dimension security defined) after the secured dimensions and (attribute security defined) after the secured attributes. This lets you easily see what's going on in the Dimension and Attribute Hierarchy drop-downs.
7. As you select members on the Basic tab, the Role Designer constructs an MDX set of allowed (or denied) members behind the scenes. You can see this set by switching to the Advanced tab. Because you selected only one allowed member, the generated MDX set has the following definition:
\{\[Reseller\].
\[Country-Region\].
&\[Australia\]\}
If you select more members, the allowed set will contain a comma-separated list of these members.
8. Save the role definition.
9. In Solution Explorer, rename the role you've just created to Basic.role and click Yes in the confirmation box to change the object name as well.
10. In Solution Explorer, right-click the Dimension Security project node and choose Deploy to send the changes to the server.
Testing Dimension Data Security
Let's give dimension data security a try.
1. In Solution Explorer, right-click the Adventure Works.cube node and choose Browse to open the Cube Browser. By default, the Cube Browser connects to the cube under the Windows identity of the interactive user (that's you). Assuming you have local administrator rights on your computer, you have unrestricted access to the cube. That's because the SQL Server 2005 setup program grants implicit administrator rights to local administrators.
2. Expand the Reseller dimension in the metadata tree, and drag and drop the Reseller Name attribute hierarchy on the report columns. Observe that you can see all resellers (a few hundred members).
3. Let's now find what members of the Basic role would see. On the toolbar, click Change User, as Figure 3 shows. Select the Roles option, click the drop-down arrow, and select the Basic role. Click OK. The Cube Browser clears the results pane and establishes a new session under the Basic role. The message You are browsing the cube using the credentials of the following roles:Basic is displayed under the toolbar.
Note that when a user connects to the server, the server evaluates the role permissions during the process of initializing the user session (i.e., before the cube is available for browsing). If the user belongs to multiple roles and so wishes, he can tell the server which role(s) he wants the server to honor on connect. The SSAS connection string property supports a Roles setting, which the user or the application can use to specify a comma-delimited list of roles. The user can select only roles that he is a member of. UDM roles are additive, so if the user is a member of multiple roles, the effective permission set is the union of the allowed role permissions.
4. Drag and drop the Reseller Name attribute hierarchy on the report columns once again. Now the Cube Browser shows only about 40 members—the Australian resellers only. We can verify that the results are correct by either dropping the Country-Region hierarchy next to the Reseller Name hierarchy in the Cube Browser or by using the following MDX query in SQL Server Management Studio:
select \{\[Reseller\].\[Reseller
Name\].\[Reseller Name\].Members\}
on 0
from \[Adventure Works\]
where \[Reseller\].\[Country-
Region\].&\[Australia\];
How Data Dimension Security Affects Data
Recall that we configured the allowed set on the Country-Region attribute hierarchy, but we used the Reseller Name hierarchy on the report (note that both hierarchies belong to the Reseller dimension). Dimension data security has filtered the resellers in Australia even though we haven't set up a filter on the Reseller Name attribute hierarchy. It turns out that behind the scenes, the server applies a special behavior called Auto-exists that cross-joins attribute hierarchies.
Understanding Autoexists. Thanks to Autoexists, when attribute hierarchies from the same dimension are requested side by side, the server automatically cross-joins their members and returns only the members that exist in both hierarchies (i.e., the intersecting members). Because the Basic role can see only the Australia member in the Country-Region attribute hierarchy, the Reseller Name column shows only the Australian resellers. Autoexists is applied to all attribute hierarchies within the same dimension. For example, if you request the Bank Name attribute instead of Reseller Name on the report, only banks for resellers in Australia will be returned.
Autoexists shouldn't be confused with the MDX NON EMPTY behavior. Autoexists is applied at the attribute level for all attribute hierarchies within the same dimension and can't be turned off. NON EMPTY simply filters out members that have empty cells from the query results and is entirely optional. For example, you can turn off NON EMPTY in Cube Browser by clicking Show Empty Cells on the toolbar. To see the difference between NON EMPTY and Autoexists in your report, click the drop-down arrow in the Reseller Name column header and note that only the Australian resellers are shown.
Data security. As I mentioned at the beginning of this article, dimension data security secures dimension members and the data associated with them. From an end-user perspective, members that the user isn't authorized to see and their data simply don't exist in the cube. Imagine that a global WHERE clause is applied that finds only the data that's associated with allowed members of all secured dimensions. For this reason, when using dimension data security on a cube, avoid hard-coding dimension members in any cube scripts or MDX queries. If you hard-code a member that a user isn't allowed to see, the user will get an error when she runs the script or query.
An interesting question for your business users is what totals should the user see when he browses the cube by another dimension? Should the totals exclude the data contributed by the members the user isn't authorized to see, or not? SSAS supports both scenarios. For better performance, the server includes the disallowed members when calculating the aggregated totals (i.e., the All member totals are used). For example, the report in Figure 4 shows the same results irrespective of the user's role rights.
If your business requirements dictate that the totals should reflect the contributions by the allowed members only, you need to enable a special server behavior called Visual Total. To do so, go back to the Advanced tab on the Dimension Data tab in Role Designer, and select the Enable Visual Total check box for the Country-Region attribute hierarchy you secured and deploy. After you reconnect in Cube Browser, the totals in Figure 4 will decrease to show Australian sales only.
Cross-dimension security. In fact, the report in Figure 4 will show only fiscal year 2004 (because apparently Australians bought AdventureWorks bikes in 2004 only). That's because (as noted above), the default NON EMPTY behavior filters out the empty members in the Time dimension (click Show Empty Cells on the toolbar to see all the years).
But shouldn't Autoexists propagate to all dimensions? Certainly there could be scenarios in which cross-dimension security would be desirable. For example, if you have Customer and Account dimensions that have a logical one-to-many relationship (i.e., one customer can have many accounts), it's reasonable to expect that if a user is allowed to see only a subset of customers, she should see only the accounts that belong to that subset of customers; she shouldn't be able to see other customers' accounts. Cross-dimension security could also yield performance benefits (e.g., an OLAP browser wouldn't have to load all the accounts of a large Account dimension).
(Remember that by cross-dimension security, I mean preventing access to members, not their associated data. If a user doesn't have access to a dimension member, dimension data security prevents access to the data associated with a member without any extra work.)
As it turns out, Autoexists is not applied across dimensions, and there's nothing you can do to enable it. You might be tempted to try a workaround that simply cross-joins dimensions together to flow the security context from one to the other, as in:
Exists (\[Date\].\[Fiscal Year\]. \[Fiscal Year\].Members, \[Reseller\].\[Reseller\]. \[Reseller\].Members)
Here, the MDX Exists function cross-joins all members of the Fiscal Year and Reseller attribute hierarchies. Because you've already defined an allowed set on the Reseller dimension, you might expect that this statement would return only years in which Australian resellers have sales (2004, in this case). Unfortunately, the statement doesn't work as expected, because when the dimension security expressions are evaluated, they're evaluated before the security filters are applied. For more information about the event execution order, see "Default members, MDX Scripts, Security, KPIs and Perspectives" (listed in the Related Resources box).
The easy workaround for cross-dimension Autoexists is to apply a separate security filter on the Account dimension. It would be nice if a future release of Analysis Services would support cross-dimension Autoexists to simplify cross-dimension security.
Parent-Child Dimensions
Parent-child dimensions present a special case for a couple of reasons. First, dimension data security with parent-child dimensions can't be applied on the dimension key attribute. That's why the Employee attribute hierarchy doesn't appear in the Attribute drop-down list when you attempt to set up dimension data security on the Employees dimension.
Second, allowing access to a given member in a parent-child hierarchy automatically grants access to the member's parents all the way to the root member(s). It this weren't the case, the user wouldn't be able to navigate to the member. To test this access, select the Kevin F. Brown member of the Employees attribute, and notice that Role Designer automatically selects his managers David M. Bradley and Ken J. Sanchez, as Figure 5 shows.
Steps to UDM Security
Setting and maintaining robust security policies is an essential task that every UDM administrator has to master. A database role can enforce security policies at different levels in the cube. Dimension data security restricts members of a role from seeing dimension members and their associated data by defining appropriate allowed and denied sets. Autoexists automatically propagates the security filter to all attribute hierarchies within the same dimension.
Consider enabling Visual Total when you need the aggregated values to include the contribution of the allowed members only and exclude denied members. Dimension data security with parent-child dimensions is applied at the parent attribute, and enabling a member enables access to its parents.
Related Resource |
