Глушаков С.В., Ломотько Д.В. Базы данных

Подождите немного. Документ загружается.

240

Часть II. Структурированный язык запросов SQL

После этого легко ориентироваться в полученных оценках.

Например, простой запрос

SELECT *

FROM STUDOCEN

WHERE SFAM = 'Поляков';

предоставит подробную информацию об оценках этого сту-

дента:

UNUM SFAM PNAME OCENKA

1001 Поляков Физика 5

1004 Поляков Математика 4

Как было сказано выше, допускается объединять представле-

ния с другими базовыми таблицами или представлениями. На-

пример, можно вывести информацию не только об оценках, но и

о дате ее получения. Для этого воспользуемся запросом:

SELECT SFAM, PNAME, OCENKA, UDATE

FROM STUDOCEN FIRST, USP SECOND

WHERE FIRST.SFAM = 'Поляков

AND FIRST.UNUM = SECOND. UNUM;

Вывод для этого запроса будет следующий:

SFAM PNAME OCENKA UDATE

Поляков Физика 5 10/06/1999

Поляков Математика 4 12/06/1999

Представления могут также использовать подзапросы, в том

числе соотнесенные. Например, с помощью следующего пред-

ставления можно увидеть все оценки по дисциплине со значе-

ниями выше средней по этой же дисциплине:

CREATE VIEW AVGOC

AS SELECT *

FROM USP FIRST

WHERE OCENKA >

(SELECT AVG (OCENKA)

Глава 2.6. Представления

241

FROM USP SECOND

WHERE SECOND.PNUM = FIRST.PNUM);

Извлечение данных выполняется простым запросом:

SELECT *

FROM AVGOC;

Их этого видно, насколько представления SQL упрощают ра-

боту с данными, предоставляя возможность пользователю обой-

тись без сложных запросов.

К сожалению, достаточно часто представления являются объ-

ектами, доступными только для чтения. Это означает, что ин-

формацию из них можно запрашивать, но они не могут подвер-

гаться действиям команд модификации. Кроме того, существуют

также некоторые команды, которые не допустимы в определени-

ях представлений: представление должно основываться на оди-

ночном запросе, поэтому объединение UNION не разрешается.

Помимо этого, в определении представления не используется

упорядочение ORDER BY, т.к., по аналогии с базовой таблицей, в

представлении записи являются неупорядоченными.

Синтаксис удаления представления из БД подобен удалению

базовых таблиц:

DROP VIEW <VIEW NAME>

Следует иметь в виду, что в этом случае нет необходимости

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

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

представления пользователь должен являться его владельцем.

Как уже было отмечено, не все представления способны к мо-

дификации. Вообще говоря, команды модификации языка DML -

INSERT, UPDATE и DELETE - конечно же могут применяться

для представлений. Рассмотрим ряд правил, определяющих, яв-

ляется ли представление модифицируемым. Данный момент яв-

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

представлений.

Команды модификации фактически воздействуют на значения

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

воречием в силу того, что представление состоит из результатов

запроса. Следовательно, когда происходит модификация пред-

242

Часть II. Структурированный язык запросов SQL

ставления, то редактируется набор результатов запроса. Но мо-

дификация не должна воздействовать на запрос - она должна воз-

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

и таким образом изменять вывод запроса.

При этом может возникнуть ряд неоднозначных ситуаций.

Вспомним рассмотренное выше представление, выводящее фа-

милию студента, наименование предмета и оценку. Предполо-

жим, что это представление является модифицируемым и необ-

ходимо из него удалить строку. Если каким-либо специальным

образом не оговорить правила удаления, то неясно - удаление

строки из представления повлечет за собой удаление оценки из

таблицы успеваемости USP. студента из таблицы STUDENTS,

предмета из PREDMET или какой-то комбинации этих записей.

Итак, если команды модификации могут выполняться в пред-

ставлении, то оно считается модифицируемым; в противном слу-

чае оно предназначено только для чтения при запросе. Основное

отличие между ними заключается в том, что модифицируемое

представление - это представление, в котором команда модифи-

кации может выполниться так. чтобы изменить только одну за-

пись основной таблицы в каждый момент времени, не воздейст-

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

ципа на практике, однако, затруднено. Кроме того, некоторые

представления, которые являются модифицируемыми в теории,

на самом деле не являются модифицируемыми в SQL.

Критерии, по которым определяют, является ли представле-

ние модифицируемым в SQL, следующие:

