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

SSAS: Using AMO to Secure Analysis Service Cube

Analysis Management Objects (AMO) opens up the object model of SSAS cube for programming. With a bit of .NET programing, AMO can be effectively used to automate administration tasks. Securing the cube is one such task that can be easily automated using AMO. The rest of this post explains how to automate cube security using AMO.

SSAS security is role-based where object permissions are defined at role level. Members/users are then granted membership to roles. Role members are either windows users or windows groups. Object permissions can be defined on

• Analysis Service Database
• Cubes
• Shared Database Dimensions
• Cube Dimensions and Role Playing Dimensions
• Cells (Cell Data Access)
• Dimension Attribute Members (Dimension Data Access) and
• Data Mining Models

The sequence of steps to secure SSAS cube using AMO would be:

1. Instantiate Major Objects of Analysis Services which is Analysis Server, Analysis Service Database and Cube
2. Backup Database before securing cube (recommended)
3. Create Role.
4. Add Member to role.
5. Grant Read Access on database to role.
6. Grant Read Definition on database to role (optional)
7. Grant Read Access on cube to role.
8. Grant Read Access to shared database Dimension and/or Cube Dimension.
9. Grant Read Access to dimension attribute

To automate these steps a metadata table would be required that stores the following information which would be passed as parameters to the AMO routine

• Role Name
• Role Member (Windows user/windows Group)
• ID of Database to be secured
• ID of Cube to be secured.
• ID of Dimension that is to be secured
• ID of Dimension Attribute that is to be secured.
• Flag to indicate Shared Database Dimension or Cube Dimension
• Columns required create the Attribute Member MDX expression that will be used to secure the Attribute Member.

Code Snippet

A simple implementation of using wrappers over AMO to automate cube security would be as shown below. A detailed explanation of individual methods is given in subsequent sections

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

//Instantiate Major Objects CsaWrappers csa = new CsaWrappers(server, database, cube); //Backup Database (recommended) csa. BackupDatabase(filename); //for each metadata record while (metadata.Read()) { //Create Role and add member to role csa.AddMemberToRole(roleMember, roleName); //Grant Database Read Access csa.GrantDatabaseRead(roleName); //Grant Cube Read Access to role csa.GrantCubeRead(roleName); //Grant Cube Dimension Access to role csa.GrantDimensionRead(roleName, dimName); //Grant Cube Dimension Access to role csa.GrantDimensionDataRead(roleName, dimName, dimAttribName, mdxExp); }

Pre-requisites

1. To program AMO, download and install Microsoft Analysis Management Objects (MSAMO) installer from the relevant version of SQL Server Feature Pack.
2. Create a Console Application project in Visual Studio and create a reference to Microsoft.AnalysisServices
3. Create a new class file and add the namespace Microsoft.AnalysisServices

Constructor

• The constructor instantiates the Major Analysis Service Objects namely the Analysis Server, Analysis Service Database and Cube using the parameters passed
• It is important to pass the ID of Database and Cube and NOT the names. The ID and Name of the Database and Cube may not always be the same especially if the names are modified after initial build.
• 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 CsaWrappers(string server, string database, string cube) {  try { //Server this.Server = new Server(); this.Server.Connect(server);  //Database this.Database = this.Server.Databases.FindByName(database);  //Cube this.Cube = this.Database.Cubes.FindByName(cube);  } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: Constructor 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 '" + server + "'. Connection failed with error message " + ServerNotFoundException.Message); } catch (ArgumentNullException ArgNullException) { if (server == null) { throw new AmoException("ERROR: server parameter supplied with NULL value to constructor"); } if (database == null) { throw new AmoException("ERROR: database parameter supplied with NULL value to constructor"); } if (cube == null) { throw new AmoException("ERROR: cube parameter supplied with NULL value to constructor"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: Constructor failed with exception " + OpException.Message + ". Parameters passed were server=" + server + ",database=" + database + ",cube=" + cube); } catch (AmoException GenericAmoException) { if (this.Database == null) throw new AmoException("ERROR: Unable to connect to Database '" + database + "' on '" + server + "'"); if (this.Cube == null) throw new AmoException("ERROR: Unable to connect to Cube '" + cube + "' within '" + database + "' on '" + server + "'");  throw GenericAmoException; }  }

BackupDatabase()

• Backs up Analysis Service Database using the fully qualified filename as parameter.
• Timestamp the filename parameter to create a rolling backup
• 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

public int BackupDatabase(string fileName) { int retVal = 1; try { this.Database.Backup(this.Database.DbStorageLocation + fileName, true); } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: BackupDatabase() 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 (fileName == null) { throw new AmoException("ERROR: fileName parameter supplied with NULL value to BackupDatabase()"); }  throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: BackupDatabase() failed with exception " + OpException.Message + ". Parameters passed were fileName =" + fileName); } catch (AmoException GenericAmoException) { if (fileName.Trim() == "") { throw new AmoException("ERROR: fileName parameter supplied with blank value to BackupDatabase()"); }  throw GenericAmoException; }  return retVal; }

AddRole()

• Creates a Role in Database if it does not exist already.
• 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

public int AddRole(String roleName) { int retVal = 1; try { if (this.Database.Roles.FindByName(roleName) == null) { Role role = this.Database.Roles.Add(roleName); role.Update(); }; } catch (OutOfMemoryException memoryException) { throw new OutOfMemoryException("ERROR: AddRole() 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 AddRole()"); } throw ArgNullException; } catch (OperationException OpException) { throw new AmoException("ERROR: AddRole() 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 AddRole()"); throw GenericAmoException; }  return retVal;  }