Операторы управления доступом

Основные принципы

Стандарты SQL поддерживают избирательный подход к управлению доступом (см. п. 5.2.2). Согласно SQL2, объектами защиты являются базовые таблицы, представления, домены, определённые пользователями наборы символов, трансляции и сравнения (см. п. 6.1.12).

Пользователь с идентификатором _SYSTEM (Администратор базы данных) автоматически получает все системные и объектные привилегии. Ему принадлежат права регистрации пользователей, создания объектов и наделения других ID любыми объектными и некоторыми системными привилегиями. В частности, пользователь _SYSTEM может предоставить любому ID право создания схемы.

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

Для каждой предоставленной привилегии создаётся дескриптор привилегии. Он определяет:

­– получателя привилегии;

– разрешенное действие (привилегию как таковую);

– объект, на который распространяется привилегия;

– пользователя, предоставившего привилегию (предоставителя);

– право получателя привилегии быть её предоставителем.

Дескриптор сохраняется в специальной таблице системного каталога. Эти сведения используются системой для принятия решения об исполнении затребованного пользователем действия.

Обработка любого оператора начинается с идентификации его автора. Затем система определяет, какие привилегии нужны для исполнения оператора, и пытается найти соответствующий дескриптор. Если дескриптор найден, оператор исполняется. В противном случае выдаётся сообщение об ошибке.

Стандарт не определяет синтаксиса для наделения пользователей системными привилегиями, но во входном языке каждой промышленной СУБД имеются соответствующие средства. Операторы предоставления и отмены объектных привилегий определены стандартом.

Оператор предоставления привилегий

Синтаксис оператора имеет вид:

GRANT { привилегия., .. }

| { ALL PRIVILEGES }

ON имя_объекта

TO { ID_получателя.,.. }

| PUBLIC

[ WITH GRANT OPTION ];

привилегия::= SELECT

| DELETE

| { INSERT [ (имя_столбца.,..) ] }

| { UPDATE [ (имя_столбца.,..) ] }

| { REFERENCES [ (имя_столбца.,..) ] }

| USAGE

имя_объекта::= [ TABLE ] имя_таблицы

| DOMAIN имя_домена

| COLLATION имя_сравнения

| CHARACTER SET имя_набора_символов

| TRANSLATION имя_трансляции

Оператор создаёт дескрипторы привилегий. Его исполнение гарантирует, что все указанные ID получат право выполнять все указанные операции с указанным объектом. Предоставитель должен не только сам обладать предоставляемыми привилегиями, но и иметь право на их предоставление. Владелец схемы получает его автоматически и может предоставить другому пользователю, указав параметр WITH GRANT OPTION в операторе GRANT.

Привилегия USAGE (использование) предоставляется для всех типов объектов, кроме таблиц. Остальные привилегии нужны только для работы с таблицами.

Привилегии SELECT, DELETE, INSERT, UPDATE предоставляют права на выполнение одноимённых операций с таблицей. Привилегия REFERENCES означает право использования таблицы для ссылок в определениях ограничений. Привилегии INSERT, UPDATE, REFERENCES могут быть предоставлены на отдельные столбцы таблицы. По умолчанию они предоставляются на все столбцы таблицы, на которые предоставитель имеет право давать привилегии.

Привилегия USAGE предоставляет право использования объекта для определения других объектов, например, домена в определении таблицы.

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

Параметр PUBLIC означает предоставление привилегий всем ID, существующим в настоящее время и будущим.

Привилегии, назначенные одним объектам, могут распространяться на другие объекты (наследоваться).

Примеры.

GRANT SELECT ON S TO Elephant;

GRANT SELECT ON SPJ TO Elephant;

Пользователь (ID) Elephant получает право просмотра таблиц S и SPJ.[30]

Следующий оператор предоставляет пользователю, имеющему ID Lump, привилегии просмотра таблицы S и обновления значений её столбца St с правом их передачи:

GRANT SELECT, UPDATE (St) ON S TO Lump

WITH GRANT OPTION;

Оператор REVOKE

Оператор используется для отмены привилегий, предоставленных оператором GRANT. Его синтаксис следующий:

REVOKE [ GRANT OPTION FOR ] { ALL PRIVILEGES }

| { привилегия.,.. }

ON объект

FROM PUBLIC

| { ID_ обладатель_привилегии.,.. }

CASCADE | RESTRICT;

Привилегии могут быть отменены только предоставителем. Если указан параметр GRANT OPTION FOR, то отменяется только право передачи привилегий. В противном случае привилегии безусловно теряются.

