Аудит базы данных

Рис. 1. Схема журнала аудита

Описание полей таблиц для аудита представлено в таблицах 1 и 2.

Таблица 1. Таблица AuditInf

Содержание поля	Имя поля	Тип, длина	Примечания
Идентификатор	id	int	Первичный ключ
Тип операции (INSERT, UPDATE или DELETE)	type	char(6)	Обязательное поле
Изменяемая таблица	table_name	nvarchar(200)	Обязательное поле
Дата изменения	date_change	datetime	Заполняется автоматически
Пользователь	user_change	sysname	Заполняется автоматически
Приложение	app	sysname	Заполняется автоматически

Таблица 2. Таблица UpdateLog

Содержание поля	Имя поля	Тип, длина	Примечания
Идентификатор	id	int	Первичный ключ
Идентификатор таблицы AuditInf	id_AI	int	Внешний ключ (к AuditInf)
Идентификатор изменяемой записи	id_rec	int	Обязательное поле
Название изменяемого поля	column_name	sysname	Обязательное поле
Старое значение	old_value	sql_variant	Необязательное поле
Новое значение	new_value	sql_variant	Необязательное поле

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

Основная проблема в реализации таблиц аудита заключается в том, что исходная база данных содержит большое количество таблиц. Разработка базы данных продолжается и число таблиц может увеличиться. Поэтому необходимо реализовать автоматическое определение существующих в базе данных таблиц и создание для них таблиц аудита, а также возможность создавать журнал аудита для конкретной таблицы, заданной пользователем.

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

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

6.3 Физическое проектирование

Для автоматического определение существующих в базе данных таблиц и создания для них таблиц аудита была использована специальная переменная типа 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.

Рис. 2. Листинг создания курсора

При реализации таблицы аудита 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 соответственно.

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

Рис. 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 соответственно.



7. Разработка приложения

Delphi является объектно-ориентированным языком программирования, соответственно приложение реализовано с использованием объектно-ориентированного подхода.

Объектно-ориентированное программирование - это методология для реализации систем, основанная на абстракции объектов, данные и поведение которых инкапсулированы в классы, при этом базовое поведение может быть расширено наследованием. Эти объекты должны взаимодействовать друг с другом посредством обмена сообщениями (вызов методов) и иметь идентификатор, состояние и время существования.

Концепции и правила, используемые в объектно-ориентированном программировании, обеспечивают следующие важные преимущества:

1) Использование классов данных, а также подклассов, которые разделяют некоторые или все характеристики основного класса, сокращает время разработки программы.

2) Поскольку класс определяет только необходимые данные при создании экземпляра этого класса, нельзя получить доступ к другим данным программы. Этот принцип скрытых данных обеспечивает большую безопасность системы и позволяет избежать непреднамеренного повреждения данных.

3) Класс может быть повторно использован не только программой, для которой он изначально создан, но и другими объектно-ориентированными программами.

4) Имеется возможность создавать любой новый тип данных, который еще не определен в самом языке.

При использование объектно-ориентированного подхода необходимо выделить основные объекты и определить связи между ними.

Поскольку основная цель приложения извлекать данные из хранилища данных и отображать их для пользователя, для разработки интерфейса использована схема «Модель/Вид/Контроллер» (Model/View/Controller, MVC).

Деятельность пользовательского интерфейса обычно состоит из двух частей: представление данных и их обновление. Часть представление извлекает данные из источника данных и форматирует их для отображения. Когда пользователь выполняет действие, затрагивающее данные, часть, отвечающая за обновление, передает управление приложению для обновления данных.

MVC - это программная архитектура, которая способствует разделению моделирования, представлений и действий пользователя, на три отдельных класса:

? Модель - обрабатывает и предоставляет данные в вид;

? Вид - отвечает за представление данных для пользователя;

? Контроллер - обрабатывает запрос или события от пользователя, подготавливает данные и посылает модели информацию о необходимых изменениях.

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

7.1 Средства для работы с базами данных

