Поиск

Операторы администрирования базы данных

Операторы управления учетными записями
Синтаксис DROP USER
DROP USER пользователь
Оператор DROP USER удаляет учетную запись пользователя MySQL, который не имеет никаких привилегий. Он служит для удаления учетной записи из таблицы user. Учетная запись именуется в таком же формате, как для GRANT или REVOKE, например, 1 jeffrey' Q 'localhost1. Части имени пользователя и имени хоста соответствуют значе­нию столбцов User и Host записи о пользователе в таблице user.
Чтобы удалить пользовательскую учетную запись, вы должны использовать следую­щую процедуру, выполняя шаги в указанном порядке:

  1. Воспользоваться SHOW GRANTS для определения, какими привилегиями обладает учетная запись. См. раздел Синтаксис SHOW GRANTS
  2. С помощью revoke лишить пользователя привилегий, показанных SHOW GRANTS. См. раздел Синтаксис GRANT и REVOKE
  3. Удалить учетную запись с помощью оператора DROP USER, или удалив запись о пользователе из таблицы user.

Оператор DROP USER был добавлен в MySQL 4.1.1. До этой версии необходимо было лишить пользователя привилегий, как описано, а потом только удалить запись из табли­цы user и сбросить таблицы привилегий, как показано ниже:
mysql> DELETE FROM mysql.user
-> WHERE User='имя_ пользователя' and Host='имя_хоста'; mysql> FLUSH PRIVILEGES;

