- •Содержание
- •Глава 1. Анализ предметной области асу «Библиотека».
- •1.1. Системный анализ предметной области.
- •1.2. Обзор информационных технологий, подходящих для разработки бд.
- •Классификации субд.
- •1.3. Требования к разрабатываемой базе данных.
- •1.4. Выводы.
- •Глава 2. Проектирование базы данных «Библиотека».
- •2.1. Разработка инфологической модели.
- •2.2. Обоснование выбора модели данных.
- •Сетевая модель.
- •Иерархическая модель.
- •Объектно-ориентированная модель.
- •Реляционная модель.
- •Многомерные структуры.
- •2.3. Логическое проектирование бд.
- •2.4. Нормализация, схема базы данных.
- •2.5. Выводы.
- •Глава 3. Программная реализация бд «Библиотека».
- •3.1. Анализ и выбор субд.
- •3.2. Физическое проектирование бд.
- •3.3. Реализация ограничений.
- •Создание пользователей.
- •Создание внешних ключей.
- •Создание ограничения.
- •Создание триггеров.
- •3.4. Безопасность и контроль.
- •Общая концепция безопасности.
- •Защищаемые объекты в sql Server.
- •Участники в sql Server.
- •Параметры проверки подлинности sql Server.
- •Участники уровня базы данных.
- •Разрешения в sql Server.
- •Шифрование баз данных.
- •3.5. Выводы.
- •Программный код.
- •Запросы на создание таблиц.
- •Запросы на заполнение таблиц.
- •Заключение.
- •Список литературы.
3.3. Реализация ограничений.
Создание пользователей.
С базой данных могут работать 2 типа пользователей: библиотекарь и читатель (абонент).
Библиотекари организуют работу всей базы данных. Они имеют доступ к любой информации и могут изменять структуры таблиц и данные в них.
/*Запрос на создание имени входа на сервер*/
USE [master]
GO
CREATE LOGIN [reader] WITH PASSWORD='adm', DEFAULT_DATABASE=[Library], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Library]
GO
CREATE USER [admn] FOR LOGIN [adm]
GO
USE [Library]
GO
EXEC sp_addrolemember N'db_datawriter', N' adm'
GO
/*запрос на предоставление прав этому пользователю к тем же базам*/
USE [Library]
GO
GRANT DELETE TO [admn]
GO
USE [Library]
GO
GRANT INSERT TO [admn]
GO
USE [Library]
GO
GRANT SELECT TO [admn]
GO
USE [Library]
GO
GRANT UPDATE TO [admn]
GO
Читатели могут только просматривать необходимую им информацию о книгах.
/*«Запрос на создание имени входа на сервер*/
USE [master]
GO
CREATE LOGIN [reader] WITH PASSWORD='reader', DEFAULT_DATABASE=[Library], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Library]
GO
CREATE USER [readerrr] FOR LOGIN [reader]
GO
USE [Library]
GO
EXEC sp_addrolemember N'db_datawriter', N'reader'
GO
/*запрос на предоставление прав этому пользователю к тем же базам*/
USE [Library]
GO
REVOKE DELETE TO [readerrr]
GO
USE [Library]
GO
REVOKE INSERT TO [readerrr]
GO
USE [Library]
GO
GRANT SELECT TO [readerrr]
GO
USE [Library]
GO
REVOKE UPDATE TO [readerrr]
GO
USE [Library]
GO
REVOKE SELECT ON dbo.books_in_use TO [readerrr];
GO
USE [Library]
GO
REVOKE SELECT ON dbo.customers TO [readerrr];
GO
Создание внешних ключей.
Внешние ключи (Foreign Key) являются некими ограничения для поддержания логической целостности БД, гарантирующие непротиворечивость информации.
Создание внешних ключей для таблицы "author_subject".
ALTER TABLE author_subject
ADD CONSTRAINT FK_author_subject_authors
FOREIGN KEY(ID_author)
REFERENCES Authors(ID_author);
---------------------------------------------------------------------------------------
ALTER TABLE author_subject
ADD CONSTRAINT FK_author_subject_subject
FOREIGN KEY(ID_subject)
REFERENCES Subject(ID_subject);
Создание внешних ключей для таблицы "book_author".
ALTER TABLE book_author
ADD CONSTRAINT FK_book_author_authors
FOREIGN KEY(ID_author)
REFERENCES Authors(ID_author);
---------------------------------------------------------------------------------------
ALTER TABLE book_author
ADD CONSTRAINT FK_book_author_books
FOREIGN KEY(ID_book)
REFERENCES Books(ID_books);
Создание внешних ключей для таблицы "Books".
ALTER TABLE Books
ADD CONSTRAINT FK_books_subject
FOREIGN KEY(ID_subject)
REFERENCES Subject(ID_subject);
Создание внешних ключей для таблицы "Books_in_use".
ALTER TABLE Books_in_use
ADD CONSTRAINT FK_books_in_use_books
FOREIGN KEY(ID_book)
REFERENCES Books(ID_books);
---------------------------------------------------------------------------------------
ALTER TABLE Books_in_use
ADD CONSTRAINT FK_books_in_use_customers1
FOREIGN KEY(ID_customers)
REFERENCES Customers(ID_customers);
Создание ограничения.
Реализуем ограничение для таблицы “books_in_use” в виде невозможности добавления “return_date” (предполагаемая дата возврата книги) более ранней, чем “date_of_issue” (дата выдачи книги), выполнив следующий запрос:
USE library
ALTER table books_in_use
ADD CONSTRAINT DATA
CHECK (return_date > date_of_issue)
Создание триггеров.
1. Триггер addbook при добавлении новой строки в таблицу «books_in_use» (количество книг, находящихся в пользовании) прибавляет единицу к числу, которое уже находится в столбце «books_in_use» таблицы «books», определяя нужную строку по «id_book».
USE [Library]
GO
/****** Object: Trigger [dbo].[addbook] Script Date: 18.05.2018 14:07:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[addbook]
on [dbo].[books_in_use]
after insert
as
begin
declare @old int
set @old = (select books_in_use from books where id_books = (select id_book from inserted))
update books
set books_in_use = @old + 1
where id_books = (select id_book from inserted)
end
Проверка триггера addbook.
Добавим одну книгу с id_book = 1 в таблицу «books_in_use».
Обновим таблицы и посмотрим результат столбца «books_in_use» в таблице «books», где все начальные значения данного столбца были равны нулю.
Количество взятых книг из библиотеки увеличилось на одну позицию. Следовательно, триггер работает исправно.
2. Триггер delbook при удалении записи из таблицы «books_in_use» (количество книг, находящихся в пользовании) вычитает единицу из числа, которое уже находится в столбце «books_in_use» таблицы «books», определяя нужную строку по «id_book».
USE [Library]
GO
/****** Object: Trigger [dbo].[delbook] Script Date: 18.05.2018 14:55:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[delbook]
on [dbo].[books_in_use]
after delete
as
begin
declare @ol int
set @ol = (select books_in_use from books where id_books = (select id_book from deleted))
update books
set books_in_use = @ol - 1
where id_books = (select id_book from deleted)
end
Проверка триггера delbook.
Удалим запись о выдачи книги из таблицы,
выполнив запрос вида:
delete from books_in_use
where id = 1
Теперь проверим результат в таблице «books».
Количество выданных книг с id_book=1 уменьшилось на единицу. Следовательно, триггер работает исправно.
3. Триггер checkbook делает невозможным добавление новой строки в таблицу «books_in_use», если число книг, находящихся в пользовании (столбец «books_in_use таблицы «books») равно общему количеству данной книги в библиотеке («number_of_books» таблицы «books»).
USE [Library]
GO
/****** Object: Trigger [dbo].[checkbook] Script Date: 18.05.2018 14:55:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[checkbook]
on [dbo].[books_in_use]
instead of insert
as
begin
declare @ol int
declare @old int
set @ol = (select books_in_use from books where id_books = (select id_book from inserted))
set @old = (select number_of_books from books where id_books = (select id_book from inserted))
if @ol <> @old
begin
insert into books_in_use([ID_book]
,[ID_customers]
,[date_of_issue]
,[return_date])
select [ID_book]
,[ID_customers]
,[date_of_issue]
,[return_date]
from inserted
update books
set books_in_use = @ol + 1
where id_books = (select id_book from inserted)
end
else
print 'Недостаточно книг в библиотеке'
end
Проверка триггера checkbook.
Посмотрим в таблицу «books» и увидим, что книга с id_books=10 находится в библиотеке в единственном экземпляре.
Теперь попробуем записать выдачу этой книги два раза в таблицу «books_in_use». Сначала добавим первую запись о выдаче книги.
После попробуем «выдать» единственную книгу второй раз.
Вторая строка с id_book=10 не была добавлена из-за отсутствия книги на данный момент в библиотеке. Следовательно, триггер работает исправно.
3.4. Безопасность и контроль.
SQL Server предназначен для защиты данных платформы и включает ряд функций безопасности. Архитектура безопасности в SQL Server основана на прочно установившихся принципах, с которыми необходимо ознакомиться перед настройкой отдельных параметров безопасности.
Общая концепция безопасности.
Безопасность является одной из основных функций всех систем корпоративного программного обеспечения, поэтому многие из понятий, относящихся к безопасности, схожи в различных системах.
Базовая концепция безопасности включает в себя:
Защищаемые объекты (Securables);
Субъектов или Участников (Principals);
Разрешения (Permissions).
Защищаемые объекты в sql Server.
К защищаемым объектами относятся ресурсы, доступ к которым регулируется системой авторизации компонента Database Engine. Некоторые защищаемые объекты могут храниться внутри других защищаемых объектов, создавая иерархии «областей», которые сами могут защищаться. К областям защищаемых объектов относятся сервер, база данных и схема.
В SQL Server защищаемые объекты на верхнем уровне экземпляра называются объектами уровня сервера. К ним относятся:
Имя входа (Logins)
Роль сервера (Server roles)
База данных (database)
Конечная точка (Endpoints)
Группа доступности (Credentials)
Защищаемые объекты уровня базы данных:
Схема
Роль приложения
Сборка
Асимметричный ключ
Сертификат
Контракт
Полнотекстовый каталог
Привязка удаленной службы
Тип сообщений
Роль (база данных)
Маршрут
Поиск в списке свойств
Служба
Полнотекстовый список стоп-слов
Симметричный ключ
Пользователь
Защищаемые объекты уровня схемы:
Тип
Коллекция схем XML
Объект:
Таблица
Представление
Процедура
Функция
Статистика
Синоним
Очередь
По определению и клиент, и сервер базы данных являются защищаемыми субъектами безопасности. Данные сущности могут пройти взаимную проверку подлинности перед установкой безопасного сетевого соединения.
Некоторые защищаемые объекты являются также участниками. Например, имя входа является участником, который обеспечивает доступ к экземпляру SQL Server; но это также и защищаемый объект, потому что на нем могут быть выполнены действия (например, отключение или удаление), которые требуют разрешения.
Участники в sql Server.
Применительно к компоненту Database Engine Участники (или Субъекты) – это сущности, которые могут запрашивать ресурсы SQL Server. Наиболее часто в роли участников выступают имена входа и пользователи базы данных. Как и другие компоненты модели авторизации SQL Server, участников можно иерархически упорядочить.
Область влияния субъекта зависит не только от области определения (Windows, сервер, база данных), но и от того, неделимый это субъект или коллективный. Имя входа Windows является примером индивидуального (неделимого) субъекта, а группа Windows — коллективного. Каждый субъект имеет идентификатор безопасности (SID).
Имя входа – это субъект безопасности, с помощью которого система безопасности может проверить подлинность лица или сущности. Имя входа необходимо пользователю для соединения с SQL Server.
SQL Server поддерживает два типа имен входа:
Имена входа Windows. Это учетные записи безопасности, управляемые с помощью Windows, например, пользователь или группа Windows. SQL Server не проверяет подлинность этих имен входа, а скорее доверяет Windows проверку их личности. По этой причине подключения к SQL Server с помощью имени входа Windows часто называют доверенные соединения.
Имена входа SQL Server. Это имена входа с учетными данными безопасности, которые определены в базе данных master. SQL Server проверяет подлинность этих имен входа путем проверки пароля. Существуют только для обратной совместимости с приложениями и пользователями, которым необходимо получать доступ к SQL Server, используя явную учетную запись пользователя и пароль.
При использовании имен входа Windows важно отметить, что имя входа Windows в SQL Server может ссылаться на отдельного пользователя Windows, на глобальную группу домена, определенную в Active Directory или на локальную группу (локальная группа домена в Active Directory или локальная группа Windows Server, на котором находится SQL Server). Имя входа Windows, которое ссылается на группу, позволяет всем пользователям в этой группе получить доступ к экземпляру SQL Server.
Использование имен входа групп Windows может значительно упростить администрирование SQL Server. Пользователи Windows добавляются к глобальным группам в зависимости от их роли в организации, а глобальные группы добавляются в локальные группы на основе конкретных требований доступа к SQL Server. По мере появления новых пользователей, изменения статуса существующих пользователей или их ухода, доступ к SQL Server контролируется с помощью членства в группе Active Directory без необходимости внесения каких-либо изменений в SQL Server. Следует также отметить, что доступ, основанный только на именах входа групп Windows, может сделать более сложным решение вопросов тестирования и устранения неполадок в рамках SQL Server; но в целом можно на это пойти, чтобы получить долгосрочные преимущества.
Роли сервера являются участниками безопасности, к которым можно добавлять имена входа, чтобы упростить управление разрешениями.
SQL Server 2014 поддерживает два типа ролей:
Фиксированные роли уровня сервера: системные (предопределенные) роли, которые автоматически получают необходимые разрешения для выполнения конкретных задач управления на уровне сервера.
Роли сервера, определяемые пользователем: роли, которые администраторы могут создать для того, чтобы определить пользовательские группы управления на уровне сервера.