• представление должно основываться только на одной ба-

зовой таблице;

• оно должно содержать первичный ключ этой таблицы;

• оно не должно иметь никаких полей, которые бы явля-

лись агрегатными функциями;

• оно не должно содержать DISTINCT в своем определении;

• представление не должно использовать GROUP BY или

HAVING в своем определении,

• желательно, чтобы оно не использовало в своем опреде-

лении подзапросы;

• оно может быть использовано в другом представлении, но

это представление должно также быть модифицируемым;

• оно не должно использовать константы, строки или вы-

ражения значений среди выбранных полей вывода;

Глава 2.6. Представления

243

• для команды INSERT оно может содержать любые поля ос-

новной таблицы, которые имеют ограничение NOT NULL.

если другое ограничение по умолчанию не определено.

Таким образом, модифицируемые представления фактически

подобны фрагментам базовых таблиц, отображая определенную

часть их содержимого. Они могут ограничивать определенные

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

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

нечном счете, такие представления предоставляют пользователю

значения непосредственно, не выводя информацию с использова-

нием составных функций и выражений

Различия между модифицируемыми представлениями и пред-

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

Цели, для которых их используют, различны: модифицируемые

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

базовые таблицы. Фактически, пользователи не могут даже осоз-

нать, является ли объект, который они запрашивают, базовой

таблицей или представлением, т.е. в основном это средство защи-

ты для скрытия частей таблицы, являющихся конфиденциальны-

ми или не относящихся к потребностям данного пользователя.

Представления режима только для чтения, с другой стороны,

позволяют получать и форматировать данные более рационально.

Они дают целый арсенал сложных запросов, которые можно вы-

полнить и повторить снова, сохраняя полученную информацию.

Наконец, результаты этих запросов мопт затем использоваться в

других запросах самостоятельно, что позволяет избежать слож-

ных предикатов и снизить вероятность ошибочных действий.

Приведем ряд примеров. Следующее представление выводит

количество оценок, полученных всеми студентами в тот или иной

день. Оно является представлением только для чтения, т.к. со-

держит в определении агрегатную функцию и GROUP BY:

CREATE VIEW PRCOUNT (UDATE, COL)

AS SELECT UDATE, COUNT (*)

FROM USP

GROUP BY UDATE;

Следующее представление является модифицируемым в силу

того, что оно удовлетворяет перечисленным выше условиям. В

качестве вывода в нем содержатся данные об успеваемости по

предмету с кодом 2003, т.е. математике:

244

Часть II. Структурированный язык запросов SQL

CREATE VIEW MATEMUSP

AS SELECT *

FROM USP

WHERE PNUM = 2003;

Приведенное ниже представление - только для чтения:

CREATE VIEW IDXSTIP (SNUM, SFAM, NEWSTIP)

AS SELECT SNUM, SFAM, STIP*2

FROM STUDENTS

WHERE STIP=25.50;

Это связано с тем, что в определении присутствует функция

STIP*2. Кстати говоря, в большинстве СУБД последнее пред-

ставление будет допускать удаление строк с соответствующим

ключу SNUM удалением их в базовой таблице.

Представление, выводящее информацию о студентах, полу-

чивших оценки в определенный день, будет только для чтения:

CREATE VIEW DATEOC

AS SELECT *

FROM STUDENTS

WHERE SNUM IN

(SELECT SNUM

FROM USP

WHERE UDATE = 10/06/1999) ;

Это происходит из-за того, что в определении представления

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

является модифицируемым:

CREATE VIEW DATEOC2

AS SELECT *

FROM USP

WHERE UDATE IN

(10/06/1999, 11/06/1999);

Другой вывод о возможности модификации представления

может быть основан на возможности ввода того или иного значе-

ния. Например, следующее модифицируемое представление вы-

водит данные о студентах, имеющих отличные оценки:

CREATE VIEW ONLY5

AS SELECT SNUM, OCENKA

FROM USP

WHERE OCENKA = 5;

Глава 2.6. Представления

245

В данном случае представление просто ограничивает доступ

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

только часть значений. Выполним следующую команду:

INSERT INTO ONLY5

VALUES (3415, 4);

Это - допустимая команда INSERT в данном представлении и

строка будет вставлена, с помощью ONLY5, в таблицу успеваемо-

сти USP. Однако, когда эта информация будет добавлена, строка

исчезнет из представления, поскольку значение оценки не равно 5.

Иногда это может стать проблемой, т.к. данные уже находятся

