3323 ЗАЩИТА в СУБД
ЛАБОРАТОРНАЯ РАБОТА № 1
Изучение общих принципов работы в MS SQL Server 7.0
Цель работы: получить простейшие навыки работы с SQL Server 7.0, изучить принципы администрирования SQL Server 7.0, а также получить практические навыки по созданию, обслуживанию и защите пользовательских баз данных.
- 1. Теоретическая часть
SQL Server 7.0 - это хорошо масштабируемый, полностью реляционный, быстродействующий многопользовательский сервер баз данных (БД) масштаба предприятия, способный обрабатывать большие объемы данных для клиент-серверных приложений.
1.1. Использование SQL Server
Все работы с SQL Server делятся на две категории. Проектирование и реализация БД обычно относятся к компетенции разработчика, тогда как администрирование и сопровождение составляют обязанности администратора.
Разработка БД включает следующие задачи:
P проектирование с учетом требований оптимального использования аппаратного обеспечения и возможности расширения в процессе эксплуатации; выявление и моделирование объектов БД и логики приложения; определение типа информации для каждого объекта и типа отношений между объектами;
P создание БД и ее объектов, включая таблицы, механизмы обеспечения целостности данных, объекты ввода и извлечения данных, индексы и механизмы защиты;
P оптимизация приложений БД для достижения максимальной производительности;
P планирование развертывания, включая анализ нагрузки и выработку рекомендаций по созданию оптимального набора индексов БД;
P администрирование приложения после развертывания, включая настройку серверов и клиентов, мониторинг производительности сервера, управление заданиями, предупреждениями и операторами, управление защитой и выполнение процедур резервного копирования БД.
1.2. Администрирование БД SQL Server
Администрирование БД SQL Server включает:
P установку и настройку SQL Server и сетевой защиты;
P модернизацию SQL Server при появлении новых версий;
P создание БД, включая выделение дискового пространства для БД и журналов транзакций, и управление использованием дискового пространства;
P импорт/экспорт данных;
P создание плана резервного копирования и восстановления и его выполнение;
P разработку плана защиты пользователей и приложений баз данных;
P автоматизацию решения повторяющихся задач с помощью заданий и использование предупреждений для сбора информации об ошибках;
P мониторинг и оптимизацию баз данных и сервера;
P применение репликации для публикации данных на нескольких узлах.
В состав SQL Server включен административный инструментарий, позволяющий снизить затраты на решение рутинных задач администрирования и автоматизировать их выполнение.
SQL Server Enterprise Manager предназначен для администрирования сервера и управления базами данных с компьютера-клиента.
SQL Server Query Analyzer – инструмент для анализа и выполнения запросов на языке Transact-SQL. Анализатор запросов позволяет оптимизировать запросы и собирать статистическую информацию, а также работать с несколькими запросами в нескольких окнах.
SQL Server Agent представляет собой сервис, работающий совместно с SQL Server над решением таких административных задач, как управление сигналами, оповещениями, выполнение заданий и репликация.
1.3. Типы баз данных
Базы данных SQL Server бывают системные и пользовательские. Принципиальной разницы между ними нет. Системные БД хранят информацию о сервере SQL Server в целом, применяемую для управления системой.
Системные БД:
База данных |
Описание |
master |
Служит для управления пользовательскими БД; хранит информацию об учетных записях, параметрах конфигурации, размещении данных и системных сообщениях об ошибках. |
model |
Шаблон для создания новых пользовательских БД. |
tempbd |
Хранилище для временных таблиц и других временных объектов. |
msdb |
Обеспечивает поддержку службы SQL Server Agent и предоставляет хранилище для расписания и журнала заданий. |
distribution |
Хранит протоколы и транзакции, используемые при репликации. |
Данные, хранящиеся в системных БД, не рекомендуется изменять и удалять. Для чтения и изменения системных данных следует применять только системные хранимые процедуры.
Пользовательские БД создают пользователи. Один сервер может управлять несколькими такими БД.
1.4. Планирование емкости
Одна из главных обязанностей администратора – размещение данных БД, их сопровождение и мониторинг занимаемого ими пространства. Оценка необходимого пространства БД помогает планировать структуру хранения и вырабатывать требования к аппаратуре.
Основные факторы, учитываемые при оценке объема пространства, необходимого БД, следующие:
P размер БД model и системных таблиц (включая их предполагаемый рост), обычно эта цифра составляет небольшой процент размера БД;
P объем данных в таблицах БД пользователя (включая их предполагаемый рост);
P число и размер индексов – в частности, размер ключа, число записей и коэффициент заполнения;
P размер журнала транзакций; здесь необходимо учесть частоту изменения данных, типичный размер транзакции и частоту архивирования журнала.
В средах оперативной обработки транзакций журналу транзакций отводится 25 % пространства БД. В БД, используемых преимущественно для выполнения запросов, журналу можно выделять меньше места.
1.5. Планирование разрешений и системы защиты
Доступ к SQL Server и базам данных осуществляется на основе учетных записей и ролей.
Разрешения предоставляются (или отзываются) пользователям и ролям.
В SQL Server три типа разрешений: на выполнение операторов, на доступ к объектам и неявные разрешения.
Операции, связанные с созданием БД или элементов БД, требуют наличия разрешений на выполнение операторов Transact-SQL. Разрешения этого типа применяются к самому оператору, а не к элементу БД. Разрешения на выполнение операторов могут предоставлять только пользователи ролей sysadmin, db_owner и db_securityadmin.
Для работы с данными и выполнения процедур нужны разрешения на доступ к объектам. Например, разрешения на доступ к таблицам и представлениям дают пользователю возможность обращаться к данным таблиц и представлений при помощи операторов SELECT, INSERT, UPDATE и DELETE.
Кроме операций, управляемых обычными разрешениями на выполнение хранимых процедур и на доступ к объектам, члены фиксированных ролей и владельцы объектов БД могут выполнять и другие операции, разрешения на которые называются неявными. Например, фиксированные роли обладают неявными административными разрешениями. Пользователь, приписанный к роли sysadmin, автоматически наследует все разрешения на любые операции на сервере. Неявные разрешения нельзя передавать другим ролям.
Владельцы объектов также обладают неявными разрешениями, позволяющими выполнять любые операции с принадлежащими им объектами. Например, владелец или член группы, владеющий ею, может просматривать таблицу, удалять и добавлять данные, изменять ее определение, а также управлять разрешениями на доступ к ней других пользователей.
- 2. Порядок выполнения работы
2.1. Создание базы данных
Для создания БД используется средство Enterprise Manager.
- 1. Заpегистpиpоваться с пpавами администpатоpа или ввести любую учетную запись, входящую в локальную гpуппу Administrators.
- 2. Запустить Enterprise Manager. Для создания БД в левой панели раскрыть группу SQL Server, к которой относится сервер. Дважды щелкнуть на имени сервера. Затем щелкнуть правой кнопкой мыши по папке Databases и выбрать в контекстном меню команду New Database.
- 3. Откроется диалоговое окно Database Properties с вкладкой General. Ввести имя БД в текстовом поле Name. Enterprise Manager автоматически создаст основной файл данных, используя введенное имя БД как префикс, и группу файлов с именем PRIMARY. Изменить группу для основного файла данных нельзя. Ввести имя файла (логическое имя), местоположение (физическое имя), размер и группу для каждого файла.
Примечание. По умолчанию все файлы сохраняются в папке MSSQL7\data, которая находится на том же диске, что и SQL Server. Для изменения стандартного пути выделить соответствующую ячейку таблицы и ввести новый путь.
- 4. Задать начальный размер файла в мегабайтах: выделить ячейку с текущим значением и ввести нужное значение.
Примечание. Если файл дополнительный, можно указать для него пользовательскую группу: выделить имя группы и ввести новое имя. После создания пользовательской группы файлов можно выбирать ее из списка Filegroup.
- 5. В группе File Properties в нижней части окна определяются параметры роста для каждого файла. Выбрав имя файла, выделить параметр Automatically Grow File (размер данного файла будет увеличиваться автоматически). Также следует указать фиксированное приращение размера в процентах или в мегабайтах, в зависимости от объема свободного пространства в файле. Здесь же можно задать максимальный размер файла, выбрав параметр Restrict Filegrowth, и указать предельное значение (в Мб) либо оставить размер файла неограниченным. После создания БД заданные параметры можно модифицировать. Для этого необходимо открыть в Enterprise Manager диалоговое окно Database Properties. Для удаления файла из списка следует выделить его имя и нажать клавишу Delete.
- 6. Настроить параметры журнала транзакций. Выбрать вкладку Transaction Log в окне Database Properties. Для файлов журнала нельзя указать группу (поскольку они не могут быть отнесены ни к одной из них). Ввести имя файла (логическое имя), местоположение (физическое имя) и размер для каждого из файлов. Указать все параметры автоматического роста файлов журнала, выполнив последовательность действий, описанную в п.5.
- 7. После настройки параметров всех файлов нажать кнопку OK. SQL Server создаст БД. Вернуться в окно Enterprise Manager и выделить папку Databases того сервера, где создана новая БД. В правой панели Enterprise Manager появится значок для созданной БД.
2.2. Просмотр сведений о базе данных
Просмотр информации о БД осуществляется средствами Enterprise Manager.
- 1. Раскрыть (щелкнуть на значке «+») нужную группу SQL Server, затем – папку Databases.
- 2. Выделить имя БД для просмотра сведений о ней. На правой панели находятся три заголовка: General, Tables&Indexes и Space Allocated. На странице General содержится общая информация о БД, дате ее архивирования и параметрах обслуживания. Для каждого заголовка предусмотрен еще ряд пунктов.
- 3. Выделить заголовок Tables&Indexes для просмотра информации о таблицах и индексах. Здесь содержится информация о числе строк и размере каждой таблицы.
- 4. Выделить заголовок Space Allocated для просмотра информации о свободном и заполненном пространстве каждого файла данных и файла транзакций.
- 5. Для удаления БД выделить удаляемую базу и выбрать в контекстном меню команду Delete. Откроется окно сообщения Delete Database. Для подтверждения удаления нажать кнопку Yes.
2.3. Создание таблиц БД
Все таблицы БД и связи между ними следует продумать перед их фактическим созданием. Создать таблицу можно с помощью SQL Server Enterprise Manager.
- 1. В Enterprise Manager открыть группу SQL Server, затем – сервер.
- 2. Открыть папку Databases и просмотреть доступные БД.
- 3. Открыть нужную БД.
- 4. Выделить папку Tables, выбрать в контекстном меню команду New Table. Откроется диалоговое окно Choose Name. Ввести имя таблицы и нажать кнопку OK.
- 5. Откроется окно New Table, напоминающее электронную таблицу. Каждый ряд сетки соответствует полю таблицы, каждый столбец – одному из атрибутов поля.
- 6. Определить все поля таблицы БД.
Записи в таблице физически хранятся в том порядке, который определен в полях. Для вставки нового поля следует выделить нижнее поле и в контекстном меню выбрать команду Insert Column. Для удаления поля следует в контекстном меню выбрать Delete Column. Для задания полю признака первичного ключа в контекстном меню выбрать команду Primary Key.
- 7. Для задания других ограничений и индексов следует в контекстном меню выбрать команду Properties. Также можно воспользоваться значком Table And Index Properties на панели инструментов. Для обоих вариантов откроется диалоговое окно Properties.
- 8. Для сохранения результатов работы выбрать значок Save на панели инструментов SQL Server Enterprise Manager.
2.4. Заполнение БД
Существуют разнообразные способы загрузки данных в SQL Server 7.0. Наиболее простой - непосредственное занесение информации в таблицы.
В Enterprise Manager открыть группу SQL Server, сервер, папку Databases, в которую необходимо занести информацию, выделить поле Tables. На правой панели Enterprise Manager появится список таблиц. Выбрать таблицу для загрузки информации, в контекстном меню выбрать команду Open Table и далее Return all Rows. Активизируя нужное поле, можно занести в него информацию.
Примечание. Следует учесть, что при завершении работы с выбранной таблицей после внесения изменений не выдается предупреждающего сообщения об изменении информации, то есть все изменения будут записаны в БД и на диск.
Задание 1
Создать базу данных MyDB, состоящую из нескольких таблиц, содержащую один основной файл, три дополнительных файла и один файл журнала транзакций. Использовать варианты задания (без учета политики безопасности) из лабораторной работы №3 (с. 29).
2.5. Создание и использование триггеров
Триггер – особый тип хранимой процедуры, которая автоматически выполняется при изменении таблицы с помощью операторов UPDATE, INSERT или DELETE.
Триггер создается для одной таблицы в текущей базе данных, хотя он может использовать данные других таблиц и объекты в других БД. Триггеры можно создавать только для пользовательских таблиц.
Правила использования триггеров
- Триггеры запускаются только после выполнения вызвавшего их оператора. Например, триггер UPDATE не работает, пока не завершено выполнение оператора UPDATE.
- Если при выполнении оператора происходит нарушение какого-либо ограничения таблицы или возникает другая ошибка, триггер не срабатывает.
- Триггер и вызвавший его оператор образуют транзакцию. В результате вызова из триггера оператора ROLLBACK отменяются коррективы, выполненные триггером и оператором. При возникновении серьезной ошибки, например при отключении пользователя, SQL Server автоматически выполнит откат всей транзакции.
- Триггер срабатывает единожды для каждого оператора, независимо от количества изменяемых оператором записей.
Для каждого триггера SQL Server создает две временные таблицы (deleted и inserted), на которые можно ссылаться в описании триггера. Эти таблицы хранятся в памяти и являются локальными по отношению к триггеру, то есть триггер имеет доступ только к своей собственной версии таблиц. Временные таблицы применяются для сравнения состояния таблицы «до» и «после» внесения изменений.
Создание триггера средствами Enterprise Manager
- 1. В Enterprise Manager выделить имя таблицы, для которой должен быть создан триггер, выбрать в контекстном меню команду All Tasks, затем команду Manager Triggers. Откроется диалоговое окно Triggers Properties.
- 2. В поле Text ввести команды T-SQL, описывающие триггер. Например, следующий триггер с именем Print_Update будет выдавать сообщение при обновлении таблицы:
CREATE TRIGGER Print_Update ON My Table
FOR UPDATE
AS
PRINT “My table was updated
GO
- 3. Для проверки синтаксиса следует щелкнуть по кнопке Check Syntax. Исправить ошибки, если они есть. Для создания триггера щелкнуть по кнопке Apply. Созданный триггер будет добавлен в список Name.
- 4. Окно Trigger Properties останется открытым, позволяя создать другие триггеры для заданной таблицы. После описания необходимых триггеров щелкнуть по кнопке Close.
- 5. Для удаления триггера следует выбрать его из списка Name и нажать клавишу Delete.
Задание 2
В созданной БД выбрать таблицу для создания на ней триггера. Создать триггер по указанию преподавателя и проверить его выполнение.
Примечание. Для проверки работоспособности триггера рекомендуется использовать программу SQL Server Query Analyzer. Для этого после создания триггера запустите эту программу и выполните необходимые действия для его срабатывания операторов T-SQL.
2.6. Создание и использование представлений
Представление можно определить как некоторую виртуальную таблицу, содержащую результаты выполнения запроса (оператора SELECT) к одной или нескольким реальным таблицам.
Для конечного пользователя представление выглядит как обычная таблица в БД, для которой можно применить операторы SELECT, INSERT, UPDATE и DELETE.
Ограничения представлений
- Представление может ссылаться не более чем на 1024 поля в таблице. Если необходимо сослаться на большее число полей, следует использовать другой метод.
- Представление может быть создано только на основе таблицы в текущей БД.
- Разработчик представления должен обладать правами доступа ко всем полям представления.
- Любые обновления, модификации и прочие действия не могут нарушить целостность данных. Например, если в исходной таблице не допускаются значения NULL, то в представлении они также недопустимы.
- Представления могут основываться на других представлениях. Можно создать представление, которое будет извлекать информацию из других представлений, вплоть до 32-х уровней вложенности.
- В исходном запросе представления не допускается содержание разделов ORDER BY, COMPUTE, COMPUTE BY и ключевого слова INTO.
- Нельзя строить индексы на основе представлений. Тем не менее, исходный запрос представления способен реализовать доступ к индексам.
Создание представления средствами Enterprise Manager
- 1. В Enterprise Manager открыть папку Databases и выбрать базу данных. Откроется окно БД.
- 2. В левой панели выделить базу данных и в контекстном меню выбрать команду New, далеем – View. Откроется окно New View. Указать имя представления и выбрать его поля исходных таблиц.
- 3. Ввести в панели оператор SQL :
SELECT <поле1> [, <поле2>, <поле3> …]
FROM <таблица1> [, <таблица2> ,…].
Допустимо использование других разделов оператора SELECT. После ввода оператора щелкнуть на кнопке Verify SQL, чтобы проверить корректность запроса. Если запрос верен, щелкнуть на кнопке OK в следующем диалоговом окне, чтобы Enterprise Manager заполнил панель диаграммы и полей запроса окна New View.
- 4. Для изменения или удаления представления в Enterprise Manager открыть папку Databases, затем БД и щелкнуть по значку Views. Далее выделить имя представления, которое надо изменить или удалить. В появившемся окне выбрать Design View или Delete.
Задание 3
Создать представление для ранее созданной БД, позволяющее получить информацию, указанную преподавателем. Сохраните это представление, т.к. оно потребуется в дальнейшем при рассмотрении материала об ограничении доступа к БД.