Методические указания по выполнению лабораторных работ MS SQL-SERVER 2000 (стр. 6 )

Задание 4. Написать пример пакета запросов с использованием команд COMMIT и

ROLLBACK для автоматических, неявных и явных транзакций.

Задание 5. Написать пример пакета команд, иллюстрирующих использование средств оптимизации при откате транзакций.

Задание 6. Используя средства MS SQL Server 2000, изучить физическую и логическую архитектуру журнала транзакций.

Задание 7. С помощью системной хранимой процедуры sp_configure изменить интервал контрольных точек для базы данных Pubs.

Задание 8. Уточнить синтаксис команд управления транзакциями и написать пример пакета с использованием всех вариантов этих команд.

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

Управление и мониторинг блокировок

Цель работы – изучение проблем надежности, возникающих в многопользовательских средах обработки данных, и механизмов блокирования операций различного типа и уровня, начиная с отдельной строки таблицы и заканчивая базой данных в целом, которые используются в MS SQL Server 2000 для успешного выполнения транзакций, а также для приобретения навыков управления блокировками в запросе и текущими блокировками с помощью Enterprise Manager, обеспечивающего их мониторинг.

В многопользовательских средах обработки данных при одновременном чтении и изменении одних и тех же данных несколькими пользователями могут возникнуть следующие четыре проблемы одновременного доступа:

The Lost Update Problem – проблема последнего изменения, заключающаяся в том, что если несколько пользователей будут одновременно изменять одни и те же данные, то сохраняться изменения того пользователя, который запишет их последним; для решения этой проблемы надо обеспечить последовательное внесение изменений.

The Uncommitted Dependency Problem – проблема грязного чтения, когда пользователь считывает данные, обработка которых еще не завершена другим пользователем; для ее решения необходимо ожидание окончания всех изменений данных.

The Inconsistent Analysis Problem – проблема не повторяемого чтения, когда требуется многократное чтение одних и тех же данных, а они изменяются в это время другим пользователем; для ее решения необходим запрет на изменение таких данных другими пользователями.

The Phantom Read Problem – проблема чтения фантомов, когда один пользователь выбирает данные из таблицы, а второй пользователь вставляет новые строки; здесь также необходим запрет на изменение данных вторым пользователем.

Временно накладываемые ограничения на выполнение некоторых операций обработки данных называются блокировками (locks). Специальный стандарт ANSI поддерживает четыре уровня блокировок, при этом каждый последующий уровень поддерживает требования предыдущего и налагает дополнительные ограничения:

Уровень 0 – запрещение загрязнения данных; для выполнения этого требования надо, чтобы изменения проводил лишь один пользователь, а остальные ожидали окончания этого изменения.

Уровень 1 – запрещение грязного чтения: если один пользователь начал изменение данных, то другие пользователи не должны читать эти данные до окончания их изменения.

Уровень 2 – запрещение неповторяемого чтения: если пользователь считывает данные, то никакой другой пользователь не сможет их изменить.

Уровень 3 – запрещение фантомов: если пользователь работает с данными, то никакой другой пользователь не должен добавлять новые или удалять имеющиеся строки.

MS SQL Server 2000 поддерживает все четыре уровня блокировок. Управлением блокировками занимается менеджер блокировок (lock manager), контролирующий их наложение и разрешение конфликтов. Блокировки и транзакции тесно связаны друг с другом. Транзакции накладывают блокировки на данные, чтобы обеспечить выполнение требований ASID, так что

пользователю чаще всего не нужно предпринимать никаких действий по управлению блокировками. Однако при необходимости в запросе можно явно указать, какой тип блокировки надо использовать в том или ином случае. Команда SET LOCK_TIMEOUT позволяет регулировать время ожидания разблокирования ресурса, по истечении которого выдается ошибка. Системная хранимая процедура sp_configure ‘locks’, n устанавливает максимальное количество блокировок в системе.

SQL Server 2000 поддерживает различные уровни блокирования объектов, начиная с отдельной строки и заканчивая базой данных в целом:

RID – на уровне строки таблицы при вставке новых строк;

Key – на уровне индекса, когда блокируется его часть, соответствующая изменяемым в транзакции данным;

Page – на уровне страницы; Extent – на уровне экстента; Table – на уровне таблицы; DB – на уровне базы данных.