в таблице успеваемости, но пользователь этого не видит и не в

состоянии выполнить их удаление или модификацию. Для ис-

ключения подобных моментов служит фраза WITH CHECK

OPTION в определение представления. Теперь можно улучшить

рассмотренное представление с учетом имеющейся возможности:

CREATE VIEW ONLY5

AS SELECT SNUM, OCENKA

FROM USP

WHERE OCENKA = 5

WITH CHECK OPTION;

После этого вышеупомянутая вставка значений будет откло-

нена системой.

Фраза WITH CHECK OPTION производит действие в режиме

"все или ничего", т.к. она размещается в определении представле-

ния, и все команды модификации будут подвергаться проверке. Та-

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

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

Аналогичная проблема может возникнуть при вставке записей

в представление с предикатом, базирующемся на одном или бо-

лее исключенных полей. Рассмотрим следующее представление:

CREATE VIEW OTLSTIP

AS SELECT SNUM, SFAM

FROM STUDENTS

WHERE STIP=25.50;

Его результатом будут номера студенческих билетов и фамилии

студентов, получающих стипендию в размере 25.50. Вообще говоря.

246 Часть II. Структурированный язык запросов SQL

логично не включить в результаты поле STIP - все равно список зна-

чений в представлении для этого поля будет одинаковым.

Что же будет происходить каждый раз, когда пользователь

попытается добавить сюда запись? Т.к. он не в состоянии ввести

значение размера стипендии, а значение по умолчанию в боль-

шинстве случаев будет NULL, то введенная запись будет тут же

исключена из представления. Причем, описанная проблема будет

происходить с любой строкой, которую попытаются добавить в

OTLSTIP. Кроме того, это еще не означает, что пользователь

имеет возможность вставлять значения в базовую таблицу. Если.

например, в таблице STUDENTS поле STIP ограничено как NOT

NULL, то результаты вставки вообще будут трудно предсказуемы

пользователем. Более того, эта проблема вряд ли будет решена,

даже при определении представления следующим образом:

CREATE VIEW OTLSTIP

AS SELECT SNUM, SFAM

FROM STUDENTS

WHERE STIP=25.50

WITH CHECK OPTION;

После этого в представлении можно будет удалять и модифи-

цировать строки, однако их не удастся вставить. Поэтому при

создании представлений, нужно четко решить - что может про-

изойти при его использовании. Рекомендация здесь такая: крайне

желательно, чтобы те поля, которые участвуют в предикате пред-

ставления, были включены и в состав выводимых полей, даже

если они содержат одинаковые значения

Необходимо упомянуть о таком свойстве фразы WITH

CHECK OPTION, как отсутствие каскадного изменения данных.

Эта фраза применяется только в представлениях, которые осно-

ваны на базовых таблицах, а не на других представлениях. На-

пример, если предыдущее представление улучшить с учетом вы-

сказанных рекомендаций, т.е.

CREATE VIEW OTLSTIP

AS SELECT SNUM, SFAM, STIP

• FROM STUDENTS

WHERE STIP=25.50'

WITH CHECK OPTION;

то попытка вставки

Глава 2.6. Представления

247

INSERT INTO OTLSTIP

VALUES (3417, Решетник,

17.00)

потерпит неудачу. Однако, если будет создано другое представ-

ление с идентичным содержанием, основанное на первом:

CREATE VIEW NEWOTL

AS SELECT *

FROM OTLSTIP;

то в соответствии со стандартом SQL рассматриваемая вставка

записи будет допустима. Дело в том, что фраза WITH CHECK

OPTION просто гарантирует, что любая корректировка в пред-

ставлении произведет значения, которые удовлетворяют преди-

кату этого представления, а модификация других представлений,

базирующихся на первом, является допустимой, если эти пред-

ставления не защищены предложениями WITH CHECK OPTION

внутри этих же представлений. Причем проверке подвергаются

только предикаты тех представлений, в которых осуществляется

модификация. Даже если второе из рассматриваемых представле-

ний будет определено как

CREATE VIEW NEWOTL

AS SELECT *

FROM OTLSTIP

WITH CHECK OPTION;

это не решит проблемы, поскольку вставляемая строка удовле-

творяет предикату представления NEWOTL. Стоит иметь в вид}',

что этот недостаток SQL в некоторых современных СУБД решен

и каскадирование фразы WITH CHECK OPTION будет произво-

диться, однако в этом необходимо убедиться в документации на

данные системы.

