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

Владимир Пржиялковский,
координатор Евро-Азиатской Группы Пользователей Oracle,
преподаватель УКЦ Interface Ltd.

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

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

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

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

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

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

Решение № 1

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

Как эта механика оформлена, желающие могут подсмотреть в файле-сценарии rdbms/admin/catalog.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:

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;

SELECT ename FROM emps;

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

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

CONNECT ALLEN/ALLEN

SELECT ename FROM emps;

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

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

Решение № 2

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

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

Вы можете отправить свое мнение о материале его автору
Другие статьи по продуктам Oracle из рубрики "Мастерская Oracle"

Часть 2

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail