Триггеры на операторы ЯОД и аудит изменений структуры базы

Уважаемые подписчики! Этот выпуск, как и было обещано, посвящен появившимся в версии 8i
триггерам на операторы ЯОД и их использованию для реализации аудита изменений структуры 
базы данных. Продолжаем изучать наработки Тома
Кайта...



Триггеры на операторы ЯОД в Oracle

Привет,

Мой вопрос связан с триггерами на операторы ЯОД (DDL triggers), возможности которых
существенно расширены в версии  8 . 1 . 7  по сравнению с  8 . 1 . 6 .

 Однако по-прежнему информации об операторе ЯОД, вызвавшем срабатывание триггера, очень
мало.

Не знаете ли вы какого-нибудь фокуса, который позволил бы увидеть весь текст оператора
ЯОД (да, с конструкцией хранения и другими подробностями...)?

Я думаю об извлечении текста из представления v$SQLAREA, но там появляются
только операторы ALTER.

 Идеи есть?

 Ответ Тома Кайта:

Вы можете читать любое представление словаря данных. Но текст оператора CREATEпрочитать ни откуда нельзя.
Операторы DROP читать вообще не надо (их можно
воссоздать и так). Операторы ALTER получить можно (но не забывайте, - некоторые
операторы ALTER, на замом деле, замаскированные операторы CREATE!!!alter table t add constraint - это, на самом
деле, CREATE constraint).

 Например:



tkyte@TKYTE816>; create table log
   2   (operation varchar2( 25 ),
   3    owner     varchar2( 25 ),
   4    name      varchar2( 25 ),
   5    extra     varchar2( 4000 )); 
  
Table created. 
  
tkyte@TKYTE816>; create or replace trigger ddl_trigger
   2   after create or alter or drop on SCHEMA3  declare
   4       l_sysevent varchar2( 25 );
   5       l_extra    varchar2( 4000 );
   6   begin
   7       select ora_sysevent into l_sysevent from dual;
   8 
   9       if (l_sysevent in ('DROP', 'CREATE'))
  10       then
  11           if l_sysevent = 'CREATE'
  12           then
  13           begin
  14               select 'storage (initial ' || initial_extent ||
  15                      ' next ' || next_extent || ' ....)'
                   into l_extra
  16                 from all_tables
                  where table_name = ora_dict_obj_name
  17                  and owner = user;
  18           exception
  19               when no_data_found then null;
  20           end;
  21           end if;
  22 
  23           insert into log
  24           select ora_sysevent, ora_dict_obj_owner,
  25                          ora_dict_obj_name, l_extra
  26             from dual;
  27       elsif (l_sysevent = 'ALTER')
  28       then
  29           insert into log
  30           select ora_sysevent, ora_dict_obj_owner,
  31                          ora_dict_obj_name, sql_text
  32             from v$open_cursor
  33            where upper(sql_text) like 'ALTER%'  ||
  34                                 ora_dict_obj_name || '%'
  35              and sid = (select sid
  36                           from v$session
  37                          where audsid=userenv('sessionid'));
  38       end if;
  39   end;
  40   /
 
Trigger created.
 
tkyte@TKYTE816>; drop table t;

Table dropped. 

tkyte@TKYTE816>; create table t ( x int );

Table created. 

tkyte@TKYTE816>; alter table t add y date;

Table altered. 

tkyte@TKYTE816>; select * from log; 

OPERATION OWNER NAME EXTRA
 --------- ----- ---- -------------------------------------------
 
DROP      TKYTE T
CREATE    TKYTE T    storage (initial  131072  next  131072  ....)
ALTER     TKYTE T    alter table t add y date





Комментарий читателя от  4  декабря  2001  года

Привет, Том!

Твой ответ был очень полезен.

Я создал  "DDL_TRIGGER"  по твоему примеру, и он отлично работает, вставляя
подробности в таблицу LOG. Но при удаленном выполнении операторов ЯОД любым 
другим пользователем, имеющим соответствующие привилегии в моей схеме, эти изменения
триггером не перехватываются... Я понимаю, что этот триггер ограничен уровнем
схемы. Но чтобы избежать использования подсистемы аудита, я хотел бы использовать
подобный триггер. Не могли бы вы посоветовать, как отслеживать операторы ЯОД, 
выполняемые в данной схеме любым привилегированным пользователем?

Ответ Тома Кайта

Надо создавать триггер на операторы ЯОД на уровне базы данных (DATABASE level DDL trigger),
который будет учитывать, в какой схеме находится соответствующий объект. Следить надо
за всеми операторами ЯОД, а перехватывать - только те, которые вас интересуют.

Комментарий читателя от  19  декабря  2001  года

Можно ли отслеживать операторы select для таблицы с помощью триггеров
вида:

Create or replace trigger for select

Ответ Тома Кайта

