Практическая работа 1. Проектирование базы данных
 Скачать 2.91 Mb.
|
|
SQL Server Management Studio 2008 Rus" (рис. 1), представляющую собой наиболее распространенное и удобное средство администрирования баз данных под управлением MS SQL Server (Среда Management Studio Express доступна для свободной загрузки из центра загрузки Майкрософт - http://download.microsoft.com/download/5/C/0/5C0C5CE4-10EB-4623-A63E- 8D850D55D8EF/SQLEXPR_x86_RUS.exe ). 41 Рис. 1. Программа SQL Server Management Studio Среда SQL Server Management Studio — это интегрированная среда для доступа, настройки, управления, администрирования и разработки всех компонентов SQL Server. Среда SQL Server Management Studio объединяет большое число графических средств с набором полнофункциональных редакторов сценариев для доступа к SQL Server разработчиков и администраторов с любым опытом работы. Среда SQL Server Management Studio обеспечивает следующие основные возможности: поддерживает большинство административных задач для SQL Server; единая интегрированная среда для управления SQL Server Database Engine и разработки; новые управляющие диалоговые окна для управления объектами в компоненте SQL Server Database Engine, службах Analysis Services, Reporting Services, Notification Services и выпуске SQL Server Compact 3.5 с пакетом обновления 1 (SP1), позволяющие выполнять действия немедленно, направлять их в редактор кода или включать эти действия в сценарий для последующего выполнения; экспорт и импорт регистрации сервера среды SQL Server Management Studio из одной среды Management Studio в другую; сохранение и печать XML-файлов плана выполнения и взаимоблокировок, созданных приложением SQL Server Profiler, просмотр их в любое время и отправка для анализа администратору; новые окна сообщений об ошибках и информационных сообщений, предоставляющие гораздо больше сведений и позволяющие отправлять в Майкрософт комментарии о сообщениях, копировать сообщения в буфер обмена и отправлять их по электронной почте в службу поддержки; встроенный веб-обозреватель для быстрого обращения к библиотеке MSDN или получения интерактивной справки; встроенная справка от сообществ в Интернете и т.д. Большинство действий с базой данной MS SQL Server в среде Среда SQL Server Management Studio может быть осуществлено двумя способами: либо выполнением 42 операторов языка SQL в окнах "Script Execute" (подключение к базе данных не обязательно) и "SQL Editor" (требуется подключение к базе данных), либо с использованием меню и диалоговых окон. В последнем случае операторы SQL, которые требуются для выполнения данного действия, будут сгенерированы и выполнены средой SQL Server Management Studio автоматически. 2.5. Задание Практическую работу следует выполнять в следующем порядке: 1. Создать на сервере pi_srv (или на локальном компьютере, если нет сервера) рабочую папку для хранения файлов, получаемых при выполнении практической работы. Эта папка должна располагаться в папке \Базы данных\Группа\Студент и соответствовать номеру выполняемой практической работы. 2. На основании индивидуального задания выбрать имя файла создаваемой базы данных. Для имени лучше всего выбрать одно или несколько английских слов, соответствующих наименованию предметной области. Использование для имени русских слов, записанных латинскими буквами, не допускается. 3. Открыть приложение " Среда SQL Server Management Studio ". Для этого можно либо воспользоваться меню Пуск (Пуск/Программы/ Microsoft SQL Server 2008 / Среда SQL Server Management Studio). 4. Создать соединение с локальным или удаленным сервером. 5. Создать базу данных для своей предметной области с помощью диалога, выбрав сервер "pi_srv" или локальный сервер “Имя_компьютера\SQLEXPRESS” 6. Создать базу данных и указать в качестве имени файла "\Базы данных\Группа\ФИО_студента\Название_БД". 7. Извлечь метаданные для автоматической генерации команды создания базы данных. 8. Удалить базу данных, выполнив команду "Database/Drop Database" (База данных/Удалить базу данных). 9. Создать базу данных вторым способом, выполнив в окне "Script Executive" операторы, полученные при извлечении метаданных перед предыдущим удалением. 10. Создать резервную копию базы данных. 11. Удалить базу данных. 12. Восстановить базу данных из резервной копии. 13. Сохранить файл сценария на сервере в папке "Студент", дав ему имя «лаб.№1» и стандартное расширение "*.sql". 2.6. Ход работы 2.6.1. Создание соединения с сервером Выполните следующие инструкции: Работа с приложением SQL Server Management Studio начинается с создания соединения с установленным сервером. Убедитесь вначале, что сервер Microsoft SQL Server (2008) на локальной машине или на сервере компьютерного класса установлен и работает. Откройте приложение " SQL Server Management Studio ". Для этого можно либо воспользоваться меню Пуск (Пуск/Программы/ Microsoft SQL Server 2008 / Среда SQL Server Management Studio). В диалогом окне Соединение с сервером подтвердите заданные по умолчанию параметры и нажмите кнопку Соединить, см. рис.2. Для соединения необходимо, чтобы поле Имя сервера содержало имя компьютера, 43 на котором установлен SQL Server. Если компонент Database Engine является именованным экземпляром, то поле Имя сервера должно также содержать имя экземпляра в формате <имя_компьютера>\<имя_экземпляра>. Рис. 2. Создание соединения с сервером В параметрах указываем: Тип сервера – Компонент Database Engine. Имя сервера. Подключение может быть локальным или удаленным. Представляет собой название компьютера в сети, на котором установлен сервер СУБД. Если сервер установлен на том же компьютере, где сейчас работает пользователь, то в качестве имени используется имя компьютера и идентификатор сервера; проверка подлинности – Windows (по умолчанию), имя пользователя –имя пользователя по умолчанию, зарегистрированного на сервере MS SQL Server (задается при установке сервера), пароль – пусто или пароль для пользователя, заданного для сервера MS SQL Server; Нажмите кнопку Соединить. Если соединение будет совершенно успешно, то на экране появятся данные сервера. Среда Management Studio представляет данные в виде окон, выделенных для отдельных типов данных. Сведения о базе данных отображаются в обозревателе объектов и окнах документов. Обозреватель объектов является представлением в виде дерева, в котором отображаются все объекты базы данных на сервере. Он может содержать базы данных компонента SQL Server Database Engine, служб Analysis Services, служб Reporting Services, служб Integration Services и SQL Server Compact 3.5 с пакетом обновления 1 (SP1). Обозреватель объектов включает сведения по всем серверам, к которым он подключен. При открытии среды Management Studio пользователю предлагается применить при подключении обозревателя объектов параметры, которые использовались в прошлый раз. Чтобы подключиться к любому из серверов, следует дважды щелкнуть его в компоненте «Зарегистрированные серверы», однако регистрировать его не обязательно, см. рис.1. Окно документов представляет собой наиболее крупную часть среды Management Studio. В окнах документов могут размещаться редакторы запросов и окна обзора. По 44 умолчанию отображается страница «Сводка», подключенная к экземпляру компонента Database Engine на текущем компьютере. 2.6.2. Общие сведения о базах данных MS SQL Server Кроме четырех системных баз, SQL Server может обрабатывать до 32 734 баз данных, определяемых пользователем. База данных представляет собой: • набор взаимосвязанных таблиц; • связанный набор страниц, выделенных для хранения данных MS SQL Server; • совокупность данных при архивации; • два и более файла; • важную совокупность данных для целей защиты и управления. Файлы базы данных База данных состоит из двух и более файлов, каждый из которых может использоваться лишь одной базой. У файлов существуют два имени: логическое и физическое. Логическое имя подчиняется стандартным правилам выбора имен объектов SQL Server. Физическое имя представляет собой полное имя любого локального или сетевого файла. Максимальное число файлов в базе данных — 32 768. Файлы делятся на три типа: • Первичные файлы. Используются для хранения данных и информации, определяющих начальные действия с базой. База данных содержит лишь один первичный файл. Стандартное расширение — .mdf. • Вторичные файлы. Одна или несколько вспомогательных областей для хранения данных. Могут использоваться для распределения операций чтения/записи по нескольким дискам. Стандартное расширение — .ndf. • Файлы журналов. Содержат журналы транзакций базы данных. База данных содержит по крайней мере один файл журнала. Стандартное расширение — .ldf. Перед непосредственной записью транзакций в файл данных все вносимые изменения записываются в журнал. Группы файлов Группы файлов предназначены для объединения нескольких файлов. Каждый файл может входить не более чем в одну группу. Файлы журналов не могут принадлежать никаким группам. Группы файлов используются для распределения операций чтения/записи по нескольким дискам. Если группа содержит более одного файла, операции записи распределяются между файлами группы. Базы данных могут содержать до 32 768 групп файлов. У каждой базы данных имеется первичная группа файлов. Она содержит первичный файл данных и все файлы, которые не были явно назначены в другую группу файлов. Имя первичной группы файлов — PRIMARY. 2.6.3. Создание и регистрация базы данных Для создания базы данных можно использовать один из двух способов: Первый способ создания БД. Выполнить команду "База данных/Создать базу данных..." в программе SQL Server Management Studio, ввести параметры создаваемой базы данных в диалоговом окне "Создание базы данных" (рис. 3) и нажать кнопку [OK]. 45 Рис. 3. Диалоговое окно создания базы данных В поле Имя базы данных введите имя нашей будущей базы данных, например – University. Поле Владелец - задан по умолчанию, в зависимости от настройки сервера. Папка с базой данных будет создана по умолчанию на диске C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2\MSSQL\DATA \. Прежде чем нажать кнопку Добавить, просмотрите Параметры и Файловые группы для создаваемой базы данных. После нажатия на кнопку [OK] программа "SQL Server Management Studio " создаст базу данных, имя которой вы увидите в обозревателе объектов, а также сгенерирует необходимый SQL-код для создания базы данных с теми свойствами, которые указаны в этом диалоговом окне и передаст его серверу СУБД для выполнения. Пример этих операторов приведен на рис. 4. (нажмите на имени базы данных University правой клавишей и из контекстного меню выберите Создать скрипт как.. CREATE). Если параметры введены правильно, база данных будет создана. 46 TP PT Рис. 4. Сгенерированный sql-код созданной базы данных Содержащиеся в сценарии операторы отделяются друг от друга символом ";". Сценарий может содержать поясняющие комментарии двух видов: многострочный комментарий (начинается символами "/*" и заканчивается символами "*/") и однострочный комментарий, который начинается символами "--" и продолжается до конца строки. При создании базы данных возможны следующие типичные ошибки: 1. На целевом компьютере не запущен или не установлен сервер СУБД – т.е. выполнять команду создания базы данных просто некому. 2. На целевом компьютере нет каталога, в котором предполагается создать базу данных. 3. Файл, в котором должна будет находиться база данных на сервере, уже существует. После создания базы данных вся введенная о базе данных информация запоминается программой SQL Server Management Studio и в окно редактора в дерево на вкладке "Проводник" добавляется узел с зарегистрированной базой данных (рис. 5). 47 Рис. 5. Перечень зарегистрированных баз данных в SQL Server Management Studio Второй способ создания БД. Выполнить в программе SQL Server Management Studio команду "Создать запрос" на панели инструментов, затем ввести команду, создающую базу данных в окне "Script Execute" (рис. 3) и нажать кнопку Команда CREATE DATABASE - Создание базы данных MS SQL Server Базы данных создаются командой CREATE DATABASE. Создание баз данных разрешается любому пользователю с ролью системного администратора или всем, кому системный администратор предоставил такое право. Команда CREATE DATABASE имеет следующий синтаксис: Если при создании базы не указан первичный файл данных и/или файл журнала, то отсутствующий файл (или файлы) создается с именем по умолчанию. Физические файлы будут находиться в стандартном каталоге. Первичному файлу присваивается имя имя_базы.mdf, а файлу журнала — имя_ базы_log.ldf. Если размер файлов не задан, то при создании размер первичного файла совпадает с размером первичного устройства базы model, а размер файла журнала и вторичных файлов данных равен 1 Мбайт. Он может быть и больше, если размер первичного файла базы данных model превышает 1 Мбайт. Хотя имена и размеры файлов указывать не обязательно, на практике это всегда следует делать. SQL Server создает базу данных за два этапа. На первом этапе база model копируется в новую базу данных, а на втором этапе инициализируется все неиспользуемое пространство. Команда CREATE DATABASE имеет следующие параметры: • PRIMARY — файл определяется как первичное устройство. 48 • NAME — логическое имя; по умолчанию совпадает с именем файла. • FILENAME — полное имя файла на диске. • SIZE — исходный размер файла. Минимальный размер файла журнала равен 512 Кбайт. • MAXSIZE — максимальный размер файла. • UNLIMITED — размер файла не ограничивается. • FILEGROWTH — приращение размера в мегабайтах (MB), килобайтах (KB) или процентах (%). По умолчанию приращение равно 10%. • FOR LOAD — обеспечивает обратную совместимость со сценариями SQL, написанными для предыдущих версий SQL Server. • FOR ATTACH — указывает, что файлы базы данных уже существуют. Пользователь, создавший базу данных, является ее владельцем. Все параметры конфигурации базы копируются из базы model, если только при создании базы не был указан параметр FOR ATTACH. В этом случае параметры конфигурации читаются из существующей базы данных. Рассмотрим некоторые примеры команды CREATE DATABASE: /* База данных со стандартным размером и именами файлов */ Задача 1. Создайте sql-скрипт создания новой базы данных под именем Educator на "D:\Базы данных\Группа\ФИО_студента\Название_БД.mdf, c первичным устройством, с исходным размером файла в 10 Мбайт и запустите на выполнение скрипт (кнопка на панели инструментов). Выполните в окне обозревателя объектов Обновление. Сохраните созданный скрипт в текущую папку под именем 1.sql. После успешного выполнения и обновления проводника у вас должна появится новая база данных. Рис. 6. Окно проводника после выполнения сценария создания базы данных 2.6.4. Подключение к базе данных Чтобы подключиться к зарегистрированной базе данных, надо выбрать нужную базу данных в списке (рис. 5) и сделать двойной щелчок мышкой на выбранной базе 49 данных. Если все параметры подключения были введены правильно, то произойдет подключение к базе данных, название подключенной базы данных в окне "Обозревателя объектов" будет выделено жирным шрифтом, а также появятся вложенные узлы c объектами, содержащимися в подключенной базе данных (рис. 7). Рис. 7. Зарегистрированные базы данных в SQL Server Management Studio После подключения к базе данных можно просматривать имеющиеся объекты, создавать новые, вносить и просматривать данные, а также проводить операции с имеющимися объектами. После создания БД в окне Обозревателя объектов (его можно вызвать по Database Diagrams (Диаграммы БД) Views (Представления) Programmability (Объекты программирования) Tables (Таблицы) Synonyms (Синонимы) Security (Безопасность) Service Broker Storage На начальном этапе раздел созданной БД пуст, за исключением некоторых объектов, которые создаются по умолчанию, например в разделе Security/ Users создаются пользователи, которые имеют право на доступ к объектам БД, их можно изменить. 2.6.5. Удаление базы данных Для удаления базы данных можно использовать один из трех способов: 1. Выполнить в программе " SQL Server Management Studio " команду контекстного меню "Удалить" , выбрав перед этим в списке базу данных, а затем подтвердить свое желание в диалоговом окне. 2. Выполнить оператор DROP DATABASE в SQL-редакторе. 3. Удалить файл с базой данных. Синтаксис оператора DROP DATABASE: DROP DATABASE database_name; 2.6.6. Резервное копирование и восстановление Резервное копирование (backup) базы данных и восстановление из резервной копии (restore) – два важнейших и наиболее частых процесса, осуществляемых 50 администраторами баз данных. Резервное копирование базы данных – единственный надежный способ предохранить данные от потери в результате поломки диска, сбоев электропитания, действий злоумышленников и ошибок в программах. В процессе резервного копирования создается независимый от платформы "снимок" базы данных, с помощью которого можно перенести данные на другую операционную систему или даже другую платформу. Полный цикл: резервное копирование и восстановление из резервной копии приводит к корректировке статистической информации, является средством от излишнего "разбухания" базы данных и необходимой операцией обслуживания базы данных. Кроме того, миграция от одной версии сервера к другой также происходит при помощи процесса backup/restore. Для создания резервной копии базы данных с помощью программы " SQL Server Management Studio " необходимо подключиться к базе данных, выбрать из контекстного меню базы данных Задачи/ Создать резервную копию. В открывшемся диалоговом окне "Мастер резервного копирования" задать несколько параметров и нажать кнопку [Выполнить], см. рис.8. После выбора пути и файла для резервной копии в окне Back Up Database нажатием на OK запускаем процесс создания резервной копии. В случае успешной работы появится сообщение. В результате будет создан файл с резервной копией. Стандартным расширением таких файлов для " SQL Server Management Studio " является "*.bak". Файл с резервной копией базы данных обычно на порядок меньше оригинала. Рис.8. Создание резервной копии базы данных Рис.9. Восстановление базы данных Для восстановления базы данных из резервной копии используется команда "База данных/ Восстановление базы данных. В результате откроется диалоговое окно "Мастер восстановления баз данных", в котором надо выбрать имя БД куда будет восстанавливаться база данных, в которую будет помещен результат, способ восстановления, файл, из которого будет восстанавливаться база данных, отмечаем выбранную резервную копию, и нажать кнопку [Восстановить], см.рис.9. Запускаем процесс восстановления. В случае успешного выполнения получим сообщение. Резервное копирование и восстановление базы данных, наряду с процессом 51 извлечения метаданных и последующего выполнения полученного сценария, можно использовать при переносе разрабатываемой базы данных между различными компьютерами для обеспечения самостоятельной работы студентов над практическими работами и курсовым проектом. Самостоятельно Выполните вначале резервирование, а затем восстановление базы данных. Удалите базу данных Educator c помощью скрипта сохраните sql-запрос. 2.7. Копирование и перенос на другой сервер БД Для просмотра, запуска, остановки служб MS SQL Server необходимо запустить утилиту SQL Server Configuration Manager (рис. 10). Рис.10. Список служб сервера БД Для того чтобы скопировать БД необходимо остановить службу SQL Server (в ее контекстном меню выбрать Stop). Далее в подпапке …\MSSQL.1\MSSQL\Data\ скопировать файлы с вашим названием БД (по умолчанию их два). Не забудьте потом снова запустить службу SQL Server (в ее контекстном меню выбрать Start). Для того чтобы подключить скопированную БД на другом сервере, нужно предварительно скопировать ваши файлы в папку …\MSSQL.1\MSSQL\Data\ соответствующего сервера. Далее запустить утилиту SQL Server Management Studio. В появившемся окне с названием Object Explorer Проводник объектов (его можно вызвать по <F8>) выбираем DataBases (Базы данных) и по <правой кнопке мыши> в контекстном меню (рис. 5) выбираем Attach… (Присоединить…). В появившемся окне Attach DataBases (Присоединение базы данных) нажать <Add> и выбрать ваш файл БД с расширением .mdf. 2.8. Системные базы данных Системные базы данных сервера, создаваемые при установке, и их файлы представлены в таблице 1. 52 Все системные и пользовательские базы данных содержат в обязательном порядке 18 системных таблиц, которые хранят информацию, определяющие структуру и организацию соответствующей базы данных. MSSQL Server поддерживает два основных класса приложений клиентского типа : 1. приложения реляционных баз данных, использующие команды Transact - SQL с расширениями ODBC и набор стандартных функций и объектно-ориентированных методов; 2. web - приложения , использующие команды Transact - SQL или запросы на языке Xpath и документы XML. Оба класса приложений используют API интерфейс баз данных типа OLE DB или ODBC. 2.2. Основные принципы управления учетными записями и ролями в MS SQL Server 2.2.1. Список системных процедур и команд, которые позволяют реализовать политику разделения прав между пользователя БД. Название встроенной процедуры Описание sp_grantlogin – позволяет использовать пользователей или группы ОС для соединения с Microsoft SQL Server™ , используя Windows Authentication. Этот пример позволяет пользователю Windows NT Corporate\BobJ соединяться с SQL Server. Например, EXEC sp_grantlogin 'Corporate\BobJ' 53 sp_defaultdb Изменяет для пользователя БД по умолчанию Этот пример устанавливает БД по умолчанию pubs для пользователя Victoria. Например, EXEC sp_defaultdb 'Victoria', 'pubs' sp_grantdbaccess Добавляет учетную запись из раздела security в текущую БД, для учетных записей Microsoft Windows также дает разрешение на доступ к текущей БД. Синтаксис: EXEC sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name_in_db' [OUTPUT]] Этот пример добавляет учетную запись Corporate\GeorgeW в текущую БД и присваивает псевдоним внутри БД Georgie. Например, EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie' sp_revokedbaccess Удаляет информацию об учетной записи из текущей БД. Синтаксис: EXEC sp_revokedbaccess [ @name_in_db = ] 'name' Этот пример удаляет учетную запись Corporate\GeorgeW из текущей БД. EXEC sp_revokedbaccess 'Corporate\GeorgeW' sp_addrole Создает новую роль в текущей БД. Этот пример создает новую роль в текущей БД с названием Managers. EXEC sp_addrole 'Managers' sp_addrolemember В текущей БД назначает роль конкретному пользователю. Пример A. Этот пример добавляет учетную запись Corporate\JeffL из Windows NT в БД Sales как пользователя Jeff. Jeff затем получает роль Sales_Managers в БД Sales. USE Sales --сделать текущей БД Sales GO –выполнить команду, а потом запустить следующую EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff' GO EXEC sp_addrolemember 'Sales_Managers', 'Jeff' Пример B. Этот пример добавляет пользователя SQL Server с именем Michael к роли Engineering в текущей БД. EXEC sp_addrolemember 'Engineering', 'Michael' sp_helprotect Показывает список привилегий, ассоциированных с ролью. sp_helprolemember Показывает список пользователей БД, входящих в указанную роль sp_addsrvrolemember Присвоение встроенной серверной роли для существующей учетной записи sp_addsrvrolemember [ @loginame = ] 'login' , [ @rolename = 54 ] 'role' Например: sp_addsrvrolemember 'Admin_DB', 'sysadmin' sp_dropsrvrolemembe r Удаление встроенной серверной роли для учетной записи или группы sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role' Например: sp_dropsrvrolemember 'Admin_DB' , 'sysadmin' sp_helpsrvrole Описание только встроенных ролей в SQL Server sp_helpsrvrole [ [ @srvrolename = ] 'role' ] Например: sp_helpsrvrole 'sysadmin' sp_helpsrvrolemember Возвращает список ролей и учетных записей, которым присвоены эти роли sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ] Например: sp_helpsrvrolemember 'sysadmin' sp_srvrolepermission Возвращает список ролей и разрешений, которые присвоены этим ролям sp_srvrolepermission [[@srvrolename =] 'role'] Например: sp_srvrolepermission 'sysadmin' sp_addlogin sp_adduser Создание новой учетной записи в SQL Server в разделе Sequrity: sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ] Например: sp_addlogin 'login1',sysname, 'DB_Books' Создает пользователя в SQL Server без PUBLIC в БД ' DB_Books'. Нужно еще использовать sp_adduser [ @loginame = ] 'login' [ , [ @name_in_db = ] 'user' ] [ , [ @grpname = ] 'group' ], Пример: Создана база данных DB_Books. В ней создан пользователь Admin_DB с серверной ролью sysadmin, с ролью в БД db_owner. Cоздать в QueryAnalyzer нового пользователя с именем Public_ 55 и паролем Public_1 (пароль не должен совпадать с именем пользователя) с помощью следующих команд (не забудьте нажать F5 для запуска команд на выполнение): EXEC sp_addlogin 'Public_','Public_1', 'DB_Books' use DB_Books EXEC sp_adduser 'Public_','Public_' В БД DB_Books создан пользователь Public_ с ролью в БД DB_Books public. Deny (отрицание) Этот пример запрещает несколько системных привилегий для нескольких пользователей. Пользователи не могут использовать системные привилегии CREATE DATABASE or CREATE TABLE, если они не наделены имим через команду GRANT. Пример: DENY CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] DENY SELECT, INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom Grant (предоставлять) This example grants multiple statement permissions to the users Mary and John, and the Corporate\BobJ Windows NT group. GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] Назначение разрешения на выборку (SELECT) для роли PUBLIC в таблице Authors: GRANT SELECT ON Authors TO public Revoke (отменять) This example revokes multiple statement permissions from multiple users. REVOKE CREATE TABLE, CREATE DEFAULT FROM Mary, John This example removes the denied permission from Mary and, through the SELECT permissions applied to the Budget role, allows Mary to use the SELECT statement on the table. REVOKE SELECT ON Budget_Data TO Mary 2.2.2. Создание пользователей для доступа к серверу через утилиту Microsoft SQL Server Management Studio Создадим новую учетную запись для нашей базы данных University. Для этого выберите в Обозревателе объектов раздел Безопасность/Имена входа. Добавьте новое имя входа – Proba, установите опцию Проверка подлинности SQL Server, присвойте свой пароль, примените к выбранной базе данных, установите язык по умолчанию – русский. 56 Рис. 2.1. Раздел Безопасность (Security) для работы с пользователями и создание нового пользователя (при SQL Server аутентификации нужно снять галочки с Enforce password policy) Прежде чем добавлять нового пользователя просмотрите его назначенные серверные роли. Для этого в этом же окне выберите раздел Роли сервера. Установите для пользователя Proba роль sysadmin. Рис. 2.2. Настройка серверной роли для нового пользователя (весь список серверных ролей с их привилегиями в конце работы) Далее просмотрите раздел Сопоставление пользователя. Установите для базы данных University у пользователя Proba права доступа Db_owner, означающие, что пользовательможет выполнять любые действия с БД. Ниже перечислены все возможные варианты прав доступа. 57 Рис. 2.3. Настройка роли базы данных для нового пользователя (весь список ролей баз данных с их привилегиями ниже) Перечень ролей БД: Public – минимальные права доступа к БД (на просмотр) Db_owner – может выполнять любые действия с БД Db_accessadmin – добавляет и удаляет пользователей БД Db_sequrityadmin – управляет ролями в БД и разрешениями на запуск команд и работу с объектами БД Db_ddladmin – добавляет, изменяет и удаляет объекты БД Db_backupoperator – осуществляет резервное копирования БД Db_dataSTUDENT – может просматривать все данные в каждой таблице в БД Db_datawriter - может добавлять, удалять и изменять данные в каждой таблице в БД Db_denydataSTUDENT – запрет на просмотр всех данных в каждой таблице в БД Db_denydatawriter - запрет на добавление, удаление и изменение всех данных в каждой таблице в БД Далее перейдите на раздел Состояние. Установите опции Разрешение к подключению к ядру СУБД – предоставить и имя входа включить. 58 Рис. 4.4. Разблокирование создаваемой учетной записи После нажатия на Откройте в окне обозревателя объектов БД University и перейдите на вкладку Безопасность, там вы найдите только что созданного пользователя. 2.2.3. Создание ролей программно Для упрощения управления правами доступа в системе создаются роли, которые затем можно назначать группе пользователей. Создадим для нашего примера роли декана (DEKAN) и студента (STUDENT). Пример создания роли декана: USE University --сделать текущей БД University EXEC sp_addrole 'DEKAN' Эти операторы набрать на странице, вызванной нажатием кнопки <Создать запрос>. Для запуска команд на выполнение нажать Сохраните запрос. 59 Повторный запуск тех же команд сгенерирует ошибки типа «В БД уже существует роль DEKAN». Чтобы просмотреть, что роль добавлена, откройте вкладку Безопасность/Роли/Роли базы данных. Пример создания роли студента: USE University --сделать текущей БД university EXEC sp_addrole 'STUDENT' Декан должен обладать правами на чтение, удаление, изменение, добавление во все таблицы БД University, а также должен иметь возможность запускать на исполнение процедуры и функции БД University. Поэтому роли декана из системных привилегий назначаем EXECUTE, а из привилегий доступа к объектам назначаем DELETE, INSERT, UPDATE, SELECT. Студент должен обладать правами на чтение из таблиц. Поэтому роли читателя из привилегий доступа к объектам назначаем SELECT. Оператор представления привилегий Синтаксис: GRANT <привилегия>, ... ON < объект >, … TO <имя> [WITH grant option]; Атрибут WITH GRANT OPTION дает право пользователю самому раздавать права, которые он получил. С помощью оператора GRANT для каждого пользователя формируется список привилегий, привилегии управляют работой сервера данных с точки зрения защиты данных. Выполнению каждой транзакции предшествует проверка привилегий пользователя, сеанс которого породил транзакцию. Например (не выполнять): 60 GRANT select, update (Sales, num) ON Sales_data TO user1 WITH GRANT OPTION Пользователь, предоставивший привилегию другому, называется грантор (grantor — предоставитель). Привилегия является предоставляемой, если право на нее можно предоставить другим пользователям. PUBLIC — имя роли, которую получает пользователь при добавлении в список пользователей конкретной БД, включает в себя минимальный набор прав на чтение данных из таблиц и представлений в БД. Для примера (немного забегая вперед) создадим таблицу Discuplinu. Без объяснения синтаксиса выполните следующий sql-запрос: USE University --сделать текущей БД university create table Discuplinu ( Kod_Discuplinu int NOT NULL primary key, name_Discuplinu nchar(30) NULL, kol_chasov int NULL ); Выполните код и обновите вкладку Таблицы. Вы должны увидеть созданную таблицу для сохранения данных о всех дисциплинах. Эта таблица пока пустая с тремя столбцами Kod_Discuplinu, name_Discuplinu, kol_chasov. Роль декана названа DEKAN. Операторы назначения прав доступа для этой роли представлены ниже: GRANT DELETE, INSERT, UPDATE, SELECT ON Discuplinu TO DEKAN GRANT EXECUTE TO DEKAN Роль студента названа STUDENT. Операторы назначения прав доступа для этой роли представлены ниже: GRANT SELECT ON Discuplinu TO STUDENT Примените роли декена и студента к созданной таблице. Создание пользователей с определенной ролью Пример создания декана Ivanov_Dek и присвоения ему роли: EXEC sp_addlogin 'Ivanov_Dek','Ivanov', 'University' use University EXEC sp_adduser 'Ivanov_Dek','Ivanov_Dek' EXEC sp_addrolemember 'DEKAN', 'Ivanov_Dek' Пример создания студент Petrov_Stud и присвоения роли: EXEC sp_addlogin 'Petrov_Stud','Petrov', 'University' use University EXEC sp_adduser 'Petrov_Stud','Petrov_Stud' EXEC sp_addrolemember 'STUDENT', 'Petrov_Stud' Выполните команды. Перейдите в окне Обозреватель объектов на Роли/Роли базы данных/Dekan и просмотрите его свойства. Просмотрите назначенные общие свойства, защищаемые объекты и расширенные свойства. 61 Самостоятельно просмотрите свойства роли базы данных Student. Просмотрите назначенные общие свойства, защищаемые объекты и расширенные свойства. Оператор отмены привилегий Синтаксис отмены привилегий: REVOKE [with grant option] < привилегии >,… ON < объект >,… FROM <имя_пользователя>; Предложение with grant option сохраняет за пользователем перечисленные привилегии, но отменяет его право передавать их кому-либо другому. Пример: REVOKE SELECT ON Discuplinu FROM STUDENT Выполните команду. Оператор изымания роли у пользователя Revoke <список ролей> from <список пользователей>. Пример: use University 62 EXEC sp_droprolemember 'STUDENT', 'Petrov_Stud' Выполните команду и просмотрите результат. Задание для практической работы №2 Создать файл базы данных, согласно номеру варианта, выданного в практической работе №1 с помощью sql-команды. Создать резервную копию базы данных. Определить 2-3 должностных лица, которые смогут работать с таблицами БД. Для каждого должностного лица определить набор привилегий, которыми он может пользоваться. В утилите SQL Server Management Studio создать под каждое должностное лицо соответствующую роль, наделить эту роль определенными привилегиями. Далее создать по одному пользователю на каждую должность и присвоить им соответствующие роли. Сохранить последовательно SQL-операторы с указанием заданий в файле с названием ФамилияСтудента_Лаб_2. Создать текстовый отчет, в котором отобразить sql-команды разработанных запросов и скриншоты результатов работы из СУБД SQL Server Management Studio. 63 ПРАКТИЧЕСКАЯ РАБОТА №3. РАЗРАБОТКА ТАБЛИЦ И ОГРАНИЧЕНИЙ 3.1. Цель практической работы Изучить способы создания, изменения и удаления таблиц. Получить навыки использования приложения " SQL Server Management Studio " для создания, удаления и изменения структуры таблиц. Изучить SQL-операторы для работы с таблицами и индексами. Изучить используемые в SQL Server типы ограничений. Получить навыки использования программы " SQL Server Management Studio " для создания, изменения и удаления ограничений. Изучить SQL-операторы для работы с ограничениями. 3.2. Исходные данные Исходными данными является индивидуальное задание и результат предыдущих практических работ. 3.3. Используемые программы Программа " SQL Server Management Studio " и установленный сервер Microsoft SQL Server . |