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

Шаров Д. А. СУБД 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.

Основные термины (генерируются автоматически): СУБД Oracle, структуры БД, create or alter, alter or drop, изменения структуры, изменения структуры БД, after create or, структуру таблицы, create or replace, базы данных, структуре БД, or replace trigger, теле триггера, триггер ddl_trigger, изменений структуры БД, изменения данных, данных СУБД oracle, изменениях структуры БД, СУБД Oracle 10g, изменением структуры БД.

Обсуждение

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