Удаление ограничений целостности

Для удаления ограничения целостности (первичного ключа, уникального ключа или внешнего ключа) применяется следующий синтаксис:

ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения;

 

Например, для удаления внешнего ключа связывающего таблицы Managers и Dealers можно выполнить следующую команду:

 

ALTER TABLE Dealers DROP CONSTRAINT Managers_Dealers_fk;

Оператор DROP

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

 

DROP тип объекта имя объекта;

 

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

 

DROP TABLE Dealers;

 

В данном случае таблица Dealers успешно удалится только в том случае, если на нее не ссылаются внешний ключи других таблиц. В том случае, если внешние ключи существуют, но удалить таблицу все-таки необходимо, можно либо сначала удалить все внешние ключи, ссылающиеся на удаляемую таблицу, после чего удалить таблицу, либо используя параметр CASCADE CONSTRAINTS:

 

DROP TABLE Dealers CASCADE CONSTRAINTS;

 

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

 

4. Подмножество языка DCL: операторы GRANT, REVOKE. Системные привилегии, привилегии на объекты, роли.

Подмножество языка DCL

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

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

Для любого объекта всегда существуют две категории пользователей, которые имеют привилегии на любые действия с ним – это администраторы базы данных (пользователи, владеющие ролью dba) и создатель объекта. В том случае, если необходимо предоставить доступ к тому или иному объекту для других пользователей, используются операторы языка DCL (Data Control Language, язык управления доступом к данным) GRANT и REVOKE.

Объектные и системные привилегии

Существуют привилегии двух различных видов: объектные и системные.

Объектная привилегия (object privilege) разрешает выполнение определенной операции над конкретным объектом (например, над таблицей). Название каждой из объектных привилегии совпадает с названием оператора, который она разрешает выполнять тому или иному пользователю над конкретным объектом базы данных. Примеры объектных привилегий: SELECT, DELETE, INSERT, UPDATE, REFERENCES.

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

Операторы GRANT и REVOKE

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

Для объектных привилегий синтаксис оператора GRANT таков:

GRANT привилегия ON объект TO обладатель_привилегий

[WITH GRANT OPTION];

где привилегия – это нужная привилегия, объект – это объект, к которому разрешается доступ, а обладатель_привилегий – пользователь, получающий привилегию.

Для системных привилегий синтаксис оператора GRANT таков:

GRANT привилегия TO обладатель_привилегий

[WITH ADMIN OPTION];

где привилегия ­– это предоставляемая системная привилегия, обладатель_привилегий – пользователь, получающий привилегию.

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

Для объектных привилегий синтаксис оператора REVOKE таков:

REVOKE привилегия ON объект FROM обладатель_привилегий

[CASCADE CONSTRAINTS];

где привилегия – это отменяемая привилегия, объект – это объект, на который предоставлена привилегия, обладатель_привилегий – пользователь, получающий эту привилегию. Необязательный параметр CASCADE CONSTRAINTS имеет то же значение, что и в операторе DROP TABLE – если команда GRANT отменяет привилегию REFERENCES (разрешение создавать внешние ключи, ссылающиеся на таблицу из другой схемы), то наличие параметра CASCADE CONSTRAINTS предварительно удалит все существующие внешние ключи, созданные благодаря наличию данной привилегии.

Для системных привилегий синтаксис оператора REVOKE таков:

REVOKE привилегия FROM обладатель_привилегий;

где привилегия – это отменяемая системная привилегия, обладатель_привилегий – пользователь, который более не будет ее иметь.

Примеры выдачи и изъятия привилегий при помощи команд GRANT и REVOKE:

GRANT select ON customers TO Adrian;

GRANT select, insert ON orders TO Adrian, Diana;

GRANT ALL ON customers TO Adrian;

REVOKE insert ON orders FROM Adrian;

 

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