Параметры CASCADE и RESTRICT указывают порядок отмены наследуемых привилегий. Если определены параметры CASCADE и GRANT OPTION FOR, то привилегии сохраняются за указанным ID, но становятся непредоставляемыми. Отменяются все прямо зависящие от них привилегии и удаляются все объекты, существование которых связано с отменяемыми привилегиями. Например, если в определении столбца таблицы используется домен, на который требуется отменяемая привилегия USAGE, то таблица будет удалена.

Если определён параметр RESTRICT, то каскадные отмены привилегий и удаление объектов не выполняются.

Пример.

REVOKE GRANT OPTION FOR UPDATE (St) ON S FROM Lump

CASCADE;

Пользователь Lump сохранит привилегию обновления столбца St таблицы S, но потеряет право передачи этой привилегии другим ID. Все полученные от Lump привилегии обновления этого столбца будут отменены.

Следует отметить, что возможность распространения права передачи привилегий может приводить к довольно запутанным ситуациям. Так, пусть ID1 – владелец объекта A – предоставил одну и ту же привилегию на объект пользователю ID2 – с правом передачи, а ID3 – без такого права (рис. 6.5). ID2 также передал эту привилегию ID3, но уже с правом передачи, которым ID3 и воспользовался, передав привилегию ID4. Что произойдёт, если теперь ID1 лишит ID3 предоставленной им привилегии? Сохранится ли за ID3 привилегия, которую предоставил ID2? Сохранится ли за ID4 привилегия, которую ему предоставил “лишенец”?

Рис. 6.5 Отмена привилегии

Этот пример показывает, что параметр WITH GRANT OPTION в операторе GRANT нужно использовать осторожно. Стандарт определяет ряд правил наследованияи отмены привилегий, которые помогают разобраться в подобных ситуациях. Нет необходимости обсуждать здесь эти правила. Сведения о них можно найти в [4].

Управление транзакциями

Модель транзакции

Согласно стандарту, ID (пользователь или прикладная программа) может выполнить транзакцию всегда. Для того чтобы начать транзакцию, не требуется предпринимать никаких специальных действий. Транзакция начинается автоматически с первым оператором SQL или непосредственно по окончании предыдущей транзакции. Транзакция может завершиться одним из четырёх способов:

– фиксацией внесённых изменений;

– отменой проделанных изменений (откатом);

– успешным завершением прикладной программы с фиксацией изменений, внесённых последней исполнявшейся транзакцией;

– аварийным завершением прикладной программы с откатом последней исполнявшейся транзакции.

В двух последних случаях новая транзакция не начинается, поскольку прикладная программа завершилась.

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

Таблица 6.4 – Уровни изолированности транзакции

Стандартное обозначение	«Грязные» чтения	Неповторяющиеся чтения	Фантомы
SERIALIZABLE	НЕТ	НЕТ	НЕТ
REPEATABLE READ	НЕТ	НЕТ	ДА
READ COMMITED	НЕТ	ДА	ДА
READ UNCOMMITED	ДА	ДА	ДА

По умолчанию для любой транзакции установлен высший уровень изолированности – SERIALIZABLE. Параллельно исполняющиеся транзакции никак не влияют на данные, которые она использует.

Уровень REPEATABLE READ допускает появление строк-фантомов. Его следует устанавливать в том случае, если транзакции не требуется повторно выполнять один и тот же многострочный запрос.

На уровне READ COMMITED возможны неповторяющиеся чтения. Он является вполне удовлетворительным, если транзакции не потребуется дважды считывать одну и ту же строку, накапливать итоги или выполнять вычисления, для которых необходимы непротиворечивые данные.

В режимах REPEATABLE READ и READ COMMITED на транзакцию могут повлиять только окончательные (зафиксированные) результаты параллельно исполняющихся транзакций. В режиме READ UNCOMMITED ей доступны и промежуточные результаты. Этот режим можно использовать лишь в том случае, когда допустимы «грязные» данные в результатах запросов.

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

Рассмотрим теперь синтаксис операторов управления транзакциями.

Оператор SET TRANSACTION

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

SET TRANSACTION

{

{ ISOLATION LEVEL

{

READ UNCOMMITED

| READ COMMITED

| REPEATABLE READ

| SERIALIZABLE

}

}

| { READ ONLY | READ WRITE }

| { DIAGNOSTICS SIZE число_сообщений }

}.,..;

За предложением ISOLATION LEVEL обязательно должен следовать один из параметров, характеризующих уровень изолированности.

Предложения READ ONLY и READ WRITE сообщают, будут ли данные только извлекаться или будут ещё и обновляться. Зная это, система сможет установить подходящие режимы блокировки объектов, используемых транзакцией. Если заранее известно, что данные обновляться не будут, то следует указать READ ONLY. Это обеспечит повышение производительности всех параллельно работающих транзакций.