Одна из сильных сторон SQL - это способность функциониро-

вать по всем строкам таблицы: сколько строк удовлетворит пре-

дикату, столько и будет выведено. Однако это может вызвать ряд

проблем, когда SQL взаимодействует с другими языками про-

граммирования. Действительно, трудно передать вывод запроса

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

результаты вывода. В SQL решение этого осуществляют с помо-

щью так называемого курсора.

248

Часть il. Структурированный язык запросов SQL

Курсор - это вид переменной, которая связана с запросом.

Значением этой переменной может быть каждая строка, которая

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

будет использован, что делается командой DECLARE CURSOR

следующим образом:

EXEC SQL DECLARE CURSOR STIPCUR

FOR SELECT SNUM, SFAM, STIP

FROM STUDENTS

WHERE STIP=25.50;

Этот запрос не выполнится немедленно, т.к. в данном случае

представлено только его определение. Курсор несколько напоми-

нает представление, в котором определение содержит запрос, а

содержательной частью является любой вывод запроса, и это

происходит каждый раз, когда курсор становится открытым. Од-

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

курсора упорядочены и имеются первая, вторая и т. д., а также

последняя строка. Этот порядок может быть произвольным с яв-

ным управлением при помощи фразы ORDER BY в запросе.

Когда в программе необходимо выполнить запрос, то откры-

вают курсор с помощью следующей команды:

EXEC SQL OPEN CURSOR STIPCUR;

Значения в курсор передаются именно тогда, когда выполня-

ется приведенная выше команда, a DECLARE и FETCH на счи-

тывание информации в курсор влияния не оказывают.

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

из запроса по одной строке в каждый момент времени. Например:

EXEC SQL FETCH STIPCUR INTO :STUDENTSID,

:STUDENTSFAM, :STUDENTSSTIP;

Эта конструкция присвоит значения из первой выбранной

строк» ж переменные памяти. Следующая команда FETCH выве-

дет очередной набор значений и т. д. Как правило, команду

FETCH помещают внутрь цикла так, чтобы выбрав строку из

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

из этой строки в переменные, возвратиться обратно в цикл и пе-

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

Глава 2.6. Представления

249

Обратите внимание на оператор CLOSE CURSOR, который

должен соответствовать OPEN CURSOR. Он освобождает курсор

значений, поэтому после него запрос нужно будет выполнить

повторно с оператором OPEN CURSOR, прежде чем^ перейти к

выбору следующих значений. Для нашего случая этот оператор

будет выглядеть следующим образом:

EXEC SQL CLOSE CURSOR STIPCUR;

Другими словами, пока курсор закрыт, SQL не следит за тем.

какие строки были выбраны. Если курсор открывается снова, за-

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

должен начинаться сначала. И еще одна особенность: когда у

FETCH нет больше строк, которые надо извлекать, он просто не

меняет значения в переменных предложения INTO, т.е. если дан-

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

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

Таким образом, нами проанализирован материал о представ-

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

комендуем широко использовать эти средства в разрабатываемых

системах, т.к. они значительно расширяют возможности SQL по

выводу и ограничению данных в зависимости от конкретных по-

требностей того или иного пользователя.

Глава 2.7

Методы защиты информации

Безопасность баз данных и привилегии

При хранении информации в СУБД одной из основных задач

остается обеспечение безопасности данных. В языке SQL исполь-

зуются следующие основные принципы защиты данных:

• в БД действующими лицами являются пользователи. Ес-

ли с данными происходит какая-либо манипуляция, то

она происходит от имени конкретного пользователя.

СУБД может отказаться выполнить запрашиваемые дей-

ствия в зависимости от того, какой пользователь запра-

шивает это действие.

250

Часть II. Структурированный язык запросов SG.

• объекты БД являются теми элементами, чья защита мо-

жет осуществляться посредством SQL. Обычно обеспе-

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

других объектов БД. Система разрешает одним пользова-

телям осуществлять действия над одними объектами и

запрещает это над другими.

• в SQL используется система привилегий, т.е. прав поль-

зователя на проведение тех или иных действий над опре-

деленным объектом базы данных.

Вообще говоря, администратор БД сам создает пользователей

и дает им привилегии, но. с другой стороны, пользователи, кото-

рые создают таблицы, сами имеют права на управление этими

таблицами. Существует несколько типов привилегий, соответст-

вующих целому ряду типов операций. В SQL привилегии даются

и отменяются двумя командами SQL - GRANT (допуск) и

