SSAS Security : Consider Cube Browsing when Building Role Playing Dimensions

A Role Playing Dimensions reuses its definition to represent same concept in different contexts. Common example of Role Playing Dimension is date dimension. For instance, date dimension can be used to represent order date, delivery date and shipment date. Currency, staff, organisation hierarchy are other dimensions that are usually role played. While Role Playing Dimensions promote dimension reuse and share dimension data security, the effect on cube browsing must also be considered.

If let’s say a role played date dimension is used as filter in cube browser, you would see all possible date members that the dimension can possibly hold irrespective of whether there are facts for that date (Screen Capture 1). Because of their generic nature, dimensions that are role played must cover all possible ranges of data. As you can see, the lengthy filter list where the user is unsure which dates have facts is bad for cube browsing and could potentially turn-off users. With Role Playing Dimensions also consider Dimension Data Security which is slightly different from regular dimensions.

Screen Capture 1 – Role Played Date Dimension

Now compare this with a Non-role Playing Dimension that is customized to display only the date members that have facts associated with it (Screen Capture 2). Isn’t this a better cube browsing experience? You could achieve this by using a named query as data source for the dimension. If the query is to be used in different projects use a materialized database view instead.

Screen Capture 2 – Non-Role Playing Dimension

However take note if not careful, this would lead to creating more dimensions than what is required. You could for instance take a hybrid approach and customize only the commonly used slicers to non-Role Playing dimension and can leave the rest as Role Playing Dimensions. The key point to remember is whether the dimension is role playing or not, as long as it is derived from same source it must be a Conformed Dimension. This means the name and number of the attributes, key column, name column & value column, attribute relationships, sort order and hierarchies must be the same between the role playing and non-playing dimensions derived from same data source. The only difference is the number of members in a non-Role Playing dimension is fewer than the Role Playing Dimension.

SSAS: AMO Coding Tips

Some coding tips when using Analysis Management Objects (AMO) methods

1. Supply Object ID instead of Object Name as parameter to Find() methods.

SSAS objects like Database, Cube, Dimension, Attributes and Roles have an Object ID and Object Name. While the Object Name can be modified anytime, the Object ID is non-editable and remains constant after first build. For e.g. the Name of the dimension can be changed from Org Chart to Organisation Hierarchy. However Object ID still remains as Org Chart. In other words, the Object Name and Object ID need not be same and could have different values. Find() is a common AMO method used to return an instance of SSAS object from their respective collection. Object ID should be passed to Find() instead of Object Name. Otherwise you might get exceptions especially when the Object ID has a different value from Object Name. Alternatively you could use FindByName() method instead where available. Some objects like AttributePermission do not have FindByName() method and you have to use the Find() method by
passing the Object ID.

1	AttributePermission dimAttrPermission = dimPermission.AttributePermissions.Find(attribID);

However take note when passing MDX expressions use Object Names instead of Object ID.

2. Run AMO Code from SSAS Server

AMO methods are memory intensive. SSAS responds to commands in XMLA format. XMLA commands are issued to SSAS when Update () method is invoked. For performance reasons it is recommended to run AMO batch jobs from the SSAS server

3. Judicious use of Update() method

AMO operations are translated to XMLA command when the Update() method is invoked. So it is necessary to make judicious use of Update () method in your AMO code. For e.g. in this post – Using AMO to secure Analysis Service Cube , the overloaded GrantDatabaseRead() method invokes the Update () method only once at the end of the operation after read permission has been granted to ALL roles in the database instead of calling once for each role.

4. Cube Dimension Permission vs. Database Dimension Permission

By default, granting read permission to a cube implicitly grants read permission to all cube dimensions without the need to explicitly grant read access to related database dimensions. Only if you are defining dimension data access, then you need to explicitly grant read permission to the required database dimensions.

5. Grant Database Read Definition permission if using SSMS

Database Read Definition permission is usually not required to browse cube, however might be helpful for troubleshooting role permissions using SSMS.

6. Catch Exceptions

Apart from the generic AmoException, it is recommended to catch the following exception in the given order

• OutOfMemoryException
• ConnectionException
• ArgumentNullException
• OperationException
• AmoException

7. Deploy with care

Changes to SSAS cube AFTER the roles and membership have been established must be carried out with care; otherwise you run the risk of wiping out the already existing roles, membership and cube security when deploying your latest cube. In particular if you are using SSAS Deployment Wizard, choose the option to Retain roles and members or Deploy Roles and retain members.

Related Posts