Для выполнения операций над объектами, которые изначально были созданы в другой схеме, и доступ к которым был предоставлен при помощи команды GRANT, необходимо перед именем объекта ставить имя его владельца. Например, если пользователь А предоставил пользователю Б привилегию SELECT на таблицу MANAGERS, то для того, чтобы пользователь Б мог выбрать информацию из этой таблицы ему перед именем таблицы необходимо поставить имя владельца, то есть имя пользователя А:

-- пользователь А выдал привилегию:

GRANT SELECT ON MANAGERS TO B;

-- пользователь Б выбирает всю информацию из таблицы MANAGERS, принадлежащей пользователю А:

SELECT * FROM A.MANAGERS;

Роли

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

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

Все перечисленные проблемы решаются использованием ролей. Обычно поступают следующим образом: сначала выделяют категории пользователей, которые должны иметь различные привилегии доступа к объектам базы данных. После этого для каждой из выделенных категорий пользователей формируется набор привилегий и объединяется в роль. Далее роль предоставляется всем пользователям, относящимся к той или иной категории. При этом выдача или изъятие роли, независимо от того, сколько привилегий она включает, осуществляется при помощи всего одной команды GRANT или REVOKE соответственно.

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

Примеры команд GRANT и REVOKE для назначения и изъятия ролей, включения и изъятия привилегий из ролей:

-- создание роли

CREATE ROLE student;

-- выдача привилегий роли student

GRANT select ON TimeTable, Books TO student;

-- назначение роли student для пользователей

GRANT student TO PetrovAV, IvanovAA;

-- при включении привилегии в роль все пользователи PertovAV и IvanovAA получат эту привилегию, поскольку обладают ролью student

GRANT select ON Prepods TO student;

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

REVOKE select ON Prepods FROM student;

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

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

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

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

 

5. Транзакции, операторы управления транзакциями: COMMIT, ROLLBACK, SAVEPOINT; журнал транзакций, уровни блокировок.

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

Транзакция представляет собой последовательность операторов языка SQL, которая рассматривается как некоторое неделимое действие над базой данных. В то же время, это логическая единица работы системы. Понятие транзакции имеет непосредственное отношение к целостности БД. СУБД автоматически следит, чтобы каждая отдельная команда SQL не нарушала целостность БД. В этом случае речь идет только о декларативной и ссылочной целостности. Что касается логической или семантической целостности, то существуют такие ограничения, накладываемые на БД, которые просто невозможно не нарушить, выполнив только один SQL-оператор. Для обеспечения логической целостности иногда необходимо успешное выполнение целой группы операторов, объединенных в транзакцию, например, перевод денег с одного счета на другой в банковской системе. Данная операция может состоять минимум из двух операций: снятие денег с одного счета (один оператор UPDATE или DELETE) и принятие денег на другой счет (еще один оператор UPDATE или INSERT). Если после выполнения первого действия оказывается, что второе действие совершить невозможно (например, счет закрыт), то возникает неоднозначная ситуация – куда в итоге денутся деньги? Для разрешения подобных ситуаций и используют механизм транзакций.

Существуют различные модели транзакций, которые могут быть классифицированы на основании различных свойств, включающих структуру транзакции, параллельность внутри транзакции, продолжительность и т.д. Чаще всего имеют в виду традиционные транзакции, характеризуемые четырьмя классическими свойствами: атомарности, согласованности, изолированности, долговечности (прочности) — ACID (Atomicity, Consistency, Isolation, Durability). Иногда традиционные транзакции называют ACID-транзакциями. Упомянутые выше свойства означают следующее.

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

2.Свойство согласованности гарантирует, что по мере выполнения транзакций данные переходят из одного согласованного состояния в другое — транзакция не разрушает взаимной согласованности данных.

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

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

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

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

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

Кроме выполнения явной фиксации транзакции (COMMIT) или явного отката транзакции (ROLLBACK) существует возможность неявного выполнения этих команд, то есть без непосредственного указания одной из этих двух команд пользователем.

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

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