REVOKE (отмена)'

Каждый пользователь в среде SQL имеет специальное иден-

тификационное имя или номер (ГО) доступа. Команда, посланная

в БД, ассоциируется с определенным пользователем, т.е. специ-

альным идентификатором доступа. Поскольку это относится к

SQL БД. ID разрешения - это имя пользователя, и SQL может

использовать специальное ключевое слово USER, которое ссыла-

ется к идентификатору доступа, связанному- с текущей командой.

Команда интерпретируется и разрешается (или запрещается) на

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

зователя, подавшего команду.

В системах с большим количеством пользователей, существу-

ет специальная процедура входа в систему, которую пользователь

должен обязательно выполнить для получения доступа. Эта про-

цедура определяет, какой именно ID доступа будет связан с те-

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

БД. имеет свой собственный ГО доступа и при регистрации пре-

вращается в действительного пользователя. Однако достаточно

часто пользователи, имеющие много задач, могут регистриро-

ваться под различными ID доступа, или наоборот - один ГО дос-

тупа может использоваться несколькими пользователями. С точ-

ки зрения SQL нет никакой разницы между этими двумя случая-

ми - он воспринимает пользователя просто как его ГО доступа.

SQL БД может использовать собственную процедуру входа в

систему или может позволить другой программе (например, опе-

рационной системе компьютера) обрабатывать файл регистрации

Глава 2.7. Методы защиты информации

251

и получать ID доступа. Однако, каким бы способом СУБД не вос-

пользовалась, в любом случае SQL будет иметь ГО доступа для

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

будет иметь значение ключевое слово USER.

Каждый пользователь в SQL БД имеет набор привилегий. Эти

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

старые удаляться. Стандартные SQL привилегии, определенные

ANSI - это привилегии объекта. Это означает, что пользователь

имеет привилегию для выполнения данной команды только на

определенном объекте в БД. Привилегии объекта связаны одно-

временно и с пользователями и с таблицами, т. е. привилегия да-

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

или представлении. При этом не стоит забывать, что пользова-

тель, создавший таблицу любого вида, является ее владельцем.

Это в свою очередь означает, что такой пользователь имеет все

привилегии в этой таблице и может передавать привилегии дру-

гим пользователям для этой таблицы.

Привилегии, которые можно назначить пользователю, сле-

дующие:

• SELECT - пользователь с этой привилегией может вы-

полнять запросы к таблице:

• INSERT - пользователь с этой привилегией может выпол-

нять вставку записей, т.е. команду INSERT в таблице;

• UPDATE - пользователь с этой привилегией может вы-

полнять корректировку данных, т.е. команду UPDATE к

данной таблице;

• DELETE - пользователь с этой привилегией может вы-

полнять команду DELETE в таблице;

• REFERENCES - с этой привилегией пользователь имеет

возможность определить внешний ключ, который ис-

пользует один или более столбцов данной таблицы, как

родительский ключ;

• INDEX - дает право пользователю создавать индекс в

таблице;

• SYNONYM - пользователь, обладающей этой привилеги-

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

• ALTER - дает право пользователю выполнять команду

ALTER TABLE в данной таблице.

Итак, SQL назначает пользователям эти привилегии с помо-

щью команды GRANT. Например, если пользователь SA владеет

таблицей студентов STUDENTS и желает позволить пользовате-

252

Часть II. Структурированный язык запросов SQL

лю SHER выполнить запрос к ней, то SA должен в этом случае

вьшолнить следующую команду:

GRANT SELECT ON STUDENTS TO SHER;

Теперь SHER может выполнять запросы к таблице STU-

DENTS без других привилегий, т.е. он может только выбрать

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

воздействовало на значения в таблице, включая использование ее

в качестве родительской таблицы внешнего ключа.

Когда SQL получает команду GRANT, он проверяет привиле-

гии пользователя, подавшего ее, и определяет ее допустимость.

При этом SHER самостоятельно не может вьшолнить эту команду

или предоставить право SELECT другому пользователю, т.к. таб-

лица ему не принадлежит. Если же для пользователя SHER необ-

ходимо предоставить право вставлять в таблицу' строки, то это

можно реализовать с помощью следующего предложения:

GRANT INSERT ON STUDENTS TO SHER;

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

отдельному пользователю командой GRANT. Тогда списки при-

вилегий или пользователей в команде отделяют запятыми. На-

пример, с целью предоставления привилегий для запроса и встав-

