Если на клетке буйвола прочтешь "Слон" - не верь глазам своим.
Козьма Прутков
Оглавление
- Введение
- Подготовка примера
- Избирательность строк и значений с учетом столбцов
- Некоторые замечания по употреблению
- Дополнительная информация
Введение
Напомню, что понятие виртуальная частная база данных (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.
Ну, и, наконец, в реальной практике эффективно использовать контекст сеанса, хотя это и усложнит программирование.