Контроль изменений в структуре базы данных (БД), с последующим сохранением этих изменений обычно используется для того, чтобы с одной стороны автоматически выполнять какие-либо действия с данными, а с другой стороны решить следующие задачи [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.
