Лабораторная работа № 14

ИЗМЕНЕНИЕ И УДАЛЕНИЕ ТАБЛИЦЫ. (ALTER TABLE, ADD, DROP)

Порядок выполнения

Внимание!

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

1. Средствами Transact-SQL cоздать базу данных для выполнения лабораторной работы 14. Имя этой базы данных состоит из номера группы и Вашей фамилии (например, КТ‑406_Иванов_лаб14).

2. Создать и заполнить таблицу car с полями:

• модель,

• год выпуска,

• страна выпуска,

• пробег,

• цвет,

• цена;

3. Изменить таблицу car с помощью команды ALTER, обеспечив:

• добавление одного нового столбца в таблицу (опция ADD), допускающего NULL и не имеющего значений,

• добавление трех новых столбцов в таблицу (опция ADD), допускающих NULL и не имеющих значений,

• удаление одного из добавленных выше столбцов из таблицы (DROP) (по Вашему усмотрению),

• изменение (типа) для одного из добавленных столбцов (опция ALTER),

• добавление одного нового столбца в таблицу (опция ADD) с ограничением UNIQUE,

• добавление одного нового столбца в таблицу (опция ADD) с ограничением DEFAULT. Столбец выбираете сами,

• добавление двух-трех новых столбцов с ограничениями по Вашему желанию (опция ADD),

• добавление нового столбца, допускающего NULL, но имеющего значение по умолчанию (ADD),

• удаление одного из добавленных ограничений (например, UNIQUE) (опция DROP),

• добавление и удаление первичного ключа по выбранным столбцам (ADD):

• по одному столбцу,

• некластеризованного индекса,

• кластеризованного индекса,

• разберитесь, в чем разница результатов, получаемых при использовании кластеризованных и некластеризованных индексов,

• по двум столбцам,

• попробовать добавить первичный ключ при уже имеющемся первичном ключе. Посмотреть и осмыслить поведение системы в этом последнем случае.

4. Предъявить результаты преподавателю в отдельном файле. Объяснить результаты.

Контрольные вопросы

1. С какой целью используется команда ALTER TABLE?

2. Опишите формат команды ALTER TABLE. Приведите пример использования команды.

3. Как добавить ограничение для существующего поля?

4. Как убрать ограничения для существующего поля?

5. Чем отличается команда ALTER TABLE от команды UPDATE?

6. Что означает опция ADD? Для каких действий она предназначена?

7. Назовите возможные опции команды ADD.

8. Как добавить в таблицу новый столбец? Удалить столбец?

9. Как изменить тип столбца?

10. Как добавить столбец с ограничениями?

11. Как отключить, а затем снова добавить ограничение?

Лабораторная работа № 15

ОБЕСПЕЧЕНИЕ БЕЗОПАСНОСТИ MS SQL SERVER 2008

Цель: научиться обеспечивать безопасность баз данных MS SQL Server 2008

Задачи:

1. Узнать об уровнях, участниках и защищаемых объектах базы данных.

2. Научиться выбирать режимы безопасности.

3. Научиться просматривать и назначать разрешения на объекты базы данных.

4. Научиться назначать роли базы данных.

Теоретические сведения

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

Основные задачи при управлении системой безопасности таковы:

• найти компромисс между доступностью данных и их защищенностью от несанкционированного доступа;

• ограничить разрешения базы данных так, чтобы свести к минимуму риск случайного или намеренного выполнения опасных команд и процедур;

• устранить другие недостатки системы безопасности.

В SQL Server 2008 все объекты базы данных находятся в схемах. Каждой схемой владеют роли, а не отдельные пользователи, поэтому объектами базы данных могут управлять несколько пользователей. Достаточно изменять параметры владения для схемы, а не для каждого объекта в отдельности.

Участники безопасности и защищаемые объекты

В SQL Server 2008 применяются участники безопасности (principal) и защищаемые объекты (securable).

& Участник безопасности – объект, способный запрашивать ресурсы сервера, базы данных или схемы. У каждого участника есть собственный идентификатор безопасности SID.

Существуют три уровня управления участниками безопасности:

1. Windows;

2. SQL Server;

3. База данных.

& Область влияния (scope of influence) участника безопасности определяются уровнем, на котором он определен.

& Коллекции (collection) – участники безопасности, включающие в себя других участников безопасности.

Любой пользователь базы данных принадлежит к общей роли базы данных. Если пользователю не были явно предоставлены определенные разрешения для защищаемого объекта, он наследует разрешения для этого объекта из общей роли.

& Область (scope) – иерархические коллекции защищаемых объектов.

В MS SQL Server 2008 существуют режимы проверки подлинности

1. Проверка подлинности Windows для авторизации пользователей применяет учетные записи пользователей и групп домена Windows. Это позволяет пользователям, прошедшим проверку в Windows, обращаться без имени входа и пароля SQL Server. На пользователей также действуют все ограничения безопасности Windows.

2. Смешанный режим безопасности использует как проверку подлинности Windows, так и учетные записи SQL Server.

Режим безопасности настраивается на уровне сервера и применяется ко всем базам данных на сервере.

Управление доступом и администрированием баз данных

Управление администрированием и доступом к базе данных осуществляется при помощи имен входа и ролей. При этом доступ к SQL Server настраивается на нескольких уровнях:

• включение имени входа в роль, которой предоставлены нужные разрешения;

• разрешение имени входа доступ к конкретным базам данных;

• разрешение или запрещение имени входа доступ к определенным объектам в этой базе данных.

& Имена входа – это пользователи базы данных, обладающие правом доступа к базе данных.

& Роли похожи на группы безопасности Windows. Они позволяют назначать разрешения группе пользователей и могут иметь встроенные (неявные, предопределенные) разрешения, которые нельзя изменять.

Существует два типа ролей:

1. Северная роль (server role) нужна для предоставления прав по администрированию сервера, задается на уровне сервера и предопределяется.

2. Роль базы данных (database role) применяется на уровне базы данных.

У каждой БД есть собственный набор ролей. В SQL Server 2008 поддерживается три типа ролей БД:

• пользовательские стандартные роли позволяют создавать уникальные наборы разрешений и полномочий. Они используются для логической группировки пользователей, чтобы можно было назначать разрешение роли, а не каждой отдельной учетной записи;

• пользовательские роли приложений предназначены для работы с приложениями и могут защищаться паролями;

• предопределенные роли – встроенные роли, разрешения которых изменить нельзя. Предопределенные роли используются для администрирования базы данных.

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

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

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

• неявные (подразумеваемые) разрешения есть только у членов предопределенных системных ролей и владельцев базы данных или объекта Подразумеваемые разрешения для роли изменить нельзя.

Список доступных разрешений таков:

• GRANT. Дает разрешение на выполнение соответствующих задач. Разрешение роли наследуют все ее члены.

• REVOKE. Отменяет предшествующее разрешение GRANT, но явно не препятствует пользователю или роли выполнять данную задачу. Пользователь или роль могут наследовать разрешение GRANT на эту задач у другой роли.

• DENY. Явный запрет на выполнение задания с запретом пользователю или роли наследовать разрешение. Запрет DENY более приоритетен, чем разрешение GRANT.

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

Специализированные имена входа и пользователи

По умолчанию в базе данных настроены следующие пользователи:

1. Группа Администраторы (Administrators) – локальная группа на сервере базы данных. В нее включена учетная запись локального администратора и других пользователей, которым доверено локальное администрирование системы, а также имена входа sа, NETWORK SERVICE и SYSTEM. Эта группа по умолчанию включена в серверную роль sysadmin.

   1. Имя входа sа – это учетная запись системного администратора SQL Server, основная задача этой учетной записи состоит в обеспечении обратной совместимости с предыдущими версиями SQL Server.

   2. Имена входа NETWORK SERVICE и SYSTEM соответствуют встроенным локальным учетным записям сервера. Создаются эти имена входа или нет, зависит от конфигурации сервера.

