Технология Клиент-Сервер 2010'2
	Home Поиск Издания Контакты k-press.ru

Продолжается подписка на наши издания! Вы не забыли подписаться?

Ориентированная на строки безопасность с использованием триггеров

Автор: Глен Купер

Опубликовано: 02.07.2010
Версия текста: 1.1

Введение

В этой статье представлена простая модель безопасности на уровне записей. Она основана на небольшом числе таблиц безопасности и нескольких триггерах. Модель не зависит от системы безопасности SQL Server и может быть с минимальными изменениями использоваться с другими СУБД

Основная исходная посылка этой модели состоит в том, что некоторые таблицы выступают в роли шлюзов для всех других таблиц. Безопасность на уровне записей использует одну таблицу для перечисления записей из таблицы-шлюза, доступных данному пользователю. Такие пользователи могут обращаться к связанным записям из других таблиц, если имеют доступ к соответствующим записям таблицы-шлюза.

Предполагается также, что пользователи будут обращаться к БД только через санкционированные приложения. Дело в том, что модель возлагает небольшую часть бремени обеспечения безопасности на front-end приложения, а не на сервер БД (где выполняется основная работа по обеспечению безопасности). В частности, эти приложения могут реализовать безопасность на уровне строк или столбцов без дополнительного программирования.

Эта модель несколько лет успешно использовалась в крупном медицинском учреждении.

Пример

Для простоты предположим, что наша БД содержит только одну таблицу Employee, для которой было написано, например, приложение для работы с персоналом.

В этом приложении мы хотим ограничить пользователя несколькими записями, зависящими от роли пользователя. Мы хотим определять эти ограничения в наборе таблиц безопасности, которые приложение читает в процессе работы, и которые администратор может редактировать в реальном времени.

Строки в Employee, доступные каждой из ролей, будут определяться фильтрами к этой таблице, например:

      SELECT EmployeeId AS SelectId FROM Employee WHERE City = 'Vancouver'

Такие фильтры просто перечисляют значения ключей в таблице-шлюзе с фиксированным именем SelectId. Для каждой роли можно создать произвольное количество фильтров, и каждому пользователю можно назначить несколько ролей. Для удобства ключ таблицы-шлюза и SelectId – это колонки типа INT (хотя это можно запросто изменить).

Мы также хотим, чтобы записи, которые любая роль (и пользователь) может видеть, точно отражали текущее состояние данных (например, работник может в любой момент сменить позицию) или фильтров, созданных для этой роли (они могут создаваться или удаляться в любое время).

Далее, само приложение должно автоматически генерировать дополнительные ограничения на уровне строк и столбцов, например, для предохранения записей от удаления или для скрытия некоторых полей, в зависимости от ограничений, введенных в таблицы безопасности администратором.

Модель, представленная здесь, делает это, используя специальные таблицы безопасности, которые определяют роль пользователя, записи в таблице-шлюзе, к которым имеет доступ каждая роль, виды редактирования, возможные для каждой записи, и столбцы, которые будут скрыты приложениями.

Функциональность

Пользователи, роли и ограничения определяются организацией с помощью специальных таблиц безопасности, добавленных в БД.

Ограничения на уровне записей – это всего лишь фильтр, определяющий, какие записи таблицы-шлюза доступны данной роли. Такие фильтры также можно определять с помощью дополнительных таблиц, например, справочников.

Пользователям можно назначить любое количество ролей.

Механизм фильтрации, обеспечивающий безопасность, работает динамически, используя триггеры, срабатывающие при изменениях таблицы-шлюза:

• Если любая запись в таблице-шлюзе становится видна фильтру безопасности (из-за изменения каких-либо нижележащих данных), эта строка немедленно становится доступной любой роли, чья безопасность включает этот фильтр.
• Соответственно, если любая запись в таблице-шлюзе становится невидимой для фильтра безопасности, эта строка становится недоступной и для всех ролей, чья безопасность использует этот фильтр (если только другой фильтр безопасности для этой роли не разрешает этого). Далее, любые изменения в наборе фильтров безопасности немедленно отражаются на всех пользователях, используя триггеры, которые срабатывают при удалении или добавлении фильтра:
• При добавлении любого фильтра безопасности каждая запись в таблице-фильтре, определяемая им, становится немедленно доступна любой роли, использующей этот фильтр.
• Соответственно, при удалении любого фильтра безопасности каждая запись в таблице-фильтре, определяемая им, становится недоступной для всех ролей, использовавших этот фильтр (если только другой фильтр безопасности для этой роли не разрешает этого).

