Сообщить об ошибке.

MySQL: Пользователь CREATE/ALTER USER и права GRANT/ROLE

Содержание:


Создание учетной записи пользователя.

Инструкция 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 регистр не учитывается.
  • Имя пользователя и имя хоста не нужно заключать в кавычки, если они допустимы как MySQL идентификаторы. Если строка user_name содержит специальные символы (например, пробел или -), или строка host_name содержит специальные символы или подстановочные знаки (например, . или %), то необходимо использовать кавычки (обратные `, одинарные ' или двойные "). Например, в имени учетной записи 'test-user'@'%.com' как имя пользователя, так и часть имени хоста должны быть заключены в кавычки.
  • Если имя пользователя и имя хоста указаны в кавычках, то они должны быть указаны отдельно. Например, 'me'@'localhost', а не 'me@localhost'. Последнее на самом деле эквивалентно 'me@localhost'@'%'.

Правила составления имени хоста учетных записей.

  • Значение хоста может быть именем хоста или IP-адресом (IPv4 или IPv6). Строка 'localhost' указывает на локальный хост. IP-адрес '127.0.0.1' указывает петлю интерфейса IPv4. IP-адрес '::1' указывает петлю интерфейса IPv6.
  • В значениях имени хоста или IP-адреса разрешены подстановочные знаки % и _ . Они имеют то же значение, что и операциях сопоставления 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-адресам, но не именам хостов.
  • Для адресов IPv4, можно указать сетевую маску, чтобы указать, сколько битов адреса использовать для номера сети. Для адресов IPv6 обозначение сетевой маски использовать нельзя.

Синтаксис 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 различаются в зависимости от контекста, в котором они применяются, и на разных уровнях работы:

  • Административные привилегии позволяют пользователям управлять работой сервера 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.

Роль 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, который используется для назначения привилегий (его нет для назначения ролей). Так как синтаксисы различны, нельзя одновременно назначать привилегии и роли в одном операторе.

Активация созданной роли MySQL.

Созданная роль изначально заблокирована и не может использоваться. Чтобы указать, какие роли должны становиться активными при подключении пользователя к серверу, необходимо вызвать инструкцию 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';

Удаление роли MySQL.

Для удаления роли, необходимо использовать инструкцию 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 в данном случае применяется только к учетной записи, указанной непосредственно перед ней. Остальные свойства применяются глобально ко всем учетным записям, указанным в одном запросе:

  • Соединения должны выполняться с использованием действительного сертификата X.509.
  • Допускается до 60 запросов в час.
  • При смене пароля нельзя повторно использовать ни один из пяти последних паролей.
  • Создаваемые учетные записи изначально заблокированы, поэтому они не могут использоваться до тех пор, пока администратор не разблокирует их.
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;