Нет, в 8i - нет. В 8i можно выполнять аудит операторов select, а в 9i есть
детальный аудит, который дает новый уровень функциональности (очень похожий на
представленный вами  "триггер на select" ).

Комментарий читателя от  15  января  2002  года

Привет, Том!

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

Однако есть ряд исключений, которые сбивают с толку этот триггер. Одно их исключений,
с которым я столкнулся - это когда в таблицу должна быть вставлена строка из-за перекомпиляции
недействительного триггера (произошло событие alter). Однако, она в представленииv$open_cursor соответствующей
информации нет, что приводит к возбуждению
исключительной ситуации no_data_found. Сейчас я использую обработчик,
игнорирующий эту исключительную ситуацию. Нет ли решения получше?.

Не подскажешь ли также, как отличить оператор alter xxx compile от
действительного изменения объекта? При изменении одного объекта, зависимые от него
объекты перекомпилируются, и обновляется также значение в столбце last_ddl_time представления dba_objects для
этих зависимых объектов. Итак, как можно
выявить эту ситуацию по словарю данных?

Ответ Тома Кайта

Перейдите на версию 9i, и сможете получать вызвавший срабатывание оператор с помощью
вызова функции...

Если нет текста в v$open_cursor в журнал аудита ничего записывать не надо.

Комментарий читателя от  16  января  2002  года

А как можно получить тело триггера или его код по словарю данных?

Ответ Тома Кайта

Этот сценарий подходит для многих случаев:



set echo off
set long  50000 
set verify off
set feedback off
set termout off
set heading off
set pagesize  0 

spool   1 ..sql select
'create or replace trigger "' ||
         trigger_name || '"' || chr( 10 )||
 decode(substr( trigger_type,  1 ,  1 ),
        'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF') ||
              chr( 10 ) ||
 triggering_event || chr( 10 ) ||
 'ON "' || table_owner || '"."' ||
       table_name || '"' || chr( 10 ) ||
 decode(instr( trigger_type, 'EACH ROW'),  0 , null,
            'FOR EACH ROW') || chr( 10 ) ,
 trigger_body
from user_triggers
where trigger_name = upper(' 1')
/
prompt /

spool off
set verify on
set feedback on
set termout on
set heading on





Комментарий читателя от  8  марта  2002  года

Привет, Том!

Мне очень понравился сценарий ddl_triger. У нас есть одна основная база данных
и множество пользователей, ее использующих и изменяющих, так что действительно
необходимо создавать триггер на уровне базы данных, чтобы контролировать изменения. 
Недавно я обнаружил, что в таблицу log вносятся записи при изменении пароля
пользователем. Интересно, нельзя ли избавиться от записей, связанных с изменением пароля?

Ответ Тома Кайта

Это же ваш триггер - перепишите его, чтобы он делал то, что нужно!

 Вместо



 29           insert into log
  30           select ora_sysevent, ora_dict_obj_owner,
  31                          ora_dict_obj_name, sql_text
  32             from v$open_cursor
  33            where upper(sql_text) like 'ALTER%'  ||
  34                                 ora_dict_obj_name || '%'
  35              and sid = (select sid
  36                           from v$session
  37                          where audsid=userenv('sessionid')); 





выбирайте sql_text и если он соответствует шаблону'ALTER%USER%IDENTIFIED%BY%' - выбирайте подстроку и помещайте
в таблицу logтолько ее.

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

Комментарий читателя от  11  апреля  2002  года

Я не знаю, почему, но при попытке создания триггера я получил сообщение об
ошибке:



( 1 ):PL/SQL: ORA- 00942 : table or view does not exit





Ответ Тома Кайта

Почитайте
 
http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html 

чтобы понять зачем, а потом подключитесь как sys или пользователь с привилегиейsysdba, выполните:



grant select on v_$open_cursor to ВЛАДЕЛЕЦ_ТРИГГЕРА;
grant select on v_$session to ВЛАДЕЛЕЦ_ТРИГГЕРА;





и попытайтесь еще раз.



Оригинал обсуждения этого вопроса можно найтиздесь.



Аудит операторов ЯОД

Привет, Том!

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

Мой вопрос связан, собственно, с документированием операторов alter. В нашей БД 
изменения объектов схемы выполняются с помощью  "alt-сценариев" , которые запускают в sqlplus.
Сценарий для удаления/добавления столбца таблицы может называться, например, 
 "customer.1 . 1 .alt", где customer - имя таблицы, а две цифры представляют, 
соответственно, номер версии и порядковый номер alt-сценария. Если ли способ получить
реальное имя сценария из таблицы словаря данных Oracle, системной переменной или переменной
контекста так, чтобы при каждом выполнении сценария его имя могло вставляться в таблицуddl_audit? Это, конечно,
пригодилось бы и для операторов create и drop,
но имена этих сценариев легко построить по значениям в ora_sysevent,ora_dict_obj_name и ora_dict_obj_type.

