SSAS - права доступа и MDS

Ограничению доступа подлежат не только персональные данные клиентов, данные о доходах сотрудников и т.п. Под категорию коммерческой тайны могут подпадать, например, списки контрагентов, детализированные данные и суммарные объемы продаж - каждый менеджер должен видеть только свои продажи / продажи своих подразделений. Microsoft SQL Server Analysis Services MultiDimensional позволяет разделять доступ как к атрибутам измерений, так и к данным атрибутов измерений и мерам (показателям). Следует напомнить, в SSAS используется только доменная Windows NT аутентификация.

В проекте 2014г. у меня изначально были следующие условия: зарегистрированных пользователей - не менее 800 чел.; волатильность пользователей - высокая; ролевых групп - не менее 4-х; бизнес-правила разграничения доступа - четко не сформулированы, лишь было ясно, что потребуется разделять доступ к элементам измерений; правила могут меняться и не исключено требование задания индивидуальных условий для отдельных пользователей.

Варианты решения:

Утилизировать Windows доменные группы - вряд ли администраторы сети будут рады такой дополнительной административной нагрузке;
Создать ролевые группы Analysis Services - неплохая идея, но вряд ли подойдет для индивидуальных условий для отдельных пользователей, к тому же возможны интенсивные перемещения сотрудников по штатной сетке;
Задействовать динамическую защиту измерений с использованием внешней CLR-сборки - реляционная база данных должна быть всегда доступна для Analysis Services, в боевом режиме не так просто идентифицировать ошибку отклонения доступа, при большом количестве пользователей вероятность возникновения исключительной ситуации возрастает;
Остается вариант использования индивидуальных ролей (1 пользователь = 1 роль SSAS) - потребуется автоматизировать процесс создания ролей, разработать управляющую программу и пользовательский интерфейс по администрированию ролей. Честно говоря, не хотелось ввязываться в разработку сервисного приложения, а воспользоваться готовым инструментарием.... Microsoft Master Data Services 2012 (MDS).

Для начала в модели данных MDS создадим нижеследующие сущности (таблицы) для данного решения:

Схема данных MDS для разграничения прав доступа к OLAP и SQL-запросам

Подробный состав атрибутов (полей) сущностей и пример наполнения приведен в Excel-файле в конце данной статьи. Поверх базовых представлений подписки mdm.*, созданных в Master Data Services, определим свои представления с помощью SQL Server Management Studio:

-- Задание на обработку (установку) прав доступа сотрудникам к OLAP-кубу(ам)
CREATE VIEW dbo.v_OLAP_users
AS
SELECT p.domain_login
      ,p.full_name
      ,p.corporative_email
      ,p.post
      ,p.layoff_date
      ,a.id                                   AS id_OLAP_resource_access 
      ,a.OLAP_permission_exec_status
      ,a.date_start
      ,a.date_end
      ,a.OLAP_resource_Code
      ,o.OLAP_cube
      ,o.OLAP_database
      ,o.OLAP_connect_string
  FROM mdm.v_OLAP_resource_access  a
  INNER JOIN mdm.v_OLAP_resource   o  ON a.OLAP_resource_Code=o.code
  INNER JOIN mdm.v_personnel       p  ON a.employee_Code=p.code
    WHERE     a.ValidationStatus='Validation Succeeded'
          AND o.ValidationStatus='Validation Succeeded'
          AND p.ValidationStatus='Validation Succeeded'



-- Права доступа сотрудников к атрибутам OLAP-кубов
CREATE VIEW dbo.v_OLAP_attribute_users
AS
SELECT p.domain_login
      ,p.full_name
      ,p.corporative_email
      ,p.post
      ,p.layoff_date
      ,a.date_start
      ,a.date_end
      ,a.OLAP_elements
      ,a.VisualTotals
      ,a.OLAP_attribute_Code
      ,t.OLAP_attribute
      ,t.OLAP_attribute_type
      ,t.OLAP_dimension
      ,r.OLAP_cube
      ,r.OLAP_database
  FROM mdm.v_OLAP_attribute_access  a
  INNER JOIN mdm.v_OLAP_attribute   t  ON a.OLAP_attribute_Code=t.code
  INNER JOIN mdm.v_OLAP_resource    r  ON t.OLAP_resource_Code=r.code
  INNER JOIN mdm.v_personnel        p  ON a.employee_Code=p.code
    WHERE     a.ValidationStatus='Validation Succeeded'
          AND t.ValidationStatus='Validation Succeeded' 
          AND r.ValidationStatus='Validation Succeeded'
          AND p.ValidationStatus='Validation Succeeded'



-- Права доступа сотрудников к элементам атрибутов OLAP-кубов
CREATE VIEW dbo.v_OLAP_elements_users
AS
SELECT p.domain_login
      ,p.full_name
      ,p.corporative_email
      ,p.post
      ,p.layoff_date
      ,a.date_start
      ,a.date_end
      ,a.OLAP_element
      ,a.OLAP_attribute_Code
      ,t.OLAP_attribute
      ,t.OLAP_attribute_type
      ,t.OLAP_dimension
      ,r.OLAP_cube
      ,r.OLAP_database
  FROM mdm.v_OLAP_element_access    a
  INNER JOIN mdm.v_OLAP_attribute   t  ON a.OLAP_attribute_Code=t.code
  INNER JOIN mdm.v_OLAP_resource    r  ON t.OLAP_resource_Code=r.code
  INNER JOIN mdm.v_personnel        p  ON a.employee_Code=p.code
    WHERE     a.ValidationStatus='Validation Succeeded'
          AND t.ValidationStatus='Validation Succeeded'
          AND r.ValidationStatus='Validation Succeeded' 
          AND p.ValidationStatus='Validation Succeeded'

В статье не освещается разделение доступа к контекстным SQL запросам к реляционным источникам. Достаточно сказать, что в серверных хранимых процедурах (T-SQL или PL/SQL) достаточно проверять данные MDS-сущностей personnel, queries_access.

В проекте SQL Server Integration Services после задач обновления OLAP-кубов включим задачу Script Task, в которой будет запрограммирован процесс автоматического создания персональной роли для каждого пользователя OLAP из списка dbo.v_OLAP_users:

Script Task для установки прав доступа к OLAP

Входным параметрам (Read Only Variables) задачи Script Task установим следующие значения:

-- запрос к MDS, возвращающий список пользователей, 
-- для которых необходимо обработать права доступа к OLAP

User::OLAP_users_for_permissions
SELECT * FROM mds.dbo.v_OLAP_users WHERE OLAP_permission_exec_status=1 ORDER BY OLAP_database, domain_login


-- тело запроса к MDS, возвращающего ограничения доступа пользователей к атрибутам кубов
User::OLAP_attribute_users
SELECT * FROM mds.dbo.v_OLAP_attribute_users WHERE GETDATE() BETWEEN date_start AND date_end


-- тело запроса к MDS, возвращающего права доступа сотрудников к элементам атрибутов
User::OLAP_elements_users
SELECT * FROM mds.dbo.v_OLAP_elements_users WHERE GETDATE() BETWEEN date_start AND date_end


-- update-запрос к MDS, сбрасывающий флаг задания на автоматическую обработку прав доступа сотрудника
-- (в Вашем случае названия таблицы и столбцов могут отличаться)
User::OLAP_permissions_result_upd
UPDATE mdm.tbl_2_45_EN SET uda_45_2404= (result_code) WHERE uda_45_2404 =1 AND ID= (id_OLAP_resource_access)


-- строка соединения к реляционной БД MDS
User::RDBMS_ConnectionString
Provider=SQLOLEDB.1;Data Source=MSSQL2012;Initial Catalog=MDS;User ID=MDS_user;Password=XXXXX;

Ну а теперь собственно программный скрипт, в котором задействована серверная объектная модель Microsoft SQL Server Analysis Services MultiDimensional - AMO:

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data.OleDb
Imports Microsoft.AnalysisServices ' AMO
' Microsoft SQL Server 2012 Feature Pack
' http://www.microsoft.com/ru-ru/download/details.aspx?id=29065
' http://msdn.microsoft.com/en-us/library/ms345081.aspx
' http://msdn.microsoft.com/en-us/library/ms174786.aspx
#End Region



  _
