Библиографическое описание:

Шаров Д. А. СУБД Oracle: DDL триггер, как средство контроля изменения структуры базы данных // Молодой ученый. — 2010. — №3. — С. 56-58.

Контроль изменений в структуре базы данных (БД), с последующим сохранением этих изменений обычно используется для того, чтобы с одной стороны автоматически выполнять какие-либо действия с данными, а с другой стороны решить следующие задачи [1,2]:

·                    кто, когда и откуда производил изменения в структуре БД;

·                    ведение истории изменения структуры БД;

·                    уведомление об изменениях структуры БД.

СУБД Oracle имеет в своем арсенале встроенный механизма аудита изменений структуры БД и данных, но мы его рассматривать не будем, т.к. анализ и тем более какие-либо модификации в журналах аудита весьма затруднительны и трудоемки. К тому же контроль операций на уровне записей средствами стандартного аудита невозможен [3].

Начав с вопроса, как отследить изменение структуры таблиц в определенной схеме данных СУБД Oracle мы, к сожалению, увидели, что для решения данной задачи возможностей не так уж много [4, 5, 6]. Оставив за рамками рассмотрения вариант, когда с БД работает конкретное приложение и можно в хранимой процедуре или даже непосредственно из кода клиентского приложения контролировать и фиксировать изменения структуры, сосредоточимся только на СУБД. Основная идея – это создание триггера, который позволяет отловить событие, связанное с изменением структуры БД [3, 6].

Примечание. Эксперименты проводятся на СУБД Oracle 10g XE для Windows, который можно бесплатно скачать с официального сайта Oracle: http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html.

И, так, триггер для отслеживания изменений в структуре какой-либо определенной схемы имеет вид, представленный на Листинге 1.

create or replace trigger audit_schema_object create or alter or drop on SCHEMA declare

            begin

-- Тело триггера

            end;

Листинг 1. Простой код для создания DDL триггера

 

Примечание: DDL (Data Definition Language, язык определения данных) – это. подмножество SQL, используемое для определения и модификации различных структур данных.

В самом триггере мы можем попытаться узнать, что за событие привело к запуску триггера: select ora_sysevent into l_sysevent from dual. Название события находящегося в ora_sysevent помещается в l_sysevent (переменная типа строка). Значением данной переменной является CREATE, ALTER или DROP, в зависимости от типа операции. Для того чтобы узнать какой объект подвергался воздействию достаточно обратиться к ora_dict_obj_name в теле триггера. Тип объекты определяется через обращение к ora_dict_obj_type и его значения могут быть, например: ‘TABLE’ или ‘COLUMN’ . Владелец объекта определяется обращением к ora_dict_obj_owner.

Обратившись в теле триггера к представлению v$open_cursor при событии ALTER, мы можем получить исходную строчку кода (sql_text), которая привела к возникновению данного события. Полученную строчку кода, представленную в sql_text можно разбирать (парсить) в теле триггера и извлекать полезную информацию, например для получения кода команды типа ALTER (Листинг 2).

select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, sql_text

from v$open_cursor

where upper(sql_text) like ‘ALTER%’ || ora_dict_obj_name || ‘%’

and sid = (select sid from v$session where audsid=userenv('sessionid'));

Листинг 2. Запрос в DDL триггере для получения кода команды ALTER

 

Рассмотрим пример, когда необходимо в таблице log фиксировать событие, объект, пользователя осуществившего операцию создания (create), удаления (drop) или изменения (alter) таблицы. Сценарий создания таблицы приведен на Листинге 3.

create table log (

    operation varchar2(25), -- название события

    owner varchar2(25), -- владелец объекта

    nameobject varchar2(25), -- название объекта

    typeobject varchar2(25),-- тип объекта

    text varchar2(60), -- код, который привел к возникновению события

    username varchar2(30) default USER, -- имя пользователя

    datechange date default sysdate

);

Листинг3. Создание таблицы log

 

В таблице log в поле username по умолчанию будет добавляться строка содержащая имя текущего пользователя СУБД Oracle, а в поле DATECHANGE текущая дата и время. Схему данных, в которой мы будем работать, назовем METAXPO, а триггер контролирующий модификацию структуры БД назовем ddl_trigger. Таким образом, триггер ddl_trigger будет реагировать на все события, происходящие в структуре БД: create, alter, drop. Код триггера реагирующего на события, происходящие после (after) изменения структуры БД, представлен на Листинге 4.

create or replace trigger ddl_trigger after create or alter or drop on SCHEMA

declare

 l_sysevent varchar2(25);

 l_text   varchar2(1000);

begin

 if ora_dict_obj_owner <> 'METAXPO' then

   return; -- если схема не METAXPO, то проигнорируем

 end if;

 select ora_sysevent into l_sysevent from dual; -- запишем название события

  if (l_sysevent='CREATE' OR l_sysevent='DROP') then – Создание/удаление объекта

  insert into log (operation, owner, nameobject, typeobject)

select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type

from dual;

 end if;

 if (l_sysevent = 'ALTER') then -- Изменение объекта

  insert into log (operation, owner, nameobject, typeobject, text)

select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, sql_text from v$open_cursor v where upper(sql_text) like 'ALTER%' || ora_dict_obj_name || '%' and sid = (select sid from v$session where audsid=userenv('sessionid'));

 end if;

end;

Листинг4. Код триггера ddl_trigger для определенной схемы данных

 

В запросе на извлечение данных об изменении таблицы используется представление v$session в котором содержится информация об установленных соединениях. Например, кто и откуда подключен к СУБД, какой статус у подключения, название компьютера, с которого произошло подключение и т.д. Извлекая данные из этого представления, мы должны фильтровать их, по идентификатору сессии (соединения), который можем получить для текущего сеанса, обратившись к userenv('sessionid'). Кстати для того, чтобы создать триггер в схеме METAXPO, приведенный на Листинге 4, необходимо разрешить пользователю METAXPO доступ к системным представлениям v$open_cursor и v$session. Для этого необходимо находясь под пользователем sys (as SYSDBA) выполнить команды:

grant select on sys.v_$session to metaxpo;

grant select on sys.v_$open_cursor to metaxpo;

Выше приведенные команды разрешают доступ пользователю METAXPO к объектам схемы sys: v$open_cursor и v$session. Теперь пользователь METAXPO сможет создать триггер ddl_trigger, а при создании, модификации и удалении объектов схемы, например, таблиц (нас интересуют в основном таблицы и поля) в таблицу log будут добавляться соответствующие записи. Для того, чтобы выполнять логику триггера только для таблиц, можно проигнорировать объекты других типов, добавив в начало тела триггера ddl_trigger строчки кода:

 if ora_dict_obj_type<>'TABLE' then

   return;

 end if;

В вышеприведенном коде (Листинг 4) есть небольшой нюанс: выполнение операторов create, alter и drop в схеме METAXPO каким либо другим привилегированным пользователем (отличным от METAXPO) не приведет к срабатыванию триггера. Для того чтобы триггер не был ограничен уровнем схемы нужно создавать триггер уровня базы данных (database level ddl trigger), а уже в теле этого триггера фильтровать выполнение операторов DDL связанных с интересующей нас схемой. В таком случае строчка в заголовке триггера определяющая его уровень будет заменена с after create or alter or drop on SCHEMA на after create or alter or drop on database.

Отметим, что начиная с версии 9i в СУБД Oracle появилась функция ora_sql_text, выдающая текст оператора, вызвавшего срабатывание триггера [6]. Использование этой функции намного проще и изящней чем обращение к v$open_cursor для большинства задач. Более подробную информацию о доступных событиях и функциях СУБД Oracle, при модификации структуры БД, можно получить на официальном сайте Oracle [5].

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

Множество интересной информации Oracle хранит в системных таблицах и именно из одной такой системной таблицы (ALL_TAB_COLUMNS) мы и извлечем структуру таблицы, которая нас интересует. Запрос, приведенный на Листинге 5, возвращает структуру таблицы, имя которой указано в переменной tablename.

select c.column_name, c.table_name, c.data_type, c.char_length,  c.nullable from all_tab_columns c where upper(c.owner) = upper(sys_context('userenv', 'current_schema')) and trim(c.table_name) = trim(tablename);

Листинг 5. Запрос, возвращающий структуру таблицы

 

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

В данной статье мы постарались показать возможности, которые раскрываются перед разработчиком информационных систем с динамической структурой БД, на основе СУБД Oracle. Благодаря DDL триггерам можно контролировать все процессы изменения структуры БД. В дальнейшем мы планируем более подробно, осветить журнализацию изменения данных, что является актуальным для любых информационных систем. Так же логическим продолжением исследования является ведения аудита изменения структуры БД и изменения данных с клиентского приложения, а не на СУБД, что позволило ба реализовывать более сложную логику и снизить нагрузку на СУБД Oracle.

 

Библиографический список

1.                  Урман, С. Oracle8: Программирование на языке PL/SQL. -М.: Изд-во Лори, 1999. – 607 с.

2.                  Кривонос, Н. Журналирование изменений структуры БД и данных. - URL: http://www.compdoc.ru/bd/sql/log_change_of_structure_bd/.

3.                  Finningan, P. Introduction to Simple Oracle Auditing. – URL: http://www.securityfocus.com/infocus/1689.

4.                  Кайт, Т. Эффективное проектирование приложений Oracle. – М.: Изд-во «Лори», 2006. – 637 с.

5.                  Oracle 9i Application Developer’s Guide – Fundamentals. - URL: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm.

6.                  Кайт, Т. Триггеры на операторы ЯОД и аудит изменений структуры базы – URL: http://www.ln.com.ua/~openxs/projects/oracle/ora022.html.

Обсуждение

Социальные комментарии Cackle