DeepEdit!

СУБД Oracle внутри себя достаточно давно поддерживает автономные транзак­ции. Мы видим их в форме рекурсивного SQL. Например, рекурсивная транзак­ция может выполняться при выборе значения из последовательности, чтобы не­медленно увеличить значение счетчика последовательности в таблице SYS.SEQ$. Обновление таблицы SYS.SEQ$ для поддержки вашей последовательности немед­ленно фиксируется и становится видимым другим транзакциям, хотя ваша тран­закция еще и не зафиксирована. Вдобавок, если вы откатите свою транзакцию, то увеличение значения счетчика последовательности останется в силе; оно не будет откачено вместе с вашей транзакцией, поскольку уже было зафиксировано. Управление пространством, аудит и другие внутренние операции выполняются в аналогичной рекурсивной манере. 

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

таблицы является попытка читать таблицу в то время, когда инициирован ее триг­гер. Используя автономную транзакцию, вы можете запросить таблицу, но при этом не сможете увидеть изменения (для чего в первую очередь и предназначено ограничение мутирующей таблицы; таблица находится в процессе модификации, поэтому результаты запроса должны быть несогласованными). Любые решения, принятые на основе запроса из триггера, сомнительны — в этот момент времени вы читаете “старые” данные. 

Потенциально корректное применение автономной транзакции — в специализи­рованном аудите, но я подчеркиваю слова “потенциально корректное”. Существуют более эффективные пути аудита информации базы данных, нежели посредством написания специального триггера. Например, вы можете использовать пакет DBMS_FGA или саму команду AUDIT. 

Часто задаваемый разработчиками приложений вопрос звучит так: “Как я могу выполнять аудит каждой попытки модификации секретной информации и запи­сывать значения, которые они пытаются модифицировать?” Они хотят не только предотвращать попытки модификации, но также сохранять записи о таких по­пытках. До появления автономных транзакций многие разработчики пытались (и безуспешно) делать это с помощью стандартных триггеров без автономных тран­закций. Триггер должен обнаруживать UPDATE, и, увидев, что пользователь моди­фицирует данные, которых он модифицировать не должен, создавать запись ауди­та и отказывать UPDATE. К сожалению, когда триггер отвергает UPDATE, он также выполняет откат записи аудита — это типичная ситуация “все или ничего”. С ав­тономными транзакциями стало возможным безопасно выполнять аудит попыток выполнения запретных операций, одновременно откатывая эти операции. В про­цессе мы можем информировать конечного пользователя о том, что он пытается модифицировать данные, которые не имеет права модифицировать, а также о том, что его попытка зафиксирована в записи аудита. 

Интересно отметить, что родная команда Oracle AUDIT уже в течение многих лет предоставляет возможность фиксации безуспешных попыток модификации информации, используя автономные транзакции. Предоставление этого средства в распоряжение разработчиков Oracle позволяет нам выполнять собственный, более гибкий специализированный аудит. 

Рассмотрим небольшой пример. Давайте добавим триггер автономной транзак­ции к таблице, которая фиксирует след аудита, детализируя, кто пытался обно­вить таблицу, и когда это произошло, наряду с информативным сообщением о том, какие именно данные этот пользователь пытался модифицировать. Логика такого триггера должна предотвращать любые попытки обновления записи сотрудника, который (прямо или опосредованно) не подчиняется вам. 

Во-первых, создадим копию таблицы EMP из схемы SCOTT, чтобы использовать в качестве таблицы примеров: 

ops$tkyte@ORA10G> create table emp

2 as

 3 select * from scott.emp;

Table created. 

ops$tkyte@ORA10G> grant all on emp to scott;Grant succeeded. 

Мы также создадим таблицу AUDIT_TAB, в которой будем размещать информа­цию аудита. Обратите внимание, что мы используем атрибут DEFAULT в столбцах, которые должны хранить имя текущего зарегистрированного пользователя и теку­щее значение даты/времени в наш “след” аудита. 

ops$tkyte@ORA10G> create table audit_tab2 ( username varchar2(30) default user,3 timestamp date default sysdate,4 msg varchar2(4000)5 )6 /

Table created. 

Далее создадим триггер EMP_AUDIT для выполнения аудита действия UPDATE над таблицей EMP: 

ops$tkyte@ORA10G> create or replace trigger EMP_AUDIT2 before update on emp3 for each row 4 declare

6 l_cnt number;7 begin

26 end;

27 /Trigger created.Триггер создан. 