Реализация

Вся система написана на триггерах и хранимых процедурах. Так что она принципиально не зависит от любых настольных или Web-приложений. Они, чтобы использовать эту систему, должны только включать некий стандартный код для обеспечения безопасности. Именно поэтому доступ к БД должен предоставляться только "одобренным" приложениям. Конечно, можно обойти модель безопасности, просто игнорируя таблицы безопасности. Это позволяет разработчикам тестировать приложения, не используя систему безопасности, прежде чем внести код, обеспечивающий безопасность, на этапе развертывания. Поскольку таблицы безопасности связывает с таблицей-шлюзом единственное отношение, добавление безопасности в любое одобренное приложение выполняется просто путем "обмана" приложения, которому вместо таблицы-шлюза выдается набор записей, отфильтрованных системой безопасности. Таким образом, систему безопасности можно довольно быстро подключить к старым приложениям.

Пользователи, роли и ограничения интерактивно определяются с помощью VB.NET-менеджера безопасности.

Такая система идеальна для серверной фермы Citrix (где и была исходно разработана), где к данным обращаются только одобренные приложения, а лежащие ниже наборы данных и код скрыты от пользователей.

Модель безопасности

В таблицах lkpUser и lkpRole определяются пользователи и роли, их связывает таблица tblUserRole.

Таблица lkpView определяет различные представления (не SQL-представления), которые роль может использовать для доступа к данным из одной таблицы в различных контекстах. Для простоты в этой статей будем использовать одно доступное всем представление.

Ключевая таблица безопасности – это tblRole­Ac­cessSelect. Для каждой записи внешний ключ SelectId указывает на запись в таблице-шлюзе Employee для данной роли и представления. Проверенные приложения теперь видят не таблицу Employee (которая переименована и скрыта), а ее подмножество, отфильтрованное таблицей безопасности tblRoleAccessSelect. Пользователи не будут знать об этом ограничении за исключением того, что строки в фильтрованной таблице будут появляться/исчезать при каждом изменении пользовательских данных или таблицы tblRole­Access­Se­lectFilter. Это возможно потому, что ключевая таблица безопасности динамически заполняется при добавлении или удалении фильтров в таблице tblRole­Access­Se­lect­Filter.

Таблица tblRoleAccessSelectFilter применяет к каждой роли и представлению любое число фильтров. Любой из этих фильтров – это простой запрос к столбцу SelectId, где перечислено, какие записи доступны в таблице Employee (в этой статье это единственная таблица-шлюз).

Основная проблема заключается в динамическом изменении tblRoleAccessSelect при изменении пользовательских данных (что влияет на результаты фильтрации) или при изменениях в таблице tblRoleAccessSelectFilter. Триггеры и хранимые процедуры делают это автоматически.

Таблицы lkpAccessRow и lkpAccessColumn определяют дополнительные ограничения доступа к записям и столбцам, которые каждое приложение может автоматически накладывать, используя клиентский код, предоставляемый моделью.

Для записей можно разрешать/запрещать редактирование, добавление или удаление. Для столбцов можно разрешать их скрытие или блокировку (или предоставлять полный доступ). У конкретного пользователя может быть несколько ролей и представлений, поэтому в каждом конкретном случае побеждает роль, имеющая высший приоритет.

Таблица tblRoleAccessRow налагает на роли и представления ограничения на уровне записей.

Таблица tblRoleAccessColumn налагает на роли и представления ограничения на уровне столбцов.

Эти таблицы клиентский код HR-приложения использует, чтобы решить, как редактировать доступные ему записи, и какие столбцы оно может видеть. Этот код можно в дальнейшем изменять для задания дополнительных ограничений в таблицах lkpAccessRow и lkpAccessColumn.

Как работает модель (серверная сторона)

Ключевая таблица безопасности tblRoleAccessSelect указывает на записи в таблице-шлюзе Employee, которые видны той или иной роли и представлению.

Если в таблице-шлюзе изменяется/появляется запись, следующий триггер передает ее ключ хранимой процедуре sqRoleAccessSelectSync.

Процедура гарантирует, что в ключевую таблицу безопасности вносятся (или удаляются) корректные записи в целях синхронизации со всеми фильтрами, назначенными для каждой роли и представления:

ALTER TRIGGER [dbo].[TR_Employee_Update_Insert] ON [dbo].[Employee] 
AFTER UPDATE, INSERT
AS

-- No counting
SET NOCOUNT ON

-- Объявления
DECLARE @MyId INT

-- Если единственная запись обновляется/вставляется
IF (SELECT COUNT(*) FROM INSERTED) = 1
  BEGIN
  -- Определение ключа записи
  SELECT @MyId = (SELECT EmployeeId FROM INSERTED)
  -- Синхронизация таблицы безопасности для обновленной/вставленной записи
  EXECUTE sqRoleAccessSelectSync @MyId
  END

Другими словами, при изменении пользовательских данных ключевая таблица безопасности всегда будет синхронизирована с фильтрами, исходно использовавшимися при ее заполнении.

Задача хранимой процедуры sqRoleAccessSelectSync – обеспечить, что для каждой роли, представления и фильтра в таблице tblRoleAccessSelectFilter запись (роль, представление, Id) принадлежит таблице tblRoleAccessSelect в том и только том случае, если Id выбран хотя бы одним из фильтров.

ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync]
  (
  @Id INT
  )
 AS

/*
Для каждой Role, View и Filter в tblRoleAccessSelectFilter
проверяется, принадлежит ли @Id к набору результатов этого фильтра.

Гарантируем, что (Role, View, @Id) принадлежит к RoleAccessSelect тогда и
только тогда, когда @Id принадлежит как минимум к наборам результатов для этих
Role and View.

Заметьте, что любая комбинация Role и View может быть ассоциирована с 
несколькими Filter.

Так что если @Id принадлежит к набору результатов одного фильтра, нужно исключить уничтожение соответствующей строки RoleAccessSelect в случае, когда @Id не принадлежит к наборам результатов другого фильтра для тех же Role и View.

Это делается с помощью сортировки Filter по Role и View и использования.

Сбросьте флаг при возникновении новой комбинации Role и View 
Reset that flag when a new Role and View combination appears. 
*/

-- Объявления
DECLARE @RoleIdCurrent  INT  -- current Role for all Filters being checked
DECLARE @ViewIdCurrent  INT  -- current View for all Filters being checked
DECLARE @ynInserted  INT  -- inserted flag for all Filters being checked with given Role, View

-- Объявления для курсора 
DECLARE @RoleId    INT
DECLARE @ViewId    INT
DECLARE @FilterName  NVARCHAR(256)

-- Курсор для tblRoleAccessSelectFilter перечисляющий Roles, Views и Filters 
-- (упорядоченных по Role, View)
DECLARE curTable CURSOR
FOR 
SELECT RoleId, ViewId, FilterName FROM dbo.tblRoleAccessSelectFilter ORDER BY RoleId, ViewId
FOR READ ONLY

-- Устанавливаем флаг в off
SET @ynInserted = 0

-- Открытие курсора
OPEN curTable 

-- Выборка первой строки 
FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName

-- сохраняем комбинацию Role, View
SET @RoleIdCurrent = @RoleId
SET @ViewIdCurrent = @ViewId

-- Делаем это пока строки выбираются и вставленный @Id не существует для 
-- текущих Role, View
-- Если вставленный @Id существует, сбрасываем флаг, чтобы предотвратить 
-- возможное удаление для текущих Role, View
-- Сбрасываем установленный флаг в off в случае новой комбинации Role, View

While @@FETCH_STATUS = 0 
  BEGIN
  -- проверяем соответствие @ID фильтру и вставляем/удаляем соответствующую
  -- строку в tblRoleAccessSelect
  -- Но делаем это только при значении флага off (иначе для текущих Row, View
  -- ничего делать не нужно)
  IF @ynInserted = 0
    EXECUTE sqRoleAccessSelectSync2
        @Id = @Id,
        @RoleId = @RoleId, 
        @ViewId = @ViewId, 
        @FilterName = @FilterName,
        @ynInserted = @ynInserted OUTPUT
  FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName
  -- If Row, View combination changes, reset inserted flag to off and continue
  IF (@RoleId <> @RoleIdCurrent) OR (@ViewId <> @ViewIdCurrent)
    BEGIN
    SET @ynInserted = 0
    SET @RoleIdCurrent = @RoleId
    SET @ViewIdCurrent = @ViewId
    END
  END

-- Закрываем курсор
CLOSE curTable