OLE DB и ODBC - это интерфейсы прикладного программирования (API), предназначенные для обеспечения доступа к широкому спектру источников данных [12]. Источник данных состоит из данных, связанной с ними системы управления базами данных (СУБД), платформы, на которой СУБД реализована, и сети, используемой для доступа к этой платформе.

ODBC состоит из ядра и соответствующих драйверов базы данных ODBC. Ядро, также называемое диспетчером драйверов, не зависит от базы данных и выступает в качестве интерпретатора между приложением и драйверами базы данных. С другой стороны, драйверы базы данных содержат специфические для СУБД компоненты и предлагают механизм для соединения с различными системами баз данных.

Благодаря такой модели ODBC является универсальным решением для создания динамических приложений. Соединение с ODBC API может быть установлено с использованием нескольких популярных языков программирования, включая PHP, Perl, Python, C, C ++ и .Net. Наличие множества драйверов баз данных позволяет ODBC соединяться с самыми популярными корпоративными системами баз данных - Oracle, MsSQL, Sybase, а также с проверенными свободными системами - MySQL, PostgreSQL.

OLE DB был разработан как более функциональная замена для ODBC. ODBC в основном ориентирован на SQL данные, тогда как OLE DB поддерживает широкое разнообразие нереляционных баз данных, таких как объектно-ориентированные базы данных, электронные таблицы, которые необязательно реализуются с помощью SQL. OLE DB не налагает никаких особых ограничений ни на синтаксис запроса, ни на структуру данных, пока они могут быть получены в табличной форме. OLE DB отделяет хранилище данных от приложения, которому необходим доступ к данным, посредством набора абстракций, включающих источники данных, команды и наборы строк.

Среда проектирования Delphi предоставляет ряд компонентов для быстрой и эффективной разработки приложений, использующих базы данных. Эти компоненты можно объединить в несколько групп:

? визуальные компоненты для создания пользовательского интерфейса и отчетов (Data Controls, Decision Cube, Quick Reports);

? компоненты, обеспечивающие доступ к данным (BDE, ADO, dbExpress, InterBase);

? компоненты, соединяющие источники данных с визуализирующими компонентами (DataSource).

7.1.1 BDE

BDE - это базовая технология доступа к данным для всех продуктов Borland, включая Delphi и C ++ Builder. BDE состоит из набора DLL библиотек и утилит, с помощью которых можно создавать, реструктурировать, извлекать данные, обновлять и иным образом управлять локальными и удаленными серверами баз данных.

BDE имеет набор драйверов, которые позволяют приложению взаимодействовать с несколькими различными типами баз данных. Эти драйверы преобразуют команды базы данных высокого уровня в команды, специфичные для определенного типа баз данных: Paradox, dBASE, MS Access или любой источник данных, использующий драйверы ODBC.

BDE использует псевдоним, называемый алиасом, для доступа к конкретной базе данных. В простейшем случае алиас указывает BDE используемый тип драйвера и расположение файлов базы данных на диске. Такой алиас в основном используется для локальной базы данных. В других случаях, например, для баз данных клиент/сервер, псевдоним также содержит информацию о максимальном размер BLOB-данных, максимальном количестве строк, имя пользователя. Еще одна особенность работы с базами данных клиент/сервер, такими как Oracle, InterBase, Informix, Sybase, заключается в том, что для них в BDE есть набор дополнительных драйверов SQL Links.

BDE - стандартная технология доступа к базам данных в ранних версиях Delphi, но теперь она считается устаревшей. Это особенно верно по отношению к использованию BDE для доступа к SQL-серверам через драйверы SQL Links, так как доступ к локальным базам данных официально поддерживается. Тем не менее в Delphi все еще есть компоненты специфичные для BDE.

7.1.2 InterBase

Borland предоставляет еще один набор компонентов доступа к базам данных для Delphi: InterBase Express (IBX). Эти компоненты специально адаптированы к собственному серверу InterBase от Borland. В отличие от dbExpress, это не независимый от сервера механизм управления базами данных, а набор компонентов для доступа к определенному серверу. При использовании InterBase в качестве внутренней базы данных, определенный набор компонентов может обеспечить больший контроль над сервером, лучшую производительность и позволить настраивать и поддерживать сервер из пользовательского клиентского приложения. Другим достоинством InterBase Express является возможность работы с специфическими пользовательскими наборами данных.

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

