Пользователь или схема?

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

СУБД Oracle, как и все ее реальные конкуренты - старая система. Недавно праздновали ее 25-летие. Такое долголетие было бы невозможно без ряда технических решений, удачно (с этой точки зрения) предложенных еще в стародавние времена. Но наряду с этим в системе есть и примеры дефектов начального проектирования. Когда-то они не казались таковыми, а потом исправлять их стало очень сложно, так что только в самых последних версиях, например в 9-ой, стали прощупываться пути решения нечаянно запрограммированной проблемы. Такой является проблема "пользователей" и "схем".

Проблема

В Oracle понятия "схема" и "пользователь" нераздельно слились воедино. Формально два разных слова "user" и "schema" используются в Oracle для обозначения одного и того же: "схемы-пользователя". Документация на этот счет стыдливо говорит, что "при заведении пользователя [с помощью предложения CREATE USER - автор] автоматически создается схема с таким же именем". С другой стороны, отдельных манипуляций со схемами в Oracle не предусмотрено (команда CREATE SCHEMA в Oracle обманчива; она не создает схему, как можно было бы подумать), вот и выходит, в системе понятий Oracle "схема" = "пользователь".

Для разработчика же эти два понятия не идентичны. Так, схема представляет собой своего рода контейнер хранимых в БД объектов, несущий традиционно двойную функциональную нагрузку: как средства организации данных (объектов в базе много, но не всем приложениям все они интересны) и как средство защиты данных от посторонних приложений. Пользователь же, по своей изначальной идее - это конкретное лицо, которое может подключаться к СУБД для работы с теми или иными данными, проверяться на наличие полномочий, контролироваться на предмет совершаемых действий и т. д.

Данные принадлежат информационной системе, предприятию, а пользователи могут наниматься и увольняться. Как сымитировать такой способ работы в Oracle?

Решение

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

(1)

Для хранения объектов "отдела кадров" создаем схему (-пользователя) HR:

CREATE USER hr IDENTIFIED BY hr;

Далее по мере необходимого уточняем все свойства этой схемы, например:

ALTER USER hr DEFAULT TABLESPACE hr_ts DEFAULT TABLESPACE temp; и так далее.

(2)

Для физических лиц создаем пользователей Oracle, например:

CREATE USER pete IDENTIFIED BY thisismepete;
CREATE USER mary IDENTIFIED BY maryiam;
:

Далее пользователям нужно приписать необходимые свойства. В типичном случае все они одинаковы, так что имеет смысл написать один-единственный сценарий, который наделял бы каждого нового "Петю" или "Машу" требуемыми полномочиями. Что туда должно входить ? Как минимум, системная привилегия CREATE SESSION. Но кроме этого, для доступа к своим таблицам от имени HR следует выдать что-то вроде

GRANT SELECT, INSERT, UPDATE, DELETE ON main_hr_table TO pete;

(3)

Это еще не все. Пользователь PETE действительно теперь сможет подключаться к СУБД от своего имени и работать с таблицей MAIN_HR_TABLE, однако ссылаться на нее он будет вынужден по полному имени: HR.MAIN_HR_TABLE, так как это не его таблица. Можно ли избежать этого и заставить его чувствовать себя "как дома"? Можно. Ему достаточно выдать:

ALTER SESSION SET CURRENT_SCHEMA=hr;

Удобнее, однако, эту команду "завернуть" в триггер, срабатывающий при подключении к "схеме" PETE, то есть выдать, например, от имени SYS:

CREATE OR REPLACE TRIGGER set_hr_schema_for_pete
AFTER LOGON ON pete.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET
CURRENT_SCHEMA=hr';
END;
/

Теперь, подключаясь к СУБД, пользователь PETE будет видеть объекты HR "как свои", по короткому имени, правда свои собственные таблицы он вынужден будет называть "целиком", например PETE.MY_PETE_TABLE, но нам, кажется, это и не важно.

Особенности предложенного решения

- его надо автоматизировать
- невозможно создавать и удалять объекты
- можно использовать системный аудит

Альтернатива

Предложена в Oracle 9.0: "корпоративные пользователи" и сервер имен.