Весной 2012г. в одном из OLAP-проектов (SQL Server Analysis Services 2008R2) меня попросили реализовать разграничение доступа пользователей к элементам атрибутов измерений куба и к определённым мерам. Филиалов у компании-Заказчика свыше сотни, пользователей ещё больше, логика разделения прав многовариантная, и, возможно, может изменяться.
Прежде всего, для понимания составил наглядную карту прав доступа (матрица в Excel, здесь не приводится). Для элементов атрибутов измерений правила разграничения доступа следующие:
- - простой сотрудник должен видеть только те бренды, подразделения, ЦФО, в рамках которых он осуществлял свою деятельность (были факты его продаж);
- - менеджеры среднего звена должны видеть те бренды, подразделения, ЦФО, которые им явно разрешены, или видеть все элементы соответствующего измерения или полный запрет к элементам ключевого атрибута измерения;
- - ТОП-менеджмент и администраторы должны видеть всё без ограничений;
Для определенных показателей (мер) правила разграничения доступа следующие:
- - ТОП-менеджмент и администраторы должны видеть всё без ограничений;
- - меры должны быть видны сотруднику только в том случае, если на то есть явное разрешение и сотрудник не считается уволенным;
- - во всех остальных случаях доступ к мерам должен быть запрещён.
Чтобы в OLAP-проекте не создавать множество ролей, не запутаться затем в их администрировании, как и в предыдущем случае для всех пользователей создадим единственную роль, а собственно логику разграничения прав вынесем за пределы куба, на сторону реляционной базы данных. Автоматическое включение пользователей в роль пропишем в виде отдельного задания (пример приведён в конце статьи) Script Task проекта ETL (SSIS).
В реляционной базе данных создадим следующие таблицы: User_List - плоский список пользователей ID_User - ID пользователя, уникальный синтетический ключ; User_Name - имя пользователя; User_SysName - системное имя пользователя (доменная учетная запись); User_Activity - статус пользователя (активный, блокирован, уволен); User_Role - роль пользователя (обычный, администратор, менеджер среднего звена, ...); .... - возможны и другие поля; Reference_Name - перечень измерений, к элементам которого требуется разделять права доступа; сюда же добавим запись "Секъюритизируемые меры" (ID_RefName=7) Reference_User_Right - собственно указание прав доступа пользователей к элементам измерений если ID_Ref = NULL, то пользователю в указанном измерении разрешен доступ ко всем элементам (должна быть единственная запись с такими ID_RefName, ID_User, ID_Ref= NULL); если ID_Ref = число, то пользователю разрешен доступ к этому элементу измерения; если запись связка ID_RefName, ID_User отсутствует, то пользователю запрещен доступ к этому элементу измерения; Measure_List - список мер; эта таблица нам пригодилась ранее; Allow_Measure - фейковая таблица, поле Allow с возможными значениями 0 или 1. Для удобства сопровождения первых четырех таблиц можно разработать Web или Desktop приложение.
Далее приведены две хранимые процедуры (T-SQL) для извлечения прав доступа пользователя к элементам указанного измерения и к указанной мере:
--============================================================================================================================= CREATE PROCEDURE dbo.p_Reference_User_Right_get @SysNameUser VARCHAR(50) ,@ID_RefName INT AS -- процедура извлечения прав доступа пользователя к записям справочника (к элементам указанного атрибута измерения) SET NOCOUNT ON DECLARE @ID_user INT, @user_role VARCHAR(200), @user_activity VARCHAR(200) SET @ID_user=(SELECT ID_User FROM dbo.User_List WHERE User_SysName=@SysNameUser) IF @ID_RefName=1 -- Справочник ЦФО BEGIN -- если для указанного пользователя есть явные ссылки (разрешенные доступы) на элементы справочника (ID_Ref), -- то возвращаем список этих явно разрешенных элементов; SELECT r.ID_Ref ,r.ID_User ,r.ID_RefName FROM dbo.Reference_User_Right r WHERE r.ID_User=@ID_user AND r.ID_RefName=@ID_RefName AND NOT r.ID_Ref IS NULL END ELSE IF @ID_RefName=5 -- Справочник компаний BEGIN ..... END ELSE IF @ID_RefName=167 -- Справочник брендов BEGIN ..... END --============================================================================================================================= CREATE PROCEDURE dbo.p_UserAccessToMeasure_Check @SysNameUser VARCHAR(50) ,@ID_Ref INT ,@p_AccessAllow BIT OUTPUT AS -- процедура извлечения права доступа пользователя к указанной мере SET NOCOUNT ON DECLARE @ID_user INT, @user_role VARCHAR(200), @user_activity VARCHAR(200) SET @ID_user=(SELECT ID_User FROM dbo.User_List WHERE User_SysName=@SysNameUser) SELECT @user_role=User_Role, @user_activity=User_Activity FROM dbo.User_List WHERE ID_User=@ID_user IF @user_activity<>'активный' SET @p_AccessAllow=0 ELSE IF @user_role='администратор' SET @p_AccessAllow=1 ELSE IF EXISTS(SELECT r.ID_Ref ,r.ID_User ,r.ID_RefName FROM dbo.Reference_User_Right r WHERE r.ID_RefName=7 AND r.ID_User=@ID_user AND ISNULL(r.ID_Ref, @ID_Ref) = @ID_Ref ) SET @p_AccessAllow=1 ELSE SET @p_AccessAllow=0 --=============================================================================================================================
Во внешней CLR-сборке (SQLQuery), подключаемой к Analysis Services, создадим нижеследующие три функции: - FilterMembersByUserName - возвращает набор мемберов указанного измерения, к которым пользователю разрешён доступ; - CountMembersByUserName - возвращает количество мемберов указанного измерения, к которым пользователю разрешён доступ; эта функция понадобилась для того, что, если предыдущая функция возвращает более 500 элементов, то возникает ошибка, поэтому далее при количестве разрешенных мемберов больше 500, будут показываться все элементы измерения;
- CheckMeasurePermission - возвращает разрешен ли пользователю доступ (True/False) к указанной мере.
//============================================================================================================================= using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using Microsoft.AnalysisServices.AdomdServer; // В проекте нужно добавить ссылку на сборку Microsoft.AnalysisServices.AdomdServer // Это файл C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\msmgdsrv.dll namespace MY_ASSP { public class SQLQuery { //строка соединения c реляционным источником: private string _connectString = "Data Source=MSSQL2008R2;Password=XXXXX;User ID=olap_process;Initial Catalog=my_1C"; public Set FilterMembersByUserName(string p_UserAccount, int p_ID_RefName) { Expression expr = new Expression(); SetBuilder sb = new SetBuilder(); using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = _connectString; SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "dbo.p_Reference_User_Right_get"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@SysNameUser", p_UserAccount); cmd.Parameters.AddWithValue("@ID_RefName", p_ID_RefName); cn.Open(); SqlDataReader r = cmd.ExecuteReader(); if (r.HasRows) { while (r.Read()) { if (p_ID_RefName == 0) { expr.ExpressionText = string.Format("[Менеджеры].[Сотрудник].&[{0}]", r["ID_Ref"].ToString()); } else if (p_ID_RefName == 1) { expr.ExpressionText = string.Format("[ЦФО].[ЦФО].&[{0}]", r["ID_Ref"].ToString()); } else if (p_ID_RefName == 5) { expr.ExpressionText = string.Format("[Компании].[Компания].&[{0}]", r["ID_Ref"].ToString()); } else if (p_ID_RefName == 167) { expr.ExpressionText = string.Format("[Бренды].[Бренд].&[{0}]", r["ID_Ref"].ToString()); } Member m = expr.CalculateMdxObject(null).ToMember(); TupleBuilder tb = new TupleBuilder(); tb.Add(m); sb.Add(tb.ToTuple()); } } r.Close(); if (cn.State == ConnectionState.Open) { cn.Close(); cn.Dispose(); } return sb.ToSet(); } return new SetBuilder().ToSet(); } //============================================================================================================================= public int CountMembersByUserName(string p_UserAccount, int p_ID_RefName) { int functionReturnValue = 0; string connString = _connectString; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "dbo.p_DataRightUserForReference_count"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter param1 = new SqlParameter(); param1.ParameterName = "@SysNameUser"; param1.SqlDbType = SqlDbType.VarChar; param1.Direction = ParameterDirection.Input; param1.Value = p_UserAccount; cmd.Parameters.Add(param1); SqlParameter param2 = new SqlParameter(); param2.ParameterName = "@ID_RefName"; param2.SqlDbType = SqlDbType.Int; param2.Direction = ParameterDirection.Input; param2.Value = p_ID_RefName; cmd.Parameters.Add(param2); SqlParameter param3 = new SqlParameter(); param3.ParameterName = "@p_Count"; param3.SqlDbType = SqlDbType.Int; param3.Direction = ParameterDirection.Output; cmd.Parameters.Add(param3); conn.Open(); cmd.ExecuteNonQuery(); functionReturnValue = (int)param3.Value; if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } return functionReturnValue; } //============================================================================================================================= public bool CheckMeasurePermission(string p_UserAccount, int p_ID_Ref) { bool functionReturnValue = false ; string connString = _connectString; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "dbo.p_UserAccessToMeasure_Check"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter param1 = new SqlParameter(); param1.ParameterName = "@SysNameUser"; param1.SqlDbType = SqlDbType.VarChar; param1.Direction = ParameterDirection.Input; param1.Value = p_UserAccount; cmd.Parameters.Add(param1); SqlParameter param2 = new SqlParameter(); param2.ParameterName = "@ID_Ref"; param2.SqlDbType = SqlDbType.Int; param2.Direction = ParameterDirection.Input; param2.Value = p_ID_Ref; cmd.Parameters.Add(param2); SqlParameter param3 = new SqlParameter(); param3.ParameterName = "@p_AccessAllow"; param3.SqlDbType = SqlDbType.Bit; param3.Direction = ParameterDirection.Output; cmd.Parameters.Add(param3); conn.Open(); cmd.ExecuteNonQuery(); functionReturnValue = (bool)param3.Value; if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } return functionReturnValue; } } } //============================================================================================================================= В проекте SSAS открываем единственную роль, переходим на вкладку "Dimension Data", далее - на вкладку "Advanced" и для интересуещего нас измерения куба прописываем выражение для разрешенных мемберов:
Для контроля доступа к мере в проекте SSAS создадим скрытое непривязанное измерение с единственным неагрегируемым ключевым атрибутом allow_measure (0 или 1) и таким же свойством. Затем также открываем единственную роль, переходим на вкладку "Dimension Data", далее - на вкладку "Advanced" и для этого измерения куба прописываем следующее выражение:
В кубе создаем скрытое измерение "_Пользователи OLAP". В MDX-скрипте для физической меры, доступ к которой хотим контролировать, прописываем нижеприведенную вычисляемую формулу (саму физическую меру скрываем):
--============================================================================================================================= -- в скрытом измерении пользователей OLAP текущим выбранным пользователем устанавливается сессионый пользователь ALTER CUBE CURRENTCUBE UPDATE DIMENSION [_Пользователи OLAP], DEFAULT_MEMBER =StrToMember( '[_Пользователи OLAP].[Пользователь OLAP].[' + UserName() + ']' ) ; CREATE MEMBER CURRENTCUBE.[MEASURES].[Себестоимость] AS IIF ([_Пользователи OLAP].[Пользователь OLAP].Properties("Состояние пользователя OLAP", typed)="активный" AND [_Пользователи OLAP].[Пользователь OLAP].Properties("Роль пользователя OLAP", typed)="администратор", [Measures].[Себестоимость_] / [Measures].[Курс_валюты], IIF([_allow_Cost].[allow_measure].Properties("allow", typed)=1, [Measures].[Себестоимость_]/[Measures].[Курс_валюты], NULL ) ) ,FORMAT_STRING = [MEASURES].[selected_format_string] ,ASSOCIATED_MEASURE_GROUP = 'Показатели' ,DISPLAY_FOLDER = 'Факт продаж' ,VISIBLE = 1; --============================================================================================================================= Весь трюк в том, что при подсоединении к кубу определяются доступные элементы измерения, и затем они кэшируются. Признаться, вначале был соблазн прямо в MDX-скрипте использовать функцию SQLQuery.CheckMeasurePermission(...) и MDX-формула работала (!), но наблюдались жуткие тормоза при работе по верхним уровням иерархий.
'============================================================================================================================== ' SSIS скрипт автоматического включения пользователей в роль SSAS для доступа к кубу ' Список пользователей хранится в созданной нами таблице RDBMS Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.AnalysisServices Imports System.Data.SqlClient ........ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() Dim queryString As String, RDBMS_connString As String, strLogin As String, myOLAPServer_ConnectionString As String 'строку соединения с RDBMS читаем из входной переменной SSIS-пакета RDBMS_connString = Dts.Variables("RDBMS_ConnectionString").Value.ToString queryString = "SELECT * FROM dbo.User_List ORDER BY User_SysName" Dim myOLAPServer As New Server 'строку соединения с SSAS читаем из входной переменной SSIS-пакета myOLAPServer_ConnectionString = Dts.Variables("OLAP_ConnectionString").Value.ToString myOLAPServer.Connect(myOLAPServer_ConnectionString) 'Установка соединения с SSAS Dim MyDatabase As Database = myOLAPServer.Databases.GetByName("MY_OLAP_PROJECT") 'Получаем ссылку на многомерную БД Dim MyRole As Role If Not MyDatabase.Roles.ContainsName("Staff") Then 'Наша единственная роль для всех пользователей Dts.TaskResult = ScriptResults.Failure Exit Sub Else MyRole = MyDatabase.Roles.GetByName("Staff") MyRole.Members.Clear() MyRole.Update() End If Dim conn As New SqlConnection conn.ConnectionString = RDBMS_connString Dim cmd As New SqlCommand(queryString, conn) 'Try conn.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() While rdr.Read() If rdr("user_activity") = "активный" And rdr("user_role") <> "администратор" Then strLogin = rdr.Item("User_SysName").ToString If userValid(strLogin) Then Dim MyRoleMember As New RoleMember(strLogin) MyRole.Members.Add(MyRoleMember) MyRole.Update() End If End If End While rdr.Close() 'Catch ex As Exception 'End Try If conn.State = ConnectionState.Open Then conn.Close() conn.Dispose() End If myOLAPServer.Disconnect() Dts.TaskResult = ScriptResults.Success End Sub '============================================================================================================================== Function userValid(ByVal MyUser As String) As Boolean Dim retVal As Boolean = False On Error Resume Next Dim objUser As Object objUser = GetObject("WinNT://MyDom/" & MyUser) If Err.Number = 0 Then retVal = True ElseIf Err.Number = -2147022676 Then retVal = False 'MsgBox("В домене НЕ существует логин " & MyUser) Else retVal = False 'MsgBox("НЕ определен статус логина " & MyUser) End If Return retVal End Function End Class '============================================================================================================================== Обновляем проект на сервере, и теперь мы можем обеспечивать разграничение доступа как к элементам измерений, так и к мерам. Вуаля!