Предложение DIAGNOSTICS SIZE определяет количество элементов, используемых для хранения сообщений об ошибках. Параметр число_сообщений должен быть положительным целым числом.

Оператор должен удовлетворять следующим условиям.

· Если предложение ISOLATION LEVEL не указано, то подразумевается уровень изолированности SERIALIZABLE.

· Если задан параметр READ WRITE, то уровень изолированности не может быть READ UNCOMMITED.

· Если указан уровень READ UNCOMMITED, то автоматически устанавливается параметр READ ONLY. В противном случае по умолчанию используется READ WRITE.

Пример.

SET TRANSACTION

ISOLATION LEVEL REPEATABLE READ,

DIAGNOSTICS SIZE 10;

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

Операторы COMMIT и ROLLBACK

Эти операторы определяют способ завершения транзакции.

Предложение

COMMIT [ WORK ];

сообщает системе, что транзакция завершена успешно и следует попытаться зафиксировать внесённые изменения в ФБД. Эта попытка может оказаться неуспешной, поскольку перед фиксацией выполняются все отложенные проверки ограничений. Если какое-либо из них нарушено, произойдёт автоматический откат транзакции и будет выдано сообщение об ошибке. Изменения могут быть не зафиксированы также из-за системного сбоя.

Оператор

ROLLBACK [ WORK ];

прекращает исполнение текущей транзакции и отменяет все внесённые изменения. Этот оператор никогда не может завершиться аварийно или остаться невыполненным.

В синтаксисе обоих операторов слово WORK смысловой нагрузки не несёт и может быть опущено.

Приложение А

А1 Концептуальная модель учебной базы данных

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

А2 Реализации таблиц

Таблица S (Поставщик) Таблица SPJ (Поставка)

Snum

Snam

St

Ci

Snum

Pnum

Jnum

Qt

S8

Владимир

Томск

S1

P1

J3

S2

Николай

Асино

S8

P3

J6

S5

Константин

Яя

S4

P4

J1

S4

Петр

Рио-де-Жанейро

S8

P9

J2

S3

Григорий

Яя

S3

P4

J8

S9

Егор

Яя

S2

P9

J4

S7

Сергей

Асино

S1

P2

J5

S1

Иван

Томск

S8

P7

J6

S6

Иван

Лесото

S6

P8

J9

S8

P1

J1

Таблица P (Деталь)

S5

P3

J8

S2

P3

J7

Pnum

Pnam

We

Co

Ci

S3

P9

J9

P1

корпус

белый

Томск

S7

P3

J2

P2

разъём

чёрный

Яя

S1

P2

J3

P3

кнопка

красный

Томск

S7

P6

J1

P4

корпус

синий

Лесото

S8

P1

J6

P5

панель

серый

Асино

S7

P8

J2

P6

кинескоп

черный

Яя

S6

P5

J9

P7

кнопка

оранжевый

Асино

P8

панель

розовый

Асино

P9

лампочка

красный

Томск

Таблица J (Изделие)

Имена

Смысл

столбцов

имён

Jnum

Jnam

Ci

Snum

Номер поставщика

J1

процессор

Асино

Snam

Имя поставщика

J10

дисплей

Яя

St

Статус поставщика

J2

клавиатура

Яя

Pnum

Номер детали

J3

дисплей

Томск

Pnam

Наименование детали

J4

принтер

Саяногорск

We

Вес детали в граммах

J5

процессор

Яя

Co

Цвет детали

J6

клавиатура

Томск

Jnum

Номер изделия

J7

мышь

Лесото

Jnam

Наименование изделия

J8

джойстик

Лондон

Ci

Город размещения

J9

винчестер

Рио-де-Жанейро

Qt

Объём поставки

 

Приложение Б

Запросы к БД «Поставщик–Деталь–Изделие»

1. Получить полные сведения обо всех изделиях.

2. Получить полные сведения обо всех изделиях в Томске.

3. Получить номер детали, для которой нет другой детали, имеющей меньшее значение веса.

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

5. Получить значения номеров поставщиков, поставляющих для изделия J1 деталь P1.

6. Получить наименования изделий, для которых выполняет поставки поставщик S1.

7. Получить значения цветов деталей, поставляемых поставщиком S1.

8. Получить значения номеров поставщиков, поставляющих детали для изделий J1 и J2.

9. Получить значения номеров поставщиков, поставляющих для изделия J1 красную деталь.

10. Получить значения номеров деталей, поставляемых для какого-либо изделия, производимого в Томске.

