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

Владимир Пржиялковский

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

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

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

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

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

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

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

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

CONNECT sys/change_on_install
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(

OBJECT_SCHEMA => 'scott',
OBJECT_NAME => 'emp',
POLICY_NAME => 'epolicy',
FUNCTION_SCHEMA => 'sys',
POLICY_FUNCTION => 'deptsallowed');

END;
/

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

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

GRANT EXECUTE ON deptsallowed TO scott;

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

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

CONNECT scott/tiger
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 sys/change_on_install
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;
EXEC DBMS_RLS.ADD_POLICY -
('scott','emp','epolicy','sys','permissions_package.deptsallowed','select,update')

CONNECT scott/tiger
EXEC system.permissions_package.set_location_context('DALLAS')
SELECT SYS_CONTEXT('dept_permissions', 'location') FROM DUAL;

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

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

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

Выглядит не самым простым образом, но зато легко обобщается!

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

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

Описанный способ разграничения доступа к отдельным строкам таблицы Oracle называет Fine Grained Access Control (FGAC).

Ссылки по теме