SQL Server 2005 : Advanced OLAP - Roles

1/2/2013 6:27:35 PM

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.

Figure 1. The Cubes tab of the role designer, showing Access for the Sales cube being set to Read

Reselect the Cell Data tab (Figure 2), and note that the Sales cube is now listed in the Cube drop-down list.

Figure 2. The Cell Data tab of the role designer, with the Sales cube selected

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.

Figure 3. The Basic tab within the Dimension Data tab of the role designer, with attribute-level security for the Sales cube’s Geography.Country attribute highlighted

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.

Figure 4. The cube browser as launched by the Test Cube Security hyperlink in the role designer’s Cell Data tab
Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone