Джонатан Льюис (Jonathan Lewis)
www.jlcomp.demon.co.uk
В предыдущей статье этой мини-серии я продемонстрировал пару простых методов обеспечения изоляции данных различных пользователей или групп пользователей. В этой статье я перехожу к "правильной" защите на уровне строк (RLS), также известной как средства детального контроля доступа (fine-grained access control - FGAC) или средства создания виртуальной приватной базы данных (virtual private database - VPD). Примеры в этих статьях были протестированы с помощью Oracle 9.2.0.3.
Требования
В исходном примере изоляции данных в первой статье была использована таблица со столбцом owner, на основе которой было построено статическое представление, выдающее результирующее множество, зависящее от обращающегося пользователя. Определение этого представления было очень простым и требовало одного условия на этот единственный столбец, а в качестве дополнительного кода потребовался только триггер уровня таблицы. Но что делать при наличии множества различных требований разделения данных, и одного простого условия недостаточно?
Рассмотрим (несколько запутанный) пример системы, используемой сетью супермаркетов для поддержки хранилища данных. Ключевая таблица, показывающая ежедневный уровень запасов товаров, имеет следующее определение:
create table stock_level ( stock_date date, product_id number(6), qty number(8), dept_id varchar2(20), supplier_code varchar2(20));
Таблица показывает уровень запасов по датам и товарам. Таблица денормализована и содержит название отдела в супермаркете, отвечающего за данный товар, а также внешнего поставщика товара.
Супермаркет использует web-систему, позволяющую внешним поставщикам запрашивать уровни запасов по продуктам, которые они поставляют в супермаркет. Внутренне то же приложение позволяет разным отделам запрашивать хранилище данных, но обращаться только к тем данным, которые имеют отношение к соответствующему отделу.
Можно, конечно, применить методы, изложенные в предыдущей статье, и создать два представления этих данных, по одному для каждого способа доступа. Побочный эффект этого подхода, однако, состоит в потенциальной необходимости создания двух экземпляров всех заранее заготовленных отчетов, всех процедур передачи данных и т.д. Ну и, конечно, может потребоваться система с более чем двумя разными наборами требований. Чтобы свести усложения к минимуму или, по крайней мере, сконцентрировать их в одном месте, можно использовать пакет dbms_rls для создания правил защиты (security policies).
Правила защиты
Цель средств детального контроля доступа - позволить вам централизованно скрыть все сложности "сокрытия данных".
Для этого необходимо выполнить два обязательных шага, а также два дополнительных необязательных шага, которые могут и не потребоваться в системе построения отчетов. Во-первых, необходимо создать функцию, получающую на входе имя схемы и имя объекта, и выдающую текст допустимой конструкции where; во-вторых, необходимо связать эту функцию с целевой таблицей, перечислив действия, для которых эта функция должна выполняться. Два необязательных шага были описаны в предыдущей статье - создание табличного триггера для установки соответствующих значений управляющих столбцов и триггера базы данных для установки пользовательской "среды" или переменных контекста. Может также иметь смысл создать одно представление на основе таблицы, чтобы скрыть столбцы, устанавливаемые табличным триггером.
В этой статье мы будем предполагать, что пакетный процесс загружает таблицу, а конечным пользователям разрешено только читать таблицу, так что можно проигнорировать особенности вставок, изменений и удалений, и сконцентрироваться на обработке только операторов select.
Подготовка к использованию RLS
В этом примере функции, генерирующей условие (в которой используется оператор case, появившийся в версии 9), мы по имени пользователя будем генерировать условие, ограничивающее строки по отделу или поставщику. В более реалистической ситуации для определения того, какое условие генерировать, вероятно, использовались бы какие-то справочные таблицы, а для различения локальных сотрудников и внешних поставщиков можно использовать глобальную установку контекста на уровне промежуточном уровне (middle tier).
create or replace function stock_predicate(
i_schema in varchar2,
i_object in varchar2) return varchar2
as
begin
return
case (sys_context('userenv','session_user'))
when 'U1' then 'supplier_code = ''Hershey'''
when 'U2' then 'dept_id = ''Confection'''
when 'TEST_USER' then null
else '1 = 0'
end;
end;
/
Следует обратить особое внимание на три особенности.
Во-первых, в качестве входных параметров передаются имя схемы и имя объекта (обычно - таблицы или представления), к которому, в конечном итоге, будет применено сгенерированное условие. Поскольку имя объекта передается как входной параметр, можно создавать условия с коррелированными подзапросами, используя входной параметр в качестве коррелирующего имени для ведущей таблицы. (Генерировать условия такого типа - не лучшая идея, однако, поскольку они могут катастрофически снизить производительность).
Вторая особенность связана с личными предпочтениями - я люблю всегда подстраховываться. Если все проверки в функции, генерирующей условие, не сработают, я люблю возвращать всегда ложное условие (такое как '1 = 0'), которое позволит оптимизатору вообще не возвращать данные, обычно - очень эффективно, а наилучшим действием по умолчанию с точки зрения защиты и будет сокрытие всего.
Наконец, обратите внимание, что я добавил одну проверку, возвращающую пустое условие. Пользователь test_user является владельцем таблицы и функции, поэтому по практическим соображениям я гарантировал, что у владельца данных не будет ограничений при их просмотре или обработке.
Создав функцию, которая может возвращать строку для конструкции where, мы теперь связываем эту функцию с таблицей. (При необходимости, можно связать одну и ту же функцию с множеством разных таблиц). Для этого мы вызываем процедуру пакета dbms_rls.
begin dbms_rls.add_policy ( object_schema => 'test_user', object_name => 'stock_level', function_schema => 'test_user', policy_function => 'stock_predicate', statement_types => 'select, insert, update, delete', update_check => TRUE, policy_name => 'stock_restrict', enable => TRUE, static_policy => FALSE -- только в v9 ); end; /
В этом вызове процедуры add_policy мы указали нашу таблицу и написанную ранее функцию. Мы также указываем, что мы хотим создавать условие для операторов select, insert, update и delete. Параметр update_check немного напоминает конструкцию "with check option" для представлений; он гарантирует, что мы не сможем вставить или изменить строку так, что после вставки или изменения не сможем ее увидеть. Наконец, мы дали набору правил (сочетанию объекта, функции и действий) имя и разрешили его использовать. Последний параметр процедуры, static_policy, очень важен - к нему я ещё вернусь.
Если теперь мы вставим тестовые данные, то сможем увидеть влияние правил. Мы начнем с подключения от имени владельца таблицы (test_user) для загрузки данных.
insert into stock_level values(sysdate,1,100,'Confection','Hershey'); insert into stock_level values(sysdate,2,60,'Deli','Hershey'); insert into stock_level values(sysdate,3,60,'Confection','Cadbury'); insert into stock_level values(sysdate,4,60,'Deli','Cadbury'); commit;
Если подключиться и запросить данные от имени этого пользователя, мы увидим все четыре строки. Однако, если подключиться как пользователь u1 и выполнить select * from test_user.stock_level, мы увидим:
STOCK_DAT PRODUCT_ID QTY DEPT_ID SUPPLIER_CODE --------- ---------- ---------- -------------------- ------------- 19-OCT-03 1 100 Confection Hershey 19-OCT-03 2 60 Deli Hershey
А если подключиться от имени пользователя u2 и выполнить тот же запрос, мы увидим:
STOCK_DAT PRODUCT_ID QTY DEPT_ID SUPPLIER_CODE --------- ---------- ---------- -------------------- ------------- 19-OCT-03 1 100 Confection Hershey 19-OCT-03 3 60 Confection Cadbury
Как видите, каждый пользователь получает свой набор данных. Исходный запрос был изменен "на лету", ссылка на таблицу stock_level была заменена сслыкой на вложенное представление, содержащее наше сгенерированное условие (подумайте, как это может сказаться на эффективности, особенно при использовании сложных внешних соединений). Например, простой оператор select, выполненный пользователем u2, будет преобразован в:
Select * from ( select * from stock_level where dept_id = 'Confection')
Кстати, если вы получаете сообщение об ошибке Oracle ORA-28113: policy predicate has error, то, вероятно, вы сделали опечатку при копировании всех повторяющихся апострофов в функции, задающей правила - сервер Oracle сообщает вам, что сгенерированный им текст не позволяет построить допустимую конструкцию where.
Проблемы
С этим механизмом связано несколько неизбежных проблем. Для начала, в Oracle 8.1 нигде в системе вообще нельзя увидеть сгенерированное условие - ни в представлении v$sql, ни в трассировочных файлах измененного SQL-оператора просто нет. Эту проблему можно обойти, установив для sql_trace значение true, а затем установив событие 10730 в сеансе, использующем RLS. После этого каждый полный разбор (hard parse) оператора будет генерировать раздел в трассировочном файле, который будет иметь примерно следующий вид:
Logon user : U1 Table/View : TEST_USER.STOCK_LEVEL Policy name : STOCK_RESTRICT Policy function: TEST_USER.STOCK_PREDICATE RLS view : SELECT "STOCK_DATE","PRODUCT_ID","QTY","DEPT_ID","SUPPLIER_CODE" FROM "TEST_USER". "STOCK_LEVEL" "STOCK_LEVEL" WHERE (supplier_code = 'Hershey')
Эта проблема была решена в Oracle 9 (хотя эффективность решения вызывает некторые сомнения) путем добавления представления v$vpd_policy. Простой запрос к этому представлению может дать следующую информацию:
ADDRESS : 6F5664F0 PARADDR : 6F5638AC SQL_HASH : 1816753535 CHILD_NUMBER : 0 OBJECT_OWNER : TEST_USER OBJECT_NAME : STOCK_LEVEL POLICY_GROUP : SYS_DEFAULT POLICY : STOCK_RESTRICT POLICY_FUNCTION_OWNER : TEST_USER PREDICATE : supplier_code = 'Hershey'
По значениям столбцов paraddr, sql_hash и child_number из этого представления можно сделать примерно такой запрос к представлению v$sql, находящий соответствующий SQL-оператор:
Select sql_text from v$sql where address = '6F5F0020' and hash_value = 2621366196 and child_number= 0
Неэффективность здесь в том, что представление v$vpd_policy строиться, в том числе, по объекту x$kglcursor, который и так является базовым для представления v$sql - так что вы вполне можете задать собственную версию представления v$vpd_policy во избежание бессмысленного дополнительного соединения. Но и в этом случае нет эффективной связи между двумя объектами x$, лежащими в основе представления.
Но есть и другие проблемы - я обещал еще раз упомянуть о параметре static_policy процедуры add_policy. Этот булев параметр появился в Oracle 9, чтобы вы могли выбрать из двух зол. Если установить этому параметру значение true, то обеспечивающее защиту условие, похоже, будет генерироваться только один раз, при первом полном разборе, а это означает, что пользователь u2 в конечном итоге сможет выполнять в точности тот же запрос, что и пользователь u1, если окажется, что пользователь u1 первым выполнил запрос.
С другой стороны, если установить этому параметру значение false, то функция защиты выполняется (предположительно, дважды) при каждом выполнении (а не только разборе) запроса, и выполняется она в следующем, достаточно объемном анонимном pl/sql-блоке, который не слишком способствует параллелизму и масштабируемости .
begin p := STOCK_PREDICATE(:sn,:on); :v1 := substr(p,1,4000); :v2 := substr(p,4001,4000); :v3 := substr(p,8001,4000); :v4 := substr(p,12001,4000); :v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000); :v7 := substr(p,24001,4000); :v8 := substr(p,28001,4000); :v9 := substr(p,32001,767); :v10 := substr(p, 4000, 1); :v11 := substr(p,8000,1); :v12 := substr(p, 12000, 1); :v13 := substr(p,16000,1); :v14 := substr(p, 20000, 1); :v15 := substr(p,24000,1); :v16 := substr(p, 28000, 1); :v17 := substr(p,32000,1); end;
Я подозреваю, что это изменение было сделано как "аварийное" в ответ на обнаружившееся отсутствие необходимой перегенерации условий защиты, связанных с моментом времени. Ответ, однако, несколько экстремальный. Проблема была решена несколько более тонко в версии 10g сервера Oracle, где появилось несколько уровней "изменяемости" в качестве типа набора правил.
Завершающее соображение - есть рекомендация Oracle не использовать, по возможности, соединения с таблицами, для которых предполагается подобная защита (подумайте, к чему могут привести все эти вложенные представления) и постараться свести условия защиты к простому использованию функции sys_context(). Но посмотрите, что говорит руководство SQL Reference (версия 9.2, стр. 6-154) о функции sys_context():
Примечание: SYS_CONTEXT возвращает атрибуты сеанса. Поэтому ее нельзя использовать в параллельных запросаз или в среде Real Application Clusters.
Вывод
Защиту на уровне строк легко спроектировать и установить, но у нее есть последствия, о которых следует знать. В частности, если вы используете механизм RLS в версии 8, то можете получить неприятный сюрприз при переходе на версию 9. Для сравнительно простых требований я не думаю, что вам действительно надо делать что-то кроме продуманного создания представлений, описанных в первой статье этой серии.