Разграничение доступа к элементам измерений и мерам

OLAP, SSAS  Разграничение Категория:  OLAP, SSAS
Опубликовал:         06.11.2012               print

Весной 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
'==============================================================================================================================

Обновляем проект на сервере, и теперь мы можем обеспечивать разграничение доступа как к элементам измерений, так и к мерам.
Вуаля!


Энергия идеи   dvbi.ru                    Последнее изменение: 2017-10-15 16:18:42Z         Возрастная аудитория: 14-70         Комментариев:  0
Теги:  DLL для SSAS Примеры
Связанные статьи:

Пожалуйста, проголосуйте и ниже поставьте лайк:   rating
2 2 0


  Comments


Следующая статья:    SSAS - анализ остатков
Предыдущая статья:  SSAS - контекстные SQL запросы к реляционным источникам