В версии Oracle10 «виртуальные частные базы данных» данных стали избирательнее

Владимир Пржиялковский

Если на клетке буйвола прочтешь "Слон" - не верь глазам своим.
Козьма Прутков

Оглавление

Введение

Напомню, что понятие виртуальная частная база данных (virtual private database, VPD), появилось в версии Oracle 8.1 для обозначения возможности ограничить конкретным сеансам доступное множество строк в таблице (в том числе выводимой, view), чтобы каждый сеанс, обращаясь формально к одной и той же таблице, имел доступ в ней ("видел") только положенные строки. Сам термин VPD рекламный, его технический эквивалент, фигурирующий наравне в документации - детальный контроль доступа [к строкам] (fine grained access control, FGAC)1. Пользоваться VPD можно напрямую, через пакет SYS.DBMS_RLS, а так же неявно и не догадываясь об этом, поскольку это средство положено в основу другого, label security, реализующего известную модель мандатного доступа применительно к строкам таблицы из приложения. Пример работы непосредственно с VPD приводился в статье каждому (пользователю) свое (данное в таблице). Часть 2 , а пример работы с label security приводился в статье "к каждой строке охранника приставишь!".

Фирма Oracle не считает для себя направление VPD случайным, о чем свидетельствует непрерывное развитие этого средства от одной версии СУБД к другой. Так, в числе новшеств VPD в версии 9 - собственная GUI-программа администрирования Policy Manager и поддержка синонимов, а в версии 10.1 - отбор строк (и даже значений в строках) с учетом указанных столбцов и возможность выбора между статическим и динамическим вычислением заданного предикатом отбора критерия видимости.

Именно первая из указанных двух новых возможностей VPD версии 10.1 и будет продемонстрирована в этой статье.

Подготовка примера

Хотя формально этого не требуется, при использовании VPD методологически правильно завести специального пользователя-администратора. Ниже ему предоставляется минимум полномочий, достаточных для примеров из этой статьи. Выдаем в SQL*Plus:

CONNECT / AS SYSDBA

CREATE USER vpd_admin IDENTIFIED BY vpd_admin
DEFAULT TABLESPACE sysaux
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON sysaux;

GRANT CREATE SESSION TO vpd_admin;

GRANT CREATE PROCEDURE TO vpd_admin;

GRANT CREATE TABLE TO vpd_admin;

GRANT EXECUTE ON dbms_rls TO vpd_admin;

Избирательность строк и значений с учетом столбцов

"Старое" решение

Сначала воспроизведем пример из упомянутой выше по тексту первой статьи с той разницей, что теперь функция политики доступа к таблице SCOTT.EMP и вспомогательная таблица, регулирующая доступ, создаются в отдельной схеме VPD_ADMIN "администратора политик" (что в методологически более правильно):

CONNECT vpd_admin/vpd_admin

Таблица с данными, параметризующими доступ:

CREATE TABLE permissions_table
(
username VARCHAR2(14)
, deptno NUMBER (2)
);

INSERT INTO permissions_table VALUES ('SCOTT', 10);

INSERT INTO permissions_table VALUES ('SCOTT', 30);

INSERT INTO permissions_table VALUES ('ADAM', 10);

(Полагаем, что пользователь SCOTT будет работать с сотрудниками 10-го и 30-го отделов, а пользователь ADAM - с сотрудниками только 10-го).

Функция, служащая предикатом доступа, задающая фильтр для строк при обращении к таблице SCOTT.EMP:

CREATE OR REPLACE FUNCTION permissions_function
(
obj_schema IN VARCHAR2
,obj_name IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN RETURN
'deptno IN (SELECT deptno FROM permissions_table '
// 'WHERE username = USER)';
END;
/

Политику доступа к таблице SCOTT.EMP по-прежнему назовем EPOLICY:

BEGIN
DBMS_RLS.ADD_POLICY
(
POLICY_NAME => 'epolicy'
,OBJECT_SCHEMA => 'scott'
,OBJECT_NAME => 'emp'
,FUNCTION_SCHEMA => 'vpd_admin'
,POLICY_FUNCTION => 'permissions_function'
);
END;
/

GRANT EXECUTE ON permissions_function TO scott;

Проверка:

SQL> CONNECT scott/tiger
Connected.
SQL> SELECT ename, sal, deptno FROM emp;

Новые параметры политики доступа в версии 10

В версии 10.1 у процедуры DBMS_RLS.ADD_POLICY появились новые необязательные (умолчательное значение - NULL) параметры:

Параметр SEC_RELEVANT_COLS

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

CONNECT vpd_admin /vpd_admin

HOST echo CONNECT vpd_admin/vpd_admin > drop_policy.sql

BEGIN
DBMS_RLS.DROP_POLICY
(
POLICY_NAME => 'epolicy'
,OBJECT_SCHEMA => 'scott'
,OBJECT_NAME => 'emp'
);
END;
/

SAVE drop_policy APPEND

(Файл drop_policy.sql я создал ради удобства, чтобы более экономно удалить политику во второй раз, ниже по тексту).

BEGIN
DBMS_RLS.ADD_POLICY
(
POLICY_NAME => 'epolicy'
,OBJECT_SCHEMA => 'scott'
,OBJECT_NAME => 'emp'
,FUNCTION_SCHEMA => 'vpd_admin'
,POLICY_FUNCTION => 'permissions_function'
,SEC_RELEVANT_COLS => 'sal, comm'
);
END;
/

Проверка:

SQL> CONNECT scott/tiger
Connected.
SQL> SELECT ename, sal, deptno FROM emp;

А вот что получим, если не обратимся к "секретному" столбцу с зарплатой:

SQL> SELECT ename, deptno FROM emp;

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

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

Параметр SEC_RELEVANT_COLS_OPT

Если для него указать константу DBMS_RLS.ALL_ROWS, то при операции SELECT будут выдаваться все строки, но значения в "секретных" столбцах некоторых строк (защищенных политикой) мы не увидим:

Проверка:

Обратите внимание, что различить в столбцах пропуски значений, обязанные применению политики, от пропусков в исходных данных в Oracle нельзя. Это контрастирует с возможностью аналогичного различения "исходных" и "благоприобретенных" пропусков, существующей, например, в SELECT с использованием GROUP BY ROLLUP/CUBE.

Некоторые замечания по употреблению

Не совсем очевидными могут представиться правила видимости строк. Так обратите внимание, что политика EPOLICY ограничивает пользователю SCOTT доступ к строкам даже собственной таблицы! Возможно, это не самый реалистичный пример, и чаще разработчик будет помещать таблицу с данными в отдельную схему, но таковы свойства VPD. С другой стороны, пользователь SYS имеет доступ ко всем данным любой таблицы вне зависимости от того, связана с ней какая-нибудь политика или нет. Точнее, не замечать политики будет любой пользователь, обладающей привилегией EXEMPT ACCESS POLICY (проверьте это!).

Обращает на себя внимание содержательная упрощенность примера выше. Отчасти она намеренная, а отчасти вынужденная. Например, прямолинейная попытка реализовать более интересный вариант, при котором каждый сотрудник будет "видеть" строки только о себе и о своих подчиненных, обречена на неудачу, в чем легко убедиться. Действительно, формулирование в функции-предикате условия отбора, содержащее упоминание самой таблицы EMP (что потребуется, если мы захотим отбирать из EMP строки с подчиненными) приведет к рекурсивному обращению к политике EPOLICY, то есть к ошибке. Как поступить в этом случае?

Во-первых, создать отдельную таблицу, наподобие того, как это сделано выше, и перенести в нее нужные данные из EMP.

Во-вторых, создать не новую таблицу, а materialized view на основе EMP, и в функции-предикате обращаться именно к materialized view. Это будет технологичнее, так как снимет проблему синхронизации данных во вспомогательной таблице с изменениями в EMP. Materialized view в этом случае удобно создать со свойством REFRESH ON COMMIT.

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

Ну, и, наконец, в реальной практике эффективно использовать контекст сеанса, хотя это и усложнит программирование.


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=1306