ки значений пользователям SHER и MAG в таблице STUDENTS,

можно воспользоваться командой:

GRANT SELECT, INSERT ON STUDENTS

TO SHER, MAG;

Когда привилегии и пользователи перечислены таким обра-

зом, как показано выше, весь список привилегий предоставля-

ются всем указанным пользователям. В некоторых СУБД, по-

мимо этого, допускается через запятую перечислить список

таблиц, к которым применяются привилегии для всех указан-

ных пользователей.

Все привилегии объекта используют один и тот же синтаксис,

кроме команд UPDATE и REFERENCES, в которых можно до-

полнительно указывать имена полей. Например, команда:

GRANT UPDATE ON STUDENTS TO SHER;

Глава 2.7. Методы защиты информации

253

позволит SHER изменять значения во всех полях таблицы STU-

DENTS. Однако владелец может ограничить его в изменении

таблицы, например, введя такую команду:

GRANT UPDATE (STIP) ON STUDENTS TO SHER;

позволяя модифицировать только поле STIP. Другими словами,

можно просто указать конкретное поле, к которому привилегия

UPDATE должна быть применена. При этом допускаются списки из

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

к которым устанавливаемая привилегия должна быть применена,

например:

GRANT UPDATE (SFAM, STIP) ON STUDENTS

TO SHER;

При использовании привилегии REFERENCES следуют тому

же самому правилу, что и для UPDATE. Когда предоставляется

привилегия REFERENCES другому пользователю, он сможет

создавать внешние ключи, ссылающиеся на столбцы таблицы,

как на родительские ключи.

Подобно UPDATE, для привилегии REFERENCES может

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

предназначена эта привилегия. Например, можно предоставить

SHER право использовать STUDENTS, как таблицу родительско-

го ключа, с помощью следующей команды:

GRANT REFERENCES (SNUM, STIP) ON STUDENTS

TO SHER;

Эта команда дает SHER право использовать поля SNUM,

STIP в качестве родительских ключей по отношению к любым

внешним ключам в его таблицах. Как и в случае с привилегией

UPDATE, здесь можно исключать список полей и, таким обра-

зом, позволять всем без исключения столбцам быть исполь-

зуемыми в качестве родительских ключей, что осуществляется

с помощью команды:

GRANT REFERENCES ON STUDENTS TO SHER;

Естественно, что привилегия будет пригодна для использова-

ния только в тех полях, которые имеют ограничения, необходи-

мые для родительских ключей.

254

Часть II. Структурированный язык запросов SQL

SQL поддерживает два аргумента для команды GRANT, кото-

рые имеют специальное значение - это ALL PRIVILEGES (или

ALL) и PUBLIC.

ALL используется вместо имен привилегий в команде GRANT

для того, чтобы отдать все привилегии в таблице. Например, для

передачи пользователю SHER всего набора привилегий для таб-

лицы STUDENTS можно воспользоваться следующей командой:

GRANT ALL ON STUDENTS TO SHER;

Привилегии PUBLIC больше похожи на тип аргумента для

таблицы - когда она предоставляется, все пользователи ее полу-

чают автоматически. Наиболее часто это применяется для приви-

легии SELECT в определенных базовых таблицах или представ-

лениях, которые необходимо сделать доступными для любого

пользователя. Например, для того, чтобы позволить любому

пользователю просматривать таблицу STUDENTS, необходимо

выполнить следующее:

GRANT SELECT ON STUDENTS TO PUBLIC;

Конечно, можно предоставить любые или даже все привиле-

гии любому пользователю, но это нежелательно, т.к. все привиле-

гии, за исключением SELECT, позволяют изменять или ограни-

чивать содержание таблицы, а это может вызвать проблемы.

Имейте в виду, что любой новый пользователь, добавляемый к

системе, автоматически получает все привилегии, назначенные

как PUBLIC.

Иногда при создании таблицы возникает необходимость в

том. чтобы другие пользователи могли получать и передавать

привилегии в таблице. SQL позволяет делать это с помощью ко-

манды WITH GRANT OPTION. Например, если SA желает, что-

бы SHER имел право предоставлять привилегию SELECT в таб-

лице STUDENTS другим пользователям, это можно реализовать

следующим образом:

GRANT SELECT ON STUDENTS TO SHER

WITH GRANT OPTION;

После этого SHER получает право передавать привилегию

SELECT третьим лицам, например, он может использовать такие

команды:

Глава 2.7. Методы защиты информации

255

GRANT SELECT ON STUDENTS TO MAG;

или

GRANT SELECT ON STUDENTS TO MAG

WITH GRANT OPTION;

Таким образом, пользователь с помощью GRANT OPTION

находится в особой привилегии для данной таблицы, и может, в

свою очередь, предоставить эту привилегию к той же таблице

другому пользователю, в том числе использовать GRANT

OPTION для передачи этой возможности. Однако стоит помнить,

что это не меняет принадлежности самой таблицы, т.е.. как и

прежде, таблица принадлежит ее создателю. Пользователь же с

помощью GRANT OPTION во всех привилегиях для данной

таблицы будет иметь всю полноту власти в этой таблице.

При возникновении потребности в удалении привилегии, ис-

пользуется команда REVOKE. Синтаксис команды REVOKE по-

хож на GRANT, но имеет обратный смысл. Например, чтобы

удалить привилегию INSERT для SHER в таблице STUDENTS,

можно ввести:

REVOKE INSERT ON STUDENTS FROM SHER;

Использование списков привилегий и пользователей здесь до-

пустимы, как и в случае с GRANT. Поэтому разрешается ввести

команду, которая отменит привилегии на удаление и вставку для

пользователей SHER и MAG:

REVOKE DELETE, INSERT ON STUDENTS

TO SHER, MAG;

При этом стоит обратить внимание на такой важный момент -

привилегии отменяются пользователем, который их предоставил,

при этом отмена будет каскадироваться, т.е. она будет автомати-

чески распространяться на всех пользователей, получивших от

него эту привилегию.

Действия привилегий можно сделать более точными, если их

использовать для представлений. Всякий раз, когда передаются

привилегии в базовой таблице пользователю, то она автоматиче-

ски распространяется на все ее строки, а при использовании воз-

256

Часть II. Структурированный язык запросов SQL

можных исключений UPDATE и REFERENCES - и на все поля

таблицы. Создавая представление, которое ссылается на основ-

ную таблицу, и затем переносит привилегию на представление, а

не на таблицу, можно ограничивать эти привилегии.

Чтобы создавать представление, пользователь должен

иметь привилегию SELECT во всех таблицах, на которые ссы-

лается определение представления. Если представление моди-

фицируемое, то любая привилегия INSERT, UPDATE или

DELETE, которые имеет пользователь в базовой таблице, бу-

дет автоматически передаваться представлению. Если имеет

место недостаток в привилегиях на модификацию в базовых

таблицах, к сожалению, пользователь не сможет получить их и

в представлениях, которые создал, даже если сами эти пред-

ставления являются модифицируемыми. Причиной этому яв-

ляется использование внешних ключей в представлениях.

Предположим, что необходимо пользователю SHER пре-

доставить возможность просматривать только поля SNUM и

SFAM таблицы STUDENTS. Это реализуется путем создания

представления

CREATE VIEW TEST

AS SELECT SNUM, SFAM

FROM STUDENTS

а затем предоставления пользователю SHER привилегии SELECT

в представлении, а не в самой таблице, т.е.

GRANT SELECT ON TEST TO SHER;

Можно создать привилегии специально для полей команд мо-

дификации. Однако надо помнить, что для других полей таблицы

в команде INSERT это будет означать вставку значений по умол-

чанию, а для команды DELETE - ограничение столбца не будет

иметь значения.

Привилегии применяют и для определенных строк таблицы

опять-таки с использованием механизма представлений. Это реа-

лизуется путем использования предиката в представлении, кото-

рый и определит включенные строки.

Например, чтобы предоставить пользователю SHER привиле-

гию UPDATE в таблице STUDENTS, но так, что модифициро-

ваться могут данные для студентов с нулевой стипендией, созда-

ется такое представление:

Глава 2.7. Методы защиты информации

257

CREATE VIEW NULLSTIP

AS SELECT *

FROM STUDENTS

WHERE STIP = 0

WITH CHECK OPTION;

а затем - передается привилегия UPDATE:

GRANT UPDATE ON NULLSTIP TO SHER;

В этом заключается отличие привилегии для определенных

строк от привилегии UPDATE для некоторых столбцов, которая

распространена на все столбцы таблицы. Фраза WITH CHECK

OPTION предохраняет SHER от замены значения поля ST1P на

любое значение, кроме 0.

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

вателям доступ к уже извлеченным данным, а не к фактическим

значениям в таблице. В данном случае удобно воспользоваться

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

ние, которое выводит среднее значение оценок каждого студента:

CREATE VIEW AVGNUM

AS SELECT SNUM, AVG(OCENKA)

FROM USP

GROUP BY SNUM;

и потом передать пользователю SHER привилегию SELECT в

представлении AVGNUM:

GRANT SELECT ON AVGNUM TO SHER;

Кстати, альтернативой ограничения является использование

представлений с WITH CHECK OPTION. Например, можно соз-

дать следующее представление для ввода оценок в таблицу успе-

ваемости:

CREATE VIEW INPOC

AS SELECT *

FROM USP

WHERE OCENKA IN (1, 2, 3,

WITH CHECK OPTION;

4, 5)

9 «Базы данных»

258

Часть II, Структурированный язык запросов SQL

Тогда неправильные оценки будут исключены для всех поль-

зователей, которые имеют привилегии для ввода или модифика-

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

проще, чем изменять ограничения полей в базовой таблице.

Теперь несколько слов о привилегиях, которые не определя-

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

привилегиями системы или правами БД. Они чаще всего вклю-

чают в себя право создавать объекты данных, отличающиеся от

базовых таблиц и представлений. Привилегии системы для соз-

дания представлений, должны дополнять, а не заменять привиле-

гии объектов, о которых речь шла выше.

Не стоит забывать, что в системе всегда имеются некото-

рые типы пользователей, которые автоматически имеют боль-

шинство или даже все привилегии, а также имеют возмож-

ность передать свой статус кому-нибудь с помощью привиле-

гии или группы привилегий. В частности, таким пользовате-

лем является администратор БД.

В самом общем случае имеется три базовых привилегии

системы:

• CONNECT - состоит из права зарегистрироваться и права

создавать представления и синонимы, если пользователю

переданы соответствующие привилегии объекта;

• RESOURCE - состоит из права создавать базовые таблицы;

• DBA - это привилегия пользователя, дающая самые вы-

сокие полномочия в БД.

Некоторые системы, кроме того, имеют специального пользо-

вателя, иногда называемого SYSADM, SYS или SA, который

имеет наивысшие полномочия. Фактически это - специальное

имя, а не просто пользователь с особой DBA привилегией. Жела-

тельно, чтобы только один человек имел право зарегистрировать-

ся с именем SA.

Команда GRANT является пригодной для использования как с

привилегиями объекта, так и с системными привилегиями. На-

пример, администратор БД может передать привилегию для соз-

дания таблицы пользователю SHER следующим образом:

GRANT RESOURCE TO SHER;

Первоначально пользователь SHER, в большинстве случаев,

создается администратором БД, автоматически предоставляя ему

Глава 2.7. Методы защиты информации

259

привилегию CONNECT. В этом случае обычно добавляется пред-

ложение IDENTIFIED BY, указывающее пароль. Например, для

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

данных вводит следующее:

GRANT CONNECT TO SHER IDENTIFIED BY Romanl8;

Это приведет к созданию пользователя с именем SHER, даст

ему право регистрироваться и назначит ему пароль Roman 18. По-

сле этого SHER и администратор БД имеют возможность при

необходимости изменить пароль.

Если новый пользователь будет создавать базовые таблицы,

для него потребуется также предоставить привилегию

RESOURCE. Данное действие порождает другую проблему - если

будет предпринята попытка удалить привилегию CONNECT это-

го пользователя, и в БД имеются таблицы, которые им созданы,

команда будет отклонена, потому что это действие оставит таб-

лицу без владельца, что не допускается. Следовательно, перед

удалением пользователя сначала необходимо удалить все создан-

ные им таблицы.

Таким образом, привилегии дают возможность SQL выпол-

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

альной системе БД в соответствии с установленными для них

разрешениями и ограничениями.

Использование системного каталога

Чтобы правильно функционировать, СУБД должна следить за

многими различными объектами и элементами БД: таблицами,

представлениями, индексами, синонимами, привилегиями, поль-

зователями и т. д. Имеются различные способы реализации этого,

однако чаще всего это происходит путем сохранения служебной

информации в специальных таблицах. Это дает возможность

СУБД размещать и управлять информацией, в которой он нужда-

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

данными предметной области. Набор SQL таблиц, хранящих

служебную информацию для своих внутренних потребностей,

называют системным каталогом, словарем данных или систем-

ными таблицами.

Таблицы системного каталога, по сути, напоминают обычные

SQL таблицы, содержащие поля и записи данных. Таблицы катало-