В наше время каждый совершеннолетний человек пользуется услугами операторов мобильной связи, страховых компаний, банков, предприятий ЖКХ, налоговой инспекции, медицинских учреждений, сообщая и документально подтверждая свои персональные данные: Ф.И.О., данные паспорта, адрес регистрации, номера своих телефонов, сведения о доходах и т.д. Эта информация накапливается, систематизируется в базах данных с целью оперативного использования и анализа уполномоченными сотрудниками. Однако персональные сведения могут попасть в руки нежелательных третьих лиц и даже преступных сообществ. Возможно, на ваши мобильные телефоны приходили маркетинговые SMS, вы отвечали на звонки с предложениями что-либо приобрести от незнакомых компаний. Не исключено - это происходит потому, что базу данных, в которой содержатся сведения о вас, кто-то "слил". И, если еще не было серьезных мошеннических действий, то это не значит, что их не может быть. Не случайно Президентом РФ был принят Федеральный закон "О персональных данных" от 27.07.2006 №152-ФЗ. Однако исполнять этот закон не просто. Так, например, в любом коммерческом банке ТОП-50 можно насчитать десятки информационных систем, с которыми работают сотни сотрудников, существует ротация / текучесть кадров, в каждой из систем могут быть скрытые уязвимые места...
Однако перейдем в практическую плоскость вопроса. Рассмотрим задачу разграничения прав доступа к персональным данным клиентов в OLAP-кубе Microsoft Analysis Services. В многомерном кубе розничного портфеля Банка собраны как детализированные данные, так и агрегированные, аналитические производные показатели. Необходимо, чтобы одним сотрудникам была доступна вся имеющаяся в кубе информация, другим, в соответствии с их функциональными обязанностями - только выборочный набор атрибутов данных клиентов, а третьим - только общие показатели.
Прежде всего, было желание создать специальный перевод (Translation) для измерения "Клиенты", соответствующие атрибуты которого наполнить фейковыми значениями (например, "*****"), а затем в MDX-скрипте принудительно переопределять локали для отдельных пользователей. Другими словами, хотелось, чтобы доступ к элементам атрибутов не блокировался, а когда надо - показывался бы замещающий текст (Caption). Но в Microsoft Analysis Services нет такой функциональности.
Также следует отметить:
- * инструкция Drillthrough не поддерживает свойства (Properties) атрибутов измерений, а именно в них целесообразнее было бы хранить некоторые данные клиентов;
- * для свойств нельзя назначить правила разграничения доступа;
- * если к атрибуту измерения установлен запрет доступа, то при выполнении команды Drillthrough не будут показываться записи детализации даже в том случае, если этот атрибут не запрашивался (!)
После ряда экспериментов с динамической безопасностью измерений (Dynamic Security) я принял решение - вертикально разделить измерение "Кредитные заявки" с анкетными данными заемщиков на несколько измерений с одним и тем же ключевым атрибутом id_request: Измерение "Кредитные заявки" - доступные для всех пользователей атрибуты; Измерение "Контактные данные" - контактные данные клиентов: личные, рабочие номера телефонов; адрес регистрации и фактического проживания - т.е. те атрибуты, зная которые, можно выйти на клиента; Измерение "Персональные данные" - должность, признак VIP, уровень совокупного дохода (не путать с мерами "Основной доход", "Доп. доход", "Совокупный доход") и т.п.; Измерение "Рисковый блок" - баллы БКИ, скоринговые баллы, коды отказов и т.п. К атрибутам последних трех измерений определяются пользовательские права доступа, которые хранятся во вспомогательных реляционных таблицах:
-------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE users ( id_user NUMBER(8,0) NOT NULL, user_name VARCHAR2(100) NOT NULL, login VARCHAR2(50) NOT NULL, user_activity VARCHAR2(50) NOT NULL DEFAULT 'активный', user_role VARCHAR2(50) NOT NULL, email VARCHAR2(50) NOT NULL DEFAULT '--', approval_access_date DATE, who_approved_access VARCHAR2(250) NOT NULL DEFAULT '--' , CONSTRAINT pk_id_user PRIMARY KEY (id_user) USING INDEX TABLESPACE dwh ) TABLESPACE dwh / ALTER TABLE users ADD CONSTRAINT ck_user_role CHECK (user_role IN ('staff', 'individual', 'admin') ) / ALTER TABLE users ADD CONSTRAINT ck_user_activity CHECK (user_activity IN ('активный', 'блокирован') ) / CREATE UNIQUE INDEX ui_login ON users (login) TABLESPACE dwh / COMMENT ON TABLE users IS 'Список пользователей' / COMMENT ON COLUMN users.approval_access_date IS 'Дата предоставления доступа к OLAP' / COMMENT ON COLUMN users.email IS 'Адрес электронной почты' / COMMENT ON COLUMN users.id_user IS 'ID пользователя' / COMMENT ON COLUMN users.login IS 'Учетная запись пользователя' / COMMENT ON COLUMN users.user_activity IS 'Состояние пользователя' / COMMENT ON COLUMN users.user_name IS 'ФИО пользователя' / COMMENT ON COLUMN users.user_role IS 'Роль пользователя: admin - администраторы OLAP, staff - пользователи без ограничений по доступам, individual - есть хотя бы одно ограничение по доступу к ресурсу OLAP' / COMMENT ON COLUMN users.who_approved_access IS 'Главные лица, согласовавшие доступ к OLAP' -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE olap_resource ( id_olap_resource NUMBER(8,0) NOT NULL, olap_name VARCHAR2(100) NOT NULL, cube_name VARCHAR2(100) NOT NULL, dimension_name VARCHAR2(100) NOT NULL, attribute_name VARCHAR2(100) NOT NULL, allowed_members VARCHAR2(1000) NOT NULL DEFAULT '{ }' , CONSTRAINT pk_id_olap_resource PRIMARY KEY (id_olap_resource) USING INDEX TABLESPACE dwh ) TABLESPACE dwh / CREATE UNIQUE INDEX ui_olap_resource ON olap_resource (olap_name, cube_name, dimension_name, attribute_name) TABLESPACE dwh / COMMENT ON TABLE olap_resource IS 'OLAP ресурсы' / COMMENT ON COLUMN olap_resource.id_olap_resource IS 'ID OLAP ресурса' / COMMENT ON COLUMN olap_resource.olap_name IS 'Наименование базы данных OLAP' / COMMENT ON COLUMN olap_resource.cube_name IS 'Наименование OLAP куба' / COMMENT ON COLUMN olap_resource.dimension_name IS 'Наименование измерения куба' / COMMENT ON COLUMN olap_resource.attribute_name IS 'Наименование атрибута измерения' / COMMENT ON COLUMN olap_resource.allowed_members IS 'Какие элементы доступны в случае ограничения прав доступа к атрибуту' -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE user_access ( id_olap_resource NUMBER(8,0) NOT NULL, id_user NUMBER(8,0) NOT NULL, access_start DATE NOT NULL, access_expiry DATE NOT NULL , CONSTRAINT pk_user_access PRIMARY KEY (id_olap_resource, id_user) USING INDEX TABLESPACE dwh ) TABLESPACE dwh / ALTER TABLE user_access ADD CONSTRAINT fk_id_user FOREIGN KEY (id_user) REFERENCES users (id_user) / ALTER TABLE user_access ADD CONSTRAINT fk_id_olap_resource FOREIGN KEY (id_olap_resource) REFERENCES olap_resource (id_olap_resource) / ALTER TABLE user_access ADD CONSTRAINT ck_access_expiry CHECK (ACCESS_EXPIRY>=ACCESS_START) / COMMENT ON TABLE user_access IS 'Доступы пользователей к OLAP ресурсам' / COMMENT ON COLUMN user_access.access_start IS 'Дата начала права доступа пользователя к атрибуту OLAP' / COMMENT ON COLUMN user_access.access_expiry IS 'Дата окончания права доступа пользователя к атрибуту OLAP' / COMMENT ON COLUMN user_access.id_olap_resource IS 'ID OLAP ресурса' / COMMENT ON COLUMN user_access.id_user IS 'ID пользователя' -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE TRIGGER tr_users_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO user_access ( id_user ,id_olap_resource ,access_start ,access_expiry ) SELECT :NEW.id_user ,id_olap_resource ,TO_DATE('01.01.2000', 'dd.mm.yyyy') ,CASE WHEN :NEW.user_role='individual' THEN TO_DATE('01.01.2000', 'dd.mm.yyyy') ELSE TO_DATE('01.01.2100', 'dd.mm.yyyy') END FROM olap_resource ; END ; -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE TRIGGER tr_olap_resource_insert AFTER INSERT ON olap_resource FOR EACH ROW BEGIN INSERT INTO user_access ( id_olap_resource ,id_user ,access_start ,access_expiry ) SELECT :NEW.id_olap_resource ,u.id_user ,TO_DATE('01.01.2000', 'dd.mm.yyyy') ,CASE WHEN u.user_role='individual' THEN TO_DATE('01.01.2000', 'dd.mm.yyyy') ELSE TO_DATE('01.01.2100', 'dd.mm.yyyy') END FROM users u ; END ; -------------------------------------------------------------------------------------------------------------------------------------------------- Все пользователи OLAP поделены на 3 категории:
- admin - администраторы OLAP, включаются в одноименную роль сервера SSAS;
- staff - пользователи, НЕ имеющие каких-либо ограничений доступа к атрибутам, включаются в одноименную роль сервера SSAS;
- individual- для каждого такого пользователя на сервере SSAS будет создана своя собственная роль с индивидуальными правами доступа к атрибутам
Каждому пользователю можно запретить доступ к серверу SSAS, записав в поле users.user_activity значение 'блокирован'. Для пользователей с индивидуальной ролью можно запретить доступ к OLAP-ресурсу, отрегулировав диапазон дат user_access.access_start, user_access.access_expiry по отношению к текущей системной дате. Для удобства администрирования можно разработать GUI интерфейс и опубликовать его на корпоративном web-портале. В ETL-пакете SQL Server Integration Services после последнего шага процессинга куба в задаче "Скрипт" поместим нижеследующий код:
--------------------------------------------------------------------------------------------------------------------------------- #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 ' 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 Private _MyDatabase As Microsoft.AnalysisServices.Database Private _RDBMS_connString As String Private _OLAP_DataBaseName As String Public Sub Main() Dim s_Login As String, sRoleName As String, personalRoleName As String Dts.TaskResult = ScriptResults.Failure _OLAP_DataBaseName = Dts.Variables("OLAP_DataBaseName").Value.ToString _RDBMS_connString = Dts.Variables("RDBMS_ConnectionString").Value.ToString Dim myOLAPServer As New Microsoft.AnalysisServices.Server myOLAPServer.Connect(Dts.Variables("OLAP_Processing_ConnectionString").Value.ToString) ' Подключаемся к экземпляру SSAS _MyDatabase = myOLAPServer.Databases.GetByName(_OLAP_DataBaseName) ' Ссылка на базу OLAP Dim conn As New OleDbConnection conn.ConnectionString = _RDBMS_connString Dim cmd As New OleDbCommand(Dts.Variables("query_UsersList").Value.ToString, conn) 'Try conn.Open() Dim rdr As OleDbDataReader = cmd.ExecuteReader() While rdr.Read() s_Login = rdr.Item("login").ToString.Trim sRoleName = rdr("user_role").ToString.Trim personalRoleName = s_Login.Replace("\", "_") RemoveRole(personalRoleName) ' удаляем персональную роль, если она есть UserRemoveFromRoles(s_Login) ' удаляем пользователя из ролей If rdr("user_activity").ToString.Trim = "активный" Then ' If DomenUserValid(s_Login) Then ' проверка на принадлежность логина к NT-домену выполняется не быстро If sRoleName = "individual" Then CreatePersonalRole(personalRoleName, s_Login) ' создаем персональную роль и включаем в нее пользователя, устанавливая ему соотв. права Else UserToRole(sRoleName, sRoleName = "admin", s_Login) End If ' 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 Private Sub CreatePersonalRole(p_personalRoleName As String, p_Login As String) Dim s_cubeName, s_cubeDim, s_Attr, s_allowed_members, queryPersonalPermissions As String, cubePermAdd As Integer = 0 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 queryPersonalPermissions = "SELECT u.login, r.olap_name, r.cube_name, r.dimension_name, r.attribute_name, " _ & "r.allowed_members, a.access_start, a.access_expiry " _ & "FROM users u INNER JOIN user_access a ON u.id_user=a.id_user " _ & "INNER JOIN olap_resource r ON a.id_olap_resource=r.id_olap_resource " _ & "WHERE r.olap_name='" & _OLAP_DataBaseName & "' AND u.login='" & p_Login & "'" myRole = _MyDatabase.Roles.Add(p_personalRoleName) myRole.Members.Add(New RoleMember(p_Login)) myRole.Update() dbperm = _MyDatabase.DatabasePermissions.Add(myRole.ID) dbperm.Read = ReadAccess.Allowed ' Разрешаем доступ на чтение данной роли dbperm.ReadDefinition = ReadDefinitionAccess.Allowed dbperm.Description = "В данную персональную роль включается пользователь, которому ограничивается доступ в кубе(ах)" dbperm.Update() Dim conn As New OleDbConnection conn.ConnectionString = _RDBMS_connString Dim cmd As New OleDbCommand(queryPersonalPermissions, conn) 'Try conn.Open() Dim rdr As OleDbDataReader = cmd.ExecuteReader() While rdr.Read() s_cubeName = rdr.Item("cube_name").ToString.Trim s_cubeDim = rdr.Item("dimension_name").ToString.Trim s_Attr = rdr.Item("attribute_name").ToString.Trim s_allowed_members = rdr.Item("allowed_members").ToString.Trim myCube = _MyDatabase.Cubes.FindByName(s_cubeName) cubeDim = myCube.Dimensions.FindByName(s_cubeDim) If cubePermAdd = 0 Then cubePerm = myCube.CubePermissions.Add(myRole.ID, "Restriction_" & myRole.ID) cubePerm.Read = ReadAccess.Allowed cubePerm.ReadSourceData = ReadSourceDataAccess.Allowed ' Магическая опция DrillThrough cubePermAdd = cubePermAdd + 1 End If If CType(rdr.Item("access_start"), Date) > Now.Date Or rdr.Item("access_expiry") < Now.Date Then For Each cubeAttr In cubeDim.Attributes If cubeAttr.Attribute.Name = s_Attr Then 'MsgBox("p_Login= " & p_Login & " cubeDim.ID= " & cubeDim.ID _ ' & " cubeAttr= " & cubeAttr.Attribute.Name & " allowed_members= " & s_allowed_members) 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_members 'attrPerm.VisualTotals = "1" Exit For End If Next End If cubePerm.Update() End While rdr.Close() 'Catch ex As Exception 'End Try If conn.State = ConnectionState.Open Then conn.Close() conn.Dispose() End If End Sub Private Sub UserToRole(p_Role As String, p_isAdminRole As Boolean, p_Login As String) Dim MyRole As Role, dbperm As DatabasePermission Dim queryCubesPermissions As String, myCube As Microsoft.AnalysisServices.Cube, cubePerm As CubePermission If _MyDatabase.Roles.ContainsName(p_Role) Then MyRole = _MyDatabase.Roles.GetByName(p_Role) Else MyRole = _MyDatabase.Roles.Add(p_Role) MyRole.Update() dbperm = _MyDatabase.DatabasePermissions.Add(MyRole.ID) If p_isAdminRole Then dbperm.Administer = True ' Устанавливаем права администратора для данной роли dbperm.Update() Else dbperm.Read = ReadAccess.Allowed ' Разрешаем доступ на чтение данной роли dbperm.ReadDefinition = ReadDefinitionAccess.Allowed dbperm.Description = "В данную роль включаются пользователи, которым не ограничивается доступ в кубах" dbperm.Update() queryCubesPermissions = "SELECT DISTINCT u.user_role, r.olap_name, r.cube_name " _ & "FROM users u INNER JOIN user_access a ON u.id_user=a.id_user " _ & "INNER JOIN olap_resource r ON a.id_olap_resource=r.id_olap_resource " _ & "WHERE r.olap_name='" & _OLAP_DataBaseName & "' AND u.user_role='" & p_Role & "'" Dim conn As New OleDbConnection conn.ConnectionString = _RDBMS_connString Dim cmd As New OleDbCommand(queryCubesPermissions, conn) 'Try conn.Open() Dim rdr As OleDbDataReader = cmd.ExecuteReader() While rdr.Read() myCube = _MyDatabase.Cubes.FindByName(rdr.Item("cube_name").ToString.Trim) cubePerm = myCube.CubePermissions.Add(MyRole.ID, "Restriction_" & MyRole.ID) cubePerm.Read = ReadAccess.Allowed cubePerm.ReadSourceData = ReadSourceDataAccess.Allowed ' Магическая опция DrillThrough cubePerm.Update() End While rdr.Close() 'Catch ex As Exception 'End Try If conn.State = ConnectionState.Open Then conn.Close() conn.Dispose() End If End If End If 'If My_Role.Members.Contains(MyRoleMember) - не подходит, поскольку: ' The .Contains method does an object reference comparison. It checks if the object you are passing ' in exists in the collection, and because you are creating a new objec there is no way that it ' can be in the members collection (although there may be another object with the same value ' in the "Name" property in the collection). You would have to loop through the collection testing the value ' of the name property and you would not want to be creating a new Role object, but returning ' a reference to the object that you found. If RoleContainsMember(MyRole, p_Login) = False Then Dim MyRoleMember As New RoleMember(p_Login) MyRole.Members.Add(MyRoleMember) MyRole.Update() End If End Sub Private Sub UserRemoveFromRoles(p_Login As String) Dim oRole As Role ' удаляем логин из всех ролей OLAP-базы, в котором он был обнаружен For Each oRole In _MyDatabase.Roles For Each rm As RoleMember In oRole.Members 'Use string.compare to do a case-insensitive compare If String.Compare(rm.Name, p_Login, True) = 0 Then '' MsgBox("Из роли " & oRole.Name & " будет удален " & rm.Name) oRole.Members.Remove(rm) oRole.Update() Exit For End If Next Next End Sub Private Sub RemoveRole(p_Role As String) If _MyDatabase.Roles.ContainsName(p_Role) Then Dim MyRole As Role MyRole = _MyDatabase.Roles.GetByName(p_Role) 'MsgBox("Удаление роли " p_Role) '_MyDatabase.Roles.Remove(MyRole) 'Try MyRole.Drop(DropOptions.AlterOrDeleteDependents) 'Catch ex As Exception ' 'End Try End If End Sub Function RoleContainsMember(p_Role As Role, p_Login As String) As Boolean For Each rm As RoleMember In p_Role.Members 'Use string.compare to do a case-insensitive compare If String.Compare(rm.Name, p_Login, True) = 0 Then Return True End If Next Return False End Function Function DomenUserValid(ByVal p_User As String) As Boolean Dim retVal As Boolean = False On Error Resume Next Dim objUser As Object objUser = GetObject("WinNT://YourDomen/" & p_User) If Err.Number = 0 Then retVal = True 'MsgBox("The user account exists in the domain " & retVal.ToString & " " & p_User) ElseIf Err.Number = -2147022676 Then retVal = False 'MsgBox("The user account does not exist in the domain " & retVal.ToString & " " & p_User) Else retVal = False 'MsgBox("The user account status could not be determined " & retVal.ToString & " " & p_User) End If Return retVal End Function End Class ---------------------------------------------------------------------------------------------------------------------------------
Данный скрипт сканирует вышеобозначенные реляционные таблицы и пересоздает роли SSAS, включает в них учетные записи и устанавливает права доступа к атрибутам измерений. Код не претендует на изысканность, но позволяет быстрее понять серверную объектную модель Microsoft.AnalysisServices (AMO) в части объектов безопасности. При количестве пользователей - 130 чел. код отрабатывает за 2 минуты. При необходимости ETL-процесс, скрипт можно модифицировать таким образом, чтобы обрабатывались только новые пользователи OLAP, а также таблица users сверялась с Active Directory. После автоматического исполнения скрипта роли на сервере SSAS должны выглядеть так:
Ну и наконец, для соблюдения административно-протокольного политеса следует разработать и согласовать с соответвующими службами и руководителями матрицу прав доступа, в которой столбцы по вертикали - эквиваленты категорий ролей SSAS, строки по горизонтали - перечень подразделений/уровни должностей, а на пересечении, в ячейках - права доступа (фон в зелено-красных цветах). В дальнейшем служба информационной безопасности должна строго придерживаться правил данного документа, который, кстати, также рекомендуется опубликовать на корпоративном web-портале, чтобы у сотрудников не было вопросов - почему кто-то не видит элементы отдельных атрибутов.