Oracle security policies

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

Пример, который я приведу, сильно упрощен, но необходимые средства, используемые в решении, предоставлены исчерпывающе. Протестировано на Oracle 11g R2.

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

Установим соединение с БД от имени SCOTT и создадим таблицы, одна из которых будет содержать список пользователей имеющих доступ, а вторая сами данные:

CREATE TABLE scott.user_allowed (   ua_id        NUMBER,   oracle_user  VARCHAR2(50) );  Table created.  insert into scott.user_allowed (ua_id, oracle_user) values (1, 'SCOTT'); insert into scott.user_allowed (ua_id, oracle_user) values (2, 'OTHERUSER');  1 row created. 1 row created.  CREATE TABLE scott.user_data (   ud_id       NUMBER,   ud_user_id  NUMBER,   ud_data     VARCHAR2(2000) );  Table created.  insert into scott.user_data (ud_id, ud_user_id, ud_data) values (1, 1, 'SCOTT DATA');      insert into scott.user_data (ud_id, ud_user_id, ud_data) values (2, 2, 'OTHERUSER DATA');  1 row created. 1 row created.  commit;  select ud_id, ud_user_id, ud_data from user_data;  2 rows selected. 

Всё прошло хорошо. Теперь выполним соединение от имени SYS, создадим функцию предикат и политику безопасности:

CREATE OR REPLACE FUNCTION sys.scott_predicate_allow  (    obj_schema IN VARCHAR2, obj_name IN VARCHAR2 ) RETURN VARCHAR2 IS     userid number; BEGIN     select ua_id into userid from scott.user_allowed where upper(oracle_user) = upper(USER);     return 'ud_id in (SELECT ud_id FROM scott.user_data WHERE ud_user_id = ' || userid || ')'; END scott_predicate_allow; /  Function created. 

BEGIN   SYS.DBMS_RLS.ADD_POLICY     (       Object_schema         => 'SCOTT'      ,Object_name           => 'USER_DATA'      ,policy_name           => 'SCOTT_ALLOW_USER_DATA'      ,function_schema       => 'SYS'      ,policy_function       => 'SCOTT_PREDICATE_ALLOW'      ,statement_types       => 'SELECT,INSERT,UPDATE,DELETE,INDEX '      ,policy_type           => dbms_rls.dynamic      ,long_predicate        => FALSE      ,sec_relevant_cols     => 'UD_ID,UD_USER_ID,UD_DATA'      ,sec_relevant_cols_opt => NULL      ,update_check          => FALSE      ,enable                => TRUE     ); END; / PL/SQL procedure successfully completed. 

Вот и всё, теперь пользователь SCOTT при обращении к таблице USER_DATA, будет видеть только те данные, которые в столбце ud_user_id принадлежат ему, в соответствии с настройкой в таблице USER_ALLOWED. Осталось это проверить, снова выполним вход от имени SCOTT и:

select ud_id, ud_user_id, ud_data from user_data;  ORA-28113: policy predicate has error 

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

ORA-28108: circular security policies detected  

По сути дела, я бы назвал это аналогом мутации в триггере. Для решения возникшей проблемы, можно использовать… как показала практика, мало чего. Хотите, попробуйте сами, но учитывая, что реальная задача, будет сильно отличаться от приведенного примера. Вернемся к соединению от имени SYS, и выполним:

create or replace type sys.number_table is table of number; /  Type created.  CREATE OR REPLACE PACKAGE SYS.SCOTT_ALLOWED AS      allowed_id number_table:=number_table();          function get_allowed_id return number_table;      END SCOTT_ALLOWED; /  Package created.  CREATE OR REPLACE PACKAGE BODY SYS.SCOTT_ALLOWED AS      function get_allowed_id return number_table is     begin         return allowed_id;     end;          END SCOTT_ALLOWED; /  Package body created.  CREATE OR REPLACE FUNCTION sys.scott_predicate_allow  (    obj_schema IN VARCHAR2, obj_name IN VARCHAR2    ) RETURN VARCHAR2 IS     userid number; BEGIN          select ua_id into userid from scott.user_allowed where upper(oracle_user) = upper(USER);          scott_allowed.allowed_id:=number_table();          select /*+ NOCACHE(ud) */ ud_id bulk collect into scott_allowed.allowed_id       from scott.user_data ud      where ud_user_id = userid;      return 'EXISTS (SELECT 1 FROM TABLE(scott_allowed.get_allowed_id) a WHERE a.column_value = ud_id)';      END scott_predicate_allow; /  Function created. 

Первое, что было сделано, это объявлен новый тип данных, отражающий таблицу из одного столбца типа NUMBER. Этот тип, будет использован для переменной пакета, в которую будет выбран список идентификаторов, по которым будет предоставлен доступ. Функция в пакете, является оберткой, позволяющей выполнить обратное обращение к данным. Проблем с содержимым переменной пакета, между сессиями возникать не будет, так как для каждой сессии выделяется отдельная область памяти. Использование хинта NOCACHE, в функции предикате, необходимо для устранения ошибки политики безопасности, после изменения данных в строках, так как кэшированный запрос будет становиться устаревшим после изменения данных таблицы. Подразумевая, что цель курсора в функции предикате – определение идентификаторов первичного ключа таблицы, формируемое ограничение целесообразнее модифицировать в конструкцию EXISTS.
Теперь проверим результат проделанной работы, выполнив соединение от имени SCOTT:

select ud_id, ud_user_id, ud_data from user_data;  1 row selected.