Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
Конференция «Технологии управления данными 2018»
СУБД, платформы, инструменты, реальные проекты.
29 ноября 2018 г.
2005 г.

Каждому (пользователю) свое (данное в таблице). Часть 2.

Владимир Пржиялковский
Преподаватель технологий Oracle
prz@yandex.ru

Статья обновлена в феврале 2005 года

Часть1

Как «засекретить» строки в таблице (решение № 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». Ей будет посвящена отдельная статья.

Новости мира IT:

Архив новостей

Последние комментарии:

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 985 1945361
Пресс-релизы — pr@citforum.ru
Обратная связь
Информация для авторов
Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2015 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...