Каждому (пользователю) свое (данное в таблице). Часть 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 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. 

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