2. Учетная запись гостя (guest) добавляется к базе данных, чтобы каждый пользователь, обладающий действительным именем входа SQL Server, мог получить доступ к этой базе данных. Гостевая учетная запись применяется только тогда, когда у учетной записи пользователя есть доступ к SQL Server, а доступ к базе данных отсутствует.

3. Пользователь dbo (database owner, владелец базы данных) – это специальная учетная запись с особыми полномочиями. В общем случае владельцем базы данных является пользователь, создавший ее. Учетной записи dbo неявно предоставляются все разрешения для работы базы данных, а также право предоставлять эти разрешения другим пользователям.

4. Пользователи sys и INFORMATION­­_SCHEMA. применяются для обращения к представлениям для просмотра метаданных всех объектов, хранящихся в базе данных.

Порядок выполнения

1. Создайте имя входа

• Откройте 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 уже существует идентичное имя, произойдет ошибка.

2. Создание стандартной роли

• Разверните узел Базы данных (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).

• Выберите нужное имя из списка.

• Нажмите ОК.

3. Назначение роли для имени входа

• Разверните узлы Безопасность (Security) и Имена входа (Logins).

• Щелкните правой кнопкой мыши нужное имя входа student и выберите команду Свойства (Properties). Откроется диалоговое окно свойств имени входа, показанное на рисунке 3.

Рисунок 3 – Диалоговое окно свойств имени входа

• Перейдите на страницу Роли сервера (Server Roles).

• Установите флажки серверных ролей, в которые нужно включить данное имя входа, в данном случае public.

• Нажмите ОК, завершив настройку серверных ролей.

4. Назначение и запрет разрешений базы данных на выполнение инструкций

• Изучите синтаксис инструкций 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. Щелкните ОК, чтобы назначить разрешения.

5. Просмотр разрешений для имени входа

• Изучите синтаксис инструкции sp_helplogins;

• Создайте новый запрос Transact-SQL для базы данных education.

• EXEC sp_helplogins ‘student’

• Нажмите Выполнить.

• Самостоятельно выясните какие что обозначает список разрешений, приведенных в решении запроса для данного имени входа.

6. Блокирование имени входа.

При помощи встроенной справки MS SQL и поиска в Интернете выясните, как заблокировать имя входа и заблокируйте созданное вами имя входа.

7. Удаление имени входа

• Разверните узел Имена входа (Logins) в узле Безопасность (Security).

• Правой кнопкой щелкните удаляемое имя входа и выберите в контекстном меню команду Удалить (Delete).

• Удаляемый объект показан в диалоговом окне Удаление объекта (Delete Object).

• Нажмите OK, чтобы удалить имя входа.

Контрольные вопросы

1. Перечислите основные задачи администратора баз данных в области безопасности.

2. Что означают термины: участники безопасности, защищаемые объекты, область влияния, коллекции, область?

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

4. Можно ли сказать, что сервер, база данных и схема – это три основные области защищаемых объектов SQL Server?

5. Что такое имя входа?

6. Что такое роль? Перечислите виды ролей.

7. Что такое разрешения? Перечислите типы разрешений.

8. В чем разница между запретом DENY и запретом REVOKE?

9. Перечислите и кратко охарактеризуйте встроенные учетные записи MS SQL Server 2008.

Список использованной литературы

1. Методические указания по выполнению контрольных работ по дисциплине Базы данных и управление ими. ‑ Екатеринбург: 2002. – 21 с.

2. Аткинсон Л. MySQL. Библиотека профессионала. Пер. с англ. – М.: Издательский дом "Вильямс", 2002. – 624 с.; ил.

3. Дюбуа П. MySQL: ‑ М.: Издательский дом "Вильямс", 2001.‑ 816 с.: ил.

4. Астахова И.Ф. SQL в примерах и задачах: Учеб. Пособие / И.Ф. Астахова, А.П. Толстобров, В.М. Мельников. – Мн.: Новое знание, 2002. – 176 с.