Benny Austin

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

//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
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.
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
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;
 }

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
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
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.
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.
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.
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.
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.
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.
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.

     

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.
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.
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:

Benny Austin

About these ads

30 comments on “SSAS: Using AMO to Secure Analysis Service Cube

  1. Thanapat
    June 8, 2011

    I realy need to dev AMO , but i’m a newbie in AMO , Would you send me a simple project please ?

    • Benny Austin
      June 9, 2011

      Sorry, for confidentiality reasons I would not be able to send you what you requested.

  2. Pingback: Jeu de rôles avec Analysis Services « Business Geek

  3. Mats Remman
    June 15, 2011

    Did you mix up the definitions for GrantDimensionRead and the overloaded version? It seems the explanation is reversed, as well as from what i can gather what the code does.

    • Benny Austin
      June 16, 2011

      Yes it was mixed up. Now it’s corrected.Thanks for letting me know.

  4. Pingback: AMO Wrappers for Securing Analysis Service Cube « Benny Austin

  5. Pingback: SSAS: AMO Coding Tips « Benny Austin

  6. Jesse
    August 5, 2011

    Great stuff Benny!

    What do you mean by a “shared” dimension?

    • Benny Austin
      August 5, 2011

      By shared dimension I mean database dimension

  7. Jesse
    August 9, 2011

    Question –

    I’m adding attribute security for a dimensional attribute (Allowed Member Set). I’ve already allowed access to the cube.

    Would I have to call GrantDimensionRead() and then GrantDimensionDataRead(), or just call GrantDimensiondataRead()?

    • Benny Austin
      August 9, 2011

      You have to call GrantDimensionRead() and then GrantDimensionDataRead()

      • Jesse
        August 10, 2011

        Noticed that in GrantDimensionDataRead() you do a check to see if the dimension has permissions and then assign permissions if they don’t exist (which I’m guessing is a fall back in case GrantDimensionRead() wasn’t called). However you don’t give it read access like you do in GrantDimensionRead(). Any reason why?

        -GrantDimensionRead()
        dimReadPermission = dimension.DimensionPermissions.Add(role.ID);
        dimReadPermission.Read = ReadAccess.Allowed;
        dimReadPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);

        -GrantDimensionDataRead()
        if (dimPermission == null)
        dimPermission = dim.DimensionPermissions.Add(role.ID);

        Thanks again!

      • Benny Austin
        August 13, 2011

        You are right, it’s a good idea to grant read access to dimension in GrantDimensionDataRead()

  8. peteadshead
    August 20, 2011

    Great post, I’ve linked to it from my page
    http://peteadshead.wordpress.com/2011/08/20/using-amo-to-administer-msas-cubes/ where i’ve got another couple of AMO options

  9. guru
    September 2, 2011

    One more blog to automatic role maintenance of SSAS roles and addiing features to exe, my goal is to convert them into SQL CLR SP and schedule it.

    http://www.consultguru.me/post/2011/09/01/Automatic-User-Role-Maintenance-in-SSAS-using-AMO.aspx

    http://www.consultguru.me/post/2011/09/02/Automatic-User-Role-Maintenance-in-SSAS-using-AMO-Part-2.aspx

  10. Great post

  11. suresh
    January 21, 2012

    I realy need to dev AMO cube, but i’m a newbie in AMO , Would you send me a simple project please ? i want basics on words…plz hlp me

  12. Harsha
    January 28, 2012

    Is there a reason this.Database.Update() is executed in a loop in the DropAllRoles method? It looks like a single statment at the end of the method is sufficient.

    • Benny Austin
      January 28, 2012

      hi Harsha, you are right. Database.Update() is only required at the end of loop. Probably a copy-paste error while posting which is now corrected. Thanks for spotting that.

  13. Kapil Kaushish
    September 4, 2012

    Hi Benny,

    Really very nice article. I am a newbie to SSAS. Could you refer me to any discussion forum or book to sharpen my skills in MDX query writing and fetching and updating(Writback) the cubes through web application.

    Thanks,
    Kapil Kaushish

  14. nmt
    February 11, 2013

    I am hitting the error, “no mapping between account names and security IDs was done” for a subset of my firms employees, about 25%. These are all active employees with active AD accounts. Searching the internet shows other people hit this but its when they are dealing with AD accounts that have been deleted. In my case the AD accounts are there and I am not specifying the SID so it should match by sAMAccountName, not by SID anyway. Any recommendations?

  15. nmt
    February 11, 2013

    The solution is to call role.Refresh() in the exception handler.

  16. silke
    March 8, 2013

    Hello Benny,
    i owe you something.
    Thank you

  17. tom
    April 12, 2013

    Hi Benny, many thanks for this, very useful.

    One comment I had is that for droping roles, I found I didn’t need to drop db/cube/dimension permissions seperately, If I just called: role.Drop(DropOptions.AlterOrDeleteDependents); then the corresponding permissions were dropped.

    • Benny Austin
      April 12, 2013

      Tom, Thanks for that tip.

  18. Pingback: SSAS Dynamic Security and Visual Totals | Gerhard Brueckl's BI Blog

  19. Pingback: AMO Code Snippets to secure SSAS | Klaus Hoeltgen's Blog

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

Copyright

© Benny Austin.
Follow

Get every new post delivered to your Inbox.

Join 190 other followers

%d bloggers like this: