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];