PL/SQL: безопасность уровня строк (RLS) с пакетом DBMS_RLS

безопасность уровня строк с использованием DBMS_RLS PL/SQLВведенный в Oracle8i механизм безопасности уровня строк, или RLS (Row Level Security), позволяет определять для таблиц (и конкретных типов операций с таблицами) политики безопасности, ограничивающие возможности просмотра и изменения строк таблиц пользователями. Большая часть соответствующей функциональности реализуется встроенным пакетом DBMS_RLS.



В Oracle в течение многих лет безопасность обеспечивалась на уровне таблиц, и в определенной степени на уровне столбцов. Пользователям могли предоставляться привилегии, разрешающие или ограничивающие доступ отдельными столбцами. Триггеры INSTEAD OF позволяли ограничить обновление таблиц из представлений. Все эти привилегии основывались на одном предположении: безопасность может быть реализована простым ограничением доступа к некоторым таблицам и столбцам. Но когда пользователь получает доступ к таблице, он видит все ее строки. А если потребуется ограничить видимость строк таблицы на основании таких критериев, как уровень полномочий текущего пользователя, или других характеристик конкретного приложения? Для примера возьмем демонстрационную таблицу EMP из схемы HR, входящей в поставку Oracle. Таблица содержит 14 строк данных с первичными ключами (кодами работников) от 7369 до 7934.

Допустим, вы предоставили пользователю Lora доступ к таблице, но хотите ограничить его так, чтобы пользователь мог видеть и изменять строки только тех пользователей, у которых поле COMM отлично от NULL.

Проблему можно решить созданием представления на базе таблицы, но что если пользователь должен иметь доступ к самой таблице? В некоторых случаях доступ к таблице может быть оправданным — например, если пользователю потребуется создавать хранимые программные блоки для работы с таблицей. Реализация с представлением для этого просто не подойдет.

В таких случаях на помощь приходит RLS. Фактически вы приказываете Oracle ограничить набор строк, видимых пользователю, на основании определяемого вами правила. Пользователь никак не сможет обойти это ограничение.

В документации Oracle RLS также иногда обозначается сокращениями VPD (Virtual Private Database) и FGAC (Fine-Grained Vccess Control).

Например, если активизировать RLS для таблицы EMP с описанным выше правилом, то при вводе запроса 

SELECT * FROM emp

пользователь Lora увидит только четыре строки (а не 14!), хотя сам запрос не включает секцию WHERE:

7499 ALLEN      SALESMAN    7698 20-FEB-81  1,600    300      30
7521 WARD       SALESMAN    7698 22-FEB-81  1,250    500      30
7654 MARTIN     SALESMAN    7698 28-SEP-81  1,250  1,400      30
7844 TURNER     SALESMAN    7698 08-SEP-81  1,500      0      30

Аналогичным образом, при обновлении таблицы без условия WHERE будут обновлены только видимые пользователю записи:

SQL> UPDATE hr.emp SET comm = 100
  2  /

4 rows updated.

Остальные 10 записей словно не существуют для пользователя Lora. Реализация RLS основана на включении предиката (условия WHERE) в любые DML-инструкции, выполняемые пользователем с таблицей. В нашем примере запрос SELECT * FROM EMP автоматически преобразуется к виду

SELECT * FROM emp WHERE comm IS NOT NULL

Чтобы для таблицы действовали автоматические ограничения доступа, необходимо определить для нее политику RLS. Политика определяет наличие возможных ограничений при обращении к данным. Допустим, вы хотите, чтобы операции UPDATE были недоступны для пользователей, а операции SELECT выполнялись без каких-либо ограничений или чтобы доступ к SELECT ограничивался только при выборке определенного столбца (скажем, SALARY). Все эти инструкции включаются в политику. Политика связывается с функцией, которая генерирует предикат (COMM IS NOT NULL в нашем примере), применяемый к запросам.

Итак, на верхнем уровне абстракции RLS состоит из трех основных компонентов:

  •  Политика — декларативная команда, которая определяет, когда и как применяются ограничения: при выборке, вставке, удалении, обновлении или в комбинациях этих операций.
  •  Функция политики — функция PL/SQL, вызываемая при выполнении условий, заданных в политике.
  •  Предикат — строка, которая генерируется функцией политики, а затем включается в SQL-инструкции пользователей для определения ограничивающих условий.

На концептуальном уровне схема действия RLS показана на рис. 1. Политика представляет собой «фильтр» для отбора строк таблицы. Если строка удовлетворяет предикату, она проходит через фильтр; в противном случае строка остается невидимой для пользователя.

Инфраструктура безопасности уровня строк

Рис. 1. Инфраструктура безопасности уровня строк

 

 

Зачем изучать RLS?