Обратите внимание на применение запроса с CONNECT BY. Это позволяет нам развернуть всю иерархию, начиная с текущего пользователя, чтобы проверить, что запись, попытка обновления которой предпринимается, относится к кому-то, кто подчиняется нам на некотором уровне. 

Ниже перечислены главные моменты, которые нужно отметить относительно этого триггера. 

PRAGMA AUTONOMOUS_TRANSACTION применяется к определению триггера. Весь триггер представляет собой “автономную транзакцию”, и потому независим от родительской транзакции, пытающейся выполнить обновление. 

Триггер пытается выполнять чтение таблицы, которую он защищает, а имен­но — таблицу EMP. Само по себе это может привести к ошибке “мутирующей” таблицы во время выполнения, но это не касается автономной транзакции. Автономная транзакция обходит эту проблему — она позволяет читать табли­цу, но с условием, что мы не увидим тех изменений, которые проводятся в таблице вышестоящей транзакцией. В таком случае следует проявлять ис­ключительную осторожность. Подобная логика должна быть тщательно про­думана. Что если текущая транзакция пытается обновить саму иерархию сотрудников? Мы не увидим этих изменений в триггере, и это следует при­нимать во внимание, проверяя корректность кода триггера. 

Этот триггер выполняет фиксацию. Раньше такое было невозможно — триг­геры никогда не фиксировали работу. Данный триггер не фиксирует ту рабо­ту, которая была причиной его вызова; вместо этого он фиксирует только ту работу, которую выполняет он сам (запись аудита). 

Итак, мы настроили таблицу EMP, имеющую замечательную иерархическую структуру (рекурсивное отношение EMPNO — MGR). Также у нас есть таблица AUDIT_TABLE, в которую мы хотим вносить записи о безуспешных попытках мо­дифицировать информацию. У нас имеется триггер, обеспечивающий выполнение нашего правила — что только наш руководитель или руководитель нашего руково­дителя (и так далее) может модифицировать нашу запись. 

Давайте посмотрим, как это работает, попытавшись обновить запись в таблице EMP: 

ops$tkyte@ORA10G> update emp set sal = sal*10;

update emp set sal = sal*10

* 

ERROR at line 1: 

ORA-20001: Access Denied 

ORA-06512: at "OPS$TKYTE.EMP_AUDIT", line 21

ORA-04088: error during execution of trigger 'OPS$TKYTE.EMP_AUDIT'

ops$tkyte@ORA10G> select * from audit_tab; 

USERNAME TIMESTAMP MSG 

OPS$TKYTE 27-APR-05 Attempt to update 7369 

Вызов триггера предотвращает операцию UPDATE, в то же время создавая по­стоянную запись о такой попытке (обратите внимание, как используется ключевое слово DEFAULT в операторе CREATE TABLE таблицы AUDIT_TAB, чтобы автоматиче­ски вносить значения USER и SYSDATE). Далее давайте зарегистрируемся как поль­зователь, который может выполнить UPDATE, и попытаемся сделать кое-что: 

ops$tkyte@ORA10G> connect scott/tiger

Connected. 

scott@ORA10G> set echo on

scott@ORA10G> update ops$tkyte.emp set sal = sal*1.05 where ename = 'ADAMS';

1 row updated.

scott@ORA10G> update ops$tkyte.emp set sal = sal*1.05 where ename = 'SCOTT';

update ops$tkyte.emp set sal = sal*1.05 where ename = 'SCOTT'

* 

ERROR at line 1: 

ORA-20001: Access Denied 

ORA-06512: at "OPS$TKYTE.EMP_AUDIT", line 21

ORA-04088: error during execution of trigger 'OPS$TKYTE.EMP_AUDIT'

В инсталляции по умолчанию демонстрационной таблицы EMP сотрудник ADAMS подчиняется SCOTT, поэтому первый оператор UPDATE выполнился успешно. Второй UPDATE, где SCOTT пытается дать самому себе прибавку, терпит провал, посколь­ку SCOTT не подчиняется SCOTT. Зарегистрируемся в схеме, содержащей таблицу AUDIT_TAB, и увидим следующее: 

scott@ORA10G> connect /

Connected. 

ops$tkyte@ORA10G> set echo on

ops$tkyte@ORA10G> select * from audit_tab; 

USERNAME TIMESTAMP MSG 

OPS$TKYTE 27-APR-05 Attempt to update 7369

SCOTT 27-APR-05 Attempt to update 7788 

Как видим, попытка SCOTT выполнить оператор UPDATE была зафиксирована.