Perspectives and translations
allow specific groups of users to see customized views of your cubes,
but only real security settings can prevent users from seeing specific
items in your physical cubes. By defining one or more roles in your
Analysis Services project, you can restrict the access that specific
users or groups have to certain data sources, cubes, dimensions,
attributes, members, and even individual cube cells in your Analysis
Services database.
You can block access to objects completely or
limit users’ permissions to read-only access; you can also control
whether users in the role can process the entire database, specific
cubes, and/or specific dimensions. Finally, you can assign administrator
rights within the database to certain roles, and you can grant read
access to the definitions of the entire database, its data sources, or
dimensions. (You can assign many of these permissions for data mining
models and structures as well.)
The basics of the role designer are fairly simple
to learn. You add a new role to your project by clicking the New Role
option on the Roles node’s shortcut menu in the Solution Explorer window
or by choosing the Project/New Role option from the main menu. Your
role will be assigned a default name (both at the object level and the
filename level), which you should change by using the Properties window
or the Rename option on the role node’s shortcut menu in the Solution
Explorer window.
The role designer consists of several tabs where
you define permissions for various Analysis Services objects. The
General tab allows you to enter a description for the role and assign
its database-level permissions: Full Control (Administrator), Process,
and/or Read Definition. The Membership tab lets you specify which users
and groups belong to the role. You use the tab’s Add... button to
specify the role’s membership by using a standard Windows Select Users
Or Groups dialog box.
The remaining tabs allow you to configure
specific permissions for your database’s data sources, cubes, cells,
dimensions, dimension data (attribute members), and mining structures.
You can also test your role, using a sort of simulated impersonation,
via the Cell Data tab’s Test Cube Security hyperlink.
To test this feature for yourself, add a role to our cube and name it NAmericaNoOrderDate (because it will limit the Geography dimension to enumerate only North American countries and will hide all attributes of the Order Date
dimension). On the General tab, enter a description if you like, and
leave all check boxes unchecked. (We do not want to assign any
database-level permissions to this role.) You can assign specific users
and/or groups to this role on the Membership tab, but this is not
strictly necessary because we will be testing this role only through the
Test Cube Security facility (covered shortly). It is also unnecessary
to make any adjustments on the Data Sources tab.
Skip to the Cell Data tab, and note that the Cube
drop-down list is empty. To address this, select the Cubes tab and set
the Access level for the Sales cube to Read, as shown in Figure 1.
Reselect the Cell Data tab (Figure 2), and note that the Sales cube is now listed in the Cube drop-down list.
Now click on the Dimension Data tab. From the Dimension drop-down list, select the Geography dimension of the Sales cube . From the Attribute Hierarchy drop-down list, select the Country
attribute. Click the Deselect All Members option button to clear the
check boxes next to each country, and then manually select the check
boxes next to Canada, Mexico, and USA, as shown in Figure 3.
Select the Sales.Order Date
dimension (again, be sure to scroll down through the dimension list
past the database dimensions to the cube dimensions), and then for each
of its attributes (10 in all) click the Deselect All Members option
button, and do not manually select any members. Click the Advanced tab,
and note that it is possible to specify allowed (or denied) members by
entering MDX expressions rather than manually selecting (or deselecting)
each member on the Basic tab.
Save and deploy your changes, and then click back
on the Cell Data tab, where you will notice that a set of three check
boxes and MDX expressions are available to enable read, read contingent,
and read/write permissions for the cells in the cube.
Click the Test Cube Security hyperlink in the
upper right. This opens a new window within Visual Studio containing the
cube browser that is connected to the database under a simulated login
emulating that of members of the role. (An information icon and
descriptive text at the top of the window remind you of these details.)
In the metadata tree view, drill down on the Members node for the Country - State-Province - City - Postal Code hierarchy of the Geography dimension and everything under the Order Date dimension. Canada, Mexico, and USA are the only members that appear for Geography.Country - State-Province - City - Postal Code.Country, and no members appear under any of the Order Date attributes or its hierarchy, as partially shown in Figure 4.
