Как «засекретить» строки в таблице (решение № 2)

Оговорюсь сразу: «таблица» в заголовке – не обязательно базовая, а может быть и выводимая, то есть view, а с версии 10 – так же и синоним таблицы. Способ, описываемый ниже, позволяет ограничить доступ к определенным строкам таблицы (базовой ли, выводимой – не важно) разным пользователям по-разному: в зависимости от условия, которое мы сами сконструируем. Способ основан на использовании системного пакета DBMS_RLS и доступен он только в Oracle, то есть для переносимости на другие СУБД это решение – не лучший вариант, хотя и не 100%-безнадежный.

Основные элементы этого специфичного Oracle-решения таковы:

• Создадим на PL/SQL функцию-предикат P, задающую условие на строки таблицы TAB.
• С помощью подпрограммы из пакета DBMS_RLS создадим так называемую «политику доступа», связывающую таблицу TAB с этим предикатом.
• Начиная с этого момента всякое обращение пользователей к таблице TAB будет автоматически заменяться СУБД на SELECT * FROM tab WHERE p, словно бы TAB была выводимой таблицей с указанной формулировкой.

Теперь дело техники – придумать определение для P так, чтобы эта функция отбирала для каждого пользователя только положенные ему строки. Тут тоже есть разные решения.

Формулируем правила доступа

Наша цель – разрешить разным пользователям Oracle работать (SELECT, INSERT, UPDATE, DELETE) только с сотрудниками из таблицы EMP определенных отделов.

Для этого создадим в SQL*Plus регламентирующую таблицу, которую назовем UDPERMISSIONS:

CONNECT / AS SYSDBA

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

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

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

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

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

Тут же создадим предикат, формулирующий условие доступа, отталкиваясь от содержимого только что созданной таблицы UDPERMISSIONS:

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

Замечание. У функции DEPTSALLOWED два неиспользуемых в теле параметра. Не использовали мы их для простоты, а при желании могли бы и использовать для передачи имен схемы и таблицы, задав функцией более сложную логику. С другой стороны опыт показал, что для безошибочной работы параметры должны быть объявлены.

Тут же создадим под именем EPOLICY «политику доступа» к таблице SCOTT.EMP на основе созданного только что предиката:

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

Перечень имеющихся «политик» можно посмотреть в таблицах DBA(USER)_POLICIES.

Так как функция DEPTSALLOWED принадлежит SYSTEM, а связываться в нашем случае она будет с таблицей SCOTT.EMP, не забудем дать пользователю SCOTT право к этой функции обращаться (неявно, через созданную политику):

GRANT EXECUTE ON deptsallowed TO scott;

Проверяем, как работает

Теперь можно посмотреть, как это работает:

SQL> CONNECT scott/tiger
Connected.
SQL> SELECT ename, loc FROM emp, dept WHERE emp.deptno = dept.deptno;

ENAME      LOC
---------- -------------
CLARK      NEW YORK
KING       NEW YORK
MILLER     NEW YORK
ALLEN      CHICAGO
WARD       CHICAGO
MARTIN     CHICAGO
BLAKE      CHICAGO
TURNER     CHICAGO
JAMES      CHICAGO

9 rows selected.

Действительно, мы видим сотрудников только двух отделов.

Если у вас получились ошибки в последнем предложении SELECT, причину можно уточнить в трассировочном файле в каталоге udump (обычно это $ORACLE_HOME/admin/<имя_БД>/udump).

В качестве упражнения создайте пользователя ADAM, дайте ему права на выборку из SCOTT.EMP и убедитесь, что политика доступа работает и для него.

Развитие темы

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

Использование контекста

С каждым сеансом работы пользователя в Oracle может быть связан так называемый контекст. Он представляет собой набор пар «параметр/значение», совсем как это сделано в таблице UDPERMISSIONS выше. Там, однако, в качестве «параметров» выступали имена пользователей, а в контексте это имена отвлеченных «параметров», каких захотим. Создается контекст SQL-предложением CREATE CONTEXT …, связывается с сеансом процедурой DBMS_SESSION.SET_CONTEXT, а вот узнается почему-то обращением к стандартной процедуре SYS_CONTEXT.

Связывать контекст с сеансом можно и вручную, явной выдачей команды, но хорошая практика – поручить это триггерной процедуре, срабатывающей при подключении прикладной программы к СУБД.

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

Использование пакета для политики доступа

Предикат условия доступа Oracle рекомендует оформлять в виде не отдельной функции, а пакетированной. Определенный резон в этом есть. Так, у вас может иметься несколько предикатов для разных таблиц, и объединить их в специальный пакет естественно и удобно для разработки. В тот же пакет разумно включить процедуру DBMS_SESSION.SET_CONTEXT установки контекста для сеанса, так как у нее есть странноватая особенность: она не вызывается напрямую (попробуйте !), а только из состава какого-нибудь пакета.

Отдельно для SELECT, INSERT, UPDATE или DELETE

У процедуры DBMS_RLS.ADD_POLICY есть еще один параметр, опущеный в примере выше в виду позволительного для него умолчания. Это параметр STATEMENT_TYPES, помощью которого имеется возможность уточнить, на какие виды действий с таблицей будет распространяться конкретный предикат. Пример использования этого параметра: STATEMENT_TYPES => 'select, update, delete'.

Пример рекомендуемого способа решения задачи

С учетом сказанного можно предложить более «правильный» (с точки зрения разработчиков Oracle) способ решения проблемы разграничения доступа к строкам:

CONNECT / AS SYSDBA

CREATE OR REPLACE CONTEXT dept_permissions USING permissions_package;

CREATE OR REPLACE PACKAGE permissions_package IS   PROCEDURE set_location_context(loc IN VARCHAR2);

  FUNCTION deptsallowed (obj_schema IN VARCHAR2, obj_name IN VARCHAR2)
  RETURN VARCHAR2;
END permissions_package;
/

CREATE OR REPLACE PACKAGE BODY permissions_package IS
  PROCEDURE set_location_context(loc IN VARCHAR2) IS
  BEGIN
  DBMS_SESSION.SET_CONTEXT('dept_permissions', 'location', loc);
  END;

FUNCTION deptsallowed (obj_schema IN VARCHAR2, obj_name IN VARCHAR2)
  RETURN VARCHAR2
  IS
  BEGIN RETURN
   'deptno in
    (SELECT deptno
    FROM scott.dept
    WHERE loc = sys_context(''dept_permissions'', ''location''))';
  END;
END permissions_package;
/

GRANT EXECUTE ON permissions_package TO scott;

EXECUTE DBMS_RLS.ADD_POLICY - ('scott','emp','epolicy','sys', - 'permissions_package.deptsallowed','select,update')

Проверка:

SQL> CONNECT scott/tiger
Connected.
SQL> EXECUTE -
> system.permissions_package.set_location_context('DALLAS')

SQL> SELECT SYS_CONTEXT('dept_permissions', 'location') FROM DUAL;

SYS_CONTEXT('DEPT_PERMISSIONS','LOCATION')
------------------------------------------
DALLAS

SQL> SELECT ename, loc FROM emp, dept WHERE emp.deptno = dept.deptno;

ENAME      LOC
---------- -------------
SMITH      DALLAS
JONES      DALLAS
SCOTT      DALLAS
ADAMS      DALLAS
FORD       DALLAS

5 rows selected.

Выглядит не самым простым образом, верно. Но зато возможностей при таком решении больше, чем при работе с непакетированной функцией-предикатом или же при использовании решения № 1. Так, меняя контекст сеанса мы приобретаем возможность регулировать объем выборки из одной и той же таблицы, не выполняя дополнительных подключений к БД.

Дальнейшее развитие

В пакете DBMS_RLS предусмотрены и другие возможности, следующего порядка значимости. О них можно самостоятельно справиться в документации. Например, сформированную «политику доступа» можно включать и выключать, обновлять и так далее.

Описанный способ разграничения доступа к отдельным строкам таблицы фирма Oracle называет Fine Grained Access Control (FGAC). В документации по СУБД Oracle вы можете встретить еще одно название для разграничения доступа к строкам: Label Security. В Label Security разработчики Oracle воспользовались FGAC как основой для построения логически иной модели доступа. В рамках терминологии этой статьи и предыдущей, эту последнюю модель можно назвать «решением № 3». Ей будет посвящена отдельная статья.