Основной единицей блокирования является страница, а минимальным элементом – строка. Сервер может динамически управлять уровнем блокирования для конкретной транзакции в зависимости от характера запросов.

Для транзакции SQL Server 2000 может устанавливать различные уровни изоляции, чтобы делать их независимыми друг от друга. Эти уровни соответствуют рассмотренными уровням стандарта ANSI и решают четыре проблемы одновременного доступа:

READ UNCOMMITED – незавершенное чтение: гарантируется только физическая целостность данных;

READ COMMITED – завершенное чтение: не возникает проблемы грязного чтения;

REPEATABLE READ – повторяющееся чтение: помимо решения проблемы грязного чтения решается проблема неповторяемого чтения;

SERIALIZABLE – сериализуемость: обеспечивает полную изоляцию транзакций друг от

друга.

Установка уровня изоляции транзакций устанавливается командой SET TRANSACTION ISOLATION LEVEL.

В зависимости от выполняемых над данными действий SQL Server обеспечивает четыре типа блокировок:

S (Shared) – коллективная блокировка при чтении данных несколькими пользователями;

V (Update) – блокировка обновления как подготовительная для перехода от коллективной к монопольной блокировке;

E (Exclusive) – монопольная блокировка, если транзакция изменяет данные;

BU (Bulk update) – блокировка массового обновлении при выполнении операций массового копирования в таблицу.

Помимо основных типов блокировок SQL Server 2000 поддерживает ряд специальных блокировок, предназначенных для повышения производительности и функциональности обработки данных. Блокировки этого типа называются блокировками намерения (intent locks). Они используются сервером в том случае, если транзакция намеривается получить доступ к данным вниз по иерархии и необходимо запретить другим транзакциям накладывать блокировки, которые будут конфликтовать с блокировкой, накладываемой первой транзакцией.

Помимо рассмотренных блокировок на данные SQL Server 2000 использует несколько блокировок на метаданные, описывающие структуру объектов. Такими блокировками являются блокировка схемы и блокировка диапазона ключа.

При работе многопользовательской системы возможны ситуации, когда две транзакции блокируют два блока данных и для завершения работы каждой из транзакций необходим доступ к данным, заблокированным ранее другой транзакцией. Это так называемые “мертвые”, или

тупиковые (deadlocks) блокировки. Для избежания подобных проблем имеется специальный механизм разрешения конфликтов тупикового блокирования. Кроме того для минимизации возможности их образования необходимо соблюдать ряд правил при разработке кода транзакции: минимизировать длительность транзакции, использовать низкий уровень изоляции, избегать в транзакции взаимодействия с пользователем и т. д.

В командах SELECT, DELETE, UPDATE и INSERT можно с помощью специальных ключевых слов, или хинтов (hint) управлять блокировками и уровнем изоляции:

HOLDLOCK – для удержания коллективной блокировки до конца транзакции;

NOLOCK – разрешает грязное чтение; PAGLOCK – блокировка на уровне страницы; READCOMMITTED – установка первого уровня изоляции; ROWLOCK – блокировка на уровне строки; SERIALIZABLE – установка высшего уровня изоляции; TABLOCK – блокировка на уровне таблицы;

TABLOCKX – блокировка на уровне таблицы до завершения транзакции.

Имеются и другие хинты.

Задание 1. Создать несколько одновременно работающих процессов, соединенным с одним и тем же сервером и использующих одни и те же базы данных, и с помощью утилиты Enterprise Manager произвести мониторинг текущих блокировок сервера, используя следующие его папки:

Management/Current Activity/Process Info – папка с подробной информацией обо всех процессах, которые в текущий момент обращаются к серверу;

Management/Current Activity/Locks/Process ID – папка с информацией о текущих блокировках, которые установлены процессами;

Management/Current Activity/Locks/Object – папка с информацией об объектах, которые блокированы в текущий момент процессами.

Поскольку за время просмотра состояние блокировок процессов изменяется, следует обновлять информацию с помощью команды Refresh контекстного меню процесса. В этом окне можно посмотреть последнюю выполненную команду, послать сообщение пользователю и уничтожить процесс, который вызвал мертвые блокировки.

В папке Process Info содержится следующая информация: Process ID – идентификационный номер процесса;

User – имя учетной записи пользователя;

Database – имя используемой процессом базы данных;

Status – состояние процесса: работает в основном режиме (Runable), работает в фоновом режиме (Background) или бездействует (Sleeping);