В Delphi включена однопользовательская копией Local InterBase. Local InterBase - это версия InterBase, которая работает с локальными базами данных. Также существует версия InterBase для работы с базой данных клиент/сервер. Основная причина, по которой в Delphi внедрена ограниченная версия, заключается в том, приложения, написанные для локальных баз данных, могут быть без значительных изменений кода перенесены на базу данных клиент/сервер.



7.1.3 dbExpress

dbExpress (DBX) в отличии от других технологий доступа к базам данных является библиотекой, а не движком базы данных [13]. Поэтому dbExpress характеризуется небольшим размером, высоким быстродействием и практически не требует настроек при использовании конечным пользователями. Быстродействие достигается за счет выполнения только запросов SQL, введенных пользователем, тогда как другие технологии генерируют дополнительные запросы для навигации и поиска метаданных.

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

Тем не менее, перечисленные недостатки ненамного усложняют работу с базами данных. Однонаправленный доступ к данным без прямого обновления являются хорошим решением, если необходимо создавать отчеты, в том числе HTML_страницы, отображающие содержимое базы данных. Проблема кеширования решается с помощью специальных компонентов Delphi (например, ClientDataSet и Provider). Эти компоненты обеспечивают более гибкую работу с базой данных по сравнению с использованием монолитных механизмов с уже встроенными функциями.

7.1.4 ADO

ADO (ActiveX Data Objects) - это высокоуровневый интерфейс Microsoft для доступа к базам данных [14]. ADO реализуется на базе технологии OLE DB, которая обеспечивает доступ к реляционным и нереляционным базам данных, а также к электронной почте и файловым системам. Подобно другим системным интерфейсам Microsoft, ADO - объектно-ориентированный интерфейс программирования.

ADO - это механизм с функциями, сопоставимыми с возможностями BDE: независимость от сервера баз данных, поддержка как локальных БД, так и SQL_серверы, упрощенная конфигурация.

По сравнению с BDE технология ADO не дает возможность тонко настраивать взаимодействие с базой данных. BDE имеет ограниченные функции обновления результата запроса, поэтому для использования динамических запросов в Delphi необходимо использовать помимо стандартного компонента Query дополнительный компонент UpdateSql с инструкциями SQL для обновления, вставки и удаления записей.

ADO производит взаимодействие с базой данных автоматически, однако при этом невозможно контролировать код SQL, отправляемый серверу. Частично тонкая настройка производится в ADO с помощью курсоров и блокировки запросов, но в зависимости от используемой СУБД эти функции работают по-разному и наиболее адаптированы для баз данных Microsoft Access и SQL Server.

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

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



7.2 Реализация приложения

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

Компоненты ADO в Delphi сгруппированы на вкладке dbGo палитры компонентов. Три основных компонента: ADOConnection, ADOCommand (для выполнения команд SQL) и ADODataSet (для выполнения запросов, возвращающих результирующий набор). Существуют также три компонента совместимости - ADOTable, ADOQuery и ADOStoredProc, которые можно использовать для адаптации кода, предназначенного для BDE. Наконец, компонент RDSConnection позволяет обращаться к удаленным данным при создании многоуровневых приложений.

Компонент ADOConnection используется для установления соединения с хранилищем данных ADO. Хотя каждый компонент ADO может напрямую подключаться к базе данных, ADOConnection наиболее удобен, поскольку предоставляет методы для активации соединения, прямой и посредством транзакций доступ к хранилищу данных ADO. Соединение, предоставляемое одним компонентом ADOConnection, может совместно использоваться несколькими компонентами команд ADO через их свойства Connection.

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

Одним из преимуществ использования ADOConnection является то, что настройка подключения производится с помощью одной централизованной строки, вместо настройки множества параметров.

