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

Введение

Напомню, что понятие виртуальная частная база данных (virtual private database, VPD), появилось в версии Oracle 8.1 для обозначения возможности ограничить конкретным сеансам доступное множество строк в таблице (в том числе выводимой, view), чтобы каждый сеанс, обращаясь формально к одной и той же таблице, имел доступ в ней («видел») только положенные строки. Сам термин VPD рекламный, его технический эквивалент, фигурирующий наравне в документации – детальный контроль доступа [к строкам] (fine grained access control, FGAC)¹. Пользоваться 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;

ENAME             SAL     DEPTNO
---------- ---------- ----------
MILLER           1300         10
KING             5000         10
CLARK            2450         10
JAMES             950         30
TURNER           1500         30
BLAKE            2850         30
MARTIN           1250         30
WARD             1250         30
ALLEN            1600         30

9 rows selected.

Новые параметры политики доступа в версии 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;

ENAME             SAL     DEPTNO
---------- ---------- ----------
MILLER           1300         10
KING             5000         10
CLARK            2450         10
JAMES             950         30
TURNER           1500         30
BLAKE            2850         30
MARTIN           1250         30
WARD             1250         30
ALLEN            1600         30

9 rows selected.

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

SQL> SELECT ename, deptno FROM emp;

ENAME          DEPTNO
---------- ----------
SMITH              20
ALLEN              30
WARD               30
JONES              20
MARTIN             30
BLAKE              30
CLARK              10
SCOTT              20
KING               10
TURNER             30
ADAMS              20
JAMES              30
FORD               20
MILLER             10

14 rows selected.

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

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

Параметр SEC_RELEVANT_COLS_OPT

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

@drop_policy

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'
   ,SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS
 );
 END;
/

Проверка:

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

ENAME             SAL     DEPTNO
---------- ---------- ----------
SMITH                         20
ALLEN            1600         30
WARD             1250         30
JONES                         20
MARTIN           1250         30
BLAKE            2850         30
CLARK            2450         10
SCOTT                         20
KING             5000         10
TURNER           1500         30
ADAMS                         20
JAMES             950         30
FORD                          20
MILLER           1300         10

14 rows selected.

SQL> SELECT ename, deptno FROM emp;

ENAME          DEPTNO
---------- ----------
SMITH              20
ALLEN              30
WARD               30
JONES              20
MARTIN             30
BLAKE              30
CLARK              10
SCOTT              20
KING               10
TURNER             30
ADAMS              20
JAMES              30
FORD               20
MILLER             10

14 rows selected.

Обратите внимание, что различить в столбцах пропуски значений, обязанные применению политики, от пропусков в исходных данных в 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.

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

^1. Формально не эквивалент, так как документация по Oracle считает, что VPD = FGAC + контекст приложения. По существу же мне разницы не видится.