На первый взгляд тема безопасности уровня строк представляет интерес для администраторов баз данных и администраторов безопасности, а не для разработчиков PL/ SQL и даже проектировщиков архитектуры приложений. Зачем разработчику PL/SQL изучать ее?

  • В наши дни безопасность — дело общее. Механизм RLS предназначен для обеспечения безопасности, которая традиционно относилась к ведению администраторов баз данных. Однако в XXI веке безопасность становится все более насущной проблемой. Бесчисленные законы, правила и рекомендации ограничивают наши приложения, и разработчикам все чаще приходится учитывать аспекты безопасности при построении программ. В частности, проектировщик архитектуры приложения должен хорошо разбираться в применении RLS на ранних стадиях проектирования.
  •  RLS — не только безопасность. По сути, концепция RLS определяет некий «фильтр», находящийся под управлением разработчика функции. Допустим, вам приходится обеспечивать поддержку стороннего приложения с двумя функциональными областями, данные которых хранятся в одной таблице; вам потребовалось внести изменения в запросы приложения, чтобы эти функциональные области более четко отделялись друг от друга. Однако для этого придется вносить изменения в код приложения, что может быть нежелательно. На помощь приходит RLS. Этот механизм позволяет реализовать логическое разбиение строк таблицы, чтобы два приложения «видели» разные наборы данных. Безусловно, такой подход упрощает задачу разработчика и сопровождение системы в целом.
  •  RLS позволяет делать то, что не может быть сделано другими средствами. Не забывайте, что RLS применяет к запросам предикат, генерируемый функцией. Если вы сгенерируете предикат 1=2, результат которого всегда равен FALSE, что выдаст ваш запрос? Ничего, потому что условие WHERE всегда ложно. Таким образом, определение политики 1=2 для команд DELETE, INSERT и UPDATE, но не для SELECT, фактически запрещает пользователю изменять таблицу, но не мешает чтению данных. Oracle по умолчанию позволяет устанавливать ограничение доступа «только для чтения» для табличных пространств, но не для конкретных таблиц. RLS позволяет сделать доступной только для чтения отдельную таблицу.

Другие средства не подходят: если просто лишить пользователя привилегий UPDATE или INSERT, процедуры станут недействительными. Если определить процедуру с использованием модели прав создающего, то вам не удастся отозвать привилегии у конкретного пользователя.

Несколько простых примеров помогут вам понять принципы работы RLS.

 

Простой пример использования RLS

В этом примере используется таблица EMP, уже упоминавшаяся ранее в этой статье. В результате применения RLS пользователи не должны видеть строки с информацией о работниках, оклад которых превышает $1500. Чтобы реализовать политику, мы должны определить следующие компоненты:

  •  Предикат, который будет автоматически включаться в команды SQL пользователей.
  •  Функция политики, генерирующая этот предикат.
  •  Политика, которая вызывает функцию и применяет предикат (прозрачно с точки зрения пользователя).

В нашем примере предикат выглядит так:

SALARY <= 1500

Функции политики:

FUNCTION authorized_emps (
   p_schema_name   IN   VARCHAR2,
   p_object_name   IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   l_return_val   VARCHAR2 (2000);
BEGIN
   l_return_val := 'SAL <= 1500';
   RETURN l_return_val;
END authorized_emps;

При выполнении эта функция возвращает строку SAL <= 1500. Следующий фрагмент кода это наглядно доказывает:

DECLARE
   l_return_string   VARCHAR2 (2000);
BEGIN
   l_return_string := authorized_emps ('X', 'X');
   DBMS_OUTPUT.put_line ('Return String = "' || l_return_string || '"');
END;

Результат:

Return String = "SAL <= 1500"

Резонно спросить, зачем передавать аргументы, если функция всегда возвращает одно и то же значение? Смысл этого требования RLS будет объяснен позднее.

Наконец, мы создаем политику при помощи функции ADD_P0LICY из встроенного пакета DBMS RLS:

1    BEGIN
2       DBMS_RLS.add_policy (object_schema  => 'HR',
3              object_name          => 'EMP',
4              policy_name          => 'EMP_POLICY',
5              function_schema      => 'HR',
6              policy_function      => 'AUTHORIZED_EMPS',
7              statement_types      => 'INSERT, UPDATE, DELETE, SELECT'
8             );
9    END;

Политика с именем EMP_POLICY (строка 4) добавляется для таблицы EMP (строка 4), принадлежащей схеме HR (строка 2). Политика применяет фильтр, генерируемый функцией AUTH0RIZED_EMPS (строка 6), принадлежащей схеме HR (строка 5), при каждом выполнении пользователем операции INSERT, UPDATE, DELETE или SELECT (строка 7). Функция AUTH0RIZED_EMPS, которая создает и возвращает предикатные строки, применяемые к запросам, была написана ранее.

После установления этой политики при выборке данных из таблицы или их обновлении пользователь сможет работать только с теми записями, для которых выполняется условие SAL <= 1500.

В зависимости от потребностей приложения функция политики может определяться произвольно, но она должна удовлетворять некоторым правилам:

  •  Функция политики может быть отдельной или пакетной функцией, но не может быть процедурой.
  •  Функция политики должна возвращать значение типа VARCHAR2, которое будет применяться как предикат. В частности, из этого следует, что длина предиката не может превышать 32 767 байт.
  •  Функция политики должна получать ровно два входных параметра, которые передаются в следующем порядке:
  • schema — схема-владелец таблицы, для которой определяется политика;
  • objectname — имя объекта, идентифицирующее таблицу или представление. 

Чтобы снять все ограничения доступа, укажите функцию политики, которая возвращает в качестве предиката один из следующих вариантов:

  •  NULL.
  •  1=1 или другое выражение, результат которого всегда равен TRUE.

Так как возвращаемое значение должно относиться к типу VARCHAR2, просто вернуть TRUE не удастся.

Аналогичным образом устанавливается ограничение для всех строк: выбирается предикат, результат которого всегда равен FALSE — например, 1=2.

Для таблицы можно определить несколько политик RLS. Приоритетов, то есть фиксированного порядка применения политик к запросам, не существует. К SQL-запросу присоединяются предикаты, возвращаемые всеми политиками.

Информацию обо всех политиках, определенных для таблицы, можно получить из представления словаря данных DBA_POLICIES: имя политики, объект, для которого она определена (и его владелец), имя функции политики (и ее владелец) и многие другие сведения. Начиная с Oracle10g, параметр statement_types может принимать дополнительное значение INDEX. В этом случае доступ к строкам ограничивается даже при создании индексов.

Допустим, вы хотите создать индекс по ключу-функции для столбца SAL; сценарию создания индекса для этого понадобятся все значения столбца, то есть фактически система безопасности будет обойдена. Пример включения значения INDEX в параметр:

1    BEGIN
2       DBMS_RLS.add_policy (object_schema  => 'HR',
3              object_name          => 'EMP',
4              policy_name          => 'EMP_POLICY',
5              function_schema      => 'HR',
6              policy_function      => 'AUTHORIZED_EMPS',
7              statement_types      => 'INSERT, UPDATE, DELETE, SELECT, INDEX'
8             );
9    END;

При попытке создания индекса по ключу-функции будет выдана ошибка:

ORA-28133: full table access is restricted by fine-grained security

Итак, вы научились создавать политики. Для удаления политик используется функция DROP_POLlCY из пакета DBMS_RLS. Например, удаление политики EMP_P0LICY осуществляется следующей командой:

BEGIN
   DBMS_RLS.drop_policy (object_schema      => 'HR',
                         object_name        => 'EMP',
                         policy_name        => 'EMP_POLICY'
                        );
END;

Обратите внимание: политики не являются объектами схемы базы данных, то есть у них нет объекта-владельца. Любой пользователь с привилегией EXECUTE для пакета DBMS_RLS может создать политику. Аналогичным образом, любой пользователь с привилегией EXECUTE может удалить политику. Следовательно, привилегии EXECUTE для этого пакета должны предоставляться только тем пользователям, которым они действительно необходимы.

Рассмотрим один нюанс: вместо других столбцов пользователь обновляет столбец SAL, который используется в предикате. Интересно увидеть результат:

SQL> UPDATE hr.emp SET sal = 1200;

7 rows updated.

SQL> UPDATE hr.emp SET sal = 1100;

7 rows updated.

Как и ожидалось, обновляются только семь строк. Теперь давайте изменим обновляемое значение:

SQL> UPDATE hr.emp SET sal = 1600;

7 rows updated.

SQL> UPDATE hr.emp SET sal = 1100;

0 rows updated.

Естественно, при втором обновлении строки остаются неизменными, потому что первое обновление сделало все строки в таблице недоступными для пользователя — ведь политика RLS устанавливает фильтрующий предикат SAL <= 1500.

Возникает весьма запутанная ситуация: сами обновления данных могут изменять видимость строк таблицы. В ходе разработки это может породить ошибки или по крайней мере создать определенную непредсказуемость в работе программы. Чтобы предотвратить путаницу, давайте воспользуемся другим параметром DBMS_RLS.ADD_P0LICY, который называется update_check. Следующий пример показывает, к каким последствиям приводит присваивание этому параметру TRUE при создании политики для таблицы:

BEGIN
   DBMS_RLS.add_policy (
          object_name          => 'EMP',
          policy_name          => 'EMP_POLICY',
          function_schema      => 'HR',
          policy_function      => 'AUTHORIZED_EMPS',
          statement_types      => 'INSERT, UPDATE, DELETE, SELECT',
          update_check         => TRUE
          );
END;

После того как данная политика будет установлена для таблицы, при выполнении того же обновления пользователь Lora получит сообщение об ошибке:

SQL> UPDATE hr.emp SET sal = 1600;
UPDATE hr.emp SET sal = 1600
          *
ERROR at line 1:
ORA-28115: policy with check option violation

Ошибка ORA-28115 возникает из-за того, что новая политика запрещает обновление столбцов значениями, изменяющими видимость строк под действием RLS. Но допустим, столбец SAL обновляется значением, не изменяющим видимости строк:

SQL> UPDATE hr.emp SET sal = 1200;

7 rows updated.

Так как с новым значением столбца SAL — 1200 — все семь строк по-прежнему остаются видимыми, такое обновление разрешается.

Присвойте параметру update_check значение TRUE при определении политики, чтобы избежать непредсказуемого (по крайней мере на первый взгляд) поведения приложения.

 

Статические и динамические политики

В предыдущем примере использовалась политика, которая возвращала предикатную строку с константой (SAL <= 1500). В реальных приложениях такие ситуации встречаются редко (разве что в специализированных приложениях — например, в системах складского учета). Чаще фильтры приходится определять в зависимости от пользователя, выдающего запрос. Например, приложение для отдела кадров может ограничивать пользователя просмотром записей, относящихся к его отделу. Такое требование является динамическим, так как условие проверки вычисляется заново для каждого пользователя, работающего с приложением.

И это не единственное правило, которое необходимо применить в данной ситуации. Таблица защищена политикой RLS, запрещающей пользователям просмотр всех записей. Но что если выборку выполнит сам владелец таблицы (пользователь HR)? Он тоже увидит только ограниченное подмножество записей, а это неправильно: владелец должен видеть все записи без исключения. Возможны два варианта:

  •  Предоставить специальные привилегии пользователю HR, чтобы политика RLS на него не распространялась.
  •  Определить функцию политики таким образом, что если вызывающий пользователь является владельцем схемы, ограничительный предикат для него не действовал.

В первом случае функцию политики изменять не нужно — администратор базы данных может предоставить пользователю HR необходимые привилегии:

GRANT EXEMPT ACCESS POLICY TO hr;

Эта инструкция освобождает пользователя HR от действия каких-либо политик RLS. Поскольку политики перестают действовать независимо от того, для какой таблицы они определены, этот способ следует применять крайне осторожно (а лучше вообще его избегать).

Во втором способе проблема решается посредством изменения функции политики. Вот как может выглядеть соответствующая функция:

1    FUNCTION authorized_emps (
2       p_schema_name   IN   VARCHAR2,
3       p_object_name   IN   VARCHAR2
4    )
5       RETURN VARCHAR2
6    IS
7       l_deptno       NUMBER;
8       l_return_val   VARCHAR2 (2000);
9    BEGIN
10       IF (p_schema_name = USER)
11       THEN
12          l_return_val := NULL;
13       ELSE
14          SELECT deptno
15            INTO l_deptno
16            FROM emp
17           WHERE ename = USER;
18
19          l_return_val := 'DEPTNO = ' || l_deptno;
20       END IF;
21
22       RETURN l_return_val;
23    END;

Рассмотрим этот код более подробно.

 

Строки Описание
10 Проверяем, является ли вызывающий пользователь владельцем таблицы. Если является —вместо предиката возвращается NULL, то есть ограничения доступа к таблице отсутствуют
14-19 Определение номера отдела пользователя и конструирование предиката в форме
«DEPTNO = номер_отдела»
22 Функция возвращает предикат вызывающей стороне

 

У этого метода есть одно интересное побочное преимущество. Функция политики возвращает ограничивающий предикат с ограничением по DEPTNO, поэтому данная политика применима к любой таблице со столбцом DEPTNO.

Приведенный пример демонстрирует крайний случай динамической политики. При возвращении каждой строки политика выполняет функцию, проверяет предикат и решает, стоит или нет пропускать строку. Несомненно, такой подход сопряжен со значительными затратами, потому что базе данных приходится каждый раз проходить полный цикл «разбор — выполнение — выборка».

Если предикат остается неизменным, производительность приложения можно повысить за счет ликвидации избыточных вызовов функции. Начиная с Oracle9i процедура ADD_POLICY поддерживает параметр static_policy, по умолчанию равный FALSE. Если параметр равен TRUE, то функция политики выполняется только один раз в начале сеанса. Значение должно использоваться только в том случае, если вы абсолютно твердо уверены, что строка предиката не изменится в ходе сеанса.

В Oracle10g появилась поддержка нескольких разновидностей «динамических» политик, задаваемых параметром policy_type процедуры ADD_P0LICY. Допустимые значения параметра:

DBMS_RLS.DYNAMIC
DBMS_RLS.CONTEXT_SENSTIVE
DBMS_RLS.SHARED_CONTEXT_SENSITIVE
DBMS_RLS.SHARED_STATIC
DBMS_RLS.STATIC

По умолчанию используется тип DYNAMIC. Если параметр static_policy (доступный в Oracle10g и Oracle11g) равен TRUE, то по умолчанию используется значение DBMS_RLS. STATIC. Если параметр static_policy равен FALSE, выбирается тип DBMS_RLS.DYNAMIC. В этих двух вариантах политики работают точно так же, как в Oracle9i с параметром static_policy, равным TRUE и FALSE соответственно.

В следующих разделах будут представлены другие типы политик, появившиеся в Oracle10g и выше.

Новые политики заметно превосходят стандартный динамический тип по производительности. Тем не менее следует учитывать возможные побочные эффекты этих видов политик. Например, поскольку статические политики не выполняют функцию заново, их результаты порой оказываются неожиданными.

 

Общая статическая политика

Общая статическая политика (DBMS_RLS.SHARED_STATIC) аналогична статической, за исключением того, что одна функция политики используется в политиках нескольких объектов. Например, в предыдущем примере функция AUTH0RIZED_EMPS использовалась в качестве функции политики для таблиц DEPT и EMP. Аналогичным образом для обеих таблиц может быть определена одна и та же политика (а не только функция). Такие политики называются общими (shared). Если общая политика может рассматриваться как статическая, то она объявляется как общая статическая (DBMS_RLS. SHARED_STATIC). Используя этот тип, можно создать одну и ту же политику для обеих таблиц:

1    BEGIN
 2       DBMS_RLS.drop_policy (object_schema       => 'HR',
 3                   object_name         => 'DEPT',
 4                   policy_name         => 'EMP_DEPT_POLICY'
 5                   );
 6       DBMS_RLS.add_policy (object_schema        => 'HR',
 7                  object_name          => 'DEPT',
 8                  policy_name          => 'EMP_DEPT_POLICY',
 9                  function_schema      => 'RLSOWNER',
10                  policy_function      => 'AUTHORIZED_EMPS',
11                  statement_types      => 'SELECT, INSERT, UPDATE, DELETE',
12                  update_check         => TRUE,
13                  policy_type          => DBMS_RLS.SHARED_STATIC
14                           );
15       DBMS_RLS.add_policy (object_schema        => 'HR',
16                  object_name          => 'EMP',
17                  policy_name          => 'EMP_DEPT_POLICY',
18                  function_schema      => 'RLSOWNER',
19                  policy_function      => 'AUTHORIZED_EMPS',
20                  statement_types      => 'SELECT, INSERT, UPDATE, DELETE',
21                  update_check         => TRUE,
22                  policy_type          => DBMS_RLS.SHARED_STATIC
23                  );
24     END;

Объявляя одну политику для обеих таблиц, мы приказываем базе данных кэшировать результат функции политики, а затем использовать его при последующих вызовах.

 

Контекстная политика

Как говорилось ранее, статические политики при всей своей эффективности сопряжены с определенным риском; так как функция не выполняется заново при каждой проверке, результаты могут оказаться неожиданными. По этой причине Oracle предоставляет другую разновидность политик — контекстные политики, которые выполняют функцию политики только при изменении контекста приложения в сеансе (см. далее раздел «Контексты приложений»). Пример использования таких политик:

1    BEGIN
 2       DBMS_RLS.drop_policy (object_schema      => 'HR',
 3                  object_name        => 'DEPT',
 4                  policy_name        => 'EMP_DEPT_POLICY'
 5                  );
 6       DBMS_RLS.add_policy (object_schema       => 'HR',
 7                 object_name         => 'DEPT',
 8                 policy_name         => 'EMP_DEPT_POLICY',
 9                 function_schema     => 'RLSOWNER',
10                 policy_function     => 'AUTHORIZED_EMPS',
11                 statement_types     => 'SELECT, INSERT, UPDATE, DELETE',
12                 update_check        => TRUE,
13                 policy_type         => DBMS_RLS.CONTEXT_SENSITIVE
14                 );
15       DBMS_RLS.add_policy (object_schema       => 'HR',
16                 object_name         => 'EMP',
17                 policy_name         => 'EMP_DEPT_POLICY',
18                 function_schema     => 'RLSOWNER',
19                 policy_function     => 'AUTHORIZED_EMPS',
20                 statement_types     => 'SELECT, INSERT, UPDATE, DELETE',
21                 update_check        => TRUE,
22                 policy_type         => DBMS_RLS.CONTEXT_SENSITIVE
23                 );
24     END;

Контекстная политика (dbms_rls.context_sensitive) обычно уступает по производительности типу SHARED_STATIC, но превосходит DYNAMIC. Давайте проанализируем затраты времени для конкретного запроса. Для хронометража будет использоваться встроенный таймер dbms_utility.get_cpu_time.

DECLARE
   l_start_time   PLS_INTEGER;
   l_count        PLS_INTEGER;
BEGIN
   l_start_time := DBMS_UTILITY.get_time;

   SELECT COUNT ( * )
     INTO l_count
     FROM hr.emp;

   DBMS_OUTPUT.put_line (DBMS_UTILITY.get_time - l_start_time);
END;

Разность между возвращаемыми значениями функции для конечного и начального момента составляет время в сотых долях секунды. В следующей таблице приведены значения времени отклика для разных типов политик.

 

Тип политики Время отклика (1/100 секунды)
Динамическая 133
Контекстная 84
Статическая 37

 

Общая контекстная политика


Общие контекстные политики сходны с контекстными политиками, но с ними одна политика используется для нескольких объектов, как было показано ранее для общих статических политик.

СТРАТЕГИИ ПЕРЕХОДА

 

Использование столбцовой модели RLS

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

SELECT empno, sal FROM emp
SELECT empno FROM emp

Первый запрос выводит оклады работников — ту самую информацию, которую вы пытаетесь защитить. В данном случае должны выводиться только данные работников из отдела пользователя. Второй запрос выводит только коды работников. Нужно ли отфильтровать и их, чтобы запрос выводил только коды работников того же отдела? Ответ зависит от политики безопасности, действующей в вашей организации. Возможно, имеются веские причины для вывода вторым запросом всех работников независимо от того, какому отделу они принадлежат. Будет ли механизм RLS эффективным в таких ситуациях?

В Oracle9i Database RLS не поможет; однако в Oracle Database 10g и выше появился параметр ADD_P0LICY с именем sec_relevant_cols, который упрощает решение этой задачи. Например, в описанном сценарии фильтр должен применяться только при выборе столбцов SAL и COMM, но не других столбцов. Политику можно записать следующим образом (обратите внимание на новый параметр):

BEGIN
   /* Drop the policy first. */
   DBMS_RLS.drop_policy (object_schema      => 'HR',
              object_name        => 'EMP',
              policy_name        => 'EMP_POLICY'
                        );
   /* Add the policy. */
   DBMS_RLS.add_policy (object_schema          => 'HR',
              object_name            => 'EMP',
              policy_name            => 'EMP_POLICY',
              function_schema        => 'RLSOWNER',
              policy_function        => 'AUTHORIZED_EMPS',
              statement_types        => 'INSERT, UPDATE, DELETE, SELECT',
              update_check           => TRUE,
              sec_relevant_cols      => 'SAL, COMM'
              );
END;

После назначения этой политики запросы к HR.EMP будут выдавать разные результаты:

SQL> -- harmless query, only EMPNO is selected
SQL> SELECT empno FROM hr.emp; ... rows are here ...

14 rows selected.

SQL> -- sensitive query, SAL is selected
SQL> SELECT empno, sal FROM hr.emp; ... rows are here ...

6 rows selected.

При выборке столбца SAL вступает в действие политика RLS, предотвращающая вывод всех строк; она отфильтровывает строки, в которых значение DEPTNO отлично от 30 (значение DEPTNO пользователя, выполняющего запрос).

Столбцовая модель распространяется не только на список выборки, но и на любые обращения к столбцам, прямые или косвенные. Возьмем следующий запрос:

SQL> SELECT deptno, count(*)
  2  FROM hr.emp
  3  WHERE sal> 0
  4  GROUP BY deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6

Здесь столбец SAL упоминается в секции WHERE, поэтому политика RLS оставляет только записи, относящиеся к отделу 30. Другой пример:

SQL> SELECT *
  2  FROM hr.emp
  3  WHERE deptno = 10;

no rows selected

Здесь вместо прямого обращения к столбцу SAL используется косвенное обращение через секцию SELECT *, поэтому политика RLS отфильтровывает записи всех отделов, кроме 30. Так как запрос вызывается для отдела 10, он не возвращает ни одну строку.

Теперь рассмотрим несколько иную ситуацию: в предыдущем случае я защитил значения столбцов SAL от отображения в строках, просмотр которых не разрешен пользователю. Однако при этом был отключен вывод всей строки, не только этого конкретного столбца. Теперь допустим, что в соответствии с новыми требованиями маскироваться должен только столбец, а не вся строка, а все остальные столбцы, не содержащие конфиденциальной информации, должны отображаться. Возможно ли это?

Да, и притом легко — в этом вам поможет другой параметр ADD_POLICY, который называется sec_relevant_cols_opt. Воссоздадим политику, задав этому параметру значение DBMS RLS.ALL ROWS:

BEGIN
   DBMS_RLS.drop_policy (object_schema             => 'HR',
                         object_name               => 'EMP',
                         policy_name               => 'EMP_POLICY'
                        );
   DBMS_RLS.add_policy (object_schema              => 'HR',
                        object_name                => 'EMP',
                        policy_name                => 'EMP_POLICY',
                        function_schema            => 'RLSOWNER',
                        policy_function            => 'AUTHORIZED_EMPS',
                        statement_types            => 'SELECT',
                        update_check               => TRUE,
                        sec_relevant_cols          => 'SAL, COMM',
                        sec_relevant_cols_opt      => DBMS_RLS.all_rows
                       );
END;

Если выдать тот же запрос сейчас, результаты будут другими:

SQL> -- Show a "?" for the NULL values in the output.
SQL> SET NULL ?
SQL> SELECT *
  2  FROM hr.emp
  3  ORDER BY deptno
  4  /

EMPNO ENAME      JOB          MGR HIREDATE     SAL   COMM DEPTNO
------ ---------- --------- ------ --------- ------ ------ ------
  7782 CLARK      MANAGER     7839 09-JUN-81 ?      ?          10
  7839 KING       PRESIDENT ?      17-NOV-81 ?      ?          10
  7934 MILLER     CLERK       7782 23-JAN-82 ?      ?          10
  7369 SMITH      CLERK       7902 17-DEC-80 ?      ?          20
  7876 ADAMS      CLERK       7788 12-JAN-83 ?      ?          20
  7902 FORD       ANALYST     7566 03-DEC-81 ?      ?          20
  7788 SCOTT      ANALYST     7566 09-DEC-82 ?      ?          20
  7566 JONES      MANAGER     7839 02-APR-81 ?      ?          20
  7499 ALLEN      SALESMAN    7698 20-FEB-81  1,600    300     30
  7698 BLAKE      MANAGER     7839 01-MAY-81  2,850 ?          30
  7654 MARTIN     SALESMAN    7698 28-SEP-81  1,250  1,400     30
  7900 JAMES      CLERK       7698 03-DEC-81    950 ?          30
  7844 TURNER     SALESMAN    7698 08-SEP-81  1,500      0     30
  7521 WARD       SALESMAN    7698 22-FEB-81  1,250    500     30

14 rows selected.

Будьте внимательны при использовании этой возможности, потому что в некоторых случаях она приводит к неожиданным результатам. Допустим, что запрос был выдан пользователем MARTIN:

SQL> SELECT COUNT(1), AVG(sal) FROM hr.emp;
COUNT(SAL)   AVG(SAL)
---------- ----------
         14 1566.66667

 

Результат содержит данные 14 работников со средним окладом $1566 — но на самом деле это средний оклад 6 работников, который разрешено видеть пользователю MARTIN, а не всех 14 работников. Иногда это создает путаницу. Если тот же запрос выдаст владелец схемы HR, результат будет другим:

SQL> CONN hr/hr
Connected.
SQL> SELECT COUNT(1), AVG(sal) FROM hr.emp;
COUNT(SAL)   AVG(SAL)
---------- ----------
        14 2073.21429

Так как результаты могут изменяться в зависимости от того, какой пользователь выдал запрос, будьте осторожны с интерпретацией результатов; в противном случае в вашем приложении могут появиться трудноуловимые ошибки.

Обратите внимание: показаны все 14 строк со всеми столбцами, но значения столбцов SAL и COMM в строках, которые пользователю видеть не положено (то есть работников отделов, отличных от 30), заменены на NULL.

Здесь RLS позволяет справиться с ситуациями, в которых требуется вывести все строки, но скрыть конфиденциальные значения. До выхода Oracle Database 10g того же результата можно было добиться при помощи представлений, но операции получались куда более сложными.

 

Отладка RLS

RLS — сложный механизм, зависящий от многих элементов архитектуры Oracle. При его использовании могут возникнуть ошибки, порожденные как недостатками архитектуры, так и неправильным применением со стороны пользователей. К счастью, для большинства ошибок RLS создает подробный файл трассировки в каталоге, заданном параметром инициализации базы данных USER_DUMP_DEST.

 

Интерпретация ошибок

Самая распространенная ошибка, с которой вы будете сталкиваться (и одновременно самая простая), — ORA-28110 (ошибка в функции политики или пакете). Исправление ошибок и перекомпиляция функции (или пакета, содержащего функцию) должны решить проблему.

Также могут встретиться ошибки времени выполнения — например, несоответствие типов или исключение VALUE_ERROR. В таких случаях Oracle инициирует ошибку ORA- 28112 (ошибка при выполнении функции политики) и создает файл трассировки. Фрагмент этого файла выглядит так:

----------------------------------------------------------
Policy function execution error:
Logon user     : MARTIN
Table/View     : HR.EMP
Policy name    : EMP_DEPT_POLICY
Policy function: RLSOWNER.AUTHORIZED_EMPS
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "RLSOWNER.AUTHORIZED_EMPS", line 14
ORA-06512: at line 1

Из данных трассировки видно, что при возникновении ошибки запрос выполнялся пользователем MARTIN. Здесь функция политики просто получила более одной строки. Просматривая код функции, вы замечаете, что она содержит сегмент следующего вида:

SELECT deptno
  INTO l_deptno
  FROM hr.emp
 WHERE ename = USER

Похоже, данные содержат более одного работника с именем MARTIN: количество выбранных строк превышает 1. Проблема решается либо обработкой ошибки через исключение, либо использованием другого условия в качестве предиката для получения номера отдела.

Исключение ORA-28113 (ошибка в предикате политики) возникает при некорректном построении предиката. Фрагмент файла трассировки с этой ошибкой:

Error information for ORA-28113:
Logon user     : MARTIN
Table/View     : HR.EMP
Policy name    : EMP_DEPT_POLICY
Policy function: RLSOWNER.AUTHORIZED_EMPS
RLS predicate  :
DEPTNO = 10,
ORA-00907: missing right parenthesis

Из этого фрагмента видно, что функция политики возвращает следующий предикат:

DEPTNO = 10,

Условие синтаксически неверно, поэтому при попытке применения политики происходит сбой и запрос MARTIN не работает. Проблема решается исправлением логики функции политики и возвращением правильного предиката.

 

Выполнение прямых операций

Если вы используете прямые операции — прямую загрузку в SQCLoader, прямую вставку с рекомендацией APPEND (INSERT /*+ APPEND */ INTO ...) или прямое экспортирование — учтите, что политики RLS для задействованных таблиц активизироваться не будут. В конце концов, прямые операции предназначены для работы в обход уровня SQL. Вам придется принять особые меры для таких ситуаций.

С экспортированием дело обстоит относительно просто. Вот что происходит при экспортировании таблицы EMP, защищенной одной или несколькими политиками RLS, с параметром DIRECT=Y:

About to export specified tables via Direct Path ...
EXP-00080: Data in table "EMP" is protected. Using conventional mode.
EXP-00079: Data in table "EMP" is protected. Conventional path may only be
exporting partial table.

Экспортирование выполняется успешно, но как видно из выходных данных, при выводе используется традиционный, а не прямой режим. А в процессе выполнения операции к таблице применяются политики RLS — то есть пользователь сможет экспортировать не все строки, а только те, которые ему разрешено видеть.

Из-за успешного завершения операции экспортирования таблицы с RLS может возникнуть ложное впечатление, будто были экспортированы все строки. Однако следует помнить, что экспортируются только те строки, которые пользователю разрешено видеть. Кроме того, хотя экспортирование вроде бы должно было выполняться в прямом режиме, оно выполняется в традиционном режиме.

Теперь при попытке выполнить прямую загрузку/вставку в SQL*Loader выдается сообщение об ошибке:

SQL> INSERT /*+ APPEND */
  2  INTO hr.EMP
  3  SELECT *
  4  FROM hr.emp
  5  WHERE rownum < 2;
from hr.emp
        *
ERROR at line 4:
ORA-28113: policy predicate has error

Сообщение об ошибке неточно — в предикате на самом деле нет ошибки. Политика RLS не была применена из-за выполнения прямой операции, но в сообщении этот факт не отражен. Проблема решается либо временным отключением политики для таблицы EMP, либо экспортированием через учетную запись пользователя, обладающего системной привилегией EXEMPT ACCESS POLICY.

 

Основные принципы RLS

 

Просмотр команд SQL

Во время отладки может возникнуть необходимость в просмотре команд, генерируемых Oracle при применении политики RLS, чтобы полностью избежать любых догадок и интерпретаций. Для просмотра замененных команд можно воспользоваться двумя способами.

 

Представления VPD

Первый способ — использование словарного представления V$VPD_P0LICY. VPD в имени означает «Virtual Private Database», то есть «виртуальная приватная база данных» — другое название для RLS. Представление содержит полную информацию о преобразовании запроса:

SQL> SELECT sql_text, predicate, POLICY, object_name
   2   FROM v$sqlarea, v$vpd_policy
   3  WHERE hash_value = sql_hash

SQL_TEXT                       PREDICATE
-----------------------------  ------------------------------
POLICY                         OBJECT_NAME
------------------------------ ------------------------------
select count(*) from hr.emp    DEPTNO = 10
EMP_DEPT_POLICY                EMP

В столбце SQL_TEXT показана точная команда SQL, выданная пользователем, а в столбце PREDICATE — предикат, сгенерированный функцией политики и примененный к запросу. Используя это представление, можно идентифицировать команды, введенные пользователями, и примененные к ним предикаты.

 

Назначение события

Второй способ — назначение события в сеансе:

SQL> ALTER SESSION SET EVENTS '10730 trace name context forever, level 12';

Session altered.

SQL>SELECT COUNT(*) FROM hr.emp;

После завершения запроса в каталоге, заданном параметром инициализации базы данных USER_DUMP_DEST, появляется файл трассировки:

Logon user     : MARTIN
Table/View     : HR.EMP
Policy name    : EMP_DEPT_POLICY
Policy function: RLSOWNER.AUTHORIZED_EMPS
RLS view :
SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
FROM "HR"."EMP" "EMP" WHERE (DEPTNO = 10)

Любой из этих двух способов позволит вам увидеть точную команду, полученную в результате замены пользовательского запроса.

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1462 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 2166 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Символьные функции и аргументы...
Символьные функции и аргументы... 3338 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 1707 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Comments on PL/SQL: безопасность уровня строк (RLS) с пакетом DBMS_RLS

Будьте первым прокомментировавшим
Пожалуйста, авторизуйтесь, для комментирования