В таких случаях предикат IsUserInRole(rolename) принимает вид:
| exists(select * from users where ID = CurrentUserID() and user_group = rolename) |
или
| exists(select * from UserRoles where RoleName = rolename and UserID = CurrentUserID()) |
В дальнейшем мы будем подразумевать под выражением IsUserInRole(rolename) либо подходящую встроенную функцию СУБД, либо предикат, построенный вручную в соответствии с выбранной моделью.
Ограничения на основе существующих атрибутов
Если правила корпоративной политики выражаются в терминах предметной области, то можно сформировать соответствующий предикат безопасности в терминах данных, хранимых в СУБД.
В самом простом случае достаточно данных из той же таблицы. Предположим, доступ к еженедельным документам финансовой отчетности компании определяется следующими правилами:
младшие финансовые аналитики имеют право чтения отчетов старше шести месяцев;
старшие финансовые аналитики имеют право чтения отчетов старше одного месяца;
всем остальным сотрудникам доступ к документам запрещен.
В таком случае можно построить примерно такой предикат:
| (IsUserInRole('senior_analyst') and ReportDate < DateAdd(month, GetDate(), 1)) OR (IsUserInRole('junior_analyst') and ReportDate < DateAdd(month, GetDate(), 6)) |
В принципе, тот же самый результат можно получить и другими способами. Например, вот такое выражение SQL отражает те же самые правила:
| case when ReportDate > DateAdd(month, GetDate(), 1) then false when ReportDate > DateAdd(month, GetDate(), 6) then IsUserInRole('senior_analyst') else IsUserInRole('junior_analyst')end |
Однако здесь проследить логику уже труднее, и ситуация станет еще хуже, когда в рассмотрение войдут другие поля таблицы. Поэтому мы ограничим возможные предикаты вот такой структурой:
| (IsUserInRole(<role1>) AND <role1_restrictions>) OR (IsUserInRole(<role2>) AND <role2_restrictions>) OR ...(IsUserInRole(<roleN>) AND <roleN_restrictions>) |
Таким образом, предикат определяет для каждой группы пользователей требования, которым должна удовлетворять строка таблицы, чтобы доступ к ней был разрешен.
Лишение доступа
Форма предиката безопасности, рассмотренная выше, подразумевает «пессимистичный» режим, т.е. доступ к данным имеют только те категории пользователей, которые явно перечислены в предикате.
| ПРИМЕЧАНИЕСтрого говоря, если мы не опишем ни одного правила, то предикат будет пустым и доступ получат все пользователи. Однако выдача таким способом доступа хотя бы одной роли автоматически лишит доступа всех остальных пользователей. Решить эту проблему можно раз и навсегда - введя дополнительный член ...OR FALSE в предикат безопасности. Однако подобный вырожденный случай нетрудно обработать специальным образом, и далее в тексте везде предполагается наличие в списке доступа хотя бы одного явного разрешения. |
Иногда удобнее описывать правила безопасности в терминах «оптимистичного» режима, т.е. лишить доступа только некоторое множество пользователей. В этом случае предикат приобретет такой вид:
| NOT( (IsUserInRole(<restricted_role1>) [AND <restricted_role1_restrictions>]) OR ... (IsUserInRole(<restricted_roleN>) [AND <restricted_roleN_restrictions>])) |
В таком случае доступ предоставляется всем, кроме указанных ролей, да и они лишены доступа только к ограниченной части данных.
Соединив эти два подхода, мы получим возможность как «карать», так и «миловать» пользователей:
| (-- секцияразрешений (IsUserInRole(<role1>) AND <role1_restrictions>) OR ... (IsUserInRole(<roleN>) AND <roleN_restrictions>))AND NOT(-- секциязапретов (IsUserInRole(<restricted_role1>) [AND <restricted_role1_restrictions>]) OR ... (IsUserInRole(<restricted_roleN>) [AND <restricted_roleN_restrictions>])) |
Такой принцип защиты очень похож на используемый в 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.