45799 (Row-Level Security в РСУБД), страница 2

Соединив эти два подхода, мы получим возможность как «карать», так и «миловать» пользователей:

(-- секция разрешений (IsUserInRole() AND ) OR ... (IsUserInRole() AND ) ) AND NOT (-- секция запретов (IsUserInRole() [AND ]) OR ... (IsUserInRole() [AND ]) )

Такой принцип защиты очень похож на используемый в Windows NT.

Рассмотрим варианты применения данной техники на примере базы данных Northwind, которая входит в поставку MS SQL Server.

Локальные атрибуты

В простейшем случае предикаты для всех ролей зависят только от значений полей защищаемой записи. Рассмотрим таблицу Orders (несущественные для рассматриваемой задачи ограничения пропущены):

CREATE TABLE Orders ( OrderID int IDENTITY(1, 1) NOT NULL , CustomerID nchar(5) NULL , EmployeeID int NULL , OrderDate datetime NULL , RequiredDate datetime NULL , ShippedDate datetime NULL , ShipVia int NULL , Freight money NULL CONSTRAINT DF_Orders_Freight DEFAULT(0), ShipName nvarchar(40) NULL , ShipAddress nvarchar(60) NULL , ShipCity nvarchar(15) NULL , ShipRegion nvarchar(15) NULL , ShipPostalCode nvarchar(10) NULL , ShipCountry nvarchar(15) NULL , CONSTRAINT FK_Orders_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID) )

Предположим, что правила корпоративной безопасности по отношению к данным заказов определены следующим образом:

Менеджеры по продажам (роль ‘Sales Representative’) имеют право просматривать только «свои» заказы и не могут видеть заказы, введенные другими менеджерами по продажам.

Директор по продажам (роль ‘Vice President, Sales’) имеет право просматривать любые заказы.

Все остальные сотрудники доступа к заказам не имеют.

Этим правилам соответствует вот такое представление:

CREATE VIEW [Secure Orders] AS SELECT * FROM Orders where (IsUserInRole('Sales Representative') AND EmployeeID = CurrentEmployeeID()) OR (IsUserInRole('Vice President, Sales') AND TRUE)

Здесь подразумевается, что функция CurrentEmployeeID() неким «магическим» образом возвращает идентификатор сотрудника, соответствующий пользователю, от имени которого произведено подключение. Реализация этой функции, как и функции IsUserInRole(), зависит от используемой СУБД. Обратите внимание на вторую часть предиката: для директора по продажам никаких дополнительных ограничений не предусмотрено, но для общности мы использовали выражение TRUE для представления этого факта. При подготовке предиката вручную фрагмент AND TRUE можно опустить, хотя оптимизаторы, используемые в современных СУБД, достаточно интеллектуальны, чтобы выбросить избыточные выражения из плана запроса.

СОВЕТ Иногда предикат безопасности может оказаться настолько сложным, что оптимизатор СУБД не сможет самостоятельно построить оптимальный план выполнения запроса. В таких случаях может потребоваться ручное преобразование выражения предиката в более адекватную форму. Пока что мы будем считать, что оптимизатор идеален, и все выражения будут представлены в наиболее удобном для чтения виде.

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

(IsUserInRole('Sales Representative') AND EmployeeID = CurrentEmployeeID()) OR (IsUserInRole('Vice President, Sales') AND TRUE) OR (IsUserInRole('Everyone') AND ShippedDate < DateAdd(month, -6, GetDate())

Стоит обратить внимание на две особенности предиката:

Во-первых, (опять же из соображений общности) в дополнительном условии проверяется принадлежность текущего пользователя к группе Everyone. Эта специальная группа по определению включает всех сотрудников, и выражение IsUserInRole('Everyone') является тождественно истинным. Это позволяет исключить его из предиката в целях оптимизации.

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

DateAdd(month, 6, ShippedDate) < GetDate()

и

DateDiff(month, ShippedDate, GetDate()) >= 6

хотя и являются математически эквивалентными, скорее всего, помешают оптимизатору СУБД использовать индекс по полю ShippedDate (если он есть).

Атрибуты связанных таблиц

В корпоративную политику безопасности могут входить и более сложные правила, которые связывают различные сущности предметной области между собой. Например, предположим, что компания Northwind выросла, и в ней есть несколько филиалов. Структура таблицы сотрудников претерпевает соответствующие изменения:

ALTER TABLE [Employee] ADD [DivisionID] int CONSTRAINT [DivisionID_FK] REFERENCES [Division]([DivisionID])

Новый вариант правила №1 из предыдущего раздела формулируется так:

Менеджеры по продажам (роль ‘Sales Representative’) имеют право просматривать только заказы, введенные менеджерами из того же филиала.

Соответствующая часть предиката безопасности теперь примет такой вид:

(IsUserInRole('Sales Representative') AND (select DivisionID from Employees where EmployeeID = CurrentEmployeeID()) = (select DivisionID from Employees where EmployeeID = EmployeeID)

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

(IsUserInRole('Sales Representative') AND select(EmployeeID from Orders o where o.OrderID = OrderID) = CurrentEmployeeID()) OR (IsUserInRole('Vice President, Sales') AND TRUE) OR (IsUserInRole('Everyone') AND select(ShippedDate from Orders o where o.OrderID = OrderID) < DateAdd(month, -6, GetDate())

К сожалению, такой вид предиката не слишком нагляден. Он не отражает взаимосвязи между правилами безопасности для деталей заказов и для самих заказов. Поэтому лучше применить немного другой способ построения представления, чем был рассмотрен ранее:

create view [Secure Order Details] as select od.* from [Order Details] od join [Secure Orders] so on od.OrderID = so.OrderID

В таком виде сущность используемого ограничения безопасности очевидна. Кроме того, изменение правил безопасности для заказов (которое повлияет на определение представления Secure Orders) автоматически отразится и на их деталях.

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

Некоторые итоги

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

Однако в некоторых случаях требуется предоставлять или отказывать в доступе к конкретным записям в административном порядке, независимо от хранящихся в них значений. Такие требования могут быть связаны с быстроменяющимися правилами безопасности, для которых недопустимы задержки в реализации, неизбежные при модификации схемы базы данных. Кроме того, иногда быстродействия СУБД будет недостаточно для вычисления предикатов безопасности на основе существующих атрибутов.

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

Ограничения на основе дополнительных атрибутов

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

Вспомогательная таблица

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

CREATE TABLE [Orders Security] ( OrderID int CONSTRAINT Order_FK REFERENCES Orders(OrderID), RoleID int CONSTRAINT Role_FK REFERENCES UserRoles(RoleID), CONSTRAINT [Orders_Security_PK] PRIMARY KEY (OrderID, RoleID) )

Теперь предикат безопасности будет выглядеть так:

exists ( select * from [Orders Security] os join UserRoles ur on ur.RoleID = os.RoleID where ur.UserID = GetCurrentUserID() and os.OrderID = OrderID )

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

Теперь можно динамически выдавать или отбирать разрешения на записи таблицы заказов каждой из ролей. Очевидно, что сразу после введения предиката в действие никто ничего не увидит – таблица Orders Security пуста. Давайте выдадим вице-президенту разрешение на доступ ко всем заказам:

insert into [Orders Security] (OrderID, RoleID) select OrderID, @VicePresidentRoleID from Orders

Мы предполагаем, что переменная @VicePresidentRoleID уже проинициализирована соответствующим значением. Обратите внимание на то, что выполнение такой команды требует привилегий администратора БД – нужен доступ на чтение из таблицы Orders и запись в таблицу Orders Security. Отметим также то, что, в отличие от ограничений на основе существующих атрибутов, которые автоматически применяются к новым записям, динамические ограничения требуют модификации вспомогательной таблицы при каждой вставке в основную.

Хранение данных в той же таблице

Рассмотренный выше способ реализации динамических ограничений может оказаться не самым эффективным. Если в случае естественных ограничений, выражаемых при помощи локального предиката, накладные расходы были связаны только с вычислением дополнительных выражений, то теперь в любом случае требуется обращение к вспомогательной таблице. В большинстве случаев список ролей, которым доступна конкретная запись, достаточно короток. Правило №3 нашей воображаемой корпоративной политики безопасности позволяет связать заказы старше полугода с единственной ролью Everyone, так как все остальные роли уже присутствуют в ней. Это означает, что рано или поздно большинство записей таблицы заказов будут связаны с этой ролью.

В связи с этим возникает искушение сохранить список ролей прямо в защищаемой записи. Некоторые СУБД предоставляют возможность хранить списки значений в поле записи, однако это является экзотикой. Поэтому мы выберем другой способ нарушить требования первой нормальной формы – будем хранить список ролей в виде строки. Язык SQL предоставляет нам возможность проверить строку на наличие заданной подстроки при помощи оператора like.

В таблицу Orders придется внести следующие изменения:

alter table orders add ACL varchar(1024) default ','

В поле ACL (Access Control List, список контроля доступа) мы будем хранить список идентификаторов ролей, разделенный запятыми. Поэтому предикат безопасности примет такой вид:

ACL like '%,' + cast(GetCurrentUserRoleID() as varchar(12)) + ',%'

Чтобы упростить задачу серверу, нам придется добавить «лишние» запятые в начале и в конце строки.

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