Open Transaction – количество транзакций, открытых процессов; Command – последняя выполненная команда;

Application – имя приложения, установившего соединение; Wait time – время ожидания разблокирования ресурса;

Wait type – тип ожидания разблокирования ресурса; Wait resource – имя ожидаемого ресурса;

CPU time – время работы процесса;

Physical ID – физический идентификатор процесса; Memory Usage – объем памяти, занятой процессом; Login time – время регистрации процесса;

Last Batch – время выполнения процессом последней команды; Host – имя компьютера, на котором был запущен процесс;

Network Library – имя сетевой библиотеки соединения с процессом;

Network Address – адрес сетевой карты, которую использовал процесс для установления соединения;

Blocked by – количество процессов, блокируемых данным процессом;

Blocking – количество процессов, блокирующих выполнение данного процесса.

В папках Locks/Process ID и Locks/Object содержится одна и та же информация,

сгруппированная соответственно по процессам и по объектам:

Process ID – идентификационный номер процесса; Object – имя блокированного объекта;

Lock Type – тип блокировки: базы данных (DB), таблица (TAB), экстент (EXT), страница

(PAG), диапазон ключа (KEY) и строка (RID); Mode – вид блокировки:

S – коллективная блокировка; V – блокировка обновления;

X – монопольная блокировка;

IS, IX, SIX – блокировки намерения; Sch-C, Sch-M – блокировки схемы;

BV – блокировка массового обновления; Status – статус блокировки:

GRANT – блокировка установлена и успешно работает;

WAIT – блокировка ожидает ресурса;

CNVT – выполняется конвертирование блокировки;

Owner – владелец блокировки: Sess – сессия, Xact – транзакция, Curs – курсор; Index – имя индекса, связанного с ресурсом;

Resource – идентификатор заблокированного ресурса.

Задание 2. Используя системные хранимые процедуры sp_lock, sp_who и команды Transact – SQL DBCC OPENTRANT, DB_ID, произвести мониторинг текущих блокировок сервера. Описание процедур и команд найти в документации.

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

Создание, применение и управление курсорами

Цель работы – изучение назначения и типов курсоров, синтаксиса и семантики команд языка Transact – SQL для создания и открытия курсоров, выборки данных из курсора и изменения строк таблиц с помощью курсоров, удаления данных, закрытия и освобождения курсоров, а также приобретения навыков их применения и управления с помощью команд и системных хранимых процедур SQL Server 2000.

Набор данных, имеющийся в таблице базы данных, называется полным набором строк таблицы (complete set of rows). Набор строк, возвращаемый команду SELECT, называется результирующим набором данных (result set). Он является частью полного набора, отфильтрованного горизонтально с помощью условий, заданных в разделе WHERE. Можно в результирующий набор не включать те или иные столбцы, применяя вертикальную фильтрацию.

Результирующие наборы могут содержать сотни тысяч строк, и клиентские приложения не всегда справляются с таким объемом данных. Для решения этой проблемы используются курсоры, которые представляют собой окна, налагаемые на результирующие набором данных и выделяющие требуемую часть данных. Перемещая созданный курсор, можно получить доступ ко всем результирующим данным. Таким образом, курсоры SQL Server 2000 представляют собой механизм обмена данными между сервером и клиентом, который минимизирует ресурсы клиентского приложения. Однако всегда, когда это возможно, следует избегать использования курсоров и применять команды SELECT, UPDATE, DELETE и INSERT.

MS SQL Server 2000 поддерживает три вида курсоров:

1. Курсоры Transact – SQL, которые применяются внутри триггеров, хранимых процедур и сценариев;

2. Курсоры сервера, которые действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB и DB_Library;

3. Курсоры клиента, которые реализуются на клиенте и выбирают весь результирующий набор для ускорения обработки данных.

Один курсор может базироваться на нескольких таблицах, расположенных в разных базах данных. Операция считывания определенных в курсоре данных называется выборкой (fetch). Если за одну операцию курсор позволяет выбрать несколько строк таблицы, то такой курсор называется блочным. По способу просмотра данных курсоры бывают последовательные (forward only), которые обеспечивают просмотр строк только в одном направлении – от начала к концу, и прокручиваемые, которые допускают просмотр в обоих направлениях и переход к произвольной строке.

По представляемым возможностям курсоры делятся на четыре типа: статические, динамические, последовательные и ключевые. Тип курсора определяется на стадии его создания и не может быть изменен.

