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

Выпуск 22

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

Уважаемые подписчики! Этот выпуск, как и было обещано, посвящен появившимся в версии 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 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 || ' ....)'
                   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

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

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

Copyright © 2002 Oracle Corporation

В следующем выпуске

Кое-что про массивы (в том числе, ассоциативные) в Oracle. Выпуск выйдет на следующей неделе. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.