Тема 10: SQL – администрирование субд

 

Администрирование доступа к базам данных реализуется специальными операторами СУБД MySQL, таких как GRANT и REVOKE.

 

Синтаксис команд GRANT и REVOKE

GRANT priv_type [(column_list)] [, priv_type [(column_list)]...]

ON {tbl_name | * | *.* | db_name.*}

TO user_name [IDENTIFIED BY [PASSWORD] 'password']

[, user_name [IDENTIFIED BY 'password']...]

[REQUIRE

NONE |

[{SSL| X509}]

[CIPHER cipher [AND]]

[ISSUER issuer [AND]]

[SUBJECT subject]]

[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |

MAX_UPDATES_PER_HOUR # |

MAX_CONNECTIONS_PER_HOUR #]]

 

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]...]

ON {tbl_name | * | *.* | db_name.*}

FROM user_name [, user_name...]

 

GRANT включен в MySQL начиная с версии 3.22.11 и выше. В более ранних версиях MySQL оператор GRANT ничего не выполняет.

Команды GRANT и REVOKE позволяют системным администраторам создавать пользователей MySQL, а также предоставлять права пользователям или лишать их прав на четырех уровнях привилегий: Глобальном, Базы данных, Таблицы БД, Столбца таблицы

Глобальный уровень

Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user.

 

Уровень базы данных

Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах mysql.db и mysql.host.

 

Уровень таблицы

Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.tables_priv.

 

Уровень столбца

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

Если привилегии предоставляются пользователю, которого не существует, то этот пользователь создается. Чтобы просмотреть примеры работы команды GRANT.

В таблице приведен список возможных значений параметра priv_type для операторов GRANT и REVOKE:

ALL [PRIVILEGES]	Задает все простые привилегии, кроме WITH GRANT OPTION
ALTER	Разрешает использование ALTER TABLE
CREATE	Разрешает использование CREATE TABLE
CREATE TEMPORARY TABLES	Разрешает использование CREATE TEMPORARY TABLE
DELETE	Разрешает использование DELETE
DROP	Разрешает использование DROP TABLE
EXECUTE	Разрешает пользователю запускать хранимые процедуры (для MySQL 5.0)
FILE	Разрешает использование SELECT... INTO OUTFILE и LOAD DATA INFILE
INDEX	Разрешает использование CREATE INDEX and DROP INDEX
INSERT	Разрешает использование INSERT
LOCK TABLES	Разрешает использование LOCK TABLES на таблицах, для которых есть привилегия SELECT
PROCESS	Разрешает использование SHOW FULL PROCESSLIST
REFERENCES	Зарезервировано для использования в будущем
RELOAD	Разрешает использование FLUSH
REPLICATION CLIENT	Предоставляет пользователю право запрашивать местонахождение головного и подчиненных серверов
REPLICATION SLAVE	Необходимо для подчиненных серверов при репликации (для чтения информации из бинарных журналов головного сервера)
SELECT	Разрешает использование SELECT
SHOW DATABASES	SHOW DATABASES выводит все базы данных
SHUTDOWN	Разрешает использование mysqladmin shutdown
SUPER	Позволяет установить одно соединение (один раз), даже если достигнуто значение max_connections, и запускать команды CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS и SET GLOBAL
UPDATE	Разрешает использование UPDATE
USAGE	Синоним для «без привилегий»
GRANT OPTION	Синоним для WITH GRANT OPTION

Значение USAGE можно задавать, если необходимо создать пользователя без привилегий.

Привилегии CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION..., SHOW DATABASES и SUPER являются новыми для версии 4.0.2. Чтобы воспользоваться этими новыми привилегиями после обновления до версии 4.0.2, необходимо запустить скрипт mysql_fix_privilege_tables.

В боле старых версиях MySQL привилегия PROCESS предоставляет такие же права, как и новая привилегия SUPER.

Чтобы лишить пользователя привилегий, предоставленных командой GRANT, воспользуйтесь значением priv_type в GRANT OPTION:

mysql> REVOKE GRANT OPTION ON... FROM...;

Для таблицы можно указать только следующие значения priv_type: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX и ALTER.

Для столбца можно указать только следующие значения priv_type (при использовании оператора column_list): SELECT, INSERT и UPDATE.

Глобальные привилегии можно задать, воспользовавшись синтаксисом ON *.*, а привилегии базы данных - при помощи синтаксиса ON db_name.*. Если указать ON * при открытой текущей базе данных, то привилегии будут заданы для этой базы данных.

Заметьте: шаблонные символы `_' и `%' не допускаются в определении имени баз данных в операторе GRANT. Это означает, что если вы хотите использовать, скажем, символ `_' в имени базы данных, то вы должны указать его как `\_' в GRANT, чтобы пользователь не имел возможности получить доступ к другим базам данных, соответствующих шаблону: GRANT... ON `foo\_bar`.* TO....

С тем, чтобы можно было определять права пользователям с конкретных компьютеров, в MySQL обеспечивается возможность указывать имя пользователя (user_name) в форме user@host. Если необходимо указать строку user, в которой содержатся специальные символы (такие как `-') или строку host, в которой содержатся специальные или групповые символы (такие как `%'), можно заключить имя удаленного компьютера или пользователя в кавычки (например, 'test-user'@'test-hostname').

В имени удаленного компьютера также можно указывать групповые символы. Например, user@'%.loc.gov' относится к user всех удаленных компьютеров домена loc.gov, а user@'144.155.166.%' относится к user всех удаленных компьютеров подсети 144.155.166 класс C.

Простая форма user является синонимом для user@"%".