Статический курсор (static cursor) называют также курсорами моментального снимка (snapshot cursor). При открытии такого курсора сервер выбирает все данные, соответствующие заданным критериям, и сохраняет результирующий набор строк в системной базе данных tempdb без изменения, если даже исходные строки и изменяются. Поэтому статический курсор всегда открывается в режиме “только для чтения”.

Динамические курсоры (dynamic cursor) противоположны статическим. При их использовании не создается полная копия исходных данных, а выполняется динамическая выборка данным из исходных таблиц только при обращении пользователя к тем или иным данным, при этом на время выборки соответствующие строки блокируются сервером. После выборки строк исходные строки могут изменяться пользователями, но эти изменения уже не отражаются в выбранных строках. С другой стороны, изменения в выбранных строках не будут видны другим пользователям, пока они не будут подтверждены (committed).

Последовательный курсор выбирает данные только от начала к его концу. Он не хранит результирующий набор. Строки считываются из базы данных, как только они выбираются в курсоре. Это позволяет отображать все изменения в базе данных. В курсоре видно самое последнее состояние данных.

Курсоры, зависящие от набора ключей (keyset-driven cursor), или ключевые курсоры, построены на основе уникальных идентификаторов. Множество всех уникальных идентификаторов (ключей) строк таблиц базы данных называется набором ключей. Сервер блокирует строки исходных таблиц только на время составления таблицы ключей.

Ключевой курсор представляет набор ключей, идентифицирующих строки полного результирующего набора курсора. Набор ключей строится в системной базе данных tempdb.

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

Формат команды языка Transact – SQL для создания курсора следующий:

DECLARE Имя курсора CURSOR [LOCAL/GLOBAL] [FORWARD_ONLY\SCROLL] [STATIC\KEYSET\DINAMIC\FAST_FORWARD] [READ_ONLY\SCROLL_LOCKS\OPTIMISTIC] [TYPE_WARNING]

FOR select_statement

[FOR UPDATE [OF column_name [,…n]]].

Для открытия курсора используется команда

OPEN {{[GLOBAL] Имя курсора}\Имя переменной}.

Для выборки данных необходимо применять команду

FETCH [[NEXT\PRIOR\FIRST\LAST\ABSOLUTE {n\@nvar}\ RELATIVE {n\@nvar}

] FROM

]

{{[GLOBAL] Имя курсора}\Имя переменной} []INTO @ Имя переменной [,…n]]

Команды UPDATE, DELETE, CLOSE и DEALLOCATE позволят соответственно произвести изменение данных, удаление данных, закрытие и освобождение курсора.

Задание 1. Создать курсор curs для таблицы authors базы данных Pubs, выполнил следующие действия:

1. Создание курсора: DECLARE curs cursor

GLOBAL SCROLL KEYSET TYPE_WARNING FOR SELECT au_lname, au_fname, phone, title,

price, advance, sales = ytd_sales

FROM titleauthor INNER JOIN authors

ON titleauthor. au_id = authors. au_id

INNER JOIN titles

ON titleauthor. title_id = titles. titles_id

WHERE authors. state <> ‘CA’ FOR UPDATE.

2. Открытие курсора: OPEN curs

3. Выборка данных:

DECLARE @@Str1 char (5),

@@VFName varchar (20),

@@VLName varchar (40),

@@VPhone char (12),

@@ VTitle varchar (80),

@@VPrice money,

@@VAdrance money,

@@VSales int,

@@Count1 timyint,

@@Var1 money

SET @@Count = 1

SET @@Var1 = 0

WHILE @@Count1 <@@CURSOR_ROWS BEGIN

IF @@Count = 1

FETCH ABSOLUTE 1 FROM CURS INTO @@VFName,

@@VLName, @@VPhone, @@VTitle, @@VPrice,

@@VAdrance, @@VSales

ELSE

FETCH curs INTO @@VFName,

@@VLName, @@VPhone, @@VTitle, @@VPrice,

@@VAdrance, @@VSales

SET @@Count1 = @@Count1+1

SET @@Var1 = @@Var1+@@Vprice*@@VSales-@@VAdrance

END

SELECT ‘Итого прибыли:’, @@Var1

4. Закрытие и освобождение курсора: CLOSE curs

DEALLOCATE curs.

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

Реализация клиентских приложений баз данных