SSAS: Using AMO to Secure Analysis Service Cube

SSAS: Secure Analysis Service Cube (Part-4)

SSAS: Membership in Multiple Roles Could Throw Unexpected Surprises

Analysis Service supports role based model to secure cube data. Data can be secured at object level, cell level (Cell Data Access) and at member level (Dimension Data Access). There are relative merits of each approach, which isn’t the topic of this post but more details can be found here. A user can be a member of more than one role and it is important to understand the behaviour of Analysis Services role permissions in such a scenario. Analysis Services role permissions are additive in nature. This means if you are a member of a role that has been granted access to an Analysis Service object; you have access to that object even if you belong to another role which denies access to the same object. In other word the least restrictive roles permissions override the most restrictive role permissions. This could throw some nasty surprises that you never expected.  

To illustrate this, I’m using Dimension Data Access method on AdventureWorks cube. 

Allow Country Data but Deny Customer Info

A role called Australian SalesRep is created for the Sales Office in Australia and this role has been granted access only to the Australia member of the Sales Territory Dimension as shown in Screen Capture 1. 

Screen Capture 1 - Australia SalesRep Role

Another role called Deny CustomerInfo is created to deny access to customer data for privacy reasons as shown in Screen Capture 2. 

Screen Capture 2 - Deny CustomerInfo Role

Let’s say certain users in Australian Sales Office are required to have access to their sales data but no access to their customer data, so these users are granted access to both Australian SalesRep and Deny CustomerInfo roles. You would expect these users to have access to Australia’s Sales data but no drill-down to customer data. Now, let’s see what happens. 

Screen Capture 3 - Additive Nature of SSAS Roles

• The result in screen capture 3 will drive you nuts. It shows Sales data from all countries and for all customers! At the outset it may seem that the roles are not taking effect, but this is what happens and it is important to understand why this happens.
   The reason you are able to see all customers is because the Australian SalesRep secures the Sales Territory but not the customers. So even though Deny CustomerInfo denies access to Customer data, the additive nature of Australian SalesRep role overrides the restrictive role of Deny CustomerInfo on customer data.
•  So why are you able to see sales data from all the countries? That’s because the Deny CustomerInfo secures the Customer data and not the Sales Territory data. So the combined effect is Deny CustomerInfo overrides the restrictive role of Australian SalesRep on Sales Territory.
  As you can see creating multiple roles and granting users memberships to multiple roles does not always solve the security requirement. In fact, if not properly understood and implemented this can create serious security violations. In this instance, the requirement can be satisfied my amending the role definition of Australian SalesRep to deny access to customer dimension, thereby eliminating the need for Deny CustomerInfo role (Screen Capture 4).

Screen Capture 4 - Australian SalesRep Role with no access to Customer Data

SSAS: Secure Analysis Service Cube (Part-3)

Securing Role Playing Dimensions in Analysis Services

In Analysis Services, dimension data can be secured at either Shared Dimension or Cube Dimension. A Shared Dimension is an object of Analysis Service Database which is available to all the cubes in the database. The Cube Dimension on the other hand is an object of the cube and is an instance of the Common Dimension which is available only to a specific cube
(Screen Capture 1).

Screen Capture 1 – Shared Dimension Vs Cube Dimension

Usually, it’s a good idea to secure the data at Shared Dimension level as the permissions gets passed down to Cube Dimensions. This is especially helpful if the Dimension Data security is automated using AMO code – less code to maintain.

This works in most scenarios, except when the dimension is used as Role Playing Dimension. For e.g. Time, Currency, Organization Structure, Staff dimensions are commonly role played. Analysis Services does not permit securing the data of Shared Dimension if it is used as role playing dimension. Exceptions are thrown when the cube if browsed, if you attempt to do so.

If a dimension is role playing, the only way data security can be implemented is by securing the individual Cube Dimension. Cube Dimension Data Security overrides Shared Data Security.

The following screen capture shows the effect of securing the role playing dimension- using Date Dimension from AdventureWorks. Here Date Dimension (Shared Dimension) role plays as Delivery Date (Cube Dimension) and Ship Date (Cube Dimension).

Screen Capture 2 – Securing Role Playing Dimension

Securing Cube Dimensions can be quite daunting especially if you are considering automating cube security because all the related dimensions need to be considered individually, but that’s the only way out.

SSAS: Using AMO to Secure Analysis Service Cube (Part-2)

SSAS: Using AMO to Secure Analysis Service Cube (Part-2)
AddMemberToRole()

