Базы данных Oracle - статьи
Подготовка к использованию 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.