11. Получить значения номеров поставщиков, поставляющих для изделий в Томске и Яе красную деталь.

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

13. Получить значения номеров деталей, поставляемых для какого-либо изделия в Томске поставщиком из Томска.

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

15. Получить значения номеров изделий, в которые не поставляется ни одной красной детали из Томска.

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

17. Получить значения номеров изделий, использующих по крайней мере одну деталь, которую можно получить от поставщика S1.

18. Получить все пары значений названий городов такие, что поставщик в первом городе снабжает изделие во втором городе.

19. Получить все тройки (Ci, Pnum, Ci) такие, что поставщик в первом городе поставляет деталь Pnum для изделия во втором городе.

20. Повторить запрос 19, не включая тройки с одинаковыми значениями Ci.

21. Получить значения номеров поставщиков, поставляющих одну и ту же деталь для всех проектов.

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

23. Получить значения номеров тех деталей, которые поставляются для всех изделий в Томске.

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

25. Получить значения номеров изделий, которые используют только те детали, которые может поставлять поставщик S1.

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

27. Получить значения номеров изделий, которые получают по крайней мере несколько деталей от поставщика S1.

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

29. Изменить название изделия J1 на КОНСОЛЬ.

30. Изменить цвет всех красных деталей на ОРАНЖЕВЫЙ.

 

Список рекомендуемой литературы

1. Дейт К. Введение в системы баз данных. – Киев-Москва: Диалектика, 1998. – 784 с.

2. Сибилёв В.Д. Модели и проектирование баз данных. Методическое пособие по дисциплине «Базы данных». – Томск, ТУСУР, 1999. – 136 с.

3. Грабер М. Введение в SQL. – М.: Бином, 1996. – 248 с.

4. Грабер М. Справочное руководство по SQL. – М.: Лори, 1998. – 292 с.

5. Грофф Д.Р., Вайнберг П.Н. SQL: полное руководство. – Киев: Ирина, 1999. – 606 с.

 

[1] Разумеется, в рамках множества собственных объектов.

[2] В примере используется псевдокод.

[3] Для определённости можно считать объектом кортеж, хотя это может быть, например, отношение или отдельное значение некоторого атрибута.

[4] Эта формулировка относится к иерархии «отношение – кортеж». Полное определение протокола можно найти в [1].

[5] Она называлась SEQUEL (Structured English Query Language).

[6] Поэтому объектами в смысле п. 6.1.4 не являются.

[7] Отметим, что стандарт не определяет набор таблиц системного каталога. Это прерогатива разработчиков СУБД.

[8] Здесь и далее предложением называется часть оператора, следующая за ключевым словом.

[9] Не следует думать, что реальная SQL-система действительно создает в своем рабочем буфере расширенное прямое произведение.

[10] Строго говоря, стандарт не запрещает использование HAVING без GROUP BY, но на практике эта возможность используется чрезвычайно редко.

[11] Квалификаторы имён опущены за ненадобностью.

[12] Строго говоря, два множества значений, возможно, одноэлементных.

[13] Это ограничение SQL1. Определение SQL2 его не содержит.

[14] Это ограничение SQL1. Определение SQL2 его не содержит.

[15] Ограничений на число столбцов таблицы, производимой подзапросом, нет в обоих стандартах языка.

[16] Стандарт SQL1 не допускал подобных операций.

[17] Иногда эту последовательность операций называют операцией отображения.

[18] Отдельные группы разделены горизонтальными линиями. Заметьте, что группы не упорядочены.

[19] Число уровней вложенности подзапросов стандартами не ограничивается, однако любая реальная SQL-система такое ограничение имеет.

[20] Аналогичные ограничения есть и в SQL2. Их отличия обусловлены различиями в определениях предиката.

[21] Ни одна из приведённых ниже формул этого запроса не удовлетворяет требованиям SQL1.

[22] TABLE А– разрешённая стандартом форма записи оператора SELECT * FROM A;

[23] Эта таблица не может содержать дубликатов строк. Почему?

[24] SQL1 разрешал таким способом добавлять только одну строку.

[25] Сравните его с [2, п. 2.4.1].

[26] За исключением тех, для которых значение по умолчанию определено явно.

[27] Если оно задано явно или косвенно, через определение домена.

[28] Характер этих проблем таков, что их невозможно отсечь путём введения ограничений. Если бы авторы стандарта пошли по этому пути, то представление было бы совершенно бесполезным с практической точки зрения понятием языка.

[29] Стандарт SQL1 запрещает использование подзапросов в определениях обновляемых представлений.

[30] Очень хочется написать так: GRANT SELECT ON S,SPJ TO Elephant; но это не допускается правилами языка.