Базы данных 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.


Содержание раздела