Синтаксис GRANT и REVOKE
GRANT типпривилегии [ {список_столбцов) ] [, тип_привилегии
l [{список_столбцов) ] 3 ...
ON {имя_таблицы | * | *.* | имя_базы_данных.*} TO user[IDENTIFIED BY [PASSWORD] 'пароль1)
[, пользователь[IDENTIFIED BY [PASSWORD] 'пароль'}} ...

[REQUIRE NONE |
[{SSL| X509}] [CIPHER 'шифр1 [AND] ] [ISSUER 'выдающий' [AND]] [SUBJECT 'субъект']]
[WITH [GRANT OPTION | MAX_QUERIES__PER_HOUR количество| MAX_UPDATES_PER_HOUR количество MAX_CONNECTIONS_PER_HOUR количество]]
REVOKE тип_привилегии [ {списокстолбцов) ] [, тип_привилегии
[ {список_столбцов) ] ] ...
ОМ {имя_таблицы | * | * .* | имя_базы_данных.*} FROM пользователь [, пользователь] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM пользователь[, пользователь] ... Операторы GRANT и REVOKE позволяют администратору системы создавать учетные записи пользователей MySQL, выдавать им привилегии и отбирать их. GRANT и REVOKE реализованы в версии MySQL 3.22.11 и выше. В более ранних версиях MySQL эти опе­раторы не делали ничего.
Информация об учетных записях хранится в таблицах базы данных mysql. Эта база и система управления доступом подробно описываются в книге MySQL. Руководство ад-мин истратора.
Привилегии могут быть выданы на четырех уровнях:

  1. Глобальный уровень. Глобальные привилегии касаются всех баз данных на сервере. Эти привилегии сохраняются в таблице mysql.user. GRANT ALL ON *.* и REVOKE ALL ON *. * выдают и отбирают только глобальные привилегии.
  2. Уровень базы данных. Привилегии уровня базы данных касаются всех таблиц в базе данных. Эти привилегии хранятся в таблицах mysql.db и mysql.host. GRANT ALL ON имя_базы_данных.* и REVOKE ALL ON имя_базы_данных. * выдают и отбирают только привилегии уровня базы данных.
  3. Уровень таблицы. Привилегии уровня таблицы касаются всех столбцов в данной таблице. Эти привилегии хранятся в таблице mysql.tables_priv. GRANT ALL ON имя_базы__данных. имя_таблицы и REVOKE ALL ON имя_базы_данных.имя_таблицывыдают и отбирают только привилегии уровня таблицы.
  4. Уровень столбца. Привилегии уровня столбца касаются отдельных столбцов в таблице. Эти привилегии хранятся в таблице mysql.columns_priv. При использовании revoke вы должны указать те же столбцы, которые присутствовали в grant.

Чтобы облегчить лишение всех привилегий, в MySQL 4.1.2 добавлен следующий синтаксис, который удаляет все привилегии уровня баз данных, таблиц и столбцов для указанных пользователей:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM пользователь [, пользователь] ...
До версии MySQL 4.1.2 все привилегии нельзя было удалить сразу. Необходимо бы­ло выполнить два оператора:
REVOKE ALL PRIVILEGES FROM пользователь[, пользователь] ... REVOKE GRANT OPTION FROM пользователь [, пользователь] ...
Для операторов GRANT и REVOKE аргумент тип_привилегии может принимать значения, перечисленные в табл. Привилегии, используемые в операторах GRANT и REVOKE

Таблица Привилегии, используемые в операторах GRANT и REVOKE>

USAGE можно применять для создания пользователя, лишенного всех привилегий.
Новые привилегии CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION..., SHOW DATABASES и SUPER введены в MySQL 4.0.2. Чтобы их использовать после обновления сервера до версии 4.0.2, необходимо запустить сценарий mysql__f ixj?rivilege_tables.
В старых версиях MySQL, где не было привилегии SUPER, вместо нее можно было использовать PROCESS.

Вы можете выдавать глобальные привилегии, применяя синтаксис ON *. *, либо при­вилегии уровня базы данных, используя ON имя__базы__данных.*. Если вы указываете ON *, и у вас есть текущая база данных по умолчанию, привилегии будут выданы на теку­щую базу данных. (Внимание! Если использовать синтаксис ON *, не имея текущей ба­зы, то привилегии будут выданы глобально.)
Привилегии EXECUTION, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN и SUPER ЯВЛЯЮТСЯ административными и могут быть выданы только глобально (с использованием синтаксиса ON *. *).
Другие привилегии могут быть выданы глобально либо на более низком уровне.
Для таблиц можно установить только следующие типы привилегий: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX И ALTER.
Для столбцов (когда используется конструкция список_ столбцов) можно установить только следующие типы привилегий: SELECT, INSERT и UPDATE.
GRANT ALL выдает привилегии, существующие только на текущем уровне. Например, если вы используете GRANT ALL ON имя_базы_данных.*, что является оператором уровня базы данных, то никакие из глобальных привилегий наподобие FILE не выдаются.
MySQL позволяет назначать привилегии уровня базы данных, даже если эта база не существует. Это облегчает подготовку к эксплуатации базы данных. Однако в настоя­щий момент MySQL не позволяет назначать привилегий уровня таблицы для несущест­вующих таблиц.
MySQL не удаляет никаких привилегий автоматически, даже если вы уничтожаете таблицу или базу данных.
На заметку!
Шаблонные символы '%' и '_' допускаются в спецификациях имен баз данных для операторов GRANT глобального уровня и уровня базы данных. Это означает, например, что если вы хотите использовать символ '_' как часть имени базы, то должны предварять его обратной косой чертой 1 ('_') в аргументах оператора GRANT, чтобы предотвратить нежелательный доступ к базам с именами, соответствующими шаблону, например, GRANT.. .ON 'foo_bar'. * TO. ..
Для того чтобы выдавать права доступа пользователям с различных хостов, MySQL под­держивает спецификацию значения пользователь в форме имя_пользователя®имя_хоста. Если вам нужно указать строку имя_пользователя, включающую специальные символы (такие как '-'), либо строку имя_хоста, включающую специальные символы или симво­лы шаблонов (подобные '%'), можете поместить в кавычки имя пользователя и имя хоста (например, ' test-user'@' test-hostname'). Имена пользователя и хоста берутся в ка­вычки по отдельности.
Вы можете использовать шаблонные символы в имени хоста. Например, имя_пользователя$'%.loc.gov' касается пользователей имя_пользователя на любых хостах домена loc.gov, а имя_пользователя$'144 .155.166.%' касается пользователей имя_пользователя с любого хоста подсети класса С 144.155.166.
Простая форма имя_пользователя — синоним для имя_пользователя$ ' V ,
MySQL не поддерживает символы шаблона в именах пользователей. Анонимные пользователи заводятся вставкой строк с User='' в таблицу mysql.user, или созданием пользователя с пустым именем, с помощью оператора grant:
mysql> GRANT ALL ON test.* TO " @'localhost' ...

Внимание!
. i Если вы разрешаете анонимному пользователю подключаться к серверу MySQL, то также долж­ны выдать привилегии всем локальным пользователям имяпользователя®localhost. В про-:J тивном случае учетная запись анонимного пользователя для локального хоста в таблице mysql.user будет задействована, когда именованные пользователи попытаются подключиться ?', к серверу MySQL с локальной машины! (Учетная запись анонимного пользователя создается во *1 время установки MySQL.)
Вы можете определить, касается ли это вас, выполнив следующий запрос: mysql> SELECT Host, User FROM mysql.user WHERE User=' ';
Если во избежание описанной проблемы вы хотите удалить учетную запись аноним­ного локального пользователя, примените следующие операторы:
mysql> DELETE FROM mysql.user WHERE Host='localhost1 AND User='' ; mysql> FLUSH PRIVILEGES;
На данный момент GRANT поддерживает имена хостов, таблиц, баз данных и столбцов до 60 символов длиной. Имя пользователя может быть не длиннее 16 символов.
Привилегии на доступ к таблицам и столбцам формируются логическим ИЛИ из привилегий, выданных на каждом из четырех уровней. Например, если в таблице raysql. user указано, что пользователь имеет глобальную привилегию SELECT, то эта при­вилегия не может быть отвергнута на уровне базы данных, таблицы или столбца. Привилегии на столбцы могут быть вычислены следующим образом: глобальные привилегии
ИЛИ {привилегии базы данных И привилегии хоста) ИЛИ привилегии таблицы ИЛИ привилегии столбца
В большинстве случаев привилегии выдаются пользователю только на одном из уровней, поэтому в реальной жизни это все не так сложно.
Если выдаются привилегии для комбинации "имя пользователя/имя хоста", которой нет в таблице mysql.user, эта комбинация будет добавлена туда и останется там до тех пор, пока не будет удалена с помощью оператора DELETE.
Другими словами, GRANT может создавать новых пользователей, но REVOKE не может удалять их. Вы должны делать это явно, применяя DROP USER или DELETE.
В MySQL 3.22.12 и последующих версиях, если создается новый пользователь или если вы имеете глобальные привилегии на выдачу других привилегий, то пользователь­ский пароль устанавливается конструкцией IDENTIFYED BY, если таковая указана. Если у пользователя уже был пароль, он заменяется новым.
U Внимание!
м
., Если вы создаете нового пользователя без конструкции IDENTIFYED BY, то пользователь не
,-.„ имеет пароля. Это небезопасно.
Пароли также могут устанавливаться оператором SET PASSWORD. См. раздел Синтаксис SET PASSWORD
Если вы не хотите отправлять пароль в виде открытого текста, можно после ключе­вого слова PASSWORD давать зашифрованный пароль, который возвращает SQL-функция PASSWORD () или функция С API make_scrambled_pas sword ().

Если выдается привилегия на доступ к базе данных, запись в mysql.user заводится при необходимости. Если все привилегии на доступ к базе отбираются оператором REVOKE, эта запись удаляется.
Если у пользователя нет привилегий на доступ к таблице, то эта таблица не отобра­жается в ответ на запрос списка таблиц базы (например, оператором SHOW TABLES). Если пользователь не имеет привилегий на доступ к базе данных, имя базы не отображается в ответ на запрос SHOW DATABASES, если только у пользователя нет специальной привиле­гии SHOW DATABASES.
Конструкция WITH GRANT option дает пользователю возможность самому выдавать привилегии заданного уровня другим пользователям. Вы должны быть осторожны с тем, кому выдаете привилегии с GRANT OPTION, поскольку два пользователя с разными приви­легиями могут объединить свои привилегии!
Вы не можете выдать другому пользователю привилегию, которой не имеете сами. GRANT OPTION позволяет выдавать только те привилегии, которые вам принадлежат.
Имейте в виду, что когда вы даете пользователю привилегию GRANT OPTION на опре­деленном уровне привилегий, то все привилегии, принадлежащие ему на этом уровне (и те, которые он получит в будущем!), он сможет выдавать другим. Предположим, что вы даете пользователю привилегию INSERT в базе данных. Если затем вы дадите ему приви­легию SELECT на эту же базу с опцией GRANT OPTION, то он сможет выдавать другим не только привилегию SELECT, но и INSERT. Если вы дадите ему позже еще и привилегию UPDATE на ту же базу данных, то он сможет раздавать INSERT, SELECT и UPDATE.
Вы не должны давать привилегий ALTER обычному пользователю. Если вы это сде­лаете, он сможет потом разрушить систему, просто переименовав таблицы!
Опции MAX_QUERIES_PER_HOUR количество, MAX_UPDATES_PER_HOUR количество И MAX_CONNECTIONS_PER_HOUR количество - новые для MySQL 4.O.2. Они ограничивают количество запросов, обновлений и регистрации, которые пользователь может выпол­нить в течение часа. Если значение количество равно 0 (по умолчанию), это означает, что ограничений для данного пользователя не накладывается.
На заметку! Чтобы указать любую из этих опций для пользователя, не затрагивая его существующих приви­легий, применяйте GRANT USAGE ON *.* ... WITH MAX ...
MySQL может проверять атрибуты сертификата Х509 в дополнение к обычной ау­тентификации, основанной на имени пользователя и пароле. Чтобы специфицировать SSL-опции пользовательской учетной записи MySQL, применяйте конструкцию REQUIRE оператора GRANT. Существуют различные возможности установить ограничения на типы подключений пользователя:

  1. Если для учетной записи не установлены требования SSL или Х509, то нешифрованные подключения разрешаются, если указаны правильно имя и пароль. Однако шифрованные подключения также могут быть установлены пользователем, если клиент имеет правильный сертификат и файлы ключей.
  2. Опция REQUIRE SSL сообщает серверу, что учетной записи разрешаются только шифрованные SSL-подключения. Отметим, что эта опция может быть опущена, если есть какие-либо записи контроля доступа, разрешающие не SSL-подключения.

mysql> GRANT ALL PRIVILEGES ON test.* TO 'root1@'localhost' -> IDENTIFIED BY 'goodsecret1 REQUIRE SSL;

  • REQUIRE X509 означает, что клиент должен иметь действительный сертификат, но какой именно, кем и кому выданный - не важно. Единственное требование, чтобы можно было сверить его сигнатуру одним из сертификатов СА.
    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'6'localhost' -> IDENTIFIED BY 'goodsecret' REQUIRE X509;
  • REQUIRE ISSUER 'выдающей' накладывает ограничение на попытки подключений, которое состоит в том, что клиент должен представить действительный сертификат Х509, выданный СА 'выдающий1• Если клиент представляет действительный сертификат, но выданный кем-то другим, сервер отвергает подключение. Применение сертификата Х509 всегда подразумевает шифрование, поэтому опция SSL необязательна.
    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost1 -> IDENTIFIED BY 'goodsecret' -> REQUIRE ISSUER 7C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';
    Отметим, что значение ISSUER должно быть представлено как одна строка.
  • REQUIRE SUBJECT 'субъект' накладывает ограничение на попытки подключений, которое состоит в том, что клиент должен предъявить действительный сертификат Х509 с указанием субъекта 'субъект' в нем. Если клиент представляет действительный сертификат, но с другим субъектом в нем, сервер отвергает подключение.
    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@' localhost' -> IDENTIFIED BY 'goodsecret' -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/Email=tonu@example. com';
    Отметим, что значение SUBJECT должно быть представлено как одна строка.
  • REQUIRE CIPHER 'шифр' необходимо для того, чтобы гарантировать, что будет использоваться достаточно строгий шифр и длина ключа. SSL сам по себе может быть ослаблен, если применяются старые алгоритмы с короткими ключами шифрования. Используя эту опцию, вы можете затребовать некоторый конкретный метод шифрования, чтобы разрешить соединение.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' -> REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
Опции SUBJECT, ISSUER и CIPHER могут комбинироваться в конструкции REQUIRE:
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' -> REQUIRE SUBJECT 7C=EE/ST=Some-State/L=Tallinn/
O=MySQL demo client certificate/
CN=Tonu Samuel/Email=tonu@example.com' -> AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com' -> AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
Следует отметить, что значения SUBJECT и ISSUER должны быть представлены как одна строка.

Начиная с MySQL 4.0.4, допускается необязательное слово AND между опциями REQUIRE.
Последовательность опций не имеет значения, но ни одна не должна повторяться.
При запуске mysqld все привилегии считываются в память. Привилегии на базы дан­ных, таблицы и столбцы вступают в действие сразу, а привилегии уровня пользователя — при следующем его подключении. О модификациях в таблицах привилегий, выполняе­мых операторами GRANT и REVOKE, сервер извещается немедленно. Если вы модифици­руете таблицы привилегий вручную (применяя INSERT, UPDATE и так далее), вы должны выполнить оператор FLUSH PRIVILEGES или запустить mysqladmin flush-privileges, чтобы сообщить серверу о необходимости перезагрузки таблиц привилегий в память.
Заметьте, что если вы используете привилегии на таблицы и столбцы всего для одно­го пользователя, сервер проверяет эти привилегии для всех пользователей и это не­сколько замедляет работу MySQL. Аналогично, если вы ограничиваете количество за­просов, обновлений или подключений для любых пользователей, сервер должен вести мониторинг этих значений.
Наибольшие различия между стандартом SQL и версией GRANT от MySQL:

  1. В MySQL привилегии ассоциируются с комбинацией имя пользователя/имя хоста,а не только с одним именем пользователя.
  2. Стандарт SQL не предусматривает привилегий глобального уровня и уровня базы данных, как и типов привилегий, поддерживаемых MySQL.
  3. MySQL не поддерживает стандартных SQL-привилегий TRIGGER и UNDER.
  4. Стандартные привилегии SQL организованы в иерархической манере. Если вы
    удаляете пользователя, все его привилегии изымаются. В MySQL выданные привилегии не отнимаются автоматически, вы должны это делать сами.
  5. В стандартном SQL, когда удаляется таблица, все привилегии на нее отнимаются. В стандартном SQL, когда отнимается привилегия, все привилегии, выданные на основе данной привилегии, также отнимаются. В MySQL привилегии могут быть отняты только явным вызовом оператора REVOKE, либо манипуляциями в таблицах привилегий.
  6. В MySQL, если вы имеете привилегию INSERT только на некоторые столбцы таблицы, вы можете выполнять оператор INSERT в этой таблице. Столбцам, для которых у вас нет привилегии INSERT, будут присвоены значения по умолчанию.Стандарт SQL требует, чтобы вы имели привилегию INSERT на все столбцы.

Синтаксис SET PASSWORD
SET PASSWORD = PASSWORD('пароль')
SET PASSWORD FOR пользователь= PASSWORD( 'пароль')
Оператор SET PASSWORD назначает пароль существующей пользовательской учетной записи MySQL.
Первый синтаксис устанавливает пароль текущему пользователю. Любой клиент, подключенный к серверу с использованием неанонимной учетной записи, может изме­нять свой пароль.
Второй синтаксис присваивает пароль указанной учетной записи на текущем хосте сервера. Только клиенты, имеющие доступ к базе данных mysql, могут это делать. Зна­чение пользователь должно быть задано в формате имя лользователя@имя хоста, где имяпользователя и имя_хоста - точно такие же, которые указаны в столбцах User и Host таблицы mysql.user. Например, если у вас есть строка со значениями User и Host, равными, соответственно, 'bob' и '%.loc.gov', вы должны написать оператор следую­щим образом:
mysql> SET PASSWORD FOR 'bob'@'%.loc.gov1 = PASSWORD('newpass');
Это эквивалентно такому варианту:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpass')
-> WHERE User='bob' AND Host='%.loc.gov'; mysql> FLUSH PRIVILEGES;