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

шать удалению строки. При большой вероятности неудачного выполнения оператора DETE.TE из-за имеющихся требований целостности сстлок, порядок выполнения действий можно изменить (по аналогии с триггером INSTEAD OF INSERT).

Теперь протестируем решение: вставим запись о пользователе в представление, проверим, создана ли учетная запись пользователя, и, наконец, удалим учетную запись пользователя.

demo ddl@TKYTE816> select * from all users where username = NEW USER; no rows selected

demo ddl@TKYTE816> insert into application users values 2 (new user, pw, connect, resource);

1 row created.

demo ddl@TKYTE816> select * from allusers where username = NEWUSER; USERNAME USER ID CREATED

NEW USER 235 15-APR-01

demo ddl@TKE816> delete from applicationusers where uname = NEWUSER; 1 row deleted.

demo ddl@TKYTE816> select * from all users where username = NEW USER;

no rows selected

(Полученное вами при выполнении этого примера значение USER ID скорее всего будет отличаться от 235. Не удивляйтесь - это вполне объяснимо.) Наконец, убедимся, что нельзя удалять и вставлять данные непосредственно в реальную таблицу.

demo ddl@TKYTE816> insert into application users tbl values

2 (new user, pw, connect, resource);

insert into application users tbl values *

ERROR at line 1:

ORA-20001: Cannot insert/delete directly

ORA-06512: at DEM0 DDL.APPLICATION USERS TBL BID , line 5 ORA-04088: error during execution of trigger ->DEMO DDL.APPLICATION USERS TBL BID

demo ddl@TKYTE816> delete from application users tbl; delete from application users tbl

ERROR at line 1:

ORA-20001: Cannot insert/delete directly ORA-06512: at DEMO DDL.APPLICATION USERS TBL BID , line 5 ORA-04088: error during execution of trigger ->DEMO DDL.APPLICATION USERS TBL BID

Вот и все. Триггеры обеспечивают добавление и удаление учетных записей при вставке и удалении строк из таблицы базы данных. С помощью триггеров INSTEAD OF можно обеспечить безопасность данной операции за счет выполнения компенсирующих транзакций, гарантируя при необходимости синхронность изменения таблиц приложения и

1174

Глава 15

выполнения операторов ЯОД. Можно пойти еще дальше и создать триггеры на события базы данных, срабатывающие при удалении учетной записи пользователя с помощью оператора DROP, чтобы исключить удаление учетной записи без изменения представления.

Запись в базу данных

В сервере Oracle 7.1 впервые появилась возможность расширять набор встроенных функций SQL с помощью функций, реализованных на языке PL/SQL. Это очень мощная возможность, особенно теперь, когда эти функции можно писать не только на языке PL/SQL, но и на Java или С. В прошлом функции, вызываемые в операторах SQL, не должны б1ли изменять состояние базы данных (Write No Database State - WNDS). Если функция выполняла операторы INSERT, UPDATE, DELETE, CREATE, ALTER, COMMIT и т.д. или вызывала процедуру или функцию, выполняющую подобные действия, ее нельзя бтло использовать в SQL-операторах.

С помощью автономных транзакций мы теперь можем изменять состояние базы данных в функциях, вызываемых в SQL-операторах. Это требуется не так уж часто:

строгая проверка; необходимо знать, какие данные видел каждый из пользователей, или надо записать идентификатор каждой записи, запрошенной у системы;

средство создания отчетов позволяет в1полнгь только SQL-операторы SELECT;

абсолютно необходимо по ходу построения отчета вызывать хранимую процедуру, выполняющую ряд вставок (например, заполняющую таблицу параметров для другого отчета).

Давайте рассмотрим, как решить эти проблемы.

Строгая проверка

Я знаю ряд правительственных учреждений, где из соображений конфиденциальности необходимо регистрировать, кто видел различные части записи. Например, налоговая служба накапливает детальные данные о том, сколько вы заработали, что вам принадлежит и т.п. Когда кто-то запрашивает данные для того или иного лица и видит эту конфиденциальную информацию, необходимо зарегистрировать это действие в журнале проверки. По этому журналу со временем можно будет понять, не получают ли сотрудники записи, которые не имеют права получать, или ретроспективно определить, кто обращался к соответствующим записям в случае публикаций в прессе или других утечек информации.

С помощью автономных транзакций и представлений можно реализовать такую проверку ненавязчиво и абсолютно прозрачно для пользователей, независимо от используемых ими инструментальных средств. Они не смогут обойти эту систему проверки, и при этом она не будет им мешать. При этом, естественно, для выполнения запросов понадобятся дополнительные ресурсы, но это вполне подходит для ситуаций, когда записи выбираются по одной, а не сотнями или тысячами. С учетом этих ограничений реализация получается достаточно простой. Используя таблицу ЕМР в качестве шаблона, можно реализовать проверку по столбцам HIREDATE, SALARY и COMMISSION, и когда кто-либо просматривает, например, данные о зарплате (SALARY), мы будем знать, кто их просматривал и какие именно записи увидел. Начнем с создания таблицы для

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

1175

журнала проверки обращений к таблице ЕМР, которую мы скопировали из схемы полователя SCO ранее в этой главе:

tkyte@TKYTE816> create table audit trail

2 (username varchar2(3 0) ,

3 pk number,

4 attribute varchar2(30) ,

5 dataum varchar2(255),

6 timestamp date

Table created.

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

tkyte@TKYTE816>

2 as

create or replace package audit trail pkg

10 11

12 end;

13 /

function record(p pk in number,

p attr in varchar2, p dataum in number)

function record(p pk in number,

p attr in varchar2, p dataum in varchar2)

function record(p pk in number,

p attr in varchar2,

p dataum in date) return date;

return number;

return varchar2;

Package created.

Итак, теперь все готово для реализации тела пакета. Каждая из объявленных выше функций RECORD вызывает внутреннюю процедуру LOG. Процедура LOG выполняется как автономная транзакция, вставляющая и фиксирующая запись в таблицу проверки. Обратите внимание, в частности, на то, как представленная ниже функция RECORD, возвращающая данные типа DATE автоматически преобразует дату в строку с сохранением времени:

tkyte@TKYTE816> create or replace package body audit trail pkg

2 as

procedure log(p pk in number,

p attr in varchar2, p dataum in varchar2)

pragma autonomous transaction; begin