Designing a Security strategy for an OLAP Solution using SSAS


Overview

This guide has been created for people who want step by step instructions on evaluating a set of requirements or a problem statement and implementing it into an OLAP solution using SSAS. Using this guide, you will learn how to –

1. Create various roles in the SSAS database

2. Assign users and permissions to each role.

3. Control access to a cube using Dimension Data, Cell data permissions

4. Control access to a cube using an external .net assembly

This guide assumes basic knowledge of SSAS and OLAP like what are dimensions, measures, cubes, how to create them using SSAS etc.

The source project accompanying this guide can be downloaded from here. The project uses the AdventureWorks2008R2 sample databases which can be downloaded from here.

Scenario

You are the lead solution designer for the multidimensional design team at Adventure Works. The company has started using a SSAS cube for its analysis and reporting requirements. Most of the employers in the organization use Excel for running ad-hoc queries against the database. At the moment, all employees have unrestricted access to the information present in the cubes. The AdventureWorks management wants to change this and has requested you to design and implement the security features that only allow employees to see data that they are authorized to.

The following table displays a list of users who need access to the cube.

Name Windows Username Designation Department Reports To
Ken Sanchez Ken CEO
Brian Welcker Brian COO Ken Sanchez
Amy Alberts Amy Marketing Manager Marketing Brian Welcker
Stephen Jiang Stephen Marketing Manager Marketing Brian Welcker
Syed Abbas Syed Marketing Manager Marketing Brian Welcker
Jae Pak Jae Marketing Executive Marketing Amy Alberts
Rachel Valdez Rachel Marketing Executive Marketing Amy Alberts
David Campbell Davidc Marketing Executive Marketing Stephen Jiang
Lynn Tsoflias Lynn Marketing Executive Marketing Syed Abbas
David Bradley Davidb CFO Finance Brian Welcker
John Wood John Accounts Executive Finance David Bradley
Jill Williams Jill HR Manager Human Resources Brian Welcker
Kevin Brown Kevin CTO IT Brian Welcker
James Hamilton James DBA IT Kevin Brown

Interview transcripts with the following stakeholders are provided:

Brian Welcker

Amy Alberts

David Bradley

Kevin Brown

Interview Transcript for Brian Weckler – COO

However much I would want to provide unrestricted access to information and data across our organization, there are certain compulsions and organizational boundaries that need to be respected. Hence our top priority at the moment is making sure that people don’t get to see data that they are not responsible for or not authorized to access. Of particular importance here, is our Sales and Finance related data. Only I, Ken, and people from the HR department should be able to get access to sales related information for all the employees. Finance folks should not have any access to this data. The people from the Marketing department should be able to see only information of Sales Persons who report to them directly or indirectly. To put it in plain and simple words, Amy should not be able to see Stephen’s sales figures and Syed should not be able to see Rachel’s sales figures. There are some other restrictions that we need to enforce too, but you should speak to Amy to get more clarity on those.

Interview Transcript for Amy Alberts – Marketing Executive

I am sure Brian must’ve already told you that he doesn’t want the Marketing department looking at data related to Sales Persons that don’t report to them directly or indirectly. Apart from that there are some other restrictions that we want to enforce. Since we operate in a number of geographical locations across the world, we don’t want Sales People assigned to one territory peeking at data that comes from another territory. I believe information about which Territory has been assigned to a Sales Person is already stored somewhere in our system.

Interview Transcript for David Bradley: CFO

I don’t have much to add here apart from what Brian has already discussed with you. One thing that I require is the ability to allow only people from the Finance department to have access to financial data. However Jill from the HR department is a little sensitive about disclosing detail level data related to an account heading called “Labor Expenses”. She wants to ensure that Accounts Executives like John Wood not have access to the values under it.

Interview Transcript for Kevin Brown: CTO

A lot of executives in the organization have questioned me if the DBAs and other engineers working in the IT department have access to all the sensitive information that is present in the cube. I know they do at the moment but I don’t want them to. I also know that they periodically need to carry out administrative related tasks on the database, but that’s all they should be having permissions for. At the moment I would like to ensure that I am the only person with administrative privileges as far as the cube is concerned.

Task 1: Creating Roles

1. Open a solution named Module6_Starter.sln from the source code accompanying this guide.

2. Based on the requirements presented earlier, the list of roles that would need to be created in SSAS and the users who would need to be added as members of those roles are mentioned below. A further explanation about the choices is given in the subsequent points.

Role Members Cube Administrator Process Dimensions Measure groups the role should not have permission for Hierarchies that should be restricted for this role Security Level
Senior Managers Ken SanchezBrian Welcker False False
Marketing Amy AlbertsStephen JiangSyed Abbas False False Financial Reporting Employee.EmployeesGeography.Geography Dimension data
Finance Managers David Bradley False False Reseller SalesInternet Sales
Finance Users John Wood False False Reseller SalesInternet Sales Account.Accounts Cell data
Cube Admins Kevin Brown True True
IT Engineers James Hamilton False True Reseller SalesInternet SalesFinancial Reporting

3. As per Kevin Brown’s interview transcript, only he should have administrative access to the cube. The other Engineers should have permissions to perform administrative related activities like processing dimensions and partitions, but not have access to dimension data or measures.

4. As per Brian Welcker’s interview transcript, Finance guys should not have any access to Sales data. Hence both Finance Managers and Finance Users are denied access to the Reseller and Internet Sales measure groups. Also only Senior Managers are allowed unrestricted access to Sales data. The folks from the Marketing department are only allowed to see data for the Employees that report directly or indirectly to them. Hence the marketing role will need to be given selective permissions to the Employee.Employees hierarchy.

5. As per Amy Albert’s interview transcript, Sales people should be allowed to see data only for the geographical location that they are assigned to. Hence the marketing role will also need to be restricted access to the Geography.Geography dimension.

6. As per David Bradley’s interview transcript, only Finance folks should be allowed to see financial data. Hence the marketing role should be denied access to the Financial Reporting measure group. Also the Finance Users role should not be able to see measures under an Account head called Labor Expenses. Hence Cell Data permissions would need to be configured for the Account.Accounts hierarchy.

  1. In the Analysis Service project that you opened earlier, right click on the “Roles” node in solution explorer and select the “New Role” option. A new file called “Role.role” should be created and added under the node.
  2. Rename the file to “Senior Managers.role”. When you are prompted to change the name of the object as well, click on the “Yes” button.
  3. In the “General” tab of the role designer, enable the “Read Definition” checkbox.

  1. Click on the “Data Sources” tab and select the “Read” permission under the “Access” column for the “AdventureWorks DW” data source
  2. Click on the “Cubes” tab and select the “Read” permission under the “Access” column for the “Module6_Starter” cube.
  3. Repeat steps 7 to 11 for all the other roles as well. At the end, you should have the following roles created – Senior Executives, Marketing, Finance Managers, Finance Users, Cube Administrators, IT Engineers.

Task 2: Assigning Permissions to Roles

1. Open the “Cube Administrators” role and enable the “Full Control (Administrator)” checkbox.

2. Open the “IT Engineers” role and enable the “Process database” checkbox.

3. Open the “Marketing” role and click on the “Dimension Data” tab. Select the “Measures Dimension” option in the “Dimension”.

4. Since members of the Marketing role are not allowed access to financial figures, remove the checkbox next to the “Amount” measure. Close the role.

5. Open the “Finance Managers” role and click on the “Dimension Data” tab. Select the “Measures Dimension” option in the “Dimension”. Since members of the Finance department are not allowed to see Sales Information, remove the checkbox next to all the measures except the “Amount” measure.

6. Repeat the above step for the “Finance Users” role as well.

7. Open the “IT Engineers” role and click on the “Cubes” tab. Since IT Engineers can only perform administrative tasks and are not allowed access to any data in the cube, change the permissions to “None” under the “Access” column for the “Module6_Starter” cube.

8. Deploy the project. Then open the “Module6_Starter” cube and go to the “Browser” tab.

9. Click on the “Change User” button on the toolbar and select one of the roles you have created. Ensure that the role selected can only view measures as per the scenario document. For example, if we try to browse the cube using the Finance Users role, then we can have access to only the Financial Reporting measure group.

Task 3: Assigning Dimension Data permissions to roles.

1. Open the “Marketing” role and click on the “Dimension Data” tab. Select the “Employee” dimension in the “Dimension” dropdown. Select the “Employees” hierarchy in the “Attribute” dropdown.

