Keeping up with an organization’s changing SQL Server Analysis Services (SSAS) security needs can be time consuming. I work for a hospital organization, so there’s a lot of focus on “appropriate use” and “need-to-know” data access. Our team is sometimes required to implement complex SSAS security definitions for a large number of cubes to meet users’ business needs. Reporting on who has access to certain data sets can be time consuming, and it’s sometimes difficult to communicate a complex security structure to leadership and data stewards. I developed an extraction and reporting system for our SSAS cubes (an SSAS security metadata cube) to reduce the effort needed to report on these security definitions.
One approach to keeping up with cube security changes is to delegate the ownership of who should and shouldn’t have access to a particular data set to the appropriate data owners (data stewards). This solution provides users with a tool they can use to audit the security for the data they own. With this approach, authorization for access can be confirmed by the appropriate data steward before the cube administrator is involved. The security metadata cube enables the data steward to quickly identify and analyze which users have access to the cubes, dimensions, and roles they’re responsible for. With this information easily available, the data steward can more efficiently communicate a security update to the cube administrator. Here, I’ll show you how to create the SSAS security metadata cube with minimal effort and time.
Extracting SSAS Security Metadata
There are many ways to create an SSAS security metadata cube to allow data stewards to audit cube security. I’ve chosen to use SQL Server Integration Services (SSIS) and the Script task to extract the necessary security metadata by leveraging the Analysis Management Objects (AMO) API. The AMO API is well documented and contains a roadmap for extracting security metadata from SSAS. To get started, you might want to download the SSIS project (Load_CubeSecurity_ISPackage) that loads the metadata security cube. (You can download this project by clicking the 103267.zip file at the top of the article page.) You’ll also need to create a table in a database to persist your cube metadata.
Figure 1 shows the high-level control flow of the SSIS package.
First, it extracts the AMO security definition using the Script task. Next, it deposits the records from the SSIS DataTable object created in the Script task into a table (AMOSecurityUDM.bAMOSecurityDefinition) using the Foreach loop container. Finally, we process the cube, which I’ll explain in more detail later. Let’s walk through the details of each control flow task.
Web Listing 1 provides the script used to extract the security metadata from AMO and pass the results back into a read/write SSIS variable, user::varDataTable. After defining the necessary objects and variables, the script creates a memory table with attributes to contain the security metadata. The script continues to do four nested loops to traverse the security portion of the AMO. Specifically, it’s looping through the AMO API Cube, Role, and Role Member security objects. At the innermost loop, the script adds a record to the DataTable variable. After walking through the security objects, the script writes the resulting data table to the user::varDataTable variable.
The Foreach loop container iterates over the records of the user::varDataTable memory object table populated from the Script task that’s shown in Figure 1. Each iteration of the Foreach loop container executes a single Execute SQL task. The script in Listing 1 deposits user::varDataTable records into a table one-by-one using an OLE DB connection. Now, let’s look at how to build the cube.
Creating the Security Metadata Cube
You can use the code in Web Listing 2 to create and publish the metadata security reporting cube, and the complete SSAS cube project, CubeSecurity_AnalysisServicesCube, is available for download in the 103267.zip file. You might want to have the project open as a reference as we outline the design details. You’ll need to update the data source connection settings for the project and the connection settings using the script in Web Listing 1.
To keep this process as simple as possible, the security metadata cube is sourced exclusively from the table (AMOSecurityUDM.bAMOSecurityDefinition) we populated in the SSIS package. All of the dimensions will be sourced from the fact table. You might choose to create independent dimension tables, but for the purpose of this article, we’ll use a single table to keep things easy. Figure 2 shows the data source view containing the single source table for the security cube.
I created attribute hierarchies from the columns in our AMOSecurityUDM.bAMOSecurityDefinition source table for Cube, CubeRoleSecurity, User, CubeAccessCode, and CubeRole. Each of these attributes represent objects in SSAS where security can be defined. I rolled these attribute hierarchies into two dimensions called UserAccess and AllowedCubeAccess. The UserAccess dimension will allow users of the security metadata cube to identify which cubeDatabase, Cube, Role, and roleMember they’re interested in for security reporting. The AllowedCubeAccess dimension is a simple yes/no qualification that states if a specific cube role has access to a particular cube. For example, a role called Testers might be created for a cube, but in production the Testers role might be disabled. The AllowedCubeAccess dimension has been hidden and its default member set to allowed to keep things as simple as possible for users of the security metadata cube. The AllowedCubeAccess dimension is a somewhat unnecessary dimension, but I included it because someone might want to know all the roles in a cube regardless of whether the roles are enabled.
Extending This Approach
This approach to reporting on SSAS security can be significantly expanded and modified based on your needs. The following are three additions you might consider for your security cube:
- Dimensional security is a very powerful security tool in SSAS. Dimensional security allows a role to be granted access to a subset of a cube based on a dimension using MDX. For example, you might want payroll in a specific location to have access to all quality indicators for a specific region and all of its subordinate departments. By utilizing dimensional security, reports can be created to dynamically include or exclude subordinate departments in a region. If you’re using dimensional security, include another nested loop in the extraction, transformation, and loading (ETL) Script task to pick up the dimensional security metadata. For the security cube, add another attribute for the new dimension field.
- Adding Active Directory (AD) groups to SSAS roles is often a better security approach than adding users directly to SSAS roles. The code in Listing 2 uses the xp_enumgroups extended stored procedure to extract local group membership from the local server security. This stored procedure can also be leveraged to extract AD domain user group memberships with some additional modifications.
- Consider adding a date dimension to the security cube to report when a user was granted access. To add this dimension, you’ll need to modify the ETL to incrementally load the fact table each time you pull AMO security information.
Audit Cube Security
Using a cube to report on the security metadata for SSAS has been very helpful for my organization. The security metadata cube is intuitive to navigate, and minimal time can be spent educating data owners about how to answer nearly all of their “who has access to that” questions, giving you more time to develop and implement data mart cubes.
Author’s Note: I’d like to thank Clarke Morris for his VBScript assistance and for creating the code in Listing 2 to extract Active Directory group information.
Web Listing 1: Code to Extract Security Metadata from AMO
'Add a reference to Microsoft.AnalysisServices 'May need to copy the “Microsoft.AnalysisServices.DLL” into your SDK\Assemblies folder Imports System Imports System.Data Imports AMO = Microsoft.AnalysisServices Public Class ScriptMain Public Sub Main() Dim amoServer As New AMO.Server Dim amoDatabase As New AMO.Database Dim amoRole As New AMO.Role Dim amoRoleMember As New AMO.RoleMember Dim amoCube As New AMO.Cube Dim amoCubePermission As New AMO.CubePermission Dim amoCubeDimensionPermissionCollection As AMO.CubeDimensionPermissionCollection Dim amoCubeDimensionPermission As New AMO.CubeDimensionPermission Dim DimensionReadPermission As String Dim tblDataTable As Data.DataTable 'create datatable tblDataTable = New DataTable("AmoSecurityTable") 'create attributes for mapping security metadata tblDataTable.Columns.Add(New DataColumn("Role", GetType(String))) tblDataTable.Columns.Add(New DataColumn("CubeDatabase", GetType(String))) tblDataTable.Columns.Add(New DataColumn("Cube", GetType(String))) tblDataTable.Columns.Add(New DataColumn("CubeAccessCode", GetType(String))) tblDataTable.Columns.Add(New DataColumn("Rolemember", GetType(String))) ''''''''''''''''' 'Establish a connection to the Analysis Server. '**** FILL IN > and >. The 'Adventureworks DW' catalog works well. The purpose is to simply establish a connection to the server. amoServer.Connect("Data Source=>;Initial Catalog=>;Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;") For Each amoDatabase In amoServer.Databases 'traverse Analysis Services databases For Each amoRole In amoDatabase.Roles 'traverse Analysis Services database roles For Each amoCube In amoDatabase.Cubes 'traverse Analysis Services cubes For Each amoRoleMember In amoRole.Members 'traverse Analysis Services RoleMembers Try 'Create DataRow based on tblDataTable Object Dim dr As DataRow = tblDataTable.NewRow() 'Assign AMO security values to DataRow attributes dr("Role") = amoRole.Name dr("CubeDatabase") = amoDatabase.Name dr("Cube") = amoCube.Name dr("CubeAccessCode") = amoCube.CubePermissions.GetByRole(amoRole.ID).Read.ToString() dr("RoleMember") = amoRoleMember.Name 'Add DataRow row to tblDataTable tblDataTable.Rows.Add(dr) Catch ex As Exception End Try Next 'traverse Analysis Services RoleMembers Next 'traverse Analysis Services cubes Next 'traverse Analysis Services database roles Next 'traverse Analysis Services databases ' Assign memory table to Integration services variable "user::varDataTable" Dts.Variables("varDataTable").Value = tblDataTable 'Disconnect from server amoServer.Disconnect() 'report task success Dts.TaskResult = Dts.Results.Success End Sub End Class
Listing 1: Code to Place user::varDataTable Records into a Table
DECLARE @role as varchar(50) DECLARE @cubeDatabase as varchar(50) DECLARE @cube as varchar(50) DECLARE @cubeAccessCode as varchar(50) DECLARE @roleMember as varchar(50) SET @role = ? SET @cubeDatabase = ? SET @cube = ? SET @cubeAccessCode = ? SET @roleMember = ? insert AMOSecurityUDM.bAMOSecurityDefinition (role, CubeDatabase, cube, CubeAccessCode, roleMember) values (@role, @cubeDatabase, @cube, @cubeAccessCode, @roleMember)
Web Listing 2: Code to Create the Security Metadata Table
--- create list of local groups and insert into table truncate table AMOSecurityUDM.localgroupmembers create table #localmembers(members varchar(300)) create table #localgroups(\[group\] varchar(300), comment varchar(300)) declare @enumstring varchar(300) create table #localmembers2c(\[Localgroup\] \[varchar\](200) NULL, \[Members\] \[varchar\](200) NULL) set @enumstring = 'execute xp_enumgroups '>'' select @enumstring insert into #localgroups(\[group\],comment) exec(@enumstring) --------------------------------------------------------------------------------------------------- --run each group name through net localgroup command –insert unfiltered data into table declare @group varchar(100) declare localgroup cursor for select \[group\] from #localgroups open localgroup fetch next from localgroup into @group while @@fetch_status = '0' BEGIN declare @netcommand varchar(300) declare @xpcommand varchar(300) set @netcommand = 'net localgroup ' + @group + ' select @netcommand set @xpcommand = 'xp_cmdshell ''+ @netcommand + '' select @xpcommand insert into #localmembers(members) exec(@xpcommand) declare @membersc varchar(200) declare cursor_column cursor for select members from #localmembers
Listing 2: xp_enumgroups Stored Procedure Used to Extract Local Group Membership
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE \[AMOSecurityUDM\].\[bAMOSecurityDefinition\]( \[role\] \[varchar\](50) NULL, \[CubeDatabase\] \[varchar\](50) NULL, \[Cube\] \[varchar\](50) NULL, \[CubeAccessCode\] \[varchar\](50) NULL, \[RoleMember\] \[varchar\](50) NULL )