В этой статье мне бы хотелось рассказать о решении, которое позволяет организовать ограничение доступа к данным на уровне СУБД, причем в том случае, когда ограничиваемая таблица сама содержит данные влияющие на ограничение.
Пример, который я приведу, сильно упрощен, но необходимые средства, используемые в решении, предоставлены исчерпывающе. Протестировано на 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.
Автор: AndyBW
