- •Лабораторный практикум по дисциплине Базы данных и управление ими
- •Часть 2
- •Лабораторный практикум по дисциплине "Базы данных и управление ими"
- •Учебная база данных education3
- •Лабораторная работа № 1
- •Лабораторная работа № 2
- •Лабораторная работа № 3
- •Лабораторная работа №4
- •Лабораторная работа № 5
- •Лабораторная работа № 6
- •Лабораторная работа № 7
- •Лабораторная работа № 8
- •Лабораторная работа № 9
- •Лабораторная работа № 10
- •Лабораторная работа № 11
- •Лабораторная работа № 12
- •Лабораторная работа № 13
- •Лабораторная работа № 14
- •Лабораторная работа № 15
- •Приложение 1
- •Приложение 2 Типы данных sql Server
- •Приложение 3
Лабораторная работа № 14
ИЗМЕНЕНИЕ И УДАЛЕНИЕ ТАБЛИЦЫ. (ALTER TABLE, ADD, DROP)
Порядок выполнения
Внимание!
После каждого изменения состояния таблицы вывести в файл результатов структуру и все данные этой таблицы.
-
Средствами Transact-SQL cоздать базу данных для выполнения лабораторной работы 14. Имя этой базы данных состоит из номера группы и Вашей фамилии (например, КТ‑406_Иванов_лаб14).
-
Создать и заполнить таблицу car с полями:
-
модель,
-
год выпуска,
-
страна выпуска,
-
пробег,
-
цвет,
-
цена;
-
Изменить таблицу car с помощью команды ALTER, обеспечив:
-
добавление одного нового столбца в таблицу (опция ADD), допускающего NULL и не имеющего значений,
-
добавление трех новых столбцов в таблицу (опция ADD), допускающих NULL и не имеющих значений,
-
удаление одного из добавленных выше столбцов из таблицы (DROP) (по Вашему усмотрению),
-
изменение (типа) для одного из добавленных столбцов (опция ALTER),
-
добавление одного нового столбца в таблицу (опция ADD) с ограничением UNIQUE,
-
добавление одного нового столбца в таблицу (опция ADD) с ограничением DEFAULT. Столбец выбираете сами,
-
добавление двух-трех новых столбцов с ограничениями по Вашему желанию (опция ADD),
-
добавление нового столбца, допускающего NULL, но имеющего значение по умолчанию (ADD),
-
удаление одного из добавленных ограничений (например, UNIQUE) (опция DROP),
-
добавление и удаление первичного ключа по выбранным столбцам (ADD):
-
по одному столбцу,
-
некластеризованного индекса,
-
кластеризованного индекса,
-
разберитесь, в чем разница результатов, получаемых при использовании кластеризованных и некластеризованных индексов,
-
по двум столбцам,
-
попробовать добавить первичный ключ при уже имеющемся первичном ключе. Посмотреть и осмыслить поведение системы в этом последнем случае.
-
Предъявить результаты преподавателю в отдельном файле. Объяснить результаты.
Контрольные вопросы
-
С какой целью используется команда ALTER TABLE?
-
Опишите формат команды ALTER TABLE. Приведите пример использования команды.
-
Как добавить ограничение для существующего поля?
-
Как убрать ограничения для существующего поля?
-
Чем отличается команда ALTER TABLE от команды UPDATE?
-
Что означает опция ADD? Для каких действий она предназначена?
-
Назовите возможные опции команды ADD.
-
Как добавить в таблицу новый столбец? Удалить столбец?
-
Как изменить тип столбца?
-
Как добавить столбец с ограничениями?
-
Как отключить, а затем снова добавить ограничение?
Лабораторная работа № 15
ОБЕСПЕЧЕНИЕ БЕЗОПАСНОСТИ MS SQL SERVER 2008
Цель: научиться обеспечивать безопасность баз данных MS SQL Server 2008
Задачи:
-
Узнать об уровнях, участниках и защищаемых объектах базы данных.
-
Научиться выбирать режимы безопасности.
-
Научиться просматривать и назначать разрешения на объекты базы данных.
-
Научиться назначать роли базы данных.
Теоретические сведения
К базам данных обычно имеется массовый доступ: к информации обращаются служащие, внешние пользователи и задача администратора – рационально управлять этим доступом.
Основные задачи при управлении системой безопасности таковы:
-
найти компромисс между доступностью данных и их защищенностью от несанкционированного доступа;
-
ограничить разрешения базы данных так, чтобы свести к минимуму риск случайного или намеренного выполнения опасных команд и процедур;
-
устранить другие недостатки системы безопасности.
В SQL Server 2008 все объекты базы данных находятся в схемах. Каждой схемой владеют роли, а не отдельные пользователи, поэтому объектами базы данных могут управлять несколько пользователей. Достаточно изменять параметры владения для схемы, а не для каждого объекта в отдельности.
Участники безопасности и защищаемые объекты
В SQL Server 2008 применяются участники безопасности (principal) и защищаемые объекты (securable).
& Участник безопасности – объект, способный запрашивать ресурсы сервера, базы данных или схемы. У каждого участника есть собственный идентификатор безопасности SID.
Существуют три уровня управления участниками безопасности:
-
Windows;
-
SQL Server;
-
База данных.
& Область влияния (scope of influence) участника безопасности определяются уровнем, на котором он определен.
& Коллекции (collection) – участники безопасности, включающие в себя других участников безопасности.
Любой пользователь базы данных принадлежит к общей роли базы данных. Если пользователю не были явно предоставлены определенные разрешения для защищаемого объекта, он наследует разрешения для этого объекта из общей роли.
& Область (scope) – иерархические коллекции защищаемых объектов.
В MS SQL Server 2008 существуют режимы проверки подлинности
-
Проверка подлинности Windows для авторизации пользователей применяет учетные записи пользователей и групп домена Windows. Это позволяет пользователям, прошедшим проверку в Windows, обращаться без имени входа и пароля SQL Server. На пользователей также действуют все ограничения безопасности Windows.
-
Смешанный режим безопасности использует как проверку подлинности Windows, так и учетные записи SQL Server.
Режим безопасности настраивается на уровне сервера и применяется ко всем базам данных на сервере.
Управление доступом и администрированием баз данных
Управление администрированием и доступом к базе данных осуществляется при помощи имен входа и ролей. При этом доступ к SQL Server настраивается на нескольких уровнях:
-
включение имени входа в роль, которой предоставлены нужные разрешения;
-
разрешение имени входа доступ к конкретным базам данных;
-
разрешение или запрещение имени входа доступ к определенным объектам в этой базе данных.
& Имена входа – это пользователи базы данных, обладающие правом доступа к базе данных.
& Роли похожи на группы безопасности Windows. Они позволяют назначать разрешения группе пользователей и могут иметь встроенные (неявные, предопределенные) разрешения, которые нельзя изменять.
Существует два типа ролей:
-
Северная роль (server role) нужна для предоставления прав по администрированию сервера, задается на уровне сервера и предопределяется.
-
Роль базы данных (database role) применяется на уровне базы данных.
У каждой БД есть собственный набор ролей. В SQL Server 2008 поддерживается три типа ролей БД:
-
пользовательские стандартные роли позволяют создавать уникальные наборы разрешений и полномочий. Они используются для логической группировки пользователей, чтобы можно было назначать разрешение роли, а не каждой отдельной учетной записи;
-
пользовательские роли приложений предназначены для работы с приложениями и могут защищаться паролями;
-
предопределенные роли – встроенные роли, разрешения которых изменить нельзя. Предопределенные роли используются для администрирования базы данных.
& Разрешения определяют действия, которые пользователь может выполнять в SQL Server или в базе данных. Разрешения предоставляются в соответствии с именем входа и членством в роли или в группе. В SQL Server применяются разрешения трех типов:
-
разрешения на доступ к объектам: коллекциям, пользовательским типам данных, пользователям, представлениям, базам данных, ролям приложений, таблицам, схемам и т.д.
-
разрешения на выполнение инструкций позволяют управлять административными действиями, например, созданием базы данных или добавлением в нее объектов. Назначать разрешения на выполнение инструкций могут только члены роли sysadmin и владельцы базы данных.
-
неявные (подразумеваемые) разрешения есть только у членов предопределенных системных ролей и владельцев базы данных или объекта Подразумеваемые разрешения для роли изменить нельзя.
Список доступных разрешений таков:
-
GRANT. Дает разрешение на выполнение соответствующих задач. Разрешение роли наследуют все ее члены.
-
REVOKE. Отменяет предшествующее разрешение GRANT, но явно не препятствует пользователю или роли выполнять данную задачу. Пользователь или роль могут наследовать разрешение GRANT на эту задач у другой роли.
-
DENY. Явный запрет на выполнение задания с запретом пользователю или роли наследовать разрешение. Запрет DENY более приоритетен, чем разрешение GRANT.
-
Разрешения предоставляются, запрещаются и отменяются на уровне базы данных или объекта. Вы также можете назначать разрешения с помощью ролей базы данных.
Специализированные имена входа и пользователи
По умолчанию в базе данных настроены следующие пользователи:
-
Группа Администраторы (Administrators) – локальная группа на сервере базы данных. В нее включена учетная запись локального администратора и других пользователей, которым доверено локальное администрирование системы, а также имена входа sа, NETWORK SERVICE и SYSTEM. Эта группа по умолчанию включена в серверную роль sysadmin.
-
Имя входа sа – это учетная запись системного администратора SQL Server, основная задача этой учетной записи состоит в обеспечении обратной совместимости с предыдущими версиями SQL Server.
-
Имена входа NETWORK SERVICE и SYSTEM соответствуют встроенным локальным учетным записям сервера. Создаются эти имена входа или нет, зависит от конфигурации сервера.
-
-
Учетная запись гостя (guest) добавляется к базе данных, чтобы каждый пользователь, обладающий действительным именем входа SQL Server, мог получить доступ к этой базе данных. Гостевая учетная запись применяется только тогда, когда у учетной записи пользователя есть доступ к SQL Server, а доступ к базе данных отсутствует.
-
Пользователь dbo (database owner, владелец базы данных) – это специальная учетная запись с особыми полномочиями. В общем случае владельцем базы данных является пользователь, создавший ее. Учетной записи dbo неявно предоставляются все разрешения для работы базы данных, а также право предоставлять эти разрешения другим пользователям.
-
Пользователи sys и INFORMATION_SCHEMA. применяются для обращения к представлениям для просмотра метаданных всех объектов, хранящихся в базе данных.
Порядок выполнения
-
Создайте имя входа
-
Откройте SQL Server Management Studio.
-
В обозревателе объектов подключитесь к серверу rsvpu и найдите папку Безопасность (Security) на уровне сервера.
-
Нажмите правой кнопкой мыши узел Имена входа (Logins) и выберите команду Создать имя входа (New Login). Откроется диалоговое окно Создание имени входа (Login - New), показанное на рисунке 1.
Рисунок 1 – Диалоговое окно «Создание имени входа»
-
Поскольку создается новое имя входа SQL Server, установите переключатель Проверка подлинности SQL Server (SQL Server Authentication) и введите имя учетной записи student.
-
Введите пароль 1 и подтвердите его.
-
Установите флажок Требовать использование политики паролей (Enforce Password Policy), чтобы применить политику паролей Windows к имени входа SQL Server.
-
Установите флажок Задать срок окончания действия пароля (Enforce password expiration), чтобы ограничить срок действия пароля.
-
Оставьте для имени входа student язык по умолчанию.
-
В качестве Базы данных по умолчанию выберите education. Назначение базы данных по умолчанию не дает имени входа права на доступ к ней. Это база, которая будет использована, если база данных не указана в команде явно.
-
Перейдите на страницу Сопоставление пользователей.
-
Установите флажок Схема напротив базы данных education и в поле Схема по умолчанию выберите db_datawriter;
-
Нажмите ОК, чтобы создать имя входа. Если в SQL Server уже существует идентичное имя, произойдет ошибка.
-
Создание стандартной роли
-
Разверните узел Базы данных (Databases), а затем – узел базы данных education.
-
Разверните узлы Безопасность (Security) и Роли сервера (Database Roles).
-
Нажмите правой кнопкой мыши элемент Роли (Roles) и раскройте подменю Создать (New) и выберите команду Создать роль базы данных (New Database Role). Откроется диалоговое окно Роль базы данных – создание (Database Role – New), представленное на рисунке 2.
Рисунок 2 – Диалоговое окно «Роль базы данных – создание»
-
Введите имя роли в поле Имя роли (Role Name) Students.
-
По умолчанию владельцем роли является dbo. Нажмите кнопку справа от поля Владелец (Owner), чтобы сменить владельца. Откроется диалоговое окно Выбор пользователя или роли базы данных (Select database user or role).
-
Введите часть имени пользователя, которые должны владеть этой ролью, и нажмите кнопку Проверить имена (Check Names).
-
Выберите нужное имя из списка.
-
Нажмите ОК.
-
Назначение роли для имени входа
-
Разверните узлы Безопасность (Security) и Имена входа (Logins).
-
Щелкните правой кнопкой мыши нужное имя входа student и выберите команду Свойства (Properties). Откроется диалоговое окно свойств имени входа, показанное на рисунке 3.
Рисунок 3 – Диалоговое окно свойств имени входа
-
Перейдите на страницу Роли сервера (Server Roles).
-
Установите флажки серверных ролей, в которые нужно включить данное имя входа, в данном случае public.
-
Нажмите ОК, завершив настройку серверных ролей.
-
Назначение и запрет разрешений базы данных на выполнение инструкций
-
Изучите синтаксис инструкций GRANT, REVOKE и DENY при помощи словаря инструкций, представленного в данном лабораторном практикуме;
-
Создайте новый запрос Transact-SQL для базы данных education для назначения запрета на выполнение инструкций по обновлению таблицы lecturers для пользователя student.
GRANT SELECT
ON lecturers
TO student
GO
-
Нажмите Выполнить.
-
Самостоятельно создайте инструкцию для разрешения на выбор данных из таблицы students в базе данных education для членов роли students.
-
-
Назначение разрешений объектов для имени входа
-
1. Разверните узел Базы данных (Databases).
-
2. Найдите базу данных education и разверните ее узел.
-
4. Разверните узлы Безопасность (Security) и Пользователи (Users).
-
5. Дважды щелкните пользователя student. Откроется диалоговое окно Пользователь базы данных (Database User).
-
6. Перейдите на страницу Защищаемые объекты (Securables).
-
7. Щелкните кнопку Найти (Search), чтобы открыть диалоговое окно Добавление объектов (Add Objects), показанное на рисунке 4.
-
-
-
Рисунок 4 - диалоговое окно «Добавление объектов»
-
-
8. Выберите Определенные объекты (Specific Objects) и щелкните ОК.
-
9. В диалоговом окне Выбор объектов (Select Objects), показанном на рисунке 5, щелкните Типы объектов (Object Types).
Рисунок 5 – Диалоговое окно «Выбор объектов»
-
10. В диалоговом окне Выбор типов объектов (Select Object Types) выберите искомый тип объектов, например, таблицы и представления, и щелкните ОК.
-
11. В диалоговом окне Выбор объектов (Select Objects) введите имена объектов. Отделяйте имена точкой с запятой. Можно также ввести часть имени и щелкнуть кнопку Проверить имена (Check Names) или найти объекты выбранного типа, щелкнув кнопку Обзор (Browse).
-
12. Завершив выбор объектов, щелкните ОК. Выбранные объекты будут отражены в диалоговом окне Пользователь базы данных (Database User).
-
13. Выделите защищаемый объект в верхней панели диалогового окна Пользователь базы данных (Database User) и просмотрите его разрешения в области Явно указанные разрешения (Permissions For).
-
14. Чтобы задать разрешения, перейдите на вкладку Явное (Explicit).
-
15. Выберите, какие именно разрешения вы хотите предоставить и установите нужные флажки на в столбцах Предоставить (Grant) или Запретить (Deny). Если вы хотите, чтобы пользователь тоже имел возможность предоставлять разрешения, установите флажок Право передачи (With Grant). Сбросьте флажки для отмены предоставленных ранее разрешений.
-
16. Щелкните ОК, чтобы назначить разрешения.
-
Просмотр разрешений для имени входа
-
Изучите синтаксис инструкции sp_helplogins;
-
Создайте новый запрос Transact-SQL для базы данных education.
-
EXEC sp_helplogins ‘student’
-
Нажмите Выполнить.
-
Самостоятельно выясните какие что обозначает список разрешений, приведенных в решении запроса для данного имени входа.
-
Блокирование имени входа.
При помощи встроенной справки MS SQL и поиска в Интернете выясните, как заблокировать имя входа и заблокируйте созданное вами имя входа.
-
Удаление имени входа
-
Разверните узел Имена входа (Logins) в узле Безопасность (Security).
-
Правой кнопкой щелкните удаляемое имя входа и выберите в контекстном меню команду Удалить (Delete).
-
Удаляемый объект показан в диалоговом окне Удаление объекта (Delete Object).
-
Нажмите OK, чтобы удалить имя входа.
Контрольные вопросы
-
Перечислите основные задачи администратора баз данных в области безопасности.
-
Что означают термины: участники безопасности, защищаемые объекты, область влияния, коллекции, область?
-
Как Вы считаете, в каких случаях предпочтительно использовать режим проверки подлинности Windows, а в каких смешанный режим?
-
Можно ли сказать, что сервер, база данных и схема – это три основные области защищаемых объектов SQL Server?
-
Что такое имя входа?
-
Что такое роль? Перечислите виды ролей.
-
Что такое разрешения? Перечислите типы разрешений.
-
В чем разница между запретом DENY и запретом REVOKE?
-
Перечислите и кратко охарактеризуйте встроенные учетные записи MS SQL Server 2008.
Список использованной литературы
1. Методические указания по выполнению контрольных работ по дисциплине Базы данных и управление ими. ‑ Екатеринбург: 2002. – 21 с.
2. Аткинсон Л. MySQL. Библиотека профессионала. Пер. с англ. – М.: Издательский дом "Вильямс", 2002. – 624 с.; ил.
3. Дюбуа П. MySQL: ‑ М.: Издательский дом "Вильямс", 2001.‑ 816 с.: ил.
4. Астахова И.Ф. SQL в примерах и задачах: Учеб. Пособие / И.Ф. Астахова, А.П. Толстобров, В.М. Мельников. – Мн.: Новое знание, 2002. – 176 с.