DEALLOCATE curTable

Она делает это, вызывая хранимую процедуру sqRoleAccessSelectSync2, которая гарантирует, что запись (роль, представление, Id) принадлежит таблице tblRoleAccessSelect тогда и только тогда, когда Id выбран выполненным фильтром. Однако эта процедура возвращает флаг, указывающий, что была выполнена вставка (или что запись (роль, представление, Id) уже существуют), чтобы вызывающая сторона знала, когда нужно избежать ненужного удаления записей:

ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync2]
  (
  @Id    INT,
  @RoleId    INT,
  @ViewId    INT,
  @FilterName  NVARCHAR(256),
  @ynInserted  INT OUTPUT
  )
AS

/*
Проверяем, что (RoleId, ViewId, @Id) принадлежит RoleAccessSelect, если @Id принадлежит к набору результатов фильтра @FilterName (иначе удаляем).

Заметьте, что этот вызов может удалить (RoleId, ViewId, @Id) в tblRoleAccessSelect.

Поэтому вызывающий скрипт должен обеспечить, что этого не случится, проверяя значение @ynInserted, установленное данным вызовом.
*/

BEGIN

SET NOCOUNT ON

-- Declarations
DECLARE @SQLString NVARCHAR(1028)

-- Устанавливаем флаг в off
SET @ynInserted = 0

-- Проверяем, принадлежит ли @Id к набору результатов фильтра @FilterName
-- Используем временную таблицу 
-- Заметьте, что SelectId – это всегда одна колонка в @FilterName
SET @SQLString = N'SELECT TOP 1 * INTO #t FROM dbo.' + @FilterName + '  WHERE (SelectId = ' + CAST(@Id AS NVARCHAR(10))  + ')'
EXEC sp_executesql @SQLString

IF (@@ROWCOUNT = 1) 
  BEGIN
  SET @SQLString = N'SELECT SelectId FROM tblRoleAccessSelect 
    WHERE SelectId = ' + CAST(@Id AS NVARCHAR(10)) + ' 
      AND RoleId = ' + CAST(@RoleId AS NVARCHAR(10)) + 
    ' AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10))
  EXEC sp_executesql @SQLString
  IF (@@ROWCOUNT = 0) 
    BEGIN
    SET @SQLString = N'INSERT INTO tblRoleAccessSelect VALUES (
      ' +  CAST(@RoleId AS NVARCHAR(10)) + ',
      ' + CAST(@ViewId AS NVARCHAR(10)) + ',
      ' + CAST(@Id AS NVARCHAR(10)) + ')'
    EXEC sp_executesql @SQLString
    -- Устанавливаем флаг в ON (поскольку его только что вставили)
    -- Set inserted flag on (because it has just been inserted)
    SET @ynInserted = 1
    END

  ELSE
    -- Устанавливаем флаг в ON (поскольку его только что вставили)
    SET @ynInserted = 1
  END

ELSE
  BEGIN
  -- Удаляем
  SET @SQLString = N'DELETE FROM tblRoleAccessSelect 
    WHERE RoleId = ' +  CAST(@RoleId AS NVARCHAR(10)) + ' 
      AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10)) + ' 
      AND SelectId = ' + CAST(@Id AS NVARCHAR(10)) 
  EXEC sp_executesql @SQLString
  END
END

Нужно заметить, что если запись удаляется из таблицы Employee, срабатывает каскадное удаление, связывающее ее с таблицей tblRoleAccessSelect.

Есть одно условие к модели, состоящее в том, что если запись видна/не видна данному фильтру в таблице-шлюзе, так оно и должно оставаться, независимо от того, как изменяются остальные записи. Например, если фильтр видит Служащего, живущего в Ванкувере, это не должно измениться, если кто-то еще переедет в этот город. В качестве извращенного примера, фильтр, который видит всё только в том случае, когда число записей точно равно 100, должен считаться неправильным. Это условие в наших условиях всегда удовлетворяется обычной фильтрацией.

Конечно, мы должны также обрабатывать события вставки/удаления записей в таблице tblRoleAccessSelectFilter (изменения записей не разрешаются).

Для вставки записей используется следующий триггер, TR_tblRoleAccessSelectFilter_Insert, заполняющий таблицу tblRoleAccessSelect значениями ключей для создаваемой роли и представления, не существующих на этот момент (во избежание дубликатов):

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Insert] ON [dbo].[tblRoleAccessSelectFilter] 
FOR INSERT
AS
BEGIN

