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