Oracle security policies

Источник: habrahabr

AndyBW

В этой статье мне бы хотелось рассказать о решении, которое позволяет организовать ограничение доступа к данным на уровне СУБД, причем в том случае, когда ограничиваемая таблица сама содержит данные влияющие на ограничение. Пример, который я приведу, сильно упрощен, но необходимые средства, используемые в решении, предоставлены исчерпывающе. Протестировано на 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.  commit;  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 BEGIN return 'ud_id in (SELECT ud_id FROM scott.user_data ud, scott.user_allowed ua WHERE ud.ud_user_id = ua.ua_id AND upper(ua.oracle_user) = upper(USER))'; 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 BEGIN  scott_allowed.allowed_id:=number_table();      select ud_id bulk collect into scott_allowed.allowed_id from scott.user_data ud, scott.user_allowed ua where ud.ud_user_id = ua.ua_id and upper(ua.oracle_user) = upper(USER);  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. Этот тип, будет использован для переменной пакета, в которую будет выбран список идентификаторов, по которым будет предоставлен доступ. Функция в пакете, является оберткой, позволяющей выполнить обратное обращение к данным. Проблем с содержимым переменной пакета, между сессиями возникать не будет, так как для каждой сессии выделяется отдельная область памяти. Подразумевая, что цель курсора в функции предикате - определение идентификаторов первичного ключа таблицы, формируемое ограничение целесообразнее модифицировать в конструкцию EXISTS. Теперь проверим результат проделанной работы, выполнив соединение от имени SCOTT:

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

Использование хранимых шаблонов при настройке приложений с недоступным исходным кодом.

Уровни изолированности транзакций.

Согласованность данных при изменении.

USING - ключевое слово PL/SQL в версии 9i.

Ускорение вставки.

Главная » Sql