В MySQL не поддерживаются групповые символы в именах пользователей. Анонимные пользователи определяются вставкой записей User='' в таблицу mysql.user или созданием пользователя с пустым именем при помощи команды GRANT.

Примечание: если анонимным пользователям разрешается подсоединяться к серверу MySQL, необходимо также предоставить привилегии всем локальным пользователям как user@localhost, поскольку в противном случае при попытке пользователя зайти в MySQL с локального компьютера в таблице mysql.user будет использоваться вход для анонимного пользователя!

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

mysql> SELECT Host,User FROM mysql.user WHERE User='';

На данный момент команда GRANT поддерживает имена удаленных компьютеров, таблиц, баз данных и столбцов длиной не более 60 символов. Имя пользователя должно содержать не более 16 символов.

Привилегии для таблицы или столбца формируются при помощи логического оператора OR из привилегий каждого из четырех уровней. Например, если в таблице mysql.user указано, что у пользователя есть глобальная привилегия SELECT, эта привилегия не отменяется на уровне базы данных, таблицы или столбца.

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

глобальные привилегии

OR (привилегии базы данных AND привилегии удаленного компьютера)

OR привилегии таблицы

OR привилегии столбца

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

Если привилегии предоставляются сочетанию пользователь/удаленный компьютер, которое отсутствует в таблице mysql.user, то в последнюю добавляется запись, которая остается в таблице до тех пор, пока не будет удалена при помощи команды DELETE. Иначе говоря, команда GRANT может создавать записи user в таблице, но команда REVOKE не может их удалить. Это необходимо делать при помощи команды DELETE.

Если в MySQL версий 3.22.12 и выше создан новый пользователь или предоставлены глобальные привилегии, пароль пользователя будет назначаться оператором IDENTIFIED BY, если он указан. Если у пользователя уже есть пароль, то этот пароль будет заменен новым.

Если вы не хотите отправлять пароль открытым текстом, можно воспользоваться параметром PASSWORD с зашифрованным паролем, полученным при помощи функции SQL PASSWORD() или функции C API make_scrambled_password(char *to, const char *password).

Предупреждение: если при создании нового пользователя не указать оператор IDENTIFIED BY, будет создан пользователь без пароля. Это ненадежно с точки зрения безопасности.

Пароли также можно задавать при помощи команды SET PASSWORD.

Если у вас привилегии для базы данных, то при необходимости в таблице mysql.db создается запись. Данная запись удаляется после удаления всех привилегий для этой базы данных командой REVOKE.

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

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

Параметры MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # и MAX_CONNECTIONS_PER_HOUR # являются новыми в MySQL версии 4.0.2. Эти параметры ограничивают количество запросов, обновлений и входов, которые пользователь может осуществить в течение одного часа. Если установлено значение 0 (принято по умолчанию), то это означает, что для данного пользователя нет ограничений.

Внимание: чтобы указать любую из этих опция для существующего пользователя, но не давать никаких дополнительных привилегий, используйте GRANT USAGE... WITH MAX_....

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

Учтите, что если пользователю назначена привилегия GRANT на определенном уровне привилегий, то все привилегии, которыми этот пользователь уже обладает (или которые будут ему назначены в будущем!) на этом уровне, также могут назначаться этим пользователем. Предположим, пользователю назначена привилегия INSERT в базе данных. Если потом в базе данных назначить привилегию SELECT и указать WITH GRANT OPTION, пользователь сможет назначать не только привилегию SELECT, но также и INSERT. Если затем в базе данных предоставить пользователю привилегию UPDATE, пользователь сможет после этого назначать INSERT, SELECT и UPDATE.

Не следует назначать привилегии ALTER обычным пользователям. Это дает пользователю возможность разрушить систему привилегий путем переименования таблиц!

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

При запуске mysqld все привилегии считываются в память. Привилегии базы данных, таблицы и столбца вступают в силу немедленно, а привилегии уровня пользователя - при следующем подсоединении пользователя. Изменения в таблицах назначения привилегий, которые осуществляются при помощи команд GRANT и REVOKE, обрабатываются сервером немедленно. Если изменять таблицы назначения привилегий вручную (используя команды INSERT, UPDATE и т.д.), необходимо запустить оператор FLUSH PRIVILEGES или mysqladmin flush-privileges, чтобы указать серверу на необходимость перезагрузки таблиц назначения привилегий.

Наиболее значительные отличия команды GRANT версий ANSI SQL и MySQL следующие:

• В MySQL привилегии назначаются для сочетания имя пользователя + удаленный компьютер, а не только для имени пользователя.
• В ANSI SQL отсутствуют глобальные привилегии и привилегии уровня базы данных, и ANSI SQL поддерживает не все типы привилегий MySQL. В свою очередь, в MySQL отсутствует поддержка привилегий ANSI SQL TRIGGER, UNDER.
• Структура привилегий ANSI SQL является иерархической. Если удалить пользователя, то все назначенные этому пользователелю привилегии будут отменены. В MySQL назначенные привилегии не отменяются автоматически, их при необходимости требуется удалять самостоятельно.
• В MySQL пользователь может применять к таблице оператор INSERT при наличии у него привилегии INSERT только для нескольких столбцов в этой таблице. Столбцы, для которых отсутствует привилегия INSERT, будут установлены в свои значения, принятые по умолчанию. В ANSI SQL требуется наличие привилегии INSERT для всех столбцов.
• При удалении таблицы в ANSI SQL все привилегии для этой таблицы будут отменены. Если отменить привилегию в ANSI SQL, то все привилегии, которые были назначены на основе этой привилегии, также будут отменены. В MySQL привилегии могут удаляться только при помощи команды REVOKE или путем изменения таблиц назначения привилегий MySQL.