Компонент ADOQuery предоставляет возможность извлекать данные из одной или нескольких таблиц из базы данных с использованием операторов SQL. Это могут быть операторы DDL (Data Definition Language), такие как CREATE TABLE, ALTER INDEX и т.д., или операторы DML (Data Manipulation Language), такие как SELECT, UPDATE и DELETE. Однако наиболее часто используемым оператором является оператор SELECT, который создает представление подобное тому, какое доступно с помощью компонента ADOTable.

Операторы SQL могут быть статическими или динамическими, т.е. они могут формироваться во время разработки или содержать параметры, которые изменяются во время выполнения. Использование параметризованных запросов дает большую гибкость для извлечения только необходимых данных, а не всего, что есть в базе данных, например, при использовании компонента ADOTable. ADOQuery имеет свойство SQL, которое используется для хранения инструкции SQL.

7.3 Описание форм

Для обеспечения необходимой функциональности в приложении реализованы:

1) Форма «Выбор БД», имеющая тип TForm, для подключения к базе данных, выбираемой из списка (рис. 10).

2) Форма «Подсистема аудита», имеющая тип TForm, основная форма приложения (рис. 11).

3) Диалоговое окно для настроек (рис. 12).

Рис. 10. Форма «Выбор БД»

Рис. 11. Форма «Подсистема аудита»

Рис. 12. Диалоговое окно

7.3.1 Форма «Выбор БД»

На форме «Выбор БД» располагаются не визуальные компоненты для работы с базой данных: ADOConnection, DataSource и ADOQuery. ADOQuery посылает запрос к системной таблице sys.databases и получает список доступных в системе баз данных. Далее список баз данных отображается с помощью визуального компонента LookupListBox.

При нажатии на кнопку «Запуск аудита», расположенную на форме, происходит формирование строки соединения с учётом выбранной базы данных. Эта строка помещается в свойство ConnectionString компонента ADOConnection, расположенного на следующей форме «Подсистема аудита», таким образом происходит подключение к базе данных.

7.3.2 Форма «Подсистема аудита»

На форме «Подсистема аудита» располагается множество визуальных и не визуальных компонентов. Правая панель представляет собой элемент типа TListBox, содержащий список таблиц базы данных, для которых реализован аудит. Этот список формируется с помощью запроса к таблице подсистемы аудита MainAudInf.

Ключевой визуальный компонент DBGrig предоставляет пользователю информацию из всех таблиц аудита. Для этого созданы три представления для каждой аудируемой таблицы на основе таблиц AuditInf, UpdateLog и InstDelLog. Каждое представление содержит общую информацию об аудите из таблицы AuditInf и детальную информацию об измененных данных из таблиц UpdateLog или InstDelLog. Выбор представления осуществляется пользователем с помощью компонента ComboBox.

Форма имеет верхнюю панель MainMenu и нижнюю информационную панель, наследующую тип TPanel. На панели меню отображается пункт «Настройки», запускающий форму «Выбор БД». На нижней панели отображается название выбранной базы данных и статус работы подсистемы аудита.

Последний блок элементов формы «Подсистема аудита» - это элементы, реализующие фильтрацию. Фильтрация может производиться:

? по пользователю, совершившему изменения данных;

? по названию приложения, с которого было совершено это изменение;

? по дате изменения.

Для фильтрации создана процедура Filtr_proc(), листинг которой представлен в приложении 3. Фильтрация производится с помощью свойства Filter компонента ADOTable.

7.3.3 Диалоговое окно для настроек

Диалоговое окно запускается двойным кликом по элементу списка аудируемых таблиц (методом DblClick компонента ListBox) с формы «Подсистема аудита». В диалоговом окне расположены два блока компонентов: для настройки процесса аудита и процесса очистки.

Два элемента ComboBox представляют списки аудируемых таблиц для выбора таблицы, к которой будут применены настройки. Для применения настроек ко всем таблицам используется элемент CheckBox. При нажатии на кнопку «Запуск» выполняются процедуры создания триггеров, выполняющих аудит, для всех таблиц или для выбранной в зависимости от свойства Checked элемента CheckBox. При нажатии на кнопку «Остановка» выполняются процедуры удаления триггеров. Также происходит изменение в таблице MainAudInf поля isAud, показывающего статус работы аудита.

Для настройки очистки на форме расположены компоненты типов TCheckBox, TDateTimePicker и TButton. Компонент CheckBox необходим для выбора отслеживаемых операций, к которым применяются настройки: Insert, Update и Delete. DateTimePicker используется для выбора даты, с которой начинается удаление старых записей в таблицах аудита. Button запускает применение выбранных настроек очистки. Настройки очистки также можно применять для всех таблиц или для одной, выбранной в зависимости от состояния компонента CheckBox.



8. Тестирование подсистемы аудита

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

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

? отвечают требованиям, поставленным при разработке;

? правильно реагируют на все виды входных данных;

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

Есть две категории приложений, в зависимости от которых выбираются оцениваемые свойства приложения:

? настольный приложения;

? Web-приложения.

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

В процессе тестирования подсистемы аудита был проведен ряд тестов для оценки функций отслеживания операций UPDATE, INSERT и DELETE. Входным параметром для проверки отслеживания операции INSERT является ряд запросов к таблице «Журнал_материалов», представленных на рисунке 13.

Рис. 13. Операции INSERT

На рисунке 14 представлен результат работы программы - таблица, содержащая информацию из таблиц аудита AuditInf и InstDelLog.

Рис. 14. Результат тестирования операции INSERT

Входным параметром для проверки отслеживания операции UPDATE является ряд запросов к таблице «Ремонтируемое_изделие», представленных на рисунке 15.

Рис. 15. Операции UPDATE

На рисунке 16 представлен результат работы программы - таблица, содержащая информацию из таблиц аудита AuditInf и UpdateLog. На этом рисунке видно, что при изменении двух параметров в одной операции в таблицах аудита создается две записи для каждого параметра.

Рис. 16. Результат тестирования операции UPDATE

Для проверки отслеживания операции DELETE были удалены все записи из таблицы «Журнал_материалов», содержащие в поле «Тип» значение «ЗИП». Результат работы программы представлен на рисунке 17.

Рис. 17. Результат тестирования операции DELETE

Заключение

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

Для реализации данной подсистемы аудита была проведена теоретическая и практическая работа, из которой можно сделать следующие основные выводы:

- подход к аудиту базы данных целесообразно выбирать в зависимости от структуры базы данных и объектов, подвергающихся аудиту;

- встроенные в СУБД средства отслеживания изменений и программы для аудита чаще всего основаны на мониторинге по журналу транзакций, что может сильно влиять на производительность системы;

- аудит с помощью триггеров можно реализовать самостоятельно, настроив при этом, какие конкретно изменения в базе данных будут фиксироваться;

- в зависимости от требований к системе размещение журнала аудита может быть осуществлено в отдельном файле или в дополнительных таблицах;

- важным этапом разработки подсистемы аудита является выбор СУБД, который должен удовлетворять основным требованиям, предъявляемым к информационной системе и программному обеспечению.

Главным результатом, полученным в ходе выполнения ВКР и соответствующим перечню поставленных задач, является разработка подсистемы аудита для базы данных конкретной информационной системы по предварительно выбранному методу аудита с помощью триггеров и с использованием журнала аудита, представляющего собой по три дополнительных таблицы к каждой исходной таблице БД. Данная подсистема аудита была разработана с использованием СУБД Microsoft SQL Server, а интерфейс для работы с подсистемой и ее администрирования был реализован с помощью среды разработки Delphi. Несмотря на то, что данная подсистема аудита была разработана для конкретной базы данных информационной системы сопровождения ремонта, она может быть применена и к сторонним системам, содержащим базы данных, реализованные с помощью СУБД Microsoft SQL Server.

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

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

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

В ходе подготовки теоретической части ВКР для реализации подсистемы аудита Колбасовой Д.Д. был произведен обзор подходов к аудиту баз данных и выбран подход к аудиту на основе триггеров, а также обоснован выбор ПО для разработки подсистемы аудита. Результатами написания теории Доценко Ю.В. стало описание подходов к реализации структуры журнала аудита и выбор в качестве журнала аудита дополнительных таблиц. Кроме того, Доценко Ю.В. провела обзор аналогов разрабатываемой подсистемы, т.е. существующих программ для аудита и встроенных в СУБД средств отслеживания изменений в базе данных.

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

Написание таких разделов ВКР, как «Введение» и «Заключение», а также аннотаций на русском и английском языках, было проведено совместно. Тестирование разработанной подсистемы аудита также было выполнено обоими авторами данной ВКР. Список использованных источников литературы составлялся совместно, в соответствии с описанными разделами ВКР.



Список использованных источников

1. 10 самых полезных функций SQL Server 2008 для администратора баз данных. URL: http://sqlcom.ru/dba-tools/top-10-helpful-functions-for-dba/ (дата обращения: 13.04.2017).

2. Аудит по журналу транзакций. URL: https://www.osp.ru/os/2012/01/13012925/ (дата обращения: 12.04.2017).

3. Нильсен П. Microsoft SQL Server 2005. Библия пользователя.: Пер. с англ. - М.: ООО “И.Д. Вильямс”, 2008. - 1232 с.: ил.

4. Станек У.Р. Microsoft SQL Server 2005. Справочник администратора / Пер. с англ. - М.: Издательство «Русская Редакция», 2006. - 544 с.: ил.

5. Auditing Advice. URL: http://sqlmag.com/t-sql/auditing-advice (дата обращения: 14.04.2017).

6. Аудит изменений структуры БД, данных и протоколирование действий пользователя на примере СУБД Oracle. URL: http://rsdn.org/article/db/db_audit.xml (дата обращения: 14.04.2017).

7. Кривонос Н. Журналирование изменений структуры БД и данных. URL: http://www.compdoc.ru/bd/sql/log_change_of_structure_bd/ (дата обращения: 14.04.2017).

8. Система управления базами данных. URL: https://ru.wikipedia.org/wiki/%D0%A1%D0%B8%D1%81%D1%82%D0%B5%D0%BC%D0%B0_%D1%83%D0%BF%D1%80%D0%B0%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D1%8F_%D0%B1%D0%B0%D0%B7%D0%B0%D0%BC%D0%B8_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85 (дата обращения: 14.04.2017).

9. Никитин М. Закончилась ли эпоха реляционных СУБД? URL: http://www.cnews.ru/reviews/free/marketBD/articles/articles2.shtml (дата обращения: 12.04.2017).

10. Полякова Л. Триггеры: создание и применение. URL: http://www.intuit.ru/studies/courses/5/5/lecture/148?page=2 (дата обращения: 03.05.2017).

11. Ицик Бен-Ган. Microsoft SQL Server 2008. Основы T-SQL: Пер. с англ. - СПб.: БВХ-Петербург, 2009. - 432 с.: ил.

12. Фараонов В.В. Delphi 2005. Разработка приложений для баз данных и Интернета. - СПб.: Питер, 2006. - 603 с.: ил.

13. Cantu M. Mastering Delphi 7. CA: Sybex Inc., 2003.

14. Roff J.T. ADO: ActiveX Data Objects. CA: O'Reilly & Associates, 2001.

15. Cantu M. Data access dilemma. URL: http://www.marcocantu.com/papers/DataADO.htm (дата обращения: 14.05.2017).



Приложение 1

Листинг главной процедуры

CREATE PROCEDURE new_proc

AS

DECLARE @Value AS varchar(100), @str_name AS varchar(200), @SQL AS varchar(8000);

CREATE TABLE MainAudInf (id int NOT NULL IDENTITY PRIMARY KEY,

Tab_NAME sysname,

isAud bit);

CREATE TABLE T_NAME (TAB_NAME sysname)

INSERT INTO T_NAME

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME != 'sysdiagrams' AND TABLE_NAME != 'T_NAME' AND TABLE_NAME != 'MainAudInf'

-- Объявляем курсор curName

DECLARE curName CURSOR FOR

SELECT TAB_NAME

FROM T_NAME

-- Открываем курсор

OPEN curName

-- Получаем первое значение

FETCH NEXT FROM curName INTO @Value

-- Цикл по записям

WHILE @@FETCH_STATUS = 0

BEGIN

--таблица с общей информацией об аудите

SET @str_name = @Value + 'AuditInf';

SET @SQL = 'CREATE TABLE ' + @str_name +

' (id int NOT NULL IDENTITY PRIMARY KEY,

type char(6) NOT NULL,

table_name nvarchar(200) NOT NULL,

date_change datetime NOT NULL DEFAULT(GETDATE()),

user_change SYSNAME NOT NULL DEFAULT(SUSER_SNAME()),

app SYSNAME NOT NULL DEFAULT(APP_NAME()) )';

exec(@SQL);

--таблица с детальной информацией при операции UPDATE

DECLARE @c_n varchar(50), @c_t varchar(50);

EXEC ident_PK @Value, @c_n OUTPUT, @c_t OUTPUT;

SET @str_name = @Value + 'UpdateLog';

SET @SQL = 'CREATE TABLE ' + @str_name +

' (column_name SYSNAME NOT NULL,

old_value sql_variant NULL,

new_value sql_variant NULL)';

exec(@SQL);

SET @SQL = 'SELECT a.*, b.' + @c_n + ' as id_rec INTO helpTable FROM ' +

@str_name + ' as a cross JOIN ' + @Value + ' as b';

exec(@SQL);

truncate table helpTable;

ALTER TABLE helpTable Add id_LOG int IDENTITY PRIMARY KEY;

SET @SQL = 'ALTER TABLE helpTable ADD id_AI INT NOT NULL REFERENCES '

+ @Value + 'AuditInf';

exec(@SQL);

SET @SQL = 'DROP TABLE ' + @str_name;

exec(@SQL);

EXEC sp_rename 'helpTable', @str_name;

--таблица с детальной информацией при операциях Insert и Delete

SET @str_name = @Value + 'InstDelLog';

SET @SQL = 'select * into ' + @str_name + ' from ' + @Value + ' where 0 = 1';

exec(@SQL);

SET @SQL = 'ALTER TABLE ' + @str_name +

' add id_AI INT NOT NULL REFERENCES ' + @Value + 'AuditInf,' +

' id_LOG INT NOT NULL IDENTITY PRIMARY KEY ';

exec(@SQL);

--создание триггеров

EXEC trig_aud_proc @Value;

INSERT INTO MainAudInf VALUES (@Value, 1);

-- создание представлений

SET @SQL =

'CREATE VIEW Update_Table_' + @Value +

' AS

SELECT a.date_change, a.user_change, a.app,

b.column_name, b.old_value, b.new_value, b.id_rec

FROM ' + @Value + 'AuditInf AS a, ' + @Value + 'UpdateLog AS b

WHERE a.id=b.id_AI' ;

exec(@SQL);

SET @SQL =

'CREATE VIEW Delet_Table_' + @Value +

' AS

SELECT a.date_change, a.user_change, a.app, b.*

FROM ' + @Value + 'AuditInf AS a, ' + @Value + 'InstDelLog AS b

WHERE a.id=b.id_AI and a.type = ''Delete''';

exec(@SQL);

SET @SQL =

'CREATE VIEW Insert_Table_' + @Value +

' AS

SELECT a.date_change, a.user_change, a.app, b.*

FROM ' + @Value + 'AuditInf AS a, ' + @Value + 'InstDelLog AS b

WHERE a.id=b.id_AI and a.type = ''Insert''';

exec(@SQL);

FETCH NEXT FROM curName INTO @Value

END

-- Закрываем курсор

CLOSE curName

DEALLOCATE curName

DROP TABLE T_NAME

GO



Приложение 2

Листинг триггера

CREATE PROCEDURE trig_aud_proc @name_Table nvarchar(200)

AS

DECLARE @SQL varchar(8000),

@o_type_U AS char(6), @o_type_I AS char(6), @o_type_D AS char(6),

@name_audinf AS varchar(200), @name_audinsdel AS varchar(200),

@name_audupd AS varchar(200), @Value1_cop AS varchar(100),

@PK_n varchar(50), @PK_t varchar(50);

EXEC ident_PK @name_Table, @PK_n OUTPUT, @PK_t OUTPUT;

SET @o_type_U = 'Update';

SET @o_type_I = 'Insert';

SET @o_type_D = 'Delete';

SET @name_audinf = @name_Table + 'AuditInf';

SET @name_audupd = @name_Table + 'UpdateLog';

SET @name_audinsdel = @name_Table + 'InstDelLog';

SET @SQL = 'CREATE TRIGGER ' + @name_Table + '_trig_aud ON ' + @name_Table +

' FOR INSERT, UPDATE, DELETE AS

DECLARE @o_type AS char(6), @headerid AS int, @Value1 AS varchar(100), @Value2

AS varchar(100)

BEGIN

SELECT * INTO my_inserted FROM inserted;

SELECT * INTO my_deleted FROM deleted;

IF EXISTS(SELECT * FROM inserted)

IF EXISTS(SELECT * FROM deleted)

SET @o_type = ''Update''; ' +

'ELSE

SET @o_type = ''Insert''; ' +

'ELSE

SET @o_type = ''Delete''; ' +

'INSERT INTO ' + @name_audinf + '(type, table_name)

VALUES(@o_type, ''' + @name_Table + '''); ' +

'SET @headerid = SCOPE_IDENTITY();

IF (@o_type = ''Update'')

BEGIN

DECLARE curName1 CURSOR FOR

select COLUMN_NAME from INFORMATION_SCHEMA.columns

where TABLE_NAME= ''' + @name_Table + ''';

OPEN curName1

FETCH NEXT FROM curName1 INTO @Value1

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @c_n varchar(50), @c_t varchar(50);

EXEC ident_PK ''' + @name_Table + ''', @c_n OUTPUT, @c_t OUTPUT;

DECLARE @Value_help3 AS varchar(100);

SET @Value_help3 = ''my_inserted.'' + @c_n;

EXEC sp_rename @Value_help3, ''prim_help_ID'', ''COLUMN'';

if ((@Value1 != ''prim_help_ID'') AND (@Value1 != @c_n))

BEGIN

DECLARE @Value_help1 AS varchar(100);

SET @Value_help1 = ''my_inserted.'' + @Value1;

EXEC sp_rename @Value_help1, ''change_help_ID'', ''COLUMN'';

DECLARE @Value_help2 AS varchar(100);

SET @Value_help2 = ''my_deleted.'' + @Value1;

EXEC sp_rename @Value_help2, ''change_help_ID'', ''COLUMN'';

if (select change_help_ID from my_inserted) != (select change_help_ID from my_deleted)

BEGIN

INSERT INTO ' + @name_audupd + '(id_AI, column_name, old_value, new_value, id_rec)

SELECT @headerid, @Value1,

(select change_help_ID from my_deleted), (select change_help_ID from my_inserted),

(select prim_help_ID from my_inserted);

END

EXEC sp_rename ''my_inserted.change_help_ID'', @Value1, ''COLUMN'';

EXEC sp_rename ''my_deleted.change_help_ID'', @Value1, ''COLUMN'';

END

EXEC sp_rename ''my_inserted.prim_help_ID'', @c_n, ''COLUMN'';

FETCH NEXT FROM curName1 INTO @Value1

END

CLOSE curName1

DEALLOCATE curName1

END

IF (@o_type = ''Delete'') or (@o_type = ''Insert'')

BEGIN

select * into tmp from my_inserted

UNION ALL

SELECT * from my_deleted;

ALTER TABLE tmp add id_AI INT;

UPDATE tmp SET id_AI = @headerid;

INSERT INTO ' + @name_audinsdel +

' SELECT *

FROM tmp;

DROP TABLE tmp;

END

DROP TABLE my_inserted

DROP TABLE my_deleted

END'

exec(@SQL);

update MainAudInf set isAud = 1 where Tab_NAME = @name_Table;

GO



Приложение 3

Листинг процедуры фильтрации

Размещено на Allbest.ru

...