Базы данных Oracle - статьи
Избирательность строк и значений с учетом столбцов
«Старое» решение
Сначала воспроизведем пример из упомянутой выше по тексту первой статьи с тою разницей, что теперь функция политики доступа к таблице 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.