Я могу помещать имя сценария в комментарии в тексте сценария, а затем извлекать его
из поля sql_text представления v$open_cursor, но поле sql_text имеет длину только  60  символов, поэтому я теряю
необходимые части sql_text и 
вынужден встраивать комментарии, что неэлегантно, например:

alter table customer  /*customer.1.1.alt*/ 
drop column obsolete_column;

Я бы хотел элегантно извлекать откуда-нибудь имя, следующее за символом 
'@', например, @'customer.1.1.alt', в переменную в триггере для любых
сценариев, выполняемых в среде sqlplus и вызывающих срабатывание триггера на уровне схемы.

Ответ Тома Кайта

Если для установки используется sqlplus, элегантное решение существует. Утилита
sqlplus вставляет имя и даже глубину вложенности текущего сценария в столбец MODULEпредставления v$session.
Рассмотрим пример:



ops$tkyte@ORA817DEV.US.ORACLE.COM>; create table log
   2   (operation varchar2( 25 ),
   3    script    varchar2( 255 ),
   4    owner     varchar2( 25 ),
   5    name      varchar2( 25 ),
   6    extra     varchar2( 4000 ));

Table created. 

ops$tkyte@ORA817DEV.US.ORACLE.COM>; create or replace trigger ddl_trigger
   2   after create or alter or drop on SCHEMA
   3   declare
   4       l_sysevent varchar2( 25 );
   5       l_extra    varchar2( 4000 );
   6   begin
   7       select ora_sysevent into l_sysevent from dual;
   8 
   9       if (l_sysevent in ('DROP','CREATE'))
  10       then
  11           if l_sysevent = 'CREATE'
  12           then
  13           begin
  14               select 'storage (initial ' || initial_extent ||
  15                      ' next ' || next_extent || ' ....)'
  16                 into l_extra
  17                 from all_tables
  18                where table_name = ora_dict_obj_name
  19                  and owner = user;
  20           exception
  21               when no_data_found then null;
  22           end;
  23           end if;
  24 
  25           insert into log
  26           select ora_sysevent, module, ora_dict_obj_owner,
  27                          ora_dict_obj_name, l_extra
  28             from v$session
  29                    where sid = (select sid from v$mystat where rownum= 1 );
  30       elsif (l_sysevent = 'ALTER')
  31       then
  32           insert into log
  33           select ora_sysevent, module, ora_dict_obj_owner,
  34                          ora_dict_obj_name, sql_text
  35             from v$open_cursor,
  36                  (select module
  37                     from v$session
  38                    where sid = (select sid from v$mystat where rownum= 1 ))
  39            where upper(sql_text) like 'ALTER%'  ||
  40                                 ora_dict_obj_name || '%'
  41              and sid = (select sid
  42                            from v$session
  43                           where audsid=userenv('sessionid'));
  44       end if;
  45   end;
  46   / 
 
Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>; create table cust (x int primary key);

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>; @customer. 1 . 1 .alt
ops$tkyte@ORA817DEV.US.ORACLE.COM>; alter table cust add new_column number; 

Table altered. 

ops$tkyte@ORA817DEV.US.ORACLE.COM>; column script format a25
ops$tkyte@ORA817DEV.US.ORACLE.COM>; column extra format a50
ops$tkyte@ORA817DEV.US.ORACLE.COM>; select script, extra from log;

SCRIPT                    EXTRA
 ------------------------- --------------------------------------------------
 
 01 @ test.sql
 01 @ test.sql              storage ( initial  524288  next  524288  .... )
 02 @ customer. 1 . 1 .alt      alter table cust add new_column number





Комментарий читателя от  9  ноября  2002  года

Привет, Том!

Это очень полезный пример. Если ты не против вопроса в догонку, где лучше всего искать информацию
о том что содержится в столбцах представлений словаря данных? Я бы хотел в этом разобраться.
Мне кажется, описание в документации Oracle несколько сжато, хотя, может, я не там смотрел. 
Еще раз спасибо за помощь и поддержку такого полезного и информативного сайта.

Ответ Тома Кайта

Я всегда пользуюсь следующим источником:

http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/nav/docindex.htm#index-REF 

но лакомые кусочки типа:

  "А, кстати, в столбце MODULE представления v$session будет
находиться имя сценария sqlplus, если сеанс использует sqlplus." 

вы вряд-ли вообще где0нибудь найдете. 

В справочном руководстве об этом не будет написано, потому что это свойство/возможность SQL*Plus. 
Столбец module содержит значение, которое подключенное к базе данных приложение туда вставило.
Я по опыту знаю, что sqlplus вставляет туда имя сценария.   "Найти"  этот факт нигде не удастся.

Комментарий читателя от  11  ноября  2002  года

Есть ли подобный встроенный механизм в версии 9i?

Ответ Тома Кайта

Представленный пример работает и в 9i, но может понадобиться добавить в сценарий команду

 

set appinfo on





поскольку стандартное его значение, похоже, изменилось.