·         This is the Role Membership method that adds a windows user/group to an existing Role.

·         Creates the Role if it does not exist.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

public int AddMemberToRole(String memberName, String roleName) { int retVal = 1; bool memberExists = false;  try { //Create role if it does not exist Role role = this.Database.Roles.FindByName(roleName); if (role == null) { role = this.Database.Roles.Add(roleName); this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); }  //check role membership already exists for windows user foreach (RoleMember member in role.Members) { if (member.Name.Contains(memberName)) memberExists = true; }  //Add member to role if not already a member if (!memberExists) { role.Members.Add(new RoleMember(memberName)); role.Update(UpdateOptions.AlterDependents, UpdateMode.CreateOrReplace); } } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: AddmemberToRole() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (memberName == null) { throw new AmoException("ERROR: memberName parameter supplied with NULL value to AddMemberToRole()"); } if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to AddMemberToRole()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: AddMemberToRole() failed with exception " + OpException.Message + ". Parameters passed were memberName =" + memberName + ",roleName=" + roleName); } catch (AmoException GenericAmoException) { if (memberName.Trim() == "") { throw new AmoException("ERROR: memberName parameter supplied with blank value to AddMemberToRole()"); } if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to AddMemberToRole()"); }  throw GenericAmoException; }  return retVal; }

GrantDatabaseRead()

·         Every role created must be given read permission to the Analysis Service Database. Use this method to grant read access to Analysis Service Database for a role.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

public int GrantDatabaseRead(String roleName) { int retVal = 1;  try { Role role = this.Database.Roles.FindByName(roleName); if (role == null) return -1;  DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID); if (dbPermission == null) { dbPermission = new DatabasePermission(); dbPermission.RoleID = role.ID; dbPermission.ID = role.Name; dbPermission.Name = role.Name; dbPermission.Read = ReadAccess.Allowed; this.Database.DatabasePermissions.Add(dbPermission); } else { dbPermission.Read = ReadAccess.Allowed; }  dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);  } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantCubeRead() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantCubeRead()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: GrantCubeRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName); } catch (AmoException GenericAmoException) { if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantCubeRead()"); }  throw GenericAmoException; }  return retVal; }

Overloaded GrantDatabaseRead()

·         The overloaded GrantDatabaseRead() method grants read access of Analysis Service Database for ALL roles.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

public int GrantDatabaseRead() { int intRetval = 1;  try { foreach (Role role in this.Database.Roles) { DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID); if (dbPermission == null) { dbPermission = new DatabasePermission(); dbPermission.RoleID = role.ID; dbPermission.ID = role.Name; dbPermission.Name = role.Name; dbPermission.Read = ReadAccess.Allowed; this.Database.DatabasePermissions.Add(dbPermission); dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); } else { dbPermission.Read = ReadAccess.Allowed; } } //update database just once (more efficient this way) this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);  } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantDatabaseReadDefinition() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); }  catch (OperationException OpException) { throw new AmoException("ERROR: GrantDatabaseReadDefinition() failed with exception " + OpException.Message); } catch (AmoException GenericAmoException) { throw GenericAmoException; }  return intRetval; }

GrantDatabaseReadDefinition()

·         Database Read Definition permission is mostly optional and is not required for browsing the SSAS cube. Usually required only to examine Database metadata from SQL Server Management Studio.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

public int GrantDatabaseReadDefinition(String roleName) { int intRetval = 1;  try { Role role = this.Database.Roles.FindByName(roleName); if (role == null) return -1;  DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID); if (dbPermission == null) { dbPermission = new DatabasePermission(); dbPermission.RoleID = role.ID; dbPermission.ID = role.Name; dbPermission.Name = role.Name; dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed; dbPermission.Read = ReadAccess.Allowed;//grant read access this.Database.DatabasePermissions.Add(dbPermission); } else { dbPermission.Read = ReadAccess.Allowed;//grant read access dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed; } dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantDatabaseReadDefinition() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDatabaseReadDefinition()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: GrantDatabaseReadDefinition() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName); } catch (AmoException GenericAmoException) { if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDatabaseReadDefinition()"); }  throw GenericAmoException; }  return intRetval; }

Overloaded GrantDatabaseReadDefinition()

·         The overloaded GrantDatabaseReadDefinition() method grants read definition permission of the Analysis Service Database to ALL roles.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

public int GrantDatabaseReadDefinition() { int intRetval = 1;  try { foreach (Role role in this.Database.Roles) { DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID); if (dbPermission == null) { dbPermission = new DatabasePermission(); dbPermission.RoleID = role.ID; dbPermission.ID = role.Name; dbPermission.Name = role.Name; dbPermission.Read = ReadAccess.Allowed;//grant read access dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed; this.Database.DatabasePermissions.Add(dbPermission); dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); } else { dbPermission.Read = ReadAccess.Allowed;//grant read access dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed; } } //update database just once (more efficient this way) this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);  } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantDatabaseReadDefinition() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (OperationException OpException) { throw new AmoException("ERROR: GrantDatabaseReadDefinition() failed with exception " + OpException.Message); } catch (AmoException GenericAmoException) { throw GenericAmoException; } return intRetval; }

GrantCubeRead()

·         This method grants the role read access to cube.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

public int GrantCubeRead(String roleName) { int retVal = 1;  try { Role role = this.Database.Roles.FindByName(roleName); if (role == null) return -1;  CubePermission cubeReadPermission = this.Cube.CubePermissions.FindByRole(role.ID);  if (cubeReadPermission == null) //no permissions cubeReadPermission = this.Cube.CubePermissions.Add(role.ID);  cubeReadPermission.Read = ReadAccess.Allowed; cubeReadPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);  } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantCubeRead() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantCubeRead()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: GrantCubeRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName); } catch (AmoException GenericAmoException) { if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantCubeRead()"); }  throw GenericAmoException; }  return retVal; }

GrantDimensionRead()

·         By default Database Read access implicitly grants read access to Database Dimensions and Cube Read access implicitly grants access to the Cube Dimensions. Use this method only if Dimension Data Access is to be defined on the Dimension’s Attribute.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

public int GrantDimensionRead(String roleName, String dimensionName) { int retVal = 1; try { Role role = this.Database.Roles.FindByName(roleName); if (role == null) return -1;  Dimension dimension = this.Database.Dimensions.FindByName(dimensionName); if (dimension == null) return -1;  DimensionPermission dimReadPermission = dimension.DimensionPermissions.FindByRole(role.ID); if (dimReadPermission == null) //no permission dimReadPermission = dimension.DimensionPermissions.Add(role.ID);  dimReadPermission.Read = ReadAccess.Allowed; dimReadPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);  } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantDimensionRead() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDimensionRead()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: GrantDimensionRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName); } catch (AmoException GenericAmoException) { if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDimensionRead()"); }  throw GenericAmoException; } return retVal; }

Overloaded GrantDimensionRead()

·         Grants read permission on all dimensions in the Analysis Service Database to the role. This is an expensive operation and time duration to complete is directly proportional to the number of dimensions and roles in the Analysis Service Database.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

public int GrantDimensionRead(String roleName) { int retVal = 1; try { Role role = this.Database.Roles.FindByName(roleName); if (role == null) return -1;  foreach (Dimension dimension in this.Database.Dimensions) { DimensionPermission dimReadPermission = dimension.DimensionPermissions.FindByRole(role.ID);  if (dimReadPermission == null) //no permission dimReadPermission = dimension.DimensionPermissions.Add(role.ID);  dimReadPermission.Read = ReadAccess.Allowed; dimReadPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); } } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantDimensionRead() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDimensionRead()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: GrantDimensionRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName); } catch (AmoException GenericAmoException) { if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDimensionRead()"); } throw GenericAmoException; } return retVal; }

GrantDimensionDataRead()

·         This method defines the Dimension Data Access by securing the members of the Dimension Attribute using an MDX expression.

·         Test the MDX expression before passing here.

·         Passing an Invalid MDX Expression will not secure the Dimension Attribute Data and doesn not throw an exception.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

public int GrantDimensionDataRead(string roleName, string dimensionName, string attribName, string mdxExpression) { int retVal = 1; try { Role role = this.Database.Roles.FindByName(roleName); if (role == null) return -1;  Dimension dim = this.Database.Dimensions.FindByName(dimensionName); if (dim == null) return -1;  DimensionPermission dimPermission = dim.DimensionPermissions.GetByRole(role.ID);  if (dimPermission == null) dimPermission = dim.DimensionPermissions.Add(role.ID);  AttributePermission dimAttrPermission = dimPermission.AttributePermissions.Find(attribName);  if (dimAttrPermission == null) { dimAttrPermission = new AttributePermission();  dimAttrPermission.AllowedSet = mdxExpression; dimAttrPermission.VisualTotals = "1"; DimensionAttribute dimAttrib = dim.Attributes.FindByName(attribName); if (dimAttrib == null) return -1; dimAttrPermission.AttributeID = dimAttrib.ID;  dimPermission.AttributePermissions.Add(dimAttrPermission); } else { dimAttrPermission.AllowedSet = mdxExpression; dimAttrPermission.VisualTotals = "1"; dimAttrPermission.AttributeID = attribName; }  dimPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate); } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: GrantDimensionDataRead() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDimensionDataRead()"); } if (dimensionName == null) { throw new AmoException("ERROR: dimensionName parameter supplied with NULL value to GrantDimensionDataRead()"); } if (mdxExpression == null) { throw new AmoException("ERROR: mdxExpression parameter supplied with NULL value to GrantDimensionDataRead()"); } if (attribName == null) { throw new AmoException("ERROR: attribName parameter supplied with NULL value to GrantDimensionDataRead()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: GrantDimensionDataRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName + ",dimensionName=" + dimensionName + ",attribName=" + attribName + ",mdxExpression=" + mdxExpression); } catch (AmoException GenericAmoException) { if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDimensionDataRead()"); } If (dimensionName.Trim() == "") { throw new AmoException("ERROR: dimensionName parameter supplied with blank value to GrantDimensionDataRead()"); } if (mdxExpression.Trim() == "") { throw new AmoException("ERROR: mdxExpression parameter supplied with blank value to GrantDimensionDataRead()"); } if (attribName.Trim() == "") { throw new AmoException("ERROR: attribName parameter supplied with blank value to GrantDimensionDataRead()"); }  throw GenericAmoException; }  return retVal; }

DropRole()

·         This method deletes the role from Analysis Service Database. All permissions associated with the role on Cube, Dimension and Dimension Data will also be deleted.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

public int DropRole(String roleName) { int retVal = 1; Role role = this.Database.Roles.FindByName(roleName); try { if (role == null) return retVal;  //delete members from role if (role.Members.Count > 0) { role.Members.Clear(); role.Update(); }  //delete dimension permissions for the role foreach (Dimension dim in this.Database.Dimensions) { DimensionPermission dimPermission = dim.DimensionPermissions.FindByRole(role.ID);  if (dimPermission != null) { dimPermission.AttributePermissions.Clear(); dimPermission.Drop(DropOptions.AlterOrDeleteDependents); } } //delete cube permissions for the role CubePermission cubePermission = this.Cube.CubePermissions.FindByRole(role.ID); if (cubePermission != null) cubePermission.Drop(DropOptions.AlterOrDeleteDependents);  //delete database permissions for role DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID); if (dbPermission != null) dbPermission.Drop(DropOptions.AlterOrDeleteDependents); //dbPermission.Update();  //finally delete role from database role.Drop(DropOptions.AlterOrDeleteDependents); } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: DropRole() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to DropRole()"); } throw ArgNullException; } catch (OperationException OpException) { role.Refresh(true); throw new AmoException("ERROR: DropRole() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName); } catch (AmoException GenericAmoException) { if (roleName.Trim() == "") throw new AmoException("ERROR: roleName parameter supplied with blank value to DropRole()"); throw GenericAmoException; } return retVal; }

DropAllRoles()

·         Deletes ALL roles from the Analysis Service Database. All permissions associated with the role on Cube, Dimension and Dimension Data will also be deleted.

·         Use this method with care. This is an expensive operation.

·         Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

public int DropAllRoles() { int retVal = 1; try { List<Role> roles = new List<Role>(); foreach (Role role in this.Database.Roles) { roles.Add(role); } foreach (Role role in roles) { this.DropRole(role.Name); } } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: DropAllRoles() failed with out of memory exception. The exception message is " + memoryException.Message); } catch (ConnectionException ServerNotFoundException) { throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (OperationException OpException) { throw new AmoException("ERROR: DropAllRoles() failed with exception " + OpException.Message); } catch (AmoException GenericAmoException) { throw GenericAmoException; }  return retVal; }

There are few important pointers to watch out for when using AMO, which I intend to cover in next post. Meanwhile here are few links related to SSAS cube security.

Related Links:

·         AMO Coding Tips

·         Cell Data Access Vs. Dimension Data Access

·         Securing Role Playing Dimensions

·         How to Automate Analysis Services Database Backup?

·         Membership in Multiple Roles Could Throw Unexpected Surprises

·         Testing/Troubleshooting Analysis Service Cube Security from SQL Server Management Studio