-- Объявления
DECLARE @SQLString    NVARCHAR(1028)
DECLARE @RoleId      INT
DECLARE @ViewId      INT
DECLARE @FilterName    NVARCHAR(256)  

-- Ничего не делаем, если INSERTED не содержит ровно одну строку
IF @@ROWCOUNT <> 1 
  RETURN

SET NOCOUNT ON

-- Получаем RoleId, ViewId, Filter из вставленной записи
SELECT @RoleId    = (SELECT RoleId FROM INSERTED)
SELECT @ViewId    = (SELECT ViewId FROM INSERTED)
SELECT @FilterName  = (SELECT FilterName FROM INSERTED)

-- Вставка в tblRoleAccessSelect
-- Заметьте, что @FilterName должно быть именем существующего фильтра 
-- (иначе вставка не удастся)
SET @SQLString = 
  N'INSERT INTO tblRoleAccessSelect(RoleId,ViewId,SelectId)
      SELECT D1.RoleId,D1.ViewId,D1.SelectId 
      FROM 
        (SELECT ' + CAST(@RoleId as NVARCHAR(10)) + ' AS RoleId,' 
                  + CAST(@ViewId as NVARCHAR(10)) + ' AS ViewId,' 
                  + @FilterName + '.SelectId '
                  + 'FROM ' + @FilterName + ') '
      + '  D1 LEFT OUTER JOIN tblRoleAccessSelect ' 
      + '  ON  D1.RoleId   = tblRoleAccessSelect.RoleId ' 
      + '  AND D1.ViewId   = tblRoleAccessSelect.ViewId '
      + '  AND D1.SelectId = tblRoleAccessSelect.SelectId '
      + 'WHERE tblRoleAccessSelect.SelectId IS NULL' 

EXEC sp_executesql @SQLString

END

-- Финализация
SET NOCOUNT OFF

Удаление несколько сложнее:

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Delete] ON [dbo].[tblRoleAccessSelectFilter] 
FOR DELETE
AS
BEGIN

-- Объявления
DECLARE @deleteSql    NVARCHAR(2048)
DECLARE @sql   NVARCHAR(2048)  -- строка для сборки SQL-запроса
DECLARE @RoleId      INT
DECLARE @ViewId      INT
DECLARE @FilterName    NVARCHAR(256)  
DECLARE @RoleIdSave    INT
DECLARE @ViewIdSave    INT
DECLARE @FilterNameSave    NVARCHAR(256)

-- Ничего не делаем, если DELETED не содержит ровно одну строку
IF (SELECT COUNT(*) FROM DELETED) <> 1 
  RETURN

SET NOCOUNT ON

-- Получаем @RoleId, @ViewId, @Filter из DELETED 
SELECT @RoleId    = (SELECT RoleId FROM DELETED)
SELECT @ViewId    = (SELECT ViewId FROM DELETED)
SELECT @FilterName  = (SELECT FilterName FROM DELETED)

-- Сохраняем Role, View и Filter для использования в конструируемой SQL-строке
SET @RoleIdSave    = @RoleId
SET @ViewIdSave    = @ViewId
SET @FilterNameSave  = @FilterName

-- Начинаем конструировать SQL-строку
SET @sql = 'SELECT SelectId FROM ' + @FilterName 

-- Курсор для tblRoleAccessSelectFilter с перечислением Filters 
-- с теми же Role и View, что у записи DELETED
DECLARE curTable CURSOR
FOR 
SELECT FilterName FROM dbo.tblRoleAccessSelectFilter 
WHERE
RoleId = @RoleId AND
ViewId = @ViewId 
FOR READ ONLY


OPEN curTable 

-- Выбираем следующую строку (удаляемая строка не принадлежит к курсору)
FETCH NEXT FROM curTable INTO @FilterName

-- Продолжаем конструировать SQL-строку
While @@FETCH_STATUS = 0 
  BEGIN
  SET @sql = @sql + ' UNION ALL SELECT SelectId FROM ' + @FilterName 
  FETCH NEXT FROM curTable INTO @FilterName
  END

-- Удаляем из tblRoleAccessSelect (объяснение ниже)
SET @deleteSql = 
N'DELETE FROM tblRoleAccessSelect WHERE 
RoleId = ' + CAST(@RoleIdSave AS NVARCHAR(10)) + ' AND
ViewId = ' + CAST(@ViewIdSave AS NVARCHAR(10)) + ' AND
SelectId IN
(
SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect
INNER JOIN ' +
@FilterNameSave +
' ON
tblRoleAccessSelect.SelectId = ' + @FilterNameSave + '.SelectId
INNER JOIN
(
SELECT SelectId 
  FROM (' + @sql + ') d1
  GROUP BY SelectId
  HAVING COUNT(*) = 1
) d2
ON
tblRoleAccessSelect.SelectId = d2.SelectId
)'

/*
Пример удаления для фильтра ftr1, где ftr2 имеет те же Role и View.
Исключаем удаление любых SelectId, принадлежащих ftr1, если они также 
принадлежат ftr2.

DELETE 
  FROM tblRoleAccessSelect WHERE
    RoleId = 1 AND
    ViewId = 1 AND
    SelectId IN
(
-- Находим значения SelectId, принадлежащие ftr1, в tblRoleAccessSelect
SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect
  INNER JOIN
  ftr1
  ON
  tblRoleAccessSelect.SelectId = ftr1.SelectId
   -- But make sure they don't also belong to ftr2 
  INNER JOIN
(
-- UNION SelectId для двух фильтров без дублирования (но с COUNT = 1).
-- Это гарантирует, что каждый SelectId принадлежит только одному фильтру.
SELECT SelectId FROM
(
-- UNION SelectId для двух фильтров (с дублированием)
-- Это inner SQL-строка
SELECT SelectId FROM ftr1
UNION ALL
SELECT SelectId FROM ftr2
) d1
GROUP BY SelectId
HAVING COUNT(*) = 1
) d2
ON
tblRoleAccessSelect.SelectId = d2.SelectId
)
*/

EXEC sp_executesql @deleteSql

-- Close cursor
CLOSE curTable

-- Deallocate cursor
DEALLOCATE curTable

-- Finalization
SET NOCOUNT OFF

END

Здесь нужно быть внимательным, чтобы не удалить запись из таблицы tblRoleAccessSelect, если какой-то другой фильтр для той же роли и представления требует ее существования.

Как работает модель (клиентская сторона)

Каждое клиентское приложение при открытии любой формы исполняет скрытую часть кода безопасности, автоматически обеспечивающую дополнительную безопасность на уровне записей и столбцов. Например, следующий код блокирует или скрывает любой элемент управления, на который ссылается таблица tblRoleAccessColumn:

Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()      ' Перебор ограничений 
  MySecurity(0) = reader.Item(0).ToString ' ColumnName
  MySecurity(1) = reader.Item(1).ToString ' AccessColumnName
  For Each ctrl As Control In Me.Controls ' Перебор всех control-ов
    ' Если это один из следующих control-ов
    If (TypeOf ctrl Is TextBox) Or _
    (TypeOf ctrl Is Label) Or _
    (TypeOf ctrl Is Button) Or _
    (TypeOf ctrl Is ListBox) Or _
    (TypeOf ctrl Is ListView) Then
      If MySecurity(0) = ctrl.Name Then  
        ctrl.Enabled = True       
        ctrl.Visible = True
        If MySecurity(1) = "Lock" Then 
          ctrl.Enabled = False
        End If
        If MySecurity(1) = "Hide" Then  
          ctrl.Visible = False
        End If
        If MySecurity(1) = "Full" Then 
          ctrl.Enabled = True
          ctrl.Visible = True
        End If
      End If
    End If
  Next
End While
reader.Close()

Столбец ColumnName в таблице tblRoleAccess­Co­lumn – это имя элемента управления формы, а не имя столбца из нижележащей таблицы. Конечно, имена приложения и формы должны также быть частью определения таблицы tblRole­Access­Column (для большей гибкости), но мы это пропустим для простоты.

Исходный код на Visual Basic 2005 Express и исполняемые файлы модели (с клиентским приложением и простой БД) можно найти в разделе Ресурсы.

Пример

В этом примере есть два пользователя, две роли и одно представление. Таблица Employee содержит 100 случайно сгенерированных записей.

User1 имеет Role1, а User2 - Role1 и Role2 (все представления – это View1, так что дальше они игнорируются).

Role1 содержит фильтр ftr1, который отбирает работников, чьи имена начинаются с A или B:

