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

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

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

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

При работе с общей БД часто возникает необходимость обеспечить разным пользователям разное видение одних и тех же таблиц. Иногда хочется, чтобы один пользователь при обращении к таблице видел одни данные, а другой – другие. Как это можно сделать в Oracle ?

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

Постановка задачи

Возьмем стандартный демонстрационный пример из любой поставки Oracle: таблицу сотрудников EMP в схеме пользователя SCOTT. Предположим, что организация, в которой работают сотрудники, устроена таким образом, что каждый пользователь Oracle, обратившись к этой таблице, может видеть в ней только перечень сотрудников из своего отдела, то есть SCOTT – только сотрудников отдела 20, ALLEN – отдела 30 и так далее.

Это, конечно, простая постановка задачи, но для иллюстрации идеи она годится. От нее рукой подать до такой организации данных, при которой каждый врач, обратившись к одной и той же таблице, видит только своих пациентов и до более сложных постановок.

В соответствие с известной дихотомией «правильный метод»/ «наш метод» рассмотрим два решения: одно более правильное, а другое – более эффективное.

Решение № 1

Это старое решение, которое давно практикуется в поставках Oracle для организации удобного доступа к таблицам словаря-справочника. В самом деле, известно, что каждый пользователь Oracle, даже при наличии у него всего-навсего привилегии CREATE SESSION, имеет возможность обратиться к примеру, к таблице USER_TABLES, чтобы посмотреть список своих собственных таблиц. Каждый пользователь обращается к одной и той же таблице (USER_TABLES), но видит в ней только свои данные.

Строго говоря, в прозвучавшей только что формулировке кроется подлог: реально USER_TABLES – это выводимая таблица (view) в схеме SYS, в определении которой присутствует ссылка на имя текущего пользователя, и для которой создан одноименный публичный (PUBLIC, то есть общедоступный) синоним. От этого-то синонима, для которого не требуется уточнения имени владельца (согласно общему правилу ссылки на небольшое количество «общесистемных» объектов, не принадлежащих никакой одной схеме), и разворачивается запрос к реальным таблицам словаря-справочника при обращении конкретного пользователя Oracle к USER_TABLES.

Как эта механика оформлена, желающие могут подсмотреть в файле-сценарии $ORACLE_HOME/rdbms/admin/catalog.sql. Он запускается (автоматически, вручную ли) при заведении базы данных почти любой конфигурации (за исключением вариантов typical и наиболее типичных в версии 10, где БД заводится не прогоном команд SQL, а копированием готовых образов с установочного клмплекта дисков). Для нашего примера эта механика будет выглядеть так.

Зайдем для начала в систему от имени SYS и заведем пользователя ALLEN:

CONNECT / AS SYSDBA (в версиях 8, 7 лучше CONNECT INTERNAL)

CREATE USER ALLEN IDENTIFIED BY ALLEN;

GRANT CREATE SESSION TO ALLEN;

Тут же, заодно, выдадим право пользователю SCOTT создавать публичные синонимы, так как изначально этого права у него нет:

GRANT CREATE PUBLIC SYNONYM TO SCOTT;

Теперь войдем как SCOTT:

CONNECT scott/tiger

CREATE VIEW emps AS SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = USER);

CREATE PUBLIC SYNONYM emps FOR emps;

GRANT SELECT ON emps TO allen;

А теперь проверка:

SQL> SELECT ename FROM emps;

ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD

5 rows selected.

А вот, что увидит ALLEN:

SQL> CONNECT allen/allen

SQL> SELECT ename FROM emps;

ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

6 rows selected.

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

Решение № 2

Выше решение № 1 было названо «правильным». Почему ? Дело в том, что оно базируется на использовании выводимых таблиц, views, которые были придуманы еще в реляционной модели (приведшей к появлению SQL) как раз для целей разграничения видимости общих данных разными приложениями (у views есть и иное предназначение, для темы этой статьи несущественное). То есть оно правильно с точки зрения старой реляционной модели.

Это «правильное» решение, однако, как и многие другие «правильные решения» не всегда оказывается эффективным или удобным на практике (только не надо последним тезисом злоупотреблять !) Иногда разработчику приложения может оказаться удобным при обращении к одной и той же таблице в течение одного и того же сеанса давать возможность предъявлять разные данные. Иногда одни и те же данные таблицы желательно предъявлять группам приложений (сеансов, пользователей). Техника использования views в таких случаях может оказаться недостаточно гибка или экономна; ей приходится искать замену.

Итак, другой способ решения нашей конкретной задачи – воспользоваться системным пакетом DBMS_RLS, поставляемым в версиях Oracle Enterprise Edition.

Он более трудоемок, и о нем будет рассказано в следующей статье.

Новости мира 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
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...