Цель работы – изучить методы создания и использования клиентских приложений для работы с базами данных SQL Server 2000. На примере создания приложения в среде Delphi 6, с использованием sql-запросов (команд), имеющихся в языке Transact-SQL для управления данными SQL Server 2000.

Серверы технологических данных обеспечивают обмен информацией между технологическими устройствами и сетью персональных компьютеров. Они поддерживают протокол работы с технологическими устройствами и протокол работы с сетью персональных компьютеров. Данные о текущих параметрах технологического процесса могут быть использованы для контроля состояния технологического процесса и управления им с автоматизированных рабочих мест операторов; для архивирования истории изменения технологических параметров; для формирования суммарных отчётных форм с целью предоставления информации руководящему персоналу.

Язык запросов Transact – SQL является основой системы программирования SQL Server

2000.

Система программирования SQL Server 2000 относится к классу командно –

интерпретирующих систем сверхвысокого уровня.

SQL Server 2000 обеспечивает разнообразные механизмы управления данными, такие как ADO, OLE DB, ODBC, DB - Library и команды языка Transact-SQL, являющегося фундаментом всех остальных технологий.

Компоненты среды Delphi 6 для связи с SQL Server 2000

Компонент ADO-Таблица (TADOTable)

Назначение

Для доступа к конкретной таблице базы данных используется этот компонент. Он напоминает компонент Таблица (ТТаblе).

Использование

Прежде всего в свойстве Connection в раскрывающемся вписке выбирается доступный объект класса TADOConnection, cсвязывающий SQL-таблицу приложения с конкретной таблицей

базы данных, а затем в свойстве TableName выбирается нужная таблица (Num_pod). Есть еще один способ соединения с базой данных это свойство ConnectionString,

После нажатия кнопки Build

выбираем провайдера и нажимаем Next

в этом окне вводим имя сервера и выбираем базу данных (созданную заранее см.

предыдущие лабораторные работы).

Список доступных пользователю полей этой таблицы формируется с помощью стандартного редактора коллекций, вызываемого двойным щелчком на размещенном на форме объекте TADOTable (вся работа с коллекцией полей ведется с помощью контекстного меню).

После этого свойство Active можно установить равным true, чтобы сделать таблицу активной. Для связи таблицы с визуальными элементами служит стандартный компонент

источник данных (TDataSource). В его свойстве DataSet просто указывается соответствующий набор данных — в нашем случае таблица ADOTablel.

Добавим к форме два компонента, предназначенные для визуализации содержимого таблицы: Навигатор (TDBNavigator), для отображении таблицы - TDBGrid,. В них указывается источник данных (свойство DataSource). Теперь приложение полностью в работоспособно. Его можно запустить и пробежаться по всем записям таблицы.

Компонент ADO-Запрос (TADOQuery)

Назначение

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

связанных друг с другом по ключевым полям, проще всего воспользоваться данным компонентом.

Использование

Связь с базой данных происходит, как и в случае ADO-Таблицы, с помощью свойства Connection. Добавим к форме компонент TDBGrid, TDataSource, TDBNavigator и приложение почти готово. Для отображения данных используются запросы (свойство sql)

ADOQuery1.Active:=false; ADOQuery1.SQL. Clear; ADOQuery1.SQL. Add('select*from Num_pod'); ADOQuery1.Active:=true;

После выполнения этой команды будет тот же эффект, что и выше с таблицей.

Но в отличие от таблицы данный компонент позволяет делать выборку из столбцов, даже не из одной таблицы, а и нескольких. А также генерировать и другие запросы (для сведения апостроф в Delphi ставится поставленными двумя подряд) например упорядочивание,

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

ADOQuery1.Edit; ADOQuery1.Fields[0].AsString:=Form_Dobav. Edit1.Text; ADOQuery1.Fields[1].AsString:=Form_Dobav. Edit2.Text; ADOQuery1.Fields[2].AsString:=Form_Dobav. Edit3.Text; ADOQuery1.Post;

Это пример редактирование (изменения) существующей записи, где ADOQuery1.Fields[0].AsString – столбец(ячейка) в таблице находящийся на первом месте, а Form_Dobav. Edit1.Text - поле редактирование на форме.

Компонент ADO-набор данных (TADODataSet)

Назначение

Компонент представляет собой обобщенный вариант компонентов TADOTable и