2. Click the “Advanced” tab and in the “Allowed Member Set” textbox, enter the following MDX expression. This MDX expression filters the members of the Employees hierarchy to include only those members whose LoginID attribute is equal to the current logged-in userid. This will ensure that each employee can only view Sales related information for Employees in the hierarchy above him or below him.

FILTER( [Employee].[Employees].Members, [Employee].[Employees].CurrentMember.Properties(“Login ID”) = UserName)

3. Go to the Membership tab of the Marketing role and add your current logged in account as a member in this role.

4. Deploy the solution.

5. In order to test this functionality, you need to take your system username and assign it to one of the users in the Marketing role eg. Amy Alberts. Open SQL Server Management Studio and execute the following sql query against the AdventureWorksDW2008R2 database. This query updates the LoginID column of Amy Alberts (employeeid = 290) to my system login. It also assigns a different sales territory (Australia) to her, which will help us in the subsequent tasks.

Update dbo.DimEmployee Set LoginID=’dellxpsnauzadk’, SalesTerritoryKey=9 where employeekey=290

6. Go back to the cube browser and click on the “Change User” button. Enable the Roles radio button and select the Marketing Role.

7. Drag the Employee.Employees hierarchies in the columns section of the cube browser. Drag the “Reseller Sales Amount” measure to the data section of the cube browser.

8. Without implementing the Dimension Data permissions for the Marketing role, this is the output that Amy Alberts would have seen.

9. However, if the Dimension Data permissions have been successfully implemented, you should only be able to see sales information of only Amy Alberts and the people who report to her and her superiors. Sales information of other marketing executives like Stephen Jiang or Syed Abbas should not be displayed.

Task 4: Assigning Cell Data Permissions

1. Open the “Finance Users” role and click on the “Cell Data” tab.

2. Enable the “Enable Read Permissions” check box and enter the following MDX expression in the textbox. This expression will allow members of the role to see only those cell values as long as the Account member is not under a parent member named “Labor Expenses”.

[Account].[Accounts].CurrentMember.Parent.Name <> “Labor Expenses”

3. Deploy the solution. Go to the cube browser and click on the “Change User” button. Enable the “Role” dropdown and select the “Finance Users” role.

4. Drag the Account.Accounts hierarchies in the columns section of the cube browser. Drag the “Amount” measure (from the Financial Reporting measure group) to the data section of the cube browser. Expand the accounts until you reach the child members under the “Labor Expenses” account. You should not see any cell values for the child accounts as shown below.

Task 5: Using a .NET Assembly to control permissions

1. Open the “Marketing” role and click on the “Dimension Data” tab. Select the “Geography” dimension in the “Dimension” dropdown. Select the “Geography Key” attribute in the “Attribute” dropdown.

2. Click the “Advanced” tab and in the “Allowed Member Set” textbox, enter the following MDX expression.

Filter([Geography].[Country].Children,StrToMember([SSASLibrary].[SSASLibrary].[Class1].GetEmployeeTerritory(UserName)))

3. This MDX expression filters the Geography dimension to include only those members whose Sales Territory Key is equal to the Sales Territory of the current logged in user. This will ensure that each member of the Marketing Role can only view Sales related information for the geographical locations that are assigned to him. This MDX expression uses a managed stored procedure called “GetEmployeeTerritory” to retrieve the Sales Territory that is assigned to a Sales Person. The source code for the stored procedure is located in the SSASLibrary folder in the source code accompanying this guide.

4. The source code connects to the AdventureWorksDW2008R2 database and queries the DimEmployee table and retrieves the Territory assigned to the loginid (passed to it as an input parameter). The sales territory is then formatted as a Member expression and returned as output from the function. The MDX expression uses the STRToMember function to convert the string value to a Member expression. Note that this requirement could even have been tackled without an external .net assembly, but I have used it just to illustrate one more way in which complex access control requirements could be implemented.

5. Deploy the solution. Go to the cube browser and click on the “Change User” button. Enable the “Role” dropdown and select the “Marketing” role. Drag the Geography.Geography hierarchy in the columns section of the cube browser. Drag the “Reseller Sales Amount” measure to the data section of the cube browser.

6. You should only be able to see sales information of the country assigned to the current logged in user (provided the current logged in user is a member of the Marketing Role). For example, in the figure below, since Amy Alberts has been assigned a Sales Territory of 9 (Australia), she can see only data for that location and no other.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: