В результате была сформирована трансформационная модель, ориентированная на формат выбранной СУБД и включающая все сущности, атрибуты, их типы данных, ограничения контроля целостности и согласованности. Результат представлен на рисунке 2.7.Рисунок 2.7 – Т-модель базы данных тестирования знаний кредитных инспекторовДалее была разработана DBMS-модель в виде SQL-кода, представленного в приложении А. Его реализация позволила сформировать и построить системный каталог БД MS SQL Server.
2.2.3 Реализация модели данных в СУБД (MS SQL Server)
Реализация модели данных будет происходить по средству передачи (в режиме прямого проектирования) схемы автоматизированной информационной системы тестирования знаний кредитных инспекторов в СУБД MS SQL Server 2019, а также заполнением данными с помощью файла Microsoft Excel 97-2003.На основе разработанной в CA ERWin DM физической модели данных выполним автоматическую генерацию системного каталога базы данных тестирования знаний кредитных инспекторов (БДТЗКИ) на сервере SQL Server 2019. Здесь особенностью является то, что до осуществления передачи модели базы данных из CA ERWin DM на сервер там должна быть заранее создана пустая база данных. Процесс создания такой пустой базы данных с именем «База данных тестирования знаний кредитных инспекторов» показан на рисунках 2.8-2.10. Рисунок 2.8 – Переход к созданию новой базы данныхРисунок 2.9 – Создание БДТЗКИРисунок 2.10 – Системный каталог баз данныхКак видно из рисунка 2.10, созданная нами база данных отображается в системном каталоге, следовательно, можно сделать вывод, что процесс создания был выполнен успешно.Процесс генерации схемы базы данных из модели данных называется прямым проектированием (Forward Engineering). При генерации схемы ERwin Data Modeler создает триггеры ссылочной целостности, хранимые процедуры, индексы, ограничения и другие объекты, доступные для выбранной СУБД. В результате мы получаем SQL-скрипт создания схемы.
Процесс создания и генерации схемы продемонстрирован на рисунках 2.11-2.12.Рисунок 2.11 – Переход к генерации схемыРисунок 2.12 – SQL-скрипт создания схемыПосле выполнения полученного скрипта убедимся в его корректности и проверим наличие созданных таблиц. Как видно из рисунка 2.13, в системном каталоге отображаются все таблицы, следовательно, можно сделать вывод, что скрипт выполнился успешно.Рисунок 2.13 – Список таблиц в БДТЗКИНа основе полученных таблиц создадим диаграмму базы данных. Полученная диаграмма представлена на рисунке 2.14.Рисунок 2.14 – Диаграмма БДТЗКИДалее заполним базу данных путем импорта данных из файла Microsoft Excel. Для каждой таблицы был создан лист Excel, заполненный соответствующими данными. Пример такого листа для таблицы Тестирование представлен на рисунке 2.15.Рисунок 2.15 – Данные для таблицы ТестированиеДалее произведём импорт данных из файла Excel в базу данных БДТЗКИ. Процесс осуществления импорта данных представлен на рисунках 2.16-2.18.Рисунок 2.16 – Переход к импорту данныхРисунок 2.16 – Выбор файла для импорта данныхРисунок 2.17 – Выбор назначения импорта данныхРисунок 2.18 – Выбор таблиц для импорта данныхПосле осуществления процедуры импорта данных убедимся, что все прошло успешно. Для этого выведем все данные из таблицы Тестирование. Как видно из рисунка 2.19, в таблице Сотрудник содержатся те же данные, что и в исходном Excel-файле, следовательно, можно сделать вывод, что импорт данных прошел успешно.
Рисунок 2.19 – Выборка данных из таблицы Тестирование
2.2.4 Построение системы защиты базы данных
Создание регистрационных имен, имен пользователей и ролей в базе данныхС помощью инструкции языка Transact-SQL создадим регистрационные имена main_admin и user1 с соответствующими паролями p34xkwFo и 3C78snyr в БДТЗКИ. После реализации регистрационных имен проверим их наличие с помощью системного каталога. Процесс и результат создания регистрационных имен представлены на рисунках 2.20-2.21.Рисунок 2.20 – Создание регистрационных имен main_admin и user1Рисунок 2.21 – Наличие созданных регистрационных имен в системном каталогеКак видно из рисунка 2.21, регистрационные имена были успешно созданы. Далее для полученных выше регистрационных имен создадим с помощью инструкций языка T-SQL соответствующие имена пользователей Admin111 и User111. После создания пользователей проверим их наличие с помощью системного каталога. Процесс и результат создания представлен на рисунках 2.22-2.23.Рисунок 2.22 – Создание имен пользователей Admin111 и User111Рисунок 2.23 – Наличие созданных имен пользователей в системном каталогеКак видно из рисунка 2.23, имена пользователей были успешно созданы. Теперь создадим две новые определяемые пользователем роли базы данных AdminRole и UserRole, после чего проверим их наличие с помощью системного каталога и отобразим информацию о полученных ролях и их членах. Процесс и результат создания представлен на рисунках 2.24-2.25. Рисунок 2.24 – Создание ролей AdminRole и UserRoleРисунок 2.25 – Наличие созданных ролей AdminRole и UserRoleв системном каталогеКак видно из рисунка 2.25, роли AdminRole и UserRole были успешно созданы.Выдача разрешенийДля реализации разрешений воспользуемся инструкциями языка Transact-SQL. Используя инструкцию GRANT, предоставим пользователю Admin111 разрешение на создание таблиц, а также на обновление столбцов и вставку строк в столбцы таблиц Эксперт, Тестирование, Вопрос и Вариант_ответа, а пользователю User111 – разрешение на считывание значений столбцов таблицы Результат. Код запроса для реализации разрешений представлен на рисунке 2.26.
Рисунок 2.26 – Реализация разрешений для пользователейAdmin111 и User111Обратимся к свойствам пользователей Admin111 и User111 и убедимся, что разрешения были успешно предоставлены. Разрешения, имеющиеся у пользователей Admin111 и User111 представлены на рисунках 2.27-2.31.Рисунок 2.27 – Разрешения пользователя Admin111 для таблицы Вариант_ответаРисунок 2.28 – Разрешения пользователя Admin111 для таблицы ВопросРисунок 2.29 – Разрешения пользователя Admin111 для таблицы ТестированиеРисунок 2.30 – Разрешения пользователя Admin111 для таблицы ЭкспертРисунок 2.31 – Разрешения пользователя User111 для таблицы РезультатДалее с помощью инструкции DENY запретим пользователю User111 считывать значения из таблицы Вариант_ответа. Для этого выполним запрос, представленный на рисунке 2.32.Рисунок 2.32 – Реализация запрета для пользователя User111Обратимся к свойствам пользователя User111 и убедимся, что запрет был успешно установлен. Как видно из рисунка 2.33, пользователь User111 не может считывать значения столбцов таблицы Вариант_ответа.Рисунок 2.33 – Запрет пользователя User111
Шифрование базы данныхОдним из способов шифрования данных в SQL Server является прозрачное шифрование. Шифрование файлов базы данных выполняется на уровне страницы. Страницы в зашифрованной базе данных шифруются до записи на диск и дешифруются при чтении в память. Прозрачное шифрование данных не увеличивает размер зашифрованной базы данных. Чтобы зашифровать базу данных способом прозрачного шифрования, необходимо выполнить следующие шаги:
-
создать главный ключ;
-
создать сертификат, защищенный главным ключом;
-
создать ключ шифрования БД и защитить его с помощью сертификата;
-
включить шифрование БД.
Кроме этого, при использовании прозрачного шифрования необходимо создать резервные копии сертификата и связанного с ним ключа. Они потребуются в случае, если сертификат становится недоступным или подключение базы данных происходит на другом сервере. Без сертификата и ключа получить доступ к базе данных будет невозможно. Приступим к шифрованию базы данных. Прежде всего необходимо создать главный ключ базы данных. Для этого выполним запрос, код которого представлен на рисунке 2.34.Рисунок 2.34 – Создание главного ключа базы данныхДалее создадим резервную копию главного ключа и сохраним его в файле masterkey.mk. Для этого выполним запрос, код которого представлен на рисунке 2.35.Рисунок 2.35 – Создание резервной копии главного ключа базы данныхДалее создадим сертификат, защищенный главным ключом. Для этого выполним запрос, код которого представлен на рисунке 2.36.Рисунок 2.36 – Создание сертификатаТеперь создадим резервную сертификата и сохраним его в файле credit_cert.cer. Для этого выполним запрос, код которого представлен на рисунке 2.37.Рисунок 2.37 – Создание резервной копии сертификатаДалее создадим ключ шифрования, защищенный сертификатом, и включим шифрование базы данных БЗТЗКИ. Для этого выполним запрос, код которого представлен на рисунке 2.38.Рисунок 2.38 – Шифрование базы данных БДТЗКИПерейдем в свойства базы данных БДТЗКИ и убедимся, что шифрование включено. Как видно из рисунка 2.39, шифрование базы данных БДТЗКИ включено.Рисунок 2.39 – Свойства БДТЗКИ