GRANT
в MySQL;Инструкция CREATE USER
создает новую строку в системной таблице mysql.user
для каждой учетной записи. Каждая создаваемая учетную запись пользователя, может сопровождаться необязательным параметрами, указывающим, как учетная запись аутентифицируется. Эти значения позволяют указать подключаемые модули аутентификации и учетные данные (например, пароль).
Для параметров, которые опускаются (не указываются) в инструкция CREATE USER
устанавливаются значения по умолчанию. Учетная запись при первом создании не имеет привилегий. Подключаемый модуль аутентификации по умолчанию default_authentication_plugin
(использует внутренние учетные данные MySQL-сервера из системной таблицы mysql.user
). Роль по умолчанию: NONE
, SSL/TLS: NONE
, лимиты ресурсов: неограниченны.
Примеры создания пользователя со значениями по умолчанию:
Здесь создается пользователь с именем 'jeffrey'
с паролем 'password'
, которому разрешено подключаться к серверу из подсети 'localhost'
.
-- пользователю 'jeffrey' разрешен доступ только с 'localhost' CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
Следующий пример создает пользователя с именем 'david'
с паролем 'paswd'
, которому разрешено подключаться к серверу с любого IP-адреса из диапазона 198.51.100.1 - 198.51.100.255.
-- пользователю 'david' разрешен доступ с IP-адресов подсети 198.51.100.[1-255] CREATE USER 'david'@'198.51.100.0/255.255.255.0' IDENTIFIED BY 'paswd'; -- эквивалентно CREATE USER 'david'@'198.51.100.%' IDENTIFIED BY 'paswd';
'user_name'@'host_name'
.@'host_name'
является необязательной. Имя учетной записи, состоящее только из имени пользователя, эквивалентно 'user_name'@'%'
. При этом часть @'%'
означает, что созданный пользователь может подключаться к серверу со всех диапазонов IP-адресов, что означает серьезную дыру в безопасности. user_name
сравнивается с учетом регистра. При сравнении значений host_name
регистр не учитывается.user_name
содержит специальные символы (например, пробел или -
), или строка host_name
содержит специальные символы или подстановочные знаки (например, .
или %
), то необходимо использовать кавычки (обратные `
, одинарные '
или двойные "
). Например, в имени учетной записи 'test-user'@'%.com'
как имя пользователя, так и часть имени хоста должны быть заключены в кавычки. 'me'@'localhost'
, а не 'me@localhost'
. Последнее на самом деле эквивалентно 'me@localhost'@'%'
.'localhost'
указывает на локальный хост. IP-адрес '127.0.0.1' указывает петлю интерфейса IPv4. IP-адрес '::1' указывает петлю интерфейса IPv6.%
и _
. Они имеют то же значение, что и операциях сопоставления LIKE
. Например, значение узла '%'
соответствует любому имени узла, тогда как значение '%.mysql.com'
соответствует любому узлу в домене mysql.com
. '198.51.100.%'
соответствует любому хосту в сети 198.51.100 класса C.'198.51.100.somewhere.com'
(например, маска '198.51.100.%'
соответствует диапазону IP 198.51.100.0/24), MySQL не выполняет сопоставление имен хостов, начинающихся с цифр и точки. Подстановочное значение IP-адреса может соответствовать только IP-адресам, но не именам хостов. CREATE USER ... IDENTIFIED WITH ...
.IDENTIFIED BY 'password_string'
устанавливает модуль аутентификации по умолчанию, передает значение 'password_string'
в виде открытого текста подключаемому модулю для возможного хеширования и сохраняет запись в системной таблице mysql.user
. Плагин может использовать указанное значение пароля, и в этом случае хеширование не происходит.IDENTIFIED WITH auth_plugin
устанавливает подключаемый модуль аутентификации на auth_plugin
, очищает пароль до пустой строки и сохраняет запись в системной таблице mysql.user
.IDENTIFIED WITH auth_plugin BY 'password_string'
устанавливает модуль аутентификации на auth_plugin
, передает незашифрованное значение 'auth_string'
подключаемому модулю для возможного хеширования и сохраняет запись в системной таблице mysql.user
. Плагин может использовать указанное значение пароля, и в этом случае хеширование не происходит.IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
устанавливает модуль аутентификации auth_plugin
, генерирует случайный пароль, передает значение пароля в открытом виде в подключаемый модуль для возможного хеширования и сохраняет запись в системной таблице mysql.user
. Также, в результате запроса на создание пользователя возвращает открытый текстовый пароль в результирующем наборе.IDENTIFIED WITH auth_plugin AS 'auth_string'
устанавливает модуль аутентификации auth_plugin
и сохраняет значение 'auth_string'
в строке учетной записи mysql.user
. Предполагается, что строка 'auth_string'
уже имеет формат, требуемый подключаемым модулем аутентификации. Другими словами, если подключаемому модулю требуется хэшированное значение, то оно должно быть уже хешировано в подходящем формате.Различие синтаксиса BY 'password_string'
и AS 'auth_string'
:
-- синтаксис BY 'password_string' -- пароль указывается открытым текстом CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'text_password'; -- синтаксис AS 'auth_string' -- пароль ДОЛЖЕН указываться как хэшированное значение CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH caching_sha2_password AS 'sha2_password'; -- пароль указывается как значение авторизации LDAP-сервера CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u1_ldap,ou=People,dc=example,dc=com'
Указанные в примерах материала имена плагинов остаются неизменными. Насчет остальных разрешенных плагинов, необходимо уточнять в официальной документации (от версии к версии названия могут меняться).
В соответствии со спецификацией, оператор CREATE USER
может включать параметры, ограничение ресурсов, управление паролями и свойства блокировки. Все такие параметры являются глобальными для одного запроса на создание нескольких пользователей и применяются ко всем учетным записям.
MySQL разрешает накладывать ограничения для отдельных учетных записей на использование ресурсов сервера:
MAX_QUERIES_PER_HOUR
: количество запросов SELECT
, которые аккаунт может сделать в час;MAX_UPDATES_PER_HOUR
: количество запросов UPDATE
, которые аккаунт может сделать в час;MAX_CONNECTIONS_PER_HOUR
: сколько раз учетная запись может подключаться к серверу в час;MAX_USER_CONNECTIONS
: количество одновременных подключений к серверу с одной учетной записью.Если какое-то ограничение указано несколько раз, то приоритет имеет последняя запись.
Чтобы установить лимиты ресурсов для учетной записи необходимо использовать предложение WITH
. Значение по умолчанию для каждого лимита равно нулю (нет лимита). Например, чтобы создать новую учетную запись, которая может получить доступ к базе данных, но только ограниченным образом, необходимо выполнить инструкции:
CREATE USER 'francis'@'localhost' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;
Не обязательно указывать сразу ВСЕ ограничения.
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
Инструкция CREATE USER
поддерживает несколько значений для управления паролями. Если указано несколько вариантов управления паролями заданного типа, последний из них имеет приоритет.
Следующий запрос создает учетную запись, у которой срок действия пароля никогда не истекает (все новые пользователи создаются с таким параметром по умолчанию):
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
Инструкция может принимать значения:
PASSWORD EXPIRE
: требует немедленной смены пароля.PASSWORD EXPIRE NEVER
: срок действия пароля никогда не истекает (по умолчанию).PASSWORD EXPIRE INTERVAL N DAY
: срок действия пароля истекает через N
дней. PASSWORD HISTORY N
: устанавливается длина истории паролей, равная N
паролям. Запрещает повторное использование любого из N
последних используемых паролей, при его замене на новый. PASSWORD REUSE INTERVAL N DAY
: устанавливает интервал повторного использования пароля равным N
дням, при его замене на новый.PASSWORD REQUIRE CURRENT
: требует, чтобы при изменении пароля указывался текущий пароль (по умолчанию не требуется).Следующий запрос создает учетную запись, которая остается заблокированной в течение двух дней после четырех неудачных попыток ввода пароля подряд:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password' FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 2;
FAILED_LOGIN_ATTEMPTS N
: отслеживает попытки входа в учетную запись с неверным паролем. Число N
должно быть в пределах от 0 до 32767. Значение 0 отключает отслеживание неудачных попыток входа. Значения больше 0 указывают, сколько последовательных неудачных попыток ввода пароля вызывают временную блокировку учетной записи (если PASSWORD_LOCK_TIME
также не равен нулю).PASSWORD_LOCK_TIME N | UNBOUNDED
: как долго блокировать учетную запись после большого количества последовательных попыток входа в систему с неверным паролем. Число N
должно быть в пределах от 0 до 32767 или UNBOUNDED
. Значение 0 отключает временную блокировку учетной записи. Значения больше 0 указывают, как долго блокировать учетную запись в днях. Значение UNBOUNDED
приводит к неограниченной продолжительности блокировки учетной записи; после блокировки учетная запись остается в заблокированном состоянии до тех пор, пока не будет разблокирована администратором. Для отслеживания неудачных попыток входа и временной блокировки обе опции учетной записи FAILED_LOGIN_ATTEMPTS
и PASSWORD_LOCK_TIME
должны быть ненулевыми.
Инструкция ALTER USER
изменяет соответствующую строку в системной таблице mysql.user
для указанной учетной записи.
Для каждого имени учетной записи используется формат, описанный в инструкция CREATE USER
, только вместо слова CREATE
используется ALTER
за исключением некоторых особенностей, рассмотренных ниже.
При изменении пользователя ALTER USER
- неуказанные свойства и параметры сохраняют свои текущие значения.
Пример 1: изменим пароль учетной записи и срок истечения его действия. В результате пользователь должен подключиться с указанным паролем и выбрать новый при следующем подключении:
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
Пример 2: Блокировка или разблокировка учетной записи:
ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK; ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;
Пример 3: Требовать подключение с использованием SSL и установить ограничение в 20 подключений в час:
ALTER USER 'jeffrey'@'localhost' REQUIRE SSL WITH MAX_CONNECTIONS_PER_HOUR 20;
Пример 4: изменим сразу несколько учетных записей, указав некоторые свойства для каждой учетной записи и некоторые глобальные свойства:
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'jeffrey_new_password', 'jeanne'@'localhost', 'josh'@'localhost' IDENTIFIED BY 'josh_new_password' REPLACE 'josh_current_password' RETAIN CURRENT PASSWORD REQUIRE SSL WITH MAX_USER_CONNECTIONS 2 PASSWORD HISTORY 5;
Значение IDENTIFIED BY
, следующее за пользователем jeffrey
, применяется только к непосредственно предшествующей учетной записи, поэтому оно изменяет пароль на jeffrey_new_password
только для jeffrey
. Для учетной записи jeanne
нет значения, таким образом, пароль остается неизменным. Для учетной записи josh
инструкция IDENTIFIED BY
устанавливает новый пароль (josh_new_password
), инструкция REPLACE
указывается для проверки того, что пользователь, выполняющий оператор ALTER USER, знает текущий пароль (josh_current_password
), и этот текущий пароль также сохраняется в качестве вторичного пароля учетной записи. В результате josh
может подключаться как с основным, так и с дополнительным паролем.
Предложения RETAIN CURRENT PASSWORD
и DISCARD OLD PASSWORD
реализуют возможность использования двойного пароля и доступны, начиная с MySQL 8.0.14. Оба являются необязательными, но если они заданы, имеют следующие эффекты:
RETAIN CURRENT PASSWORD
сохраняет текущий пароль учетной записи в качестве вторичного пароля, заменяя любой существующий вторичный пароль. Новый пароль становится основным паролем, но клиенты могут использовать учетную запись для подключения к серверу, используя либо основной, либо дополнительный пароль. Исключение: если новый пароль, указанный в операторе ALTER USER
, пуст, то вторичный пароль также становится пустым, даже если указан RETAIN CURRENT PASSWORD
.RETAIN CURRENT PASSWORD
для учетной записи с пустым основным паролем, то инструкция завершится ошибкой.RETAIN CURRENT PASSWORD
, то вторичный пароль остается неизменным.RETAIN CURRENT PASSWORD
, то оператор завершится ошибкой.DISCARD OLD PASSWORD
отбрасывает вторичный пароль, если он существует. Учетная запись сохраняет только свой основной пароль, и клиенты могут использовать учетную запись для подключения к серверу только с основным паролем.Пример 5. Установим новый основной пароль и сохраним существующий пароль в качестве дополнительного:
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;
Пример 6. Теперь отменим вторичный пароль, оставив для учетной записи только основной пароль:
ALTER USER 'jeffery'@'localhost' DISCARD OLD PASSWORD;
Оператор DROP USER
удаляет одну или несколько учетных записей MySQL и их привилегии. Он удаляет строки привилегий для учетной записи из всех таблиц привилегий.
-- Синтаксис DROP USER IF EXISTS user [, user]; -- Пример DROP USER IF EXISTS 'jeffrey'@'localhost'; DROP USER IF EXISTS 'jeanne'@'localhost', 'josh'@'localhost';
Роли, указанные в значении системной переменной required_roles
, не могут быть удалены.
DROP USER
либо завершается успешно для всех именованных пользователей, либо откатывается и не действует в случае возникновения какой-либо ошибки. По умолчанию возникает ошибка, если пытаться удалить несуществующего пользователя. Если задано предложение IF EXISTS
, то оператор выдает предупреждение для каждого несуществующего именованного пользователя, а не ошибку.
Запрос на удаление пользователя записывается в двоичный журнал, если он выполнен успешно. В случае неудачи происходит откат без внесения изменений. Оператор, записываемый в двоичный журнал, включает всех именованных пользователей. Если задано предложение IF EXISTS
, это включает даже пользователей, которые не существуют и не были удалены.
GRANT
в MySQL.Привилегии, предоставленные учетной записи MySQL, определяют, какие операции может выполнять учетная запись. Привилегии MySQL различаются в зависимости от контекста, в котором они применяются, и на разных уровнях работы:
Список статических привилегий, используемых в инструкции GRANT
:
ALL [PRIVILEGES]
: предоставляет все привилегии на указанном уровне доступа.ALTER
: использование ALTER TABLE
. Уровни: глобальный, база данных, таблица.ALTER ROUTINE
: изменение или удаление сохраненных подпрограмм. Уровни: глобальный, база данных.CREATE
: создание базы данных и таблиц. Уровни: глобальный, база данных, таблица.CREATE ROLE
: создание ролей. Уровень: глобальный.CREATE ROUTINE
: создание сохраненной процедуры. Уровни: глобальный, база данных.CREATE TEMPORARY TABLES
: создание временной таблицы. Уровни: глобальный, база данных.CREATE USER
: использование CREATE USER
, DROP USER
, RENAME USER
и REVOKE ALL PRIVILEGES
. Уровень: глобальныйCREATE VIEW
: создание или изменение представлений. Уровни: глобальный, база данных, таблица.DELETE
: использование DELETE
. Уровень: глобальный, база данных, таблица.DROP
: удаление баз данных, таблиц и представлений. Уровни: глобальный, база данных, таблица.DROP ROLE
: удаление ролей. Уровень: глобальный.EVENT
: использование событий для планировщика событий. Уровни: глобальный, база данных.EXECUTE
: выполнять сохраненные процедуры. Уровни: глобальный, база данных.FILE
: вызывать сервер для чтения или записи файлов. Уровень: глобальный.GRANT OPTION
: привилегия, имея которую пользователь может предоставлять или удалять привилегии из других учетных записей. Уровни: глобальный, база данных, таблица.INDEX
: создание или удаление индексов. Уровни: глобальный, база данных, таблица.INSERT
: использование INSERT
. Уровни: глобальный, база данных, таблица.LOCK TABLES
: использование LOCK TABLES
для таблиц, для которых есть привилегия SELECT
. Уровни: глобальный, база данных.PROCESS
: просматривать все процессы с помощью SHOW PROCESSLIST
. Уровень: глобальный.REFERENCES
: создание внешнего ключа. Уровни: глобальный, база данных, таблица.RELOAD
: использование операций FLUSH
(очищает или перезагружают различные внутренние кэши, очищает таблицы или получает блокировки). Уровень: глобальный.SELECT
: использование SELECT
. Уровни: глобальный, база данных, таблица.SHOW DATABASES
: показать все базы данных. Уровень: глобальныйSHOW VIEW
: использование SHOW CREATE VIEW
. Уровни: глобальный, база данных, таблицаTRIGGER
: триггерные операции. Уровни: глобальный, база данных, таблица.UPDATE
: использование UPDATE
. Уровни: глобальный, база данных, таблица, столбец.Глобальные привилегии являются административными или применяются ко всем базам данных на данном сервере. Чтобы назначить глобальные привилегии, используется синтаксис ON *.*
:
GRANT ALL ON *.* TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
Инструкция GRANT ALL
на глобальном уровне предоставляет все статические глобальные привилегии и все зарегистрированные динамические привилегии. Динамическая привилегия, зарегистрированная после выполнения инструкции GRANT
, не предоставляется задним числом ни одной учетной записи. Все динамические привилегии являются глобальными и могут быть предоставлены только глобально.
Статические привилегии встроены в сервер, в отличие от динамических привилегий, которые определяются во время выполнения. Статические привилегии, такие как CREATE USER
, FILE
, PROCESS
, RELOAD
, REPLICATION CLIENT
, REPLICATION SLAVE
, SHOW DATABASES
, SHUTDOWN
и SUPER
являются административными и могут предоставляться только глобально.
Другие привилегии могут быть предоставлены глобально или на более определенных уровнях.
Привилегии базы данных применяются ко всем объектам в данной базе данных. Для назначения привилегии на уровне базы данных, используется синтаксис ON db_name.*
:
GRANT ALL ON mydb.* TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
Если используется синтаксис ON *
(а не ON *.*
), то привилегии назначаются на уровне базы данных для базы данных по умолчанию. Если база данных по умолчанию не выбрана, то возникает ошибка.
Привилегии CREATE
, DROP
, EVENT
, LOCK TABLES
и REFERENCES
можно указать на уровне базы данных. Привилегии для таблицы или хранимой процедуры/функции также могут быть указаны на уровне базы данных, и в этом случае они применяются ко всем таблицам или хранимых процедур/функций в базе данных.
Привилегии для таблицы применяются ко всем столбцам в данной таблице. Для назначения привилегии на уровне таблицы, используется синтаксис ON db_name.tbl_name
:
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
Если указать tbl_name
вместо db_name.tbl_name
, то оператор применяется к tbl_name
в базе данных по умолчанию. Если база данных по умолчанию не выбрана, то возникает ошибка.
Допустимые значения привилегий на уровне таблицы: ALTER
, CREATE VIEW
, CREATE
, DELETE
, DROP
, GRANT OPTION
, INDEX
, INSERT
, REFERENCES
, SELECT
, SHOW VIEW
, TRIGGER
и UPDATE
.
Привилегии уровня таблицы применяются к базовым таблицам и представлениям. Они не применяются к таблицам, созданным с помощью CREATE TEMPORARY TABLE
, даже если имена таблиц совпадают.
Синтаксис GRANT
без предложения ON
предоставляет роли, а не отдельные привилегии. Например:
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
Роль, которая будет предоставлена, а также учетная запись пользователя должны существовать. Предоставление роли не делает ее автоматически активной. Сведения об активации и деактивации ролей смотрите в разделе "Создание и использование ролей в MySQL".
Оператор REVOKE
связан с GRANT
и позволяет администраторам удалять привилегии учетной записи.
Синтаксис, в котором за ключевым словом REVOKE
следует одно или несколько имен ролей, должно включать предложение FROM
, указывающее одного или нескольких пользователей или ролей, у которых следует отозвать роли.
-- отзывает у пользователя 'jeffrey' -- право `INSERT` для всех БД сервера REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; -- отзывает роль 'role1' у пользователя 'user1'@'localhost' -- и роль 'role2' у пользователя 'user2'@'localhost' REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost'; -- отзывает у роли 'role3' право `SELECT` для БД `world` REVOKE SELECT ON world.* FROM 'role3';
Если часть имени хоста учетной записи или имени роли не указана, то по умолчанию равна '%'
. Пользователь, который отзывает привилегию (делает запрос REVOKE
) у другого пользователя, должен иметь эту (отзываемую) привилегию.
Чтобы отозвать все привилегии (поверьте, проще удалить пользователя), необходимо использовать синтаксис, который для указанных пользователей или ролей отбрасывает все: глобальные привилегии, привилегии базы данных и привилегии таблиц. Например:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION ...
не отзывает никаких ролей.
Роль MySQL - это именованный набор привилегий. Подобно учетным записям пользователей, роли могут иметь привилегии, предоставленные им и отозванные у них.
Учетной записи пользователя могут быть предоставлены роли, которые предоставляют привилегии, связанные с каждой ролью. Это позволяет назначать наборы привилегий учетным записям и обеспечивает удобную альтернативу предоставлению индивидуальных привилегий.
Различие между ролями и пользователями заключается в том, что CREATE ROLE
создает идентификатор авторизации, который по умолчанию заблокирован, тогда как CREATE USER
создает идентификатор авторизации, который по умолчанию разблокирован.
В следующем списке перечислены возможности управления ролями, предоставляемые MySQL:
CREATE ROLE
и DROP ROLE
создают и удаляют роли.GRANT
и REVOKE
назначают привилегии для отзыва привилегий у учетных записей и ролей пользователей.SHOW GRANTS
отображает назначения привилегий и ролей для учетных записей и ролей пользователей.SET DEFAULT ROLE
показывает, какие роли учетных записей активны по умолчанию.SET ROLE
изменяет активные роли в текущем сеансе.CURRENT_ROLE()
отображает активные роли в текущем сеансе.Рассмотрим сценарий:
app_db
.Для создания роли, необходимо использовать оператор CREATE ROLE
:
CREATE ROLE 'app_developer', 'app_read', 'app_write';
Для назначения привилегий ролям, необходимо выполнить инструкцию GRANT
, используя тот же синтаксис, что и для назначения привилегий учетным записям пользователей:
GRANT ALL ON app_db.* TO 'app_developer'; GRANT SELECT ON app_db.* TO 'app_read'; GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
Теперь предположим, что изначально требуется одна учетная запись разработчика, две учетные записи пользователей, которым требуется доступ только для чтения, и одна учетная запись пользователя, которой требуется доступ для чтения/записи. Используем CREATE USER
для создания учетных записей:
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass'; CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass'; CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass'; CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
Чтобы назначить каждой учетной записи пользователя необходимые привилегии, можно использовать альтернативный синтаксис оператора GRANT
, который позволяет предоставлять роли, а не привилегии:
GRANT 'app_developer' TO 'dev1'@'localhost'; GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost'; GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
Оператор GRANT
для учетной записи rw_user1
предоставляет роли 'appread' и 'appwrite', которые в совокупности обеспечивают необходимые права для чтения и изменения записей.
Синтаксис GRANT
предоставления ролей учетной записи отличается от синтаксиса GRANT
предоставления привилегий наличием предложение ON
, который используется для назначения привилегий (его нет для назначения ролей). Так как синтаксисы различны, нельзя одновременно назначать привилегии и роли в одном операторе.
Созданная роль изначально заблокирована и не может использоваться. Чтобы указать, какие роли должны становиться активными при подключении пользователя к серверу, необходимо вызвать инструкцию SET DEFAULT ROLE
. Эта инструкция устанавливает по умолчанию все назначенные роли для каждой созданной ранее учетной записи:
SET DEFAULT ROLE ALL TO 'dev1'@'localhost', 'read_user1'@'localhost', 'read_user2'@'localhost', 'rw_user1'@'localhost';
Теперь, если подключается пользователь rw_user1
, то начальное значение CURRENT_ROLE()
отражает новые назначения ролей по умолчанию:
SELECT CURRENT_ROLE(); +--------------------------------+ | CURRENT_ROLE() | +--------------------------------+ | `app_read`@`%`,`app_write`@`%` | +--------------------------------+
Чтобы все явно назначенные и обязательные роли автоматически активировались при подключении пользователей к серверу, необходимо включить системную переменную active_all_roles_on_login
. По умолчанию автоматическая активация роли отключена.
В рамках сеанса, пользователь может выполнить команду SET ROLE
для изменения набора активных ролей. Например, для rw_user1
:
-- включим все роли, кроме 'app_write' SET ROLE ALL EXCEPT 'app_write'; -- при этом пользователь `rw_user1` -- может только читать базу данных SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | `app_read`@`%` | +----------------+ -- теперь включим все роли SET ROLE DEFAULT; SELECT CURRENT_ROLE(); +--------------------------------+ | CURRENT_ROLE() | +--------------------------------+ | `app_read`@`%`,`app_write`@`%` | +--------------------------------+
Точно так же, как роли могут быть предоставлены учетной записи, их можно отозвать из учетной записи:
REVOKE role FROM user;
REVOKE
также можно применить к роли, чтобы изменить предоставленные ей привилегии. Это влияет не только на саму роль, но и на любую учетную запись, которой предоставлена эта роль. Предположим, необходимо временно сделать всех пользователей приложения доступными только для чтения. Чтобы отозвать привилегии у роли app_write
на изменение данных, используем инструкцию REVOKE
:
REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';
По сути, пользователь rw_user1
стал пользователем только для чтения. Это также происходит для любых других учетных записей, которым предоставлена роль app_write
. Это очень хорошая иллюстрируя, как использование ролей делает ненужным изменение привилегий для отдельных учетных записей.
Чтобы восстановить права измененной роли, просто повторно предоставим их:
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
Для удаления роли, необходимо использовать инструкцию DROP ROLE
:
DROP ROLE 'app_read', 'app_write';
Удаление роли аннулирует ее для каждой учетной записи, которой она была назначена.
Пример 1: Создается учетную запись, которая использует подключаемый модуль аутентификации по умолчанию и заданный пароль. Задаются дополнительные параметры, которые указывают действия пароля (пользователь должен был выбрать новый при первом подключении к серверу):
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
За именем учетной записи может следовать параметр аутентификации, который указывает подключаемый модуль аутентификации для учетной записи, учетные данные или и то, и другое.
Пример 2: Создается учетную запись, которая использует плагин аутентификации caching_sha2_password
и указывается пароль. Также указываются требования о смене пароля каждые 180 дней, и включается отслеживание неудачных попыток входа (три неверных ввода пароля приводят к временной блокировке учетной записи на два дня):
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'text_password' PASSWORD EXPIRE INTERVAL 180 DAY FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;
Пример 3: Создаются несколько учетных записей в ОДНОМ ЗАПРОСЕ:
Каждая инструкция IDENTIFIED WITH ... BY
в данном случае применяется только к учетной записи, указанной непосредственно перед ней. Остальные свойства применяются глобально ко всем учетным записям, указанным в одном запросе:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password', 'jeanne'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'text_password' REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60 PASSWORD HISTORY 5 ACCOUNT LOCK;
Пример 4: Создается учетная запись пользователя, поддерживающая многофакторную аутентификацию:
Начиная с MySQL 8.0.27 (узнать версию SELECT VERSION();
) , инструкция CREATE USER
поддерживает многофакторную аутентификацию (MFA), так что учетные записи могут иметь до трех методов аутентификации.
В следующем примере, плагин caching_sha2_password
определяется как метод проверки подлинности с первым фактором, а плагин authentication_ldap_sasl
- как метод проверки подлинности со вторым фактором:
CREATE USER 'u1'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'text_password' AND IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u1_ldap,ou=People,dc=example,dc=com';
В следующем примере используется три способа/метод аутентификации.
CREATE USER 'u1'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'text_password' AND IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u1_ldap,ou=People,dc=example,dc=com' AND IDENTIFIED WITH authentication_fido;