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.

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.