При хранении информации в СУБД одной из основных задач остается обеспечение безопасности данных. В языке SQLиспользуется следующие основные принципы защиты данных:
§ В БД действующими лицами являются пользователи. Если с данными происходит какая либо манипуляция, то она происходит от имени конкретного пользователя. СУБД может отказаться выполнить запрашиваемые действия в зависимости от того, какой пользователь запрашивает это действие.
§ Объекты БД являются теми элементами, чья защита может осуществляться посредствомSQL.Обычно обеспечивается не только защита таблиц и представлений, но и других объектов БД. Система разрешает одним пользователям осуществлять действия над одними объектами и запрещает это над другими.
§ В SQL используется система привилегий, т.е. прав пользователя на проведение тех или иных действия над определенным объектом базы данных.
Вообще говоря, администратор БД сам создает пользователей и дает им привилегии, но, с другой стороны, пользователи, которые создают таблицы, сами имеют права на управление этими таблицами. Существует несколько типов привилегий, соответсвующих целому ряду типов операций. В SQLпривилегии даются и отменяются двумя кодами SQL- GRANT(допуск) и REVOKE(отмена).
Каждый пользователь в среде SQLимеет специальное идентификационное имя или номер (ID) доступа. Команда, посланная в БД, ассоциируется с определенным пользователем, т.е. специальным идентификатором доступа. Поскольку это относится к SQL БД, IDразрешений – это имя пользователя, и SQL можетиспользовать специальное ключевое слово USER, которое ссылается к идентификатору доступа, связанному с текущей командой. Команда интерпретируется и разрешается (или запрещается) на основе информации, связанной с идентификатором доступа пользователя, подавшего команду.
В системах с большим количеством пользователей, существует специальная процедура входа в систему, которую пользователь должен обязательно выполнить для получения доступа. Эта процедура определяет, какой именно IDдоступа будет связан с текущим пользователем. Обычно каждый человек использующий БД, имеет свой собственный IDдоступа, и при регистрации превращается в действительного пользователя. Однако достаточно часто пользователи, имеющие много задач, могут регистрироваться под различными IDдоступа , или наоборот- один IDдоступа может использоваться несколькими пользователями. С точки зрения SQLнетникакой разницы между этими двумя случаями – он воспринимает пользователя просто как его IDдоступа.
SQLБД может использовать собственную процедуру входа в систему или может позволить другой программе (например, операционной системе компьютера) обрабатывать файл регистрации и получать IDдоступа. Однако каким бы способом СУБД не пользовались, в любом случае SQLбудетиметь
IDдоступа для того, чтобы связать его с действиями пользователя, для которого будет иметь значение ключевого слова USER.
Каждый пользователь в SQL БДимеет набор привилегий. Эти привилегии могут изменяться со временем – новые добавляться, старые удаляться. Стандартные SQLпривилегий, определенные ANSI- это привилегии объекта. Это означает, что пользователь имеет привилегию для выполнения данной команды только на определенном объекте в БД. Привилегии объекта связаны одновременно и с пользователями и с таблицами, т е. привилегий дается определенному пользователю в указанной базовой таблице или представлении. При этом не стоит забывать, что пользователь, создавший таблицу любого вида, является ее владельцем. Это в свою очередь означает, что такой пользователь имеет все привилегии в этой таблице и может передавать привилегии другим пользователям для этой таблицы.
Привилегии, которые можно назначить пользователю, следующие:
§ SELECT-пользователь с этой привилегией может выполнять запросы к таблице;
§ INSERT-пользователь с этой привилегией может выполнять вставку записей, т.е. команду INSERT в таблице;
§ UPDATE-пользователь с этой привилегией может выполнять корректировку данных, т.е команду UPDATE к данной таблице;
§ DELETE-пользователь с этой привилегией может выполнять команду DELETE в таблице;
§ REFERENCES- с этой привилегией пользователь имеет возможность определить внешний ключ, который использует один или более столбцов данной таблицы. Как родительский ключ.
§ INDEX- дает право пользователю создавать индекс в таблице;
§ SYNONYM- пользователь, обладающей этой привилегией, имеет право создавать синоним для объекта;
§ ALTER дает право пользователю выполнять команду ALTER TABLE в данной таблице.
Итак, SQLназначает пользователям эти привилегии с помощью команды GRANT.Например, если пользователь SA владеет таблицей студентов STUDENTS и желает позволить пользователю SHER выполнить запрос к ней, то SA должен в этом случае выполнить следующую команду:
GRANT SELECT ONSTUDENTS TOSHER;
Теперь SHER может выполнять запросы к таблице STUDENTS без других привилегий, т.е. он может только выбрать значений, но не может выполнить любое действие, которое бы воздействовало на значения в таблице, включая использование ее в качестве родительской таблицы внешнего ключа.
Когда SQLполучает командуGRANT,он проверяет привилегии пользователя, подавшего ее, и определяет ее допустимость. При этом SHER самостоятельно не может выполнить эту команду или предоставить право SELECTдругому пользователю,т.к. таблица ему не принадлежит. Если же для пользователя SHER необходимо предоставить право вставлять в таблицу строки, то это можно реализовать с помощью следующего предложения:
GRANT INSERT ONSTUDENTS TOSHER;
Однако часто приходится передавать несколько привилегий отдельному пользователю командой GRANT.Тогда списки привилегий или пользователей в команде отделяют запятыми. Например, с целью предоставления привилегий для запроса и втавки значений пользователям SHER и MAGв таблице STUDENTS, можно воспользоваться командой:
GRANT SELECT, INSERT ONSTUDENTS TOSHER, MAG;
Когда привилегии и пользователи перечислены таким образом, как показано выше, весь список привилегий предоставляются всем указанным пользователям. В некоторых СУБД, помимо этого, допускается через запятую перечислить список таблиц список таблиц, к которым применяются
привилегии для всех указанным пользователей.
Все привилегии объекта используют один и тот же синтаксис, кроме команд UPDATE и REFERENCES,в которых можно дополнительно указывать имена полей. Например команда:
GRANT URDATE ONSTUDENTS TOSHER;
Позволит SHER изменять значение во всех полях таблицы STUDENTS.Однако владелец может ограничить его в изменении таблицы, например, введя такую команду:
GRANT URDATE (STIP) ONSTUDENTS TOSHER;
Позволяя модифицировать только поле STIP.Другими словами, можно просто указать конкретное поле, к которому привилегия URDATEдолжна быть применена. При этом допускаются списки из нескольких, указанных в произвольном порядке, столбцов таблицы, к которым устанавливаемая привилегия должна быть применена, например:
GRANT URDATE (SFAM,STIP) ONSTUDENTS TOSHER;
При использовании привилегии REFERENCESследует тому же самомуправилу, что и дляURDATE.Когда предоставляемая привилегия REFERENCESдругому пользователю, он сможет создавать внешние ключи, ссылающиеся на столбцы таблицы, как на родительские ключи.
Подобно URDATE,для привилегии REFERENCESможет быть указан список из одного или более столбцов, для которых предназначена эта привилегия. Например, можно предоставить SHER право использовать STUDENTS, как таблицу родительского ключа, с помощью следующей команды:
GRANT REFERENCES (SNUM,STIP) ONSTUDENTS TOSHER;
Эта команда дает SHER право использовать поля SNUM,STIP в качестве родительских ключей по отношению к любым внешним ключам в его таблицах. Как и в случае с привилегией URDATE,здесь можно исключать список полей и, таким образом, позволять всем без исключения столбцам быть используемыми в качестве родительских ключей, что осуществляется с помощью команды:
GRANT REFERENCES ONSTUDENTS TOSHER;
Естественно, что привилегия будет пригодна для использования только в тех полях, которые имеют ограничения, необходимые для родительских ключей.
SQLподдерживает два аргумента для команды GRANT,которые имеют специальное значение- это ALL PRIVILEGES (илиALL ) PUBLIC.
ALLиспользуется вместо имен привилегий в команде GRANTдля того, чтобы отдать все привилегии в таблице.Например, для передачи пользователю SHER всего набора привилегий для таблицы STUDENTS, необходимо выполнить следующее:
GRANT ALL ONSTUDENTS TOSHER;
Привилегии PUBLICбольше похожи на тип аргумента для таблицы- когда она предоставляется, все пользователи ее получают автоматически. Наиболее часто это применяется для привилегии SELECT вопределенных базовых таблицах или представлениях, которые необходимо сделать доступным для любого пользователя. Например, для того, чтобы позволить любому пользователю просматривать таблицу STUDENTS, необходимо выполнить следующее:
GRANT SELECT ONSTUDENTS TOSHER;
Конечно, можно предоставить любые или даже все привилегии любому пользователю, но это нежелательно, т.к. все привилегии, за исключением SELECT, позволяют изменять или ограничивать содержание таблицы, а это может вызвать проблемы. Имейте в виду, что любой новый пользователь, добавляемый к системе, автоматически получает все привилегии, назначенные как PUBLIC.
Иногда при создании таблицы возникает необходимость в том, чтобы другие пользователи могли получать и передавать привилегии в таблице. SQLпозволяет делать это с помощью команды WITH GRANT OPTION.Например, если SA желает, чтобы SHER имел право предоставлять привилегию SELECTв таблице STUDENTS другим пользователям, это можно реализовать следующим образом:
GRANT SELECT ONSTUDENTS TOSHER
WITH GRANT OPTION;
После этого SHER получает право передавать привилегию SELECTтретьим
лицам, например, он может использовать такие команды:
GRANT SELECT ONSTUDENTS TOMAG;
Или
GRANT SELECT ONSTUDENTS TOMAG
WITH GRANT OPTION;
Таким образом, пользователь с помощью GRANT OPTIONнаходится в особой привилегии для данной таблицы, и может, в свою очередь, предоставить эту привилегию к этой же таблице другому пользователю, в том числе использовать GRANT OPTIONдля передачи этой возможности. Однако стоит помнить , что это не меняет принадлежности самой таблицы, т.е. как и прежде, таблица принадлежит её создателю. Пользователь с помощью же GRANT OPTIONво всех привилегиях для данной таблицы будет имеет всю полноту власти в этой таблице.
При возникновении потребности в удалении привилегии, используется команда REVOKE. Синтаксис команды REVOKE похож наGRANT,но имеет обратный смысл. Например, чтобы удалить привилегию INSERT для SHER в таблице STUDENTS, можно ввести:
REVOKE INSERT ON STUDENTS FROMSHER;
Использование списков привилегий и пользователей здесь допустимы, как и в случае с GRANT.Поэтому разрешается ввести команду, которая отменит привилегии на удаление и вставку для пользователей SHER и MAG:
REVOKE DELETE, INSERT ON STUDENTS TO SHER, MAG;
При этом можно обратить внимание на такой важный момент – привилегии отменяются пользователем, который их предоставил, при этом отмена будет каскадироваться, т.е. она будет автоматически распространятся на всех пользователей, получивших от него эту привилегию.
Действия привилегий можно сделать более точными, если их использовать для представлений. Всякий раз, когда передаются привилегии в базовой таблице пользователю, то она автоматически распространяется на все ее строки, а при использовании возможных исключений UPDATEи REFERENCES-и на все поля таблицы. Создавая представление, которое ссылается на основную таблицу, и затем переносит привилегию на представление, а не на таблицу, можно ограничивать эти привилегии.
Чтобы создавать представление, пользователь должен иметь привилегию SELECTво всех таблицах, на которые ссылается определение представления. Если представление модифицируемое, то любая привилегия INSERT,UPDATE или DELETE,которые имеет пользователи в базой таблице, будет автоматически передаваться представлению. Если имеет место недостаток в привилегиях на модификацию в базовых таблицах, к сожалению, пользователь не сможет получить их в представлениях , которые создал, даже если сами эти представления являются модифицируемыми. Причиной этому является использование внешних ключей в представлениях.
Предположим, что необходимо пользователю SHER предоставить возможность просматривать только поля SNUM и SFAM таблицы STUDENTS. Это реализуется путем создания представления
GREATE VIEWTEST
AS SELECTSNUM, SFAM
FROMSTUDENTS
А затем предоставления пользователю SHER привилегии SELECTв представлении, а не в самой таблице, т.е.
GRANT SELECT ONTEST TOSHER;
Можно создать привилегии специально для полей команд модификации. Однако надо помнить, что для других полей таблицы в команде INSERTэто будет означать вставку значений по умолчанию, а для команды DELETE –ограничение столбца не будет иметь значения.
Привилегии применяют и для определенных строк таблицы опять таки с использованием механизма представлений. Это реализуется путем использования предиката в представлении, который и определит включенные строки.
Например, чтобы предоставить пользователю SHER привилегию UPDATE в таблице STUDENTS, но так, что модифицироваться могут данные для студентов с нулевой стипендией, создается такое представление:
GREATE VIEWNULLSTIP
AS SELECT*
FROMSTUDENTS
WHERESTIP = 0
WITH CHECK OPTION;
А затем – передается привилегия UPDATE:
GRANT UPDATE ON NULLSTIP TOSHER;
В этом заключается отличие привилегии для определенных строк от привилегии UPDATEдля некоторых столбцов, которая распространена на все столбцы таблицы. ФразаWITH CHECK OPTIONпредохраняет
SHER от замены значения поля STIP на любое значение, кроме 0.
Другая возможность состоит в том, чтобы предлагать пользователям доступ к уже извлеченным данным, а не к фактическим значением в таблице. В данном случае удобно воспользоваться агрегатными функциями. Например, можно создать представление, которое выводит среднее значение оценок каждого студента:
GREATE VIEW AVGNUM
AS SELECTSNUM, AVG( OCENKA )
FROMUSP
GROUP BYSNUM;
Потом передать пользователю SHER привилегию SELECTв представлении
AVGNUM:
GRANT SELECT ON AVGNUM TOSHER;
Кстати, альтернативной ограничение является использование с WITH CHECK OPTION.Например, можно создать следующее представление для ввода оценок в таблицу успеваемости:
GREATE VIEWINPOC
AS SELECT*
FROMUSP
WHEREOCENKA IN (1, 2, 3, 4, 5)
WITH CHECK OPTION;
Тогда неправильные оценки будут исключены для всех пользователей, которые имеют привилегии для ввода или модификация значений в данное представление. Это иногда оказывается проще, чем изменять ограничение полей в базовой таблице.
Теперь несколько слов о привилегиях, которые не определяются в терминах объектов данных. Такие привилегия называются привилегиями системы или правами БД. Они чаще всего включают в себя право создавать объекты данных, отличающиеся от базовых таблиц и представлений. Привилегии системы для создания представлений, должны дополнять, а не заменять привилегии объектов, о которых речь шла выше.
Не стоит забывать, что в системе всегда имеются некоторые типы пользователей, которые автоматически имеют большинство или даже все привилегии, а также имеют возможность передать свой статус кому – нибудь с помощью привилегий или группы привилегий. В частности таким пользователем является администратор БД.
§ CONNECT –состоит из права зарегистрироваться и права создавать представление и синонимы, если пользователю переданы соответствующие привилегии объекта;
§ RESOURCE –состоит из права создавать базовые таблицы;
§ DBA –это привилегия пользователя, дающая самые высокие полномочия в БД.
Некоторые системы, кроме того, имеют специального пользователя, иногда называемого SYSADM, SYS или SA,который имеет наивысшие полномочия. Фактически это – специальное имя, а не просто пользователь с особой DBAпривилегией. Желательно, чтобы только один человек имел право зарегистрироваться с именем SA.
Команда GRANTявляется пригодной для использования как с привилегиями объекта, так и с системными привилегиями. Например, администратор БД может передать привилегию для создания таблицы пользователю SHER следующим образом:
GRANT RESOURCE TOSHER;
Первоначально пользователь SHER, в большинстве случаев, создается администратором БД, автоматически предоставляя ему привилегию CONNECT.В этом случае обычно добавляется предложение IDENTIFIED BY,указывающее пароль. Например, для первоначальной регистрации пользователя администратор базы данных вводит следующее:
GRANT CONNECT TOSHER IDENTIFIED BYRoman 18;
Это приведет к созданию пользователя именем SHER, даст ему право регистрироваться и назначить ему пароль Roman 18. После этого SHER и администратор БД имеют возможность при необходимости изменить пароль.
Если новый пользователь будет создавать базовые таблицы, для него потребуется также предоставить привилегию RESOURCE.Данное действие поражает другую привилегию CONNECTэтого пользователя, и в БД имеются таблицы, которые им созданы, команда будет отклонена, потому что это действие оставит таблицу без владельца, что не допускается. Следовательно, перед удалением пользователя сначала необходимо удалить все созданные им таблицы.
Таким образом, привилегии дают возможность SQLвыполнять действия только через определенных пользователей в специальной системе БД в соответствии с установленными для них разрешениями и ограничениями.
