Физическое проектирование. 
Аудит базы данных

Для автоматического определение существующих в базе данных таблиц и создания для них таблиц аудита была использована специальная переменная типа CURSOR.

Как правило, курсоры используют для извлечения из базы данных определенного подмножества хранимой в ней информации. В каждый момент времени прикладной программой может быть проверена одна строка курсора. SQL Server поддерживает два различных типа курсоров: курсоры Transact_SQL и курсоры API (курсоры программного интерфейса приложений). Курсоры API создаются внутри приложения, использующего объекты Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC или DB_Library. Каждое из этих API поддерживает несколько отличающихся функциональных возможностей и использует различный синтаксис. В данной работе использовались курсоры Transact_SQL.

При работе с курсорами основными действиями над ними являются:

• — создание или объявление курсора;
• — открытие курсора (наполнение курсора данными, сохраняющимися в многоуровневой памяти);
• — выборка из курсора и изменение строк данных с его помощью;
• — закрытие курсора (после этого он недоступен для пользовательских программ);
• — освобождение курсора (удаление его как объекта, так как закрытие курсора необязательно освобождает ассоциированную с ним память).

Управление курсором происходит с помощью следующих команд:

• — DECLARE — создание или объявление курсора;
• — OPEN — открытие курсора (наполнение его данными);
• — FETCH — выборка из курсора и изменение строк данных с его помощью;
• — CLOSE — закрытие курсора;
• — DEALLOCATE — освобождение курсора, т. е. удаление курсора как объекта.

На рисунке 2 представлен листинг создания курсора для обработки множества значений (названий исходных таблиц базы данных), полученных из системной таблицы INFORMATION_SCHEMA.TABLES.

При реализации таблицы аудита UpdateLog была решена следующая проблема: чтобы минимизировать влияние на ресурсы системы было принято решение записывать изменения всех полей в одно поле таблицы аудита — new_value. Однако для сохранения данных разных типов необходимо создать поле типа sql_variant. аудит база данные программный Объекты типа sql_variant могут хранить данные SQL Server любого типа, кроме ntext, text, varchar (max), nvarchar (max), image, varbinary (max), timestamp, xml и типов данных, определяемых пользователем. Каждый объект столбца sql_variant состоит из двух частей: собственно, данные и метаданные, которые описывают значение (например, базовый тип данных поля, максимальный размер, точность и collation — сопоставление).

Максимальная длина значения типа данных sql_variant равна 8016 байт. Сюда включены тип и значение исходного типа. Максимальная длина значения соответствующего исходного типа составляет 8000 байт.

Типу данных sql_variant может быть присвоено значение по умолчанию. Этот тип данных в качестве значения может содержать NULL, однако значение NULL не будет иметь исходного типа. Также тип данных sql_variant не может в качестве исходного иметь другой тип данных sql_variant.

Протокол ODBC не полностью поддерживает тип sql_variant. Поэтому столбцы типа sql_variant, которые запрашиваются через Microsoft OLE DB для ODBC, возвращаются в виде двоичных данных.

Листинг создания таблиц аудита AuditInf, UpdateLog и InstDelLog представлен на рисунках 3−5 соответственно.

Рис. 4. Листинг создания таблицы UpdateLog

Рис. 5. Листинг создания таблицы InstDelLog

В коде создания таблиц используется процедура ident_PK, которая возвращает имя и тип столбца первичного ключа для заданной таблицы. Листинг этой процедуры представлен на рисунке 6.

Рис. 6. Листинг создания процедуры ident_PK

После создания таблиц для аудита, в которых будет храниться информация обо всех изменениях данных в базе данных, был создан один триггер, который будет записывать все эти изменения в нужные таблицы.

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

Триггер является очень полезным, но в то же время и очень опасным средством, так как при его неправильном проектировании и запуске могут произойти серьезные отрицательные последствия, например, может быть удалена целая база данных или ее часть. Именно поэтому триггеры нуждаются в тщательной проверке и отладке. В реализации СУБД Microsoft SQL Server используется структура создания или изменения триггера, представленная на рисунке 7.

Рис. 7. Структура триггера в Microsoft SQL Server

В нашем случае был создан триггер, который вызывается вместо выполнения команд (триггер INSTEAD OF), т. е. переопределяет действия триггерных операций, и реагирует на все три возможные в данном случае команды: DELETE, INSERT и UPDATE.

При выполнении операций DML (добавление, удаление и изменение записей в таблицах) в Microsoft SQL Server используются две специальные таблицы: inserted и deleted. В них находятся списки строк, которые по завершении транзакции будут удалены или вставлены в таблицу. Структура специальных таблиц inserted и deleted полностью совпадает со структурой таблицы, к которой привязан триггер. Таблицы inserted и deleted создаются для каждого триггера, и поэтому любой другой триггер не имеет к ним доступа [10]. Содержимое таблиц inserted и deleted может отличаться в зависимости от типа операции, которая вызвала выполнение триггера:

• 1. При выполнении команды INSERT (вставка) в таблице inserted будут содержаться все строки, которые пользователь попытался вставить в таблицу, при этом в таблицу deleted не будет добавлено ни одной записи. После завершения работы триггера вставленные пользователем строки из таблицы inserted будут перемещены в исходную таблицу.
• 2. При выполнении команды DELETE (удаление) в таблице deleted будут содержаться все строки, которые пользователь попытался удалить из таблицы, при этом в таблицу inserted не будет добавлено ни одной записи.
• 3. При выполнении команды UPDATE (изменение) в таблице deleted происходит поиск старого значения изменяемой строки, которое будет удалено из нее после завершения работы триггера. При этом новое значение будет находиться в таблице inserted. После успешного срабатывания триггера эта строка будет добавлена в исходную таблицу.

Перед созданием триггера были объявлены необходимые переменные:

• — @o_type — переменная, в которой хранится название операции (UPDATE, INSERT или DELETE);
• — @name_audinf — переменная, в которой хранится название таблицы для общей информации об аудите;
• — @name_audupd — переменная, в которой хранится название таблицы для фиксации изменений, произошедших по команде UPDATE;
• — @name_audinsdel — переменная, в которой хранится название таблицы для фиксации изменений, произошедших по командам INSERT или DELETE;
• — @SQL — переменная для хранения SQL_кода;
• — @headerid — переменная, в которую записывается первичный ключ.

Название триггера было задано как «@name_Table + '_trig_aud'». Это означает, что в названии триггера присутствует название таблицы, к которой привязан этот триггер, и количество триггеров будет рано количеству исходных таблиц в базе данных.

В теле триггера сначала определяется тип операции, на которую он сработал (UPDATE, INSERT или DELETE). Для этого нам понадобится информация, сохраняющаяся в таблицах inserted и deleted [11]. Фрагмент кода для определения типа операции представлен на рисунке 8.

Рис. 8. Определение типа операции

После того, как тип операции определен, заполняем таблицу для аудита, в которой хранится общая информация об измененных данных в исходной таблице. Для этого вставляем значения в столбцы type (тип операции) и table_name (название исходной таблицы). Остальные поля такие, как data_change, user_change и app, которые формируются автоматически с помощью функций, возвращающих дату, имя, под которым был выполнен вход, и приложение для текущего сеанса соответственно. Фрагмент кода для заполнения таблицы аудита AuditInf представлен на рисунке 9.

Рис. 9. Заполнение таблицы AuditInf

Далее идет заполнение таблицы UpateLog, в которую записывается подробная информация об изменениях данных, произошедших по команде UPDATE. Таблица UpateLog содержит колонки со старыми и новыми (измененными) значениями, поэтому нам необходимо определить, какое поле в таблице было изменено. Для этого был создан курсор curName1, записывающий в переменную @Value1 названия всех столбцов исходной таблицы. Далее был написан цикл, проходящий по всем этим столбцам и сравнивающий их значения со значениями этих же столбцов в таблицах inserted и deleted. Если в каком-либо столбце действительно произошли изменения, то в таблицу UpateLog вставляется внешний ключ к таблице AuditInf через функцию, определяющую первичный ключ таблицы AuditInf, идентификатор записи, название измененного поля, старое и новое значения.

Для того, чтобы заполнить таблицу InstDelLog, фиксирующую изменения данных в таблицах при выполнении операций INSERT и DELETE, заносим во вспомогательную таблицу tmp вставленные или удаленные строки, взятые из таблиц inserted и deleted соответственно. Затем добавляем ко вспомогательной таблице tmp, содержащей все столбцы исходной таблицы, столбец id_AI, в который будут записаны внешние ключи к общей таблице для аудита AuditInf. Далее копируем все данные из вспомогательной таблицы tmp в таблицу InstDelLog, после чего удаляем вспомогательную таблицу.

Полный листинг главной процедуры и триггера, с помощью которого в таблицах аудита фиксируются изменения исходных таблиц базы данных, представлены в приложениях 1 и 2 соответственно.