При работе с общей БД часто возникает необходимость обеспечить разным пользователям разное видение одних и тех же таблиц. Хочется, чтобы один пользователь при обращении к таблице видел одни данные, а другой - другие. Как это можно сделать в 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.
Он более трудоемок, и о нем будет рассказано в следующей статье.