Partial Public Class ScriptMain
  Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase


#Region "ScriptResults declaration"
  Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
  End Enum
#End Region

  Private _RDBMSconnection As New OleDbConnection
  Private _OLAPDatabase As Microsoft.AnalysisServices.Database
  Private _OLAP_DataBaseName As String = ""
  Private _OLAP_CubeName As String = ""
  Private _id_OLAP_resource_access As Long


  Public Sub Main()
    Dts.TaskResult = ScriptResults.Failure

    Dim s_Login As String, s_Role As String, OLAP_connect_error, OLAP_db_get_error As Boolean

    _RDBMSconnection.ConnectionString = Dts.Variables("RDBMS_ConnectionString").Value.ToString
    Dim cmd As New System.Data.OleDb.OleDbCommand(Dts.Variables("OLAP_users_for_permissions").Value.ToString, _RDBMSconnection)
    Dim myOLAPServer As New Microsoft.AnalysisServices.Server

    Try
      _RDBMSconnection.Open()
    Catch ex As Exception
      Exit Sub
    End Try

    Dim rdr As System.Data.OleDb.OleDbDataReader = cmd.ExecuteReader()
    While rdr.Read() ' запрос должен возвращать список только тех доменных логинов, для которых необходима обработка прав доступа к OLAP
      _OLAP_CubeName = rdr.Item("OLAP_cube").ToString
      _id_OLAP_resource_access = rdr.Item("id_OLAP_resource_access")
      s_Login = rdr.Item("domain_login").ToString
      s_Role = s_Login.Replace("\", "_")

      If _OLAP_DataBaseName <> rdr.Item("OLAP_database").ToString Then
        Try
          myOLAPServer.Connect(rdr.Item("OLAP_connect_string").ToString)       ' осуществляется подключение к экземпляру SSAS
          OLAP_connect_error = False
        Catch ex As Exception
          OLAP_connect_error = True
          PermissionsResultFix(102)                                            ' ошибка подключения к экземпляру SSAS
        End Try

        If OLAP_connect_error = False Then
          Try
            _OLAPDatabase = myOLAPServer.Databases.GetByName(rdr.Item("OLAP_database").ToString)   ' ссылка на базу OLAP
            OLAP_db_get_error = False
          Catch ex As Exception
            OLAP_db_get_error = True
            PermissionsResultFix(103)                                          ' не найдена база данных OLAP
          End Try
        End If
      End If

      If OLAP_connect_error = False And OLAP_db_get_error = False Then
        If _OLAP_DataBaseName <> rdr.Item("OLAP_database").ToString Then
          _OLAP_DataBaseName = rdr.Item("OLAP_database").ToString
        End If
        DropRole(s_Role)                                                       ' удаление персональной роли OLAP, если она есть
        ''' UserRemoveFromRoles(s_Login)                                       ' удаление логина из всех ролей OLAP

        ' если сотрудник НЕ уволен и системная дата в диапазоне даты начала и окончания действия периода доступа к кубу,
        ' то сотруднику разрешается доступ к кубу
        If IIf(rdr.Item("date_start") Is DBNull.Value, Now.AddDays(-10), rdr.Item("date_start")) < Now.Date _
           And (Now.Date < IIf(rdr.Item("date_end") Is DBNull.Value, Now.AddDays(10), rdr.Item("date_end"))) _
           And (Now.Date < IIf(rdr.Item("layoff_date") Is DBNull.Value, Now.AddDays(10), rdr.Item("layoff_date"))) Then

          CreateRole(s_Role, s_Login) ' создание роли OLAP и включение в нее доменного логина (1 роль = 1 логин) 
                                      ' с предоставлением ей соответствующих прав доступа
        Else
          PermissionsResultFix(100)                                            ' сотруднику отказано в предоставлении прав доступа к кубу
        End If
      End If
    End While
    rdr.Close()

    If _RDBMSconnection.State = ConnectionState.Open Then
      _RDBMSconnection.Close()
      _RDBMSconnection.Dispose()
    End If

    Dts.TaskResult = ScriptResults.Success
  End Sub



  Private Sub CreateRole(p_RoleName As String, p_Login As String)
    Dim s_cubeName, s_cubeDim, s_Attr, s_allowed_elements, queryAttrPermissions As String, _
        queryElementsPermissions As String, ElementsList As String, loginExists, attrFind As Boolean
    Dim myRole As Role
    Dim dbperm As DatabasePermission
    Dim myCube As Microsoft.AnalysisServices.Cube
    Dim cubeDim As CubeDimension
    Dim cubeAttr As CubeAttribute
    Dim cubePerm As CubePermission
    Dim cubeDimPerm As CubeDimensionPermission
    Dim attrPerm As AttributePermission

    myRole = _OLAPDatabase.Roles.Add(p_RoleName)
    Try
      myRole.Members.Add(New RoleMember(p_Login))
      myRole.Description = "В данную роль включен доменный логин " & p_Login
      myRole.Update()
      loginExists = True
    Catch ex As Exception
      PermissionsResultFix(101)                                                  ' возможна ошибка, если логин не существует в домене
    End Try

    If loginExists = False Then
      Exit Sub
    End If

    dbperm = _OLAPDatabase.DatabasePermissions.Add(myRole.ID)
    dbperm.Read = ReadAccess.Allowed                                             ' данной роли разрешается доступ на чтение 
    dbperm.ReadDefinition = ReadDefinitionAccess.Allowed
    dbperm.Update()

    myCube = _OLAPDatabase.Cubes.FindByName(_OLAP_CubeName)
    cubePerm = myCube.CubePermissions.Add(myRole.ID, "Restriction_" & myRole.ID)
    cubePerm.Read = ReadAccess.Allowed
    cubePerm.ReadSourceData = ReadSourceDataAccess.Allowed                       ' включение магической опции DrillThrough
    cubePerm.Update()

    Dim cmd2 As New OleDbCommand()
    cmd2.Connection = _RDBMSconnection
    Dim rdr2 As OleDbDataReader
    queryAttrPermissions = Dts.Variables("OLAP_attribute_users").Value.ToString _
                           & " AND OLAP_database ='" & _OLAP_DataBaseName & "' AND domain_login ='" & p_Login & "'"
    Dim cmd As New OleDbCommand(queryAttrPermissions, _RDBMSconnection)
    Dim rdr As OleDbDataReader = cmd.ExecuteReader()
    While rdr.Read()                                                             ' для логина - цикл по тем атрибутам куба, 
                                                                                 ' к которым следует контролировать доступ
      s_cubeName = rdr.Item("OLAP_cube").ToString
      s_cubeDim = rdr.Item("OLAP_dimension").ToString
      s_Attr = rdr.Item("OLAP_attribute").ToString
      s_allowed_elements = rdr.Item("OLAP_elements").ToString
      cubeDim = myCube.Dimensions.FindByName(s_cubeDim)

      If cubeDim Is Nothing Then
        PermissionsResultFix(104)                                                ' измерения с указанным именем в кубе не существует
      Else
        ElementsList = ""
        queryElementsPermissions = Dts.Variables("OLAP_elements_users").Value.ToString _
           & " AND OLAP_database ='" & _OLAP_DataBaseName & "' AND OLAP_cube ='" _
           & s_cubeName & "' AND OLAP_dimension ='" & s_cubeDim & "' AND OLAP_attribute ='" & s_Attr _
           & "' AND domain_login ='" & p_Login & "'"
        cmd2.CommandText = queryElementsPermissions
        rdr2 = cmd2.ExecuteReader()
        While rdr2.Read()                                                        ' уточнение перечня (через запятую) разрешенных 
                                                                                 ' элементов атрибута, если таковые есть
          ElementsList = ElementsList & "," & rdr2.Item("OLAP_element").ToString
        End While
        rdr2.Close()
        If ElementsList <> "" Then
          s_allowed_elements = "{" & ElementsList.Substring(1) & "}"
        End If

        attrFind = False
        For Each cubeAttr In cubeDim.Attributes
          If cubeAttr.Attribute.Name = s_Attr Then
            If cubePerm.DimensionPermissions.Contains(cubeDim.ID) Then
              attrPerm = cubePerm.DimensionPermissions.Find(cubeDim.ID).AttributePermissions.Add(cubeAttr.AttributeID)
            Else
              cubeDimPerm = cubePerm.DimensionPermissions.Add(cubeDim.ID)        ' добавление по указанию CubeDimensionID
              attrPerm = cubeDimPerm.AttributePermissions.Add(cubeAttr.AttributeID)  ' добавление по указанию AttributeID
            End If
            attrPerm.AllowedSet = s_allowed_elements
            attrPerm.VisualTotals = rdr.Item("VisualTotals").ToString
            attrFind = True
            Exit For
          End If
        Next
        cubePerm.Update()
        If attrFind = False Then
          PermissionsResultFix(105)                                              ' атрибут с указанным именем не найден в измерении
        End If
      End If
    End While
    rdr.Close()

    PermissionsResultFix(0)                                                      ' обработка задания по предоставлению сотруднику  
                                                                                 ' прав доступа к кубу выполнена успешно
  End Sub



  Private Sub DropRole(p_Role As String)
    ' Удаление роли OLAP
    If _OLAPDatabase.Roles.ContainsName(p_Role) Then
      Dim MyRole As Role
      MyRole = _OLAPDatabase.Roles.GetByName(p_Role)
      'Try
      MyRole.Drop(DropOptions.AlterOrDeleteDependents)
      'Catch ex As Exception
      '
      'End Try
    End If
  End Sub



  Private Sub UserRemoveFromRoles(p_Login As String)
    ' удаляется логин из всех ролей OLAP-базы, в котором он был обнаружен
    Dim oRole As Role

    For Each oRole In _OLAPDatabase.Roles
      For Each rm As RoleMember In oRole.Members
        If String.Compare(rm.Name, p_Login, True) = 0 Then                       ' Use string.compare to do a case-insensitive compare
          oRole.Members.Remove(rm)
          oRole.Update()
          Exit For
        End If
      Next
    Next
  End Sub


                              
  Function RoleContainsMember(p_Role As Role, p_Login As String) As Boolean
    ' проверка - принадлежит ли доменный логин указанной OLAP-роли 
    For Each rm As RoleMember In p_Role.Members
      If String.Compare(rm.Name, p_Login, True) = 0 Then                         ' Use string.compare to do a case-insensitive compare
        Return True                       
      End If
    Next
    Return False
  End Function



  Private Sub PermissionsResultFix(p_result As Integer)
    ' сохранение результата выполнения (сброс флага) задания на автоматическую обработку прав доступа сотрудника
    Dim cmd3 As New OleDbCommand()

    cmd3.Connection = _RDBMSconnection
    cmd3.CommandType = CommandType.Text
    cmd3.CommandText = Dts.Variables("OLAP_permissions_result_upd").Value.ToString.Replace("(result_code)", _
                       p_result.ToString).Replace("(id_OLAP_resource_access)", _id_OLAP_resource_access.ToString)
    cmd3.ExecuteNonQuery()
  End Sub

End Class

Итак, администраторы OLAP-отчетности (в этом качестве могут быть и Бизнес-пользователи), используя бесплатный Excel плагин для MDS, заполняют список сотрудников personnel, добавляют записи в MDS-сущность OLAP_resource_access о предоставлении сотрудникам доступа к ресурсу OLAP, обязательно взводя флаг-задание на автоматическую обработку OLAP_permission_exec_status=1. При необходимости в сущностях OLAP_attribute_access, OLAP_element_access для каждого пользователя могут быть прописаны индивидуальные критерии доступа (MDX-выражения) к элементам атрибутов измерений. Для новых пользователей можно просто продублировать, "протянуть" выражения, заполнив строки по аналогии с уже существующими. Если программная процедура автоматического создания пользовательской роли отработает успешно, то флаг OLAP_permission_exec_status будет сброшен в 0, в противном случае для соответствующих доменных учетных записей будет зафиксирован код ошибки.

Ну и наконец, сам процесс согласования доступа к отчетности можно формализовать, настроить и осуществлять в системе управления задачами Redmine. Предлагаемые решения подтверждены на практике.