TADOQuery. Он содержит несложный редактор SQL-команд, вызываемый при обращении к свойству CommandText

В списке Tables выбирается таблица, а в списке Fields — нужные поля этой таблицы. Для передачи параметров запросу или хранимой процедуре, которые вызываются через свойство CommandText, служит свойство

Params: TParams:

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

настраивать и в ходе работы программы, в частности с помощью метода

• function ParamByName(const Value: string): TParam:

Например, параметру SQL-запроса UserName значение можно присвоить так: ADODataSet1.ParamByName(‘UserName').AsString := Editl. Text;

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

Система безопасности SQL Server 2000

Цель работы – изучение общих правил разграничения и предоставление прав доступа пользователям баз данных, архитектуры и компонент системы безопасности SQL Server 2000 и режимов аутентификации пользователей, а также приобретение навыков администрирования

системы безопасности: создания и управления учетными записями, управления пользователями,

ролями и группами.

Система хранения информации должна быть максимально защищена как от случайного, так и от злонамеренного повреждения, искажения или утечки информации. С этой целью, прежде всего, надо определить круг пользователей, которые будут иметь доступ к базам данных. Далее, для этих пользователей необходимо создать учетные записи в домене Windows NT или Windows

2000, а также в соответствующем экземпляре сервера SQL Server 2000, чтобы разрешить им обращаться к этому серверу. Разрешение доступа к серверу не дает автоматически доступа к его базам данных и их объектам.

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

Для работы с базами данных пользователи любой категории проходят следующие два этапа проверки системой безопасности. На первом этапе пользователь идентифицируется по имени учетной записи и паролю, т. е. проходит аутентификацию. Если данные введены правильно, то пользователь подключается к требуемому серверу, выбрав его из списка серверов и исполнив команду подключения либо с помощью Enterprise Manager, либо исполнив команды Transact – SQL в Query Analyzer. Пользователю при этом будут предоставлены те права доступа к серверу, которые имеют роль сервера, содержащая данного пользователя. Подключение к выбранному серверу, или регистрация, не дает автоматического доступа к базам данных.

На втором этапе по регистрационному имени находится имя пользователя базы данных, которое было создано администратором, и пользователь получает права доступа к выбираемой базе данных в соответствии с той ролью базы данных, в которую был включен этот пользователь администратором базы на этапе конфигурирования системы без опасности. В разных базах данных один и тот же пользователь может иметь одинаковые или разные имена пользователя базы данных с разными правами доступа, как правило. Таким образом, пользователь имеет одно имя, которое он задает при входе в систему, и, возможно, несколько имен для доступа к базам данных и их объектам.

Для доступа приложений к базам данных им также понадобятся права. Чаще всего приложениям выдаются те же права, которые предоставлены пользователям, запускающим эти приложения. Однако для работы некоторых приложений необходимо иметь фиксированный набор прав доступа, не зависящих от прав доступа пользователя. Это обеспечивается использованием специальных ролей приложения.

Итак, компонентами системы безопасности SQL Server 2000 на уровне сервера являются: система аутентификации средствами Windows NT/2000 и средствами SQL Server, учетные записи пользователей и встроенные роли сервера. На уровне базы данных компонентами системы безопасности являются: идентификация пользователей баз данных, фиксированные и пользовательские роли баз данных, а также роли приложений.

Фиксированными ролями сервера являются: Sysadmin – для выполнения любых действий в сервере;

in in dmin min

Sereradm Setupadm Securitya Processad

– для конфигурирования и выключения сервера;

– для управления связанными серверами и процедурами, автоматически запускающимися при старте сервера;

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

– для управления процессами, запущенными на сервере;

Dbcreator – для создания и модификации баз данных;

Diskadmi n

Bulcadmi n

– для управления файлами сервера;

– для массивного копирования баз данных.

Фиксированную роль сервера нельзя удалить или модифицировать. Нельзя также создать новую фиксированную роль. Предоставить права доступа к серверу можно только путем включения пользователя в требуемую роль сервера. Таким образом, роли сервера позволяют объединять пользователей, выполняющих одинаковые функции, для упрощения администрирования системы безопасности SQL Server. В предыдущих версиях SQL Server можно было использовать только учетную запись sa, которая предоставляла все права доступа к серверу.

При создании базы данных сервер автоматически создает для нее фиксированные роли,

которые, как и фиксированные роли сервера, нельзя удалить или модифицировать:

Db_owner – для выполнения любых действий в базе данных; Db_accessadmin – для добавления и удаления пользователей;

Db_securityadmin – для управления всеми разрешениями, объектами, ролями и именами ролей;

Db_ddladmin – для выполнения любых команд DDL, кроме GRANT, DENY

и REVOKE;

Db_backupoperato r

– для выполнения команд DBCC, CHECK, POINT и BACKUP;

Db_datareader – для контроля данных во всех таблицах базы данных и их чтения;

Db_datawriter – для модификации данных в любых таблицах базы данных; Db_denydatareader – для запрета просмотра данных в любой таблице базы данных; Db_denydatawriter – для запрета модификации данных во всех таблицах базы

данных.

Кроме этих фиксированных ролей любой базы данных есть еще одна роль public, членами которой автоматически становятся все пользователи, имеющие тот или иной доступ к базе данных. Эта роль имеет специальное назначение и обеспечивает минимальные права доступа к базе данных тем пользователям, для которых их права не определены явно. Эта роль имеется во всех базах данных, включая системные master, tempdb, msdbu model и не может быть удалена. Если в базе данных разрешен пользователь quest, то установленный для public доступ будут иметь все пользователи, получившие доступ к SQL Server.

В отличие от сервера базы данных могут иметь пользовательские роли и роли приложения, которые создает администратор с помощью Enterprise Manager или Transact_SQL индивидуально для групп пользователей и групп приложений, наделяя их необходимыми правами доступа к конкретной базе данных.

В любую роль базы данных можно включать:

а) пользователей сервера;