CREATE VIEW dbo.ftr1
AS
SELECT     EmployeeId AS SelectId
FROM       dbo.Employee
WHERE      (SUBSTRING(LastName, 1, 1) IN ('A', 'B'))

Кроме этого, Role1 содержит фильтр ftr2 (для работников, чьи имена начинаются с B или C).

Role1 содержит фильтр ftr3, (Employees, чьи имена начинаются с C или D)

Если выбраны User2 и View1, щелчок по RoleAccessSelect дает 42 комбинации (Role, View, Select) (рисунок 4).

Конкретный Employee может встречаться несколько раз, если выбранный пользователь принадлежит к нескольким ролям (в данном случае 28 Employees встречаются 42 раза для различных ролей и представлений).

SQL-запрос, сгенерированный для этого выбора – User, Role и View:

SELECT DISTINCT 
  lkpRole.RoleName, 
  lkpView.ViewName, 
  Employee.LastName, 
  tblRoleAccessSelect.SelectId 
  FROM 
    Employee INNER JOIN 
    tblRoleAccessSelect ON 
    Employee.EmployeeId = tblRoleAccessSelect.SelectId INNER JOIN 
    lkpRole ON 
    tblRoleAccessSelect.RoleId = lkpRole.RoleId INNER JOIN 
    tblUserRole ON 
    lkpRole.RoleId = tblUserRole.RoleId INNER JOIN 
    lkpView ON 
    tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN 
    lkpUser ON 
    tblUserRole.UserId = lkpUser.UserId 
  WHERE 
    (lkpUser.UserName = 'User2') AND 
    (1=1) AND 
    (lkpView.ViewName = 'View1') 
  ORDER BY 
    lkpRole.RoleName, 
    lkpView.ViewName, 
    Employee.LastName

Тем временем, в любых приложениях, использующих стандартный snap-in безопасности:

• Role1 прячет элемент управления tbEmail.
• Role2 прячет элементы управления tbBirthDate и tbSIN, и блокирует элемент управления tbEmail
• Все несуществующие элементы управления просто игнорируются.

При открытии приложения User2 и View1 при щелчке по Employees могут видеть 28 уникальных записей.

Хранимая процедура безопасности sqMain генерирует SQL, обеспечивающий выполнение этой последовательности действий:

SELECT 
  EmployeeId, 
  LastName, 
  FirstName, 
  Address, 
  City, 
  Postal, 
  Prov,
  Phone, 
  Email, 
  BirthDate, 
  [SIN]
FROM 
  Employee 
WHERE EmployeeId IN
(
SELECT DISTINCT 
tblRoleAccessSelect.SelectId
FROM         
tblRoleAccessSelect INNER JOIN
lkpView ON 
tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN
tblUserRole ON 
tblRoleAccessSelect.RoleId = tblUserRole.RoleId
WHERE 
UserId = 2 AND 
ViewName = 'View1'
) 
ORDER BY 
LastName, 
FirstName

После выбора Employee щелчок по кнопке Test Security блокирует элемент управления Email и скрывает элементы управления BirthDate и SIN (блокировка имеет приоритет над скрытием, но это можно изменить в таблице tblRoleAccessColumn).

Конечно, этот пример при первом открытии ничего ни от чего не защищает, но он позволяет использовать кнопку Test Security для тестирования других комбинаций пользователь-представление без перезапуска. В реальной жизни код, стоящий за этой кнопкой, будет запускаться при открытии формы. Имя пользователя и представление должны передаваться хранимой процедуре sqMain. Программист должен решить, как определить эти параметры, чтобы они соответствовали содержимому таблиц безопасности.

Ограничения редактирования на уровне записей не включены в этот пример.

Резюме

Эта статья представляет простую модель реализации безопасности на уровне строк для SQL Server. Хотя она накладывает некую нагрузку по обеспечению безопасность на клиентское приложение (что увеличивает ее уязвимость), она оказалась полезной в ситуациях, когда доступ к клиентским приложениям строго контролируется. Кроме того, ее просто развертывать, и она удивительно эффективна (даже при большой ключевой таблице безопасности). Далее, ее можно использовать в унаследованных приложениях с помощью простой модификации клиентского кода.

Математически выражаясь, ключевая таблица безопасности – это на самом деле объединение динамического набора унарных отношений, которое автоматически поддерживается триггерами для данных, от которых зависит это объединение.

Copyright © 1994-2016 ООО "К-Пресс"