1170 Глава 15

гим, сам он должен иметь привилегии CONNECT и RESOURCE с опцией WITH ADMIN OPTION. Кроме того, поскольку предполагается создание и удаление учетных записей в триггере, надо предоставить соответствующие привилегии CREATE и DROP непосредственно, как показано выше. Эти привилегии должны быть предоставлены пользователю непосредственно, а не через роль, поскольку триггеры всегда выполняются с правами создателя, а в этом режиме роли не учитываются (подробнее об этом см. в главе 23).

Теперь создадим таблицу приложения, в которой будет храниться информация о пользователях. Для этой таблицы мы создадим триггер для событий BEFORE INSERT или DELETE. Этот триггер будет гарантировать, что ни один пользователь (включая владельца) не сможет вставить или удалить данные из этой таблицы непосредственно. Нам надо, чтобы все вставки/удаления выполнялись через представление и чтобы при этом обязательно выполнялись операторы ЯОД.

В представленном ниже коде MY CALLER - небольшая функция, которую я часто использую (совместно с подпрограммой WHO CALLED ME). Код этих подпрограмм можно найти в приложении Основные стандартные пакеты в конце книги, в разделе, посвященном пакету DBMS UTILITY. Эта функция просто возвращает имена процедур/функций/триггеров, вызвавших ее. Если MY CALLER вызвана не из триггера по представлениям (который еще надо создать), выполнение этой операции запрещено.

demo ddl@TKYTE816> create table application users tbl

2 (uname varchar2(30) primary key,

3 pw varchar2(30),

4 role to grant varchar2(4000)

5 ) Table created.

demo ddl@TKYTE816> create or replace trigger application users tbl bid

2 before insert or delete on application users tbl

3 begin

4 if (my caller not in (DEMO DDL.APPLICATION USERS IOI,

5 DEMO DDL.APPLICATION USERS IOD))

6 then

7 raise application error(-20001, Cannot insert/delete directly);

8 end if;

9 end;

10 /

Trigger created.

Создадим представление с триггерами INSTEAD OF, которые и будут выполнять необходимые действия. А для представления создадим триггер INSTEAD OF INSERT, позволяющий создавать учетные записи. Создадим также для представления триггер INSTEAD OF DELETE. Он будет вызывать выполнение оператора DROP USER. Можно расширить пример, воспользовавшись триггерами INSTEAD OF UPDATE, которые позволяют добавлять роли и изменять пароли с помощью простых операторов UPDATE.

Срабатывая, триггер INSTEAD OF INSERT выполняет два оператора:

Автономные транзакции 1171

оператор, аналогичный GRANT CONNECT, RESOURCE TO SOME USERNAME IDENTIFIED BY SOME PASSWORD;

оператор вставки INSERT в созданную ранее таблицу APPLICATION USERS TBL.

Причина использования оператора GRANT вместо последовательности CREATE USER, а затем уже GRANT в том, что при этом операторы COMMIT, CREATE USER, GRANT и COMMIT выполняются за один шаг. Причем, если этот единственный оператор (предоставления привилегий) завершится неудачно, не придется удалять учетную запись пользователя вручную. Оператор CREATE USER может выполниться успешно, а при выполнении GRANT может произойти сбой. Ошибки, возникающие при выполнении оператора GRANT, все равно надо перехватывать, чтобы удалить только что вставленную строку.

Поскольку операторы INSERT и GRANT выполняются для каждой вставляемой в представление строки, можно с уверенностью утверждать: если строка существует в реальной таблице, значит, соответствующая учетная запись успешно создана, а если нет строки, то нет и учетной записи. Потенциальная возможность сбоя все же остается, полностью избавиться от нее нельзя. Если после вставки строки в таблицу

APPLICATION USERS TBL оператор GRANT не срабатывает, а удалить только что

вставленную строку невозможно (из-за сбоя системы или недоступности табличного пространства, содержащего таблицу APPLICATION USERS TBL и т.п.), мы получим рассогласование. Не забывайте, что оператор GRANT на самом деле представляет собой тройку операторов COMMIT/GRANT/COMMIT, как и все операторы ЯОД, поэтому перед сбоем оператора GRANT результат оператора INSERT уже зафиксирован. Временной промежуток, когда это может случиться, однако, очень мал, чтобы можно было без опасений пользоваться этим методом.

Теперь создадим представление и описанные выше триггеры:

demo ddl@TKYTE816> create or replace view

2 application users

3 as

4 select * from application users tbl

View created.

demo ddl@TKYTE816> create or replace trigger application users IOI

2 instead of insert on application users

3 declare

4 pragma autonomous transaction;

5 begin

6 insert into application users tbl

7 (uname, pw, role to grant)

8 values

9 (upper(:new.uname), :new.pw, :new.role to grant); 10

11 begin

12 execute immediate

13 grant :new.role to grant

14 to M :new.uname

1172 Глава 15

15 identified by M :new.pw;

16 exception

17 when others then

18 delete from application users tbl

19 where uname = upper(:new.uname);

20 commit;

21 raise;

22 end;

23 end;

24 /

Trigger created.

Итак, триггер INSTEAD OF INSERT no этой таблице сначала вставляет строку в таблицу APPLICATION USERS TBL. Затем он выполняет оператор GRANT для создания учетной записи пользователя. Оператор GRANT фактически представляет собой тройку COMMIT/GRANT/COMMIT, так что после его выполнения строка в таблице APPLICATION USER TBL зафиксирована. Если оператор GRANT успешно выполнен, значит, автономная транзакция зафиксирована, и работа триггера завершается. Если же оператор GRANT не сработал (потому что учетная запись пользователя уже существует, имя пользователя - недопустимое и т.д.), мы перехватываем ошибку, явно удаляем вставленную строку и фиксируем удаление. Затем мы снова возбуждаем исключительную ситуацию.

В данном случае мы выполняем оператор INSERT, а затем - оператор ЯОД, поскольку отменить INSERT намного проще, чем отменить создание учетной записи пользователя (для отмены сделанного предпочтительнее выполнять оператор DELETE, а не DROP). В конечном итоге триггер гарантирует, что либо вставлена строка в таблицу APPLICATION USERS TBL и создана соответствующая учетная запись пользователя, либо ни одно из этих действий не выполнено.

Теперь перейдем к триггеру INSTEAD OF DELETE, удаляющему строку и учетную запись пользователя:

demo ddl@TKYTE816> create or replace trigger application users IOD

2 instead of delete on application users

3 declare

4 pragma autonomous transaction;

5 begin

6 execute immediate drop user :old.uname;

7 delete from application users tbl

8 where uname = :old.uname;

9 commit;

10 end;

11 /

Trigger created.

Я умышленно изменил порядок выполнения действий. В этом триггере сначала выполняется оператор ЯОД, а потом - оператор ЯМД, а раньше бтло наоборот. Причина снова связана с простотой восстановления в случае ошибки. Если оператор DROP USER не срабатывает, отменять ничего не нужно. Вероятность сбоя при выполнении оператора DELETE нулевая. Нет никаких требований целостности, которые могли бы поме-