б) роли сервера;

в) пользователей Windows NT/2000;

г) группы пользователей Windows NT/2000.

Средствами Enterprise Manager можно включать только пользователей сервера. Процедура SQL Server sp_addrolemember ‘role’, ‘security_account’ позволяет включать как роли сервера, так и пользователей Windows NT/2000, в том числе и групп пользователей с помощью задания их учетной записи в

SQL Server или Windows NT ‘security_account’ и указания требуемой роли ‘role’.

Работа с данными и выполнение хранимых процедур требуют наличия класса доступа, называемого правами на доступ к объектам баз данных: таблицам и ее столбцам, представлениям и хранимых процедурам. Такими правами являются:

SELECT, INSERT, UPDETE, DELETE, RFERENCES – для таблиц и представлений, а SELECT и для столбца (тоже и для UPDETE), SELECT и UPDETE – для столбца таблицы или представления;

EXECUTE – для хранимых процедур и функций.

Здесь право RFERENCES разрешает создавать внешние ключи и представления для таблиц.

Командой GRANT можно разрешать пользователям определенные права доступа к объектам, командой DENI – запрещать их.

Помимо прав доступа к объектам имеются еще и права, на создание объектов базы данных и самой базы данных:

CREATE DATABASE

– на создание базы данных ;

CREATE TABLE – на создание таблиц; CREATE VIEW – на создание представлений;

CREATE DEFAULT

– на создание умолчаний;

CREATE RULE – на создание правил;

CREATE PROCEDURE BACKUP DATABASE

– на создание хранимых процедур;

– на резервное копирование баз данных;

BACKUP LOG – на резервное копирование журнала транзакций; ALL – на создание любых объектов.

При установке SQL Server 2000 имеется возможность выбрать один из двух режимов аутентификации:

а) средствами Windows NT или Windows 2000;

б) средствами Windows NT/2000 и/или средствами SQL Server 2000.

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

Во втором режиме системным администратором, входящим в роль sysadmin или securityadmin, должна быть создана и сконфигурирована учетная запись в SQL Server 2000 для каждого пользователя. Эта запись будет содержать собственное имя, имя экземпляра сервера и пароль. Две такие записи с именем BUILTIN\Administrators и sa создаются автоматически при установке сервера. Обе эти записи автоматически включаются также во встроенную роль сервера sysadmin, в результате системные администраторы получают полный доступ ко всем базам данных с именем пользователя dbo (DataBase Owner). Если функции системного администратора и администратора баз данных выполняют разные люди, следует исключить учетную запись BUILTIN\Administrators. Запись sa не следует использовать, так как она предназначена для совместимости со старыми версиями SQL Server и для входа в сервер, если администратор баз данных забыл пароль. Как правило, для администратора баз данных создается отдельная учетная запись с ролью сервера sysadmin.