Ограничению доступа подлежат не только персональные данные клиентов, данные о доходах сотрудников и т.п. Под категорию коммерческой тайны могут подпадать, например, списки контрагентов, детализированные данные и суммарные объемы продаж - каждый менеджер должен видеть только свои продажи / продажи своих подразделений. 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 создадим нижеследующие сущности (таблицы) для данного решения:
Подробный состав атрибутов (полей) сущностей и пример наполнения приведен в 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:
Входным параметрам (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. Предлагаемые решения подтверждены на практике.