Безопасность и санкционирование доступа.

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

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

Обычно при установке СУБД в нее вводится какой-то идентификатор, который должен далее рассматриваться как идентификатор наиболее привилегированного пользователя - системного администратора. Каждый, кто может войти в систему с этим идентификатором (и может выдержать тесты на достоверность), будет считаться системным администратором до выхода из системы. Системный администратор может создавать базы данных и имеет все привилегии на их использование. Эти привилегии или их часть могут предоставляться другим пользователям (пользователям с другими идентификаторами). В свою очередь, пользователи, получившие привилегии от системного администратора, могут передать их (или их часть) другим пользователям, которые могут их передать следующим и т.д.

Привилегии предоставляются с помощью предложения GRANT (предоставить), общий формат которого имеет вид

GRANT привилегии ON объект TO пользователи;

В нем "привилегии" - список, состоящий из одной или нескольких привилегий, разделенных запятыми, либо фраза ALL PRIVILEGES (все привилегии); "объект" - имя и, если надо, тип объекта (база данных, таблица, представление, индекс и т.п.); "пользователи" - список, включающий один или более идентификаторов санкционирования, разделенных запятыми, либо специальное ключевое слово PUBLIC (общедоступный).

К таблицам (представлениям) относятся привилегии SELECT, DELETE, INSERT и UPDATE [(столбцы)], позволяющие соответственно считывать (выполнять любые операции, в которых используется SELECT), удалять, добавлять или изменять строки указанной таблицы (изменение можно ограничить конкретными столбцами). Например, предложение

GRANT SELECT, UPDATE (Труд) ON Блюда TO cook;

позволяет пользователю, который представился системе идентификатором cook, использовать информацию из таблицы Блюда, но изменять в ней он может только значения столбца Труд.

Если пользователь USER_1 предоставил какие-либо привилегии другому пользователю USER_2, то он может впоследствии отменить все или некоторые из этих привилегий. Отмена осуществляется с помощью предложения REVOKE (отменить), общий формат которого очень похож на формат предложения GRANT:

REVOKE привилегии ON объект FROM пользователи;

Например, можно отобрать у пользователя cook право изменения значений столбца Труд:

REVOKE UPDATE (Труд) ON Блюда FROM cook;

6.2. Обработка транзакций.

В п. 4.4.4 рассматривался пример, в котором требовалось изменить номер продукта ПР = 13 на ПР = 20 и для этого пришлось проводить последовательное изменение четырех таблиц:

UPDATE Продукты SET ПР = 20 SET WHERE ПР = 13;

UPDATE Состав ПР = 20 WHERE ПР = 13;

UPDATE Поставки SET ПР = 20 SET WHERE ПР = 13;

UPDATE Наличие ПР = 20 WHERE ПР = 13;

Этот пример приведен здесь для иллюстрации того, что единственная, с точки зрения пользователя, операция может потребовать нескольких операций над базой данных. Более того, между этими операциями может даже нарушаться непротиворечивость базы данных. Например, в ней могут временно содержаться некоторые записи поставок, для которых не имеется соответствующих записей поставляемых продуктов. Положение не спасает и перестановка последовательности обновляемых таблиц. Противоречивость исчезнет только после выполнения всех обновлений, т.е. выполнения логической единицы работы - полной замены номера продукта в базе данных (независимо от количества таблиц, в которых встречается номер продукта).

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

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

COMMIT (фиксировать), превращающее все предварительные обновления в окончательные ("зафиксированные");
ROLLBACK (откат), аннулирующее все предварительные обновления.

Таким образом, транзакцией можно назвать последовательность SQL-предложений, расположенных между "точками синхронизации", учреждаемых в начале выполнения программы и издании COMMIT или ROLLBACK и только в этих случаях. При этом следует иметь в виду, что возможен неявный COMMIT (существует режим AUTOCOMMIT, в котором система издает COMMIT после выполнения каждого SQL-предложения) и ROLLBACK (выполняемый при аварийном завершении программы).

Ясно теперь, что пользователь должен сам решать, включать ли механизм обработки транзакций и если включать, то где издавать COMMIT (ROLLEBACK), т.е. какие последовательности SQL-предложений являются транзакциями.

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

Большинство СУБД позволяют любому числу транзакций одновременно осуществлять доступ к одной и той же базе данных и в них существуют те или иные механизмы управления параллельными процессами, предотвращающие нежелательные воздействия одних транзакций на другие. По сути это механизм блокирования, главная идея которого достаточно проста. Если транзакции нужны гарантии, что некоторый объект (база данных, таблица, строка или поле), в котором она заинтересована, не будет изменен каким-либо непредсказуемым образом в течение требуемого промежутка времени, она устанавливает блокировку этого объекта. Результат блокировки заключается в том, чтобы изолировать этот объект от других транзакций и, в частности, предотвратить его изменение средствами этих транзакций. Для первой транзакции, таким образом, имеется возможность выполнять предусмотренную в ней обработку, располагая определенными знаниями о том, что объект в запросе будет оставаться в стабильном состоянии до тех пор, пока данная транзакция этого пожелает.

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

6.3. Прикладное программирование.

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

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

Переменные включающего языка:

могут появляться в предложениях манипулирования данными языка SQL только во фразах SELECT и INTO предложения SELECT, фразе WHERE предложений SELECT, UPDATE и DELETE, фразе SET предложения UPDATE, фразе VALUES предложения INSERT и, наконец, в качестве элемента выражения во фразах SELECT, WHERE или SET, но не VALUES;
должны иметь типы данных, совместимые с типами данных тех столбцов базы данных, с которыми они должны сравниваться, значения которых им должны быть присвоены или которым должны быть присвоены значения переменных;
могут иметь имена, совпадающие с именами столбцов базы данных (система различает их по месторасположению в предложениях SQL или по специальному символу, например, ".", устанавливаемому перед именем переменной, когда надо использовать ее значение)

После выполнения любого предложения SQL происходит обновление системной переменной SQLCODE (в нее заносится числовой индикатор состояния). Нулевое значение SQLCODE означает, что данное предложение выполнено успешно. Положительное значение означает, что предложение выполнено, но имела место некоторая исключительная ситуация. Например, значение +100 указывает, что не было найдено никаких данных, удовлетворяющих запросу. Наконец, отрицательное значение указывает, что имела место ошибка и предложение не выполнено. Поэтому за каждым предложением SQL в программе должна следовать проверка значения SQLCODE и должно предприниматься соответствующее действие, если это значение оказалось не таким, которое ожидалось. На практике же такую проверку осуществляют после тех предложений SQL, при выполнении которых возможна исключительная ситуация).

Основная проблема "встраивания" предложения SELECT в программу заключается в том, что SELECT, как правило, порождает таблицу с множеством строк и столбцов, а включающий язык не обладает хорошими средствами, позволяющими оперировать одновременно более чем одной записью (строкой). По этим причинам необходимо обеспечить своего рода мост между уровнем множеств языка SQL и уровнем записей включающего языка. Такой мост обеспечивают курсоры. Курсор состоит, по существу, из некоторого рода указателя, который может использоваться для просмотра множества записей. Поочередно указывая каждую запись в данном множестве, он обеспечивает возможность обращения к этим записям по одной одновременно.

Однако нередко программе требуются в каждый момент времени значения только из одной строки какой-либо таблицы, и для этого используется единичное SELECT, формат которого имеет вид

SELECT [[ALL] | DISTINCT]{ * | элемент_select [,элемент_select] ...} INTO переменная [[INDICATOR] индикаторная_переменная] [,переменная [[INDICATOR] индикаторная_переменная]] ... FROM базовая_таблица | представление [псевдоним] [,базовая_таблица | представление [псевдоним]] ... [WHERE фраза] [GROUP BY фраза [HAVING фраза]];

где элемент_select - это одна из следующих конструкций:

Очевидно, что это описание отличается от описания подзапроса (п. 2.1) наличием фразы INTO и включением в список элементов_select переменных. Переменные могут также включаться в выражения и фразы WHERE и HAVING.

Приведем несколько примеров. Получить общий вес продуктов в кладовой пансионата и занести его в переменную Общий_вес:

SELECT SUM(К_во) INTO Общий_вес FROM Наличие;

Здесь определяется единственное значение (сумма данных в столб-це) и поэтому системная переменная SQLCODE устанавливается в нуль.

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

SELECT Продукт, К_во INTO Продукт, К_продукта FROM Наличие, Продукты WHERE Наличие.ПР = Продукты.ПР AND ПР IN ( SELECT ПР FROM Продукты WHERE Продукт = 'Икра черная');

где требовалось узнать количество черной икры в кладовой пансионата и занести название этого продукта и его количество в переменные Продукт и К_продукта, соответственно, переменная SQLCODE примет значение +100, так как в кладовой нет икры.

Наконец, в примере

SELECT Продукт, К_во INTO Продукт, К_продукта FROM Наличие, Продукты WHERE Наличие.ПР = Продукты.ПР;

где не указан конкретный продукт и, следовательно, SELECT спродуцирует вывод всей таблицы продуктов, значение SQLCODE будет отрицательным. При этом значения переменных Продукт и К_продукта останутся неизменными, т.е. такими, какими они были после последнего правильного выполнения команды.

В единичном SELECT можно ввести за каждой целевой переменной слово INDICATOR и имя индикаторной переменной. Значения индикаторных переменных не равны нулю только при нулевом значении SQLCODE и NULL-значениях элементов SELECT для соответствующих целевых переменных. Например, если в столбцах К_во и Стоимость продукта 9 хранится значение NULL, то после выполнения запроса

SELECT ПР, К_во, Стоимость INTO ПР INDICATOR Инд1, К_прод INDICATOR Инд2, Стоим INDICATOR Инд3 FROM Наличие WHERE ПР = 9;

будут получены следующие значения переменных: SQLCODE = 0, ПР = 9, Инд1 = 0, Инд2 = Инд3 = -1, а К_прод и Стоим имеют значение NULL.

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

Изменить цены продуктов ленинградских поставщиков на величину, заданную переменной Измен:

UPDATE Поставки SET Цена = Цена + .Измен WHERE ПС IN (SELECT ПC FROM Поставщики WHERE Город = 'Ленинград');

Удалить все блюда, основа которых указана в переменной Осн:

DELETE FROM Блюда WHERE Основа = .Осн;

Добавить в таблицу Поставщики нового поставщика, атрибуты которого заданы соответствующими переменными ПС, Имя, Статус, Город, а Адрес и Телефон неизвестны:

INSERT INTO Поставщики (ПС, Название, Статус, Город) VALUES (.ПС, .Имя, .Статус, .Город);

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

Предложение

DECLARE имя_курсора CURSOR FOR подзапрос

определяет имя курсора и связанный с ним подзапрос. С его помощью идентифицируется некоторое множество столбцов и строк указаной таблицы (совокупности таблиц), которое становится активным множеством для данного курсора. (Точнее говоря, определяется множество частей строк, в которые входят только значения из указанных столбцов.) Курсор идентифицирует также позицию в этом множестве (сначала это позиция его первой записи). Активные множества всегда рассматриваются как упорядоченные. При этом упорядочение определяется фразой ORDER BY, а при ее отсутствии – системой (в порядке загрузки строк в таблицу).

Описанные с помощью DECLARE CURSOR множества используются рядом предложений SQL для удаления отмеченных строк (DELETE), их модификации (UPDATE) или присвоения значений перечисленных в SELECT столбцов переменным, список которых указывается в предложении FETCH (вызвать). Однако перед выполнением этих команд необходимо активизировать курсор, который в этот момент не должен быть открыт. Для этого используется предложение OPEN (OPEN имя_курсора).

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

FETCH имя_курсора INTO переменная [[INDICATOR] индикаторная_переменная] {,переменная [[INDICATOR] индикаторная_переменная]} ...

Команду FETCH обычно помещают в некоторый цикл, размещая непосредственно за ней команду анализа SQLCODE. Это позволяет обнаружить переход от значения 0 на +100 и организовать выход из цикла.

Наконец, следует упомянуть еще два предложения, связанные с курсорами. Это предложение для дезактивации курсора (CLOSE имя_курсора) и предложение для уничтожения курсора (DROP CURSOR имя_курсора).

Мы уже отмечали, что ограниченный объем книги не позволяет подробнее обсудить и должным образом проиллюстрировать использование курсоров. Не затронуты также предложения COMMENT ON (ввести описание таблицы или столбца), CONNECT (открыть базу данных), DISCONNECT (закрыть базу данных), WHENEVER (организовать обработку индикатора ошибки SQL) и несколько предложений, связанных с управлением транзакциями и параллельным их исполнением.