Пользователи и привилегии MySQL: CREATE USER, GRANT и роли

Учётная запись MySQL - это не имя пользователя. Это имя пользователя и шаблон хоста вместе: 'app'@'%' и 'app'@'localhost' - две разные учётные записи со своими паролями и своими привилегиями. Как только это становится понятно, большая часть системы прав MySQL оказывается простой: вы создаёте учётную запись через CREATE USER, выдаёте ей привилегии через GRANT на глобальном уровне, уровне базы данных, таблицы или столбца, объединяете типовые наборы привилегий в роли и проверяете результат через SHOW GRANTS. Привычка, которую стоит выработать: давать каждому приложению собственную учётную запись с правами на его базу данных и ничего больше, а root оставлять для администрирования.

Это руководство описывает MySQL 8.4 LTS. Почти всё здесь в 8.0 устроено так же; различия отмечены там, где они важны. Если вы пришли с MySQL 5.7, старые скрипты ломают два изменения: GRANT больше не создаёт пользователей, а плагин паролей по умолчанию - caching_sha2_password.

Учётная запись - это пользователь плюс хост#

Каждая учётная запись хранится в mysql.user со столбцами User и Host. Когда клиент подключается, MySQL смотрит, с какого адреса он пришёл и какое имя назвал, и выбирает единственную наиболее конкретную подходящую строку. Привилегии берутся из этой строки и ниоткуда больше.

SELECT user, host, plugin, account_locked, password_last_changedFROM mysql.userORDER BY user, host;

При сопоставлении строки сначала сортируются по конкретности хоста - буквальные адреса и имена раньше шаблонов, шаблоны раньше голого %, - и только потом по имени пользователя. Отсюда классическая ловушка: некоторые старые установки поставляются с анонимной учётной записью ''@'localhost'. Пользователь, подключающийся как app с localhost, совпадает с анонимной строкой раньше, чем с 'app'@'%', потому что хост конкретнее, и в итоге не имеет ни одной из выданных ему привилегий. SELECT CURRENT_USER(); после входа покажет, с какой строкой вы на самом деле совпали. Если там @localhost с пустым пользователем, удалите анонимную учётную запись.

Когда адрес клиента меняется - домашний интернет, ноутбук, платформа приложений без фиксированного исходящего адреса, - практичный выбор - %, а защищают учётную запись пароль и TLS. Когда адрес фиксирован, его использование - дешёвый дополнительный слой защиты.

Создание пользователей#

CREATE USER 'app'@'%'  IDENTIFIED BY 'a-long-generated-password'  REQUIRE SSL  PASSWORD EXPIRE NEVER;

Учётная запись создаётся с плагином аутентификации по умолчанию, caching_sha2_password. В MySQL 8.4 значение по умолчанию задаёт authentication_policy; старую переменную default_authentication_plugin удалили. mysql_native_password существует, но отключён, если сервер не запущен с mysql_native_password=ON, поэтому IDENTIFIED WITH mysql_native_password на стандартном сервере 8.4 завершается ошибкой.

MySQL может сгенерировать пароль за вас и показать его один раз:

CREATE USER 'report'@'%' IDENTIFIED BY RANDOM PASSWORD;

+--------+------+----------------------+-------------+| user   | host | generated password   | auth_factor |+--------+------+----------------------+-------------+| report | %    | 7Ba(Xr;lWk.Vx4Tq,Z%3 |           1 |+--------+------+----------------------+-------------+

Длина берётся из generated_random_password_length (по умолчанию 20). Скопируйте пароль сразу; он не хранится нигде, откуда его можно прочитать обратно.

Другие полезные предложения:

• IF NOT EXISTS делает оператор безопасным для повторного запуска в скрипте подготовки.
• REQUIRE SSL запрещает этой учётной записи незашифрованные соединения. REQUIRE X509 требует клиентский сертификат.
• WITH MAX_USER_CONNECTIONS 20 ограничивает число одновременных сессий учётной записи, и тогда один вышедший из-под контроля сервис не займёт все слоты под max_connections.
• ACCOUNT LOCK создаёт учётную запись отключённой; это полезно для учётной записи-definer, которая владеет представлениями и процедурами, но никогда не должна входить.
• COMMENT 'billing service' сохраняет заметку в mysql.user (8.0.21 и новее), и через два года вы будете этому рады.

Чтобы изменить существующую учётную запись, используйте ALTER USER; чтобы переименовать - RENAME USER 'app'@'%' TO 'shop'@'%'; чтобы удалить - DROP USER IF EXISTS 'app'@'%'. Удаление пользователя не удаляет созданные им объекты, но представления и процедуры, у которых DEFINER был этот пользователь, перестают работать, пока definer снова не появится.

GRANT и REVOKE#

Привилегии выдаются на определённом уровне, и уровень задаёт предложение ON:

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';          -- databaseGRANT SELECT ON appdb.orders TO 'report'@'%';                          -- tableGRANT SELECT (id, email, created_at) ON appdb.users TO 'support'@'%';  -- columnsGRANT EXECUTE ON PROCEDURE appdb.close_month TO 'cron'@'%';            -- routineGRANT PROCESS ON *.* TO 'monitor'@'%';                                 -- global

Начиная с MySQL 8.0 GRANT делает ровно одну вещь. Привычка времён 5.7 GRANT ALL ON db.* TO 'u'@'%' IDENTIFIED BY 'pw' - создать пользователя и задать пароль одним махом - теперь синтаксическая ошибка. Сначала создайте пользователя, потом выдавайте права.

REVOKE зеркалит GRANT и должен называть тот же уровень. Отзыв SELECT ON appdb.orders у того, у кого есть SELECT ON appdb.*, ничего не делает, потому что у него никогда не было права на уровне таблицы, которое можно отозвать. Если нужно «всё в этой базе, кроме одной таблицы», выдавайте права по таблицам или включите partial_revokes (по умолчанию выключен), который позволяет вычесть базу данных из глобального права - и только из глобального.

Какие привилегии на самом деле нужны приложению

Большинство фреймворков запускают миграции с теми же учётными данными, что и приложение, поэтому приложение в итоге получает ALL PRIVILEGES ON appdb.*. Для небольшого проекта это приемлемо - права всё равно ограничены одной базой данных, - но надёжнее две учётные записи: app с четырьмя привилегиями на данные и app_migrate с привилегиями на схему, которую использует только шаг deploy. Тогда SQL-инъекция через приложение не сможет выполнить DROP TABLE.

Чего у приложения никогда не должно быть: ничего ON *.*, GRANT OPTION, FILE (читает и пишет файлы на диске сервера), SUPER или его динамических замен, PROCESS (видит запросы всех сессий, в том числе чужих приложений), CREATE USER или SHUTDOWN.

Динамические привилегии

MySQL 8 разделил старый всеобъемлющий SUPER на динамические привилегии с именами вроде SYSTEM_VARIABLES_ADMIN (изменение глобальных переменных), CONNECTION_ADMIN (завершение чужих сессий, подключение сверх max_connections), BINLOG_ADMIN, BACKUP_ADMIN и REPLICATION_SLAVE_ADMIN. MySQL 8.4 добавил FLUSH_PRIVILEGES, OPTIMIZE_LOCAL_TABLE, TRANSACTION_GTID_TAG, а также SET_ANY_DEFINER с ALLOW_NONEXISTENT_DEFINER вместо удалённого SET_USER_ID. Они выдаются как любые другие привилегии, всегда на глобальном уровне:

GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'dba'@'%';

SUPER всё ещё существует, но объявлен устаревшим. Если старый инструмент на нём настаивает, выясните, какая динамическая привилегия ему действительно нужна.

Как проверить, что может учётная запись#

SHOW GRANTS FOR 'app'@'%';

+------------------------------------------------------------------------+| Grants for app@%                                                       |+------------------------------------------------------------------------+| GRANT USAGE ON *.* TO `app`@`%`                                        || GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `app`@`%`         |+------------------------------------------------------------------------+

USAGE означает «никаких привилегий» - эта строка есть у каждой учётной записи, и именно так выглядит учётная запись, которой ничего не выдано. SHOW GRANTS без FOR показывает вашу текущую сессию, включая активные роли. SHOW CREATE USER 'app'@'%' выводит определение учётной записи (плагин, требование TLS, лимиты, состояние блокировки) с паролем в виде хэша, и так учётные записи переносят между серверами - этому посвящена статья перенос MySQL на новый хост.

Для более широкого обзора таблицы information_schema USER_PRIVILEGES, SCHEMA_PRIVILEGES, TABLE_PRIVILEGES и COLUMN_PRIVILEGES перечисляют права по уровням, а mysql.db напрямую хранит права уровня базы данных.

Роли#

Роль - это именованный набор привилегий, который можно выдавать учётным записям. Роли появились в MySQL 8.0 и под капотом являются обычными учётными записями (они видны в mysql.user, заблокированные и без пароля), поэтому у их имён тоже есть часть с хостом; по умолчанию это %.

CREATE ROLE 'appdb_read', 'appdb_write', 'appdb_ddl';GRANT SELECT ON appdb.* TO 'appdb_read';GRANT INSERT, UPDATE, DELETE ON appdb.* TO 'appdb_write';GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON appdb.* TO 'appdb_ddl';GRANT 'appdb_read', 'appdb_write' TO 'app'@'%';GRANT 'appdb_read' TO 'report'@'%';GRANT 'appdb_read', 'appdb_write', 'appdb_ddl' TO 'app_migrate'@'%';SET DEFAULT ROLE ALL TO 'app'@'%', 'report'@'%', 'app_migrate'@'%';

Последнюю строку забывают все. Выданная роль не активна в сессии, пока её не активируют - либо через SET ROLE внутри сессии, либо назначив ролью по умолчанию. Пропустите SET DEFAULT ROLE, и учётная запись войдёт только с USAGE, каждый запрос упадёт с «command denied», а SHOW GRANTS FOR 'app'@'%' при этом сбивающим с толку образом покажет роль как выданную. Альтернатива - серверная настройка activate_all_roles_on_login=ON, по умолчанию выключенная.

-- What would this account be able to do with its roles active?SHOW GRANTS FOR 'app'@'%' USING 'appdb_read', 'appdb_write';-- Which roles are active in my session right now?SELECT CURRENT_ROLE();

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

Пароли, ротация и блокировка#

В MySQL 8 есть ожидаемые политики, которые задаются для учётной записи или как серверные значения по умолчанию:

ALTER USER 'support'@'%'  PASSWORD EXPIRE INTERVAL 180 DAY  PASSWORD HISTORY 5  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

FAILED_LOGIN_ATTEMPTS вместе с PASSWORD_LOCK_TIME (в днях или UNBOUNDED) временно блокирует учётную запись после нескольких неудач подряд; это доступно с 8.0.19. Для учётных записей людей это разумная защита. Подумайте дважды, прежде чем ставить её на учётную запись приложения: неправильно настроенный deploy, долбящий неверным паролем, заблокирует и правильно настроенные экземпляры.

Срок действия пароля подходит людям и мешает сервисам. С просроченным паролем клиент может войти только в режиме песочницы, где не может делать ничего, кроме смены пароля, а большинство драйверов показывают это как загадочную ошибку в три часа ночи. Для сервисных учётных записей меняйте пароли по своему графику с PASSWORD EXPIRE NEVER.

Ротация без простоя использует двойные пароли, доступные с 8.0.14:

-- 1. Add a new password; the old one keeps workingALTER USER 'app'@'%' IDENTIFIED BY 'new-password' RETAIN CURRENT PASSWORD;-- 2. Roll the new password out to every instance of the app-- 3. Retire the old oneALTER USER 'app'@'%' DISCARD OLD PASSWORD;

Правила стойкости паролей задаёт компонент validate_password, который одни пакеты устанавливают, а другие нет. SHOW VARIABLES LIKE 'validate_password%'; ничего не возвращает, если его нет. Сгенерированные пароли делают его почти неважным.

Минимальные привилегии для типичного приложения#

Собираем всё вместе для веб-приложения с ночным заданием бэкапа и аналитическим инструментом только для чтения:

Список учётной записи для бэкапа - это то, что нужно mysqldump для согласованного дампа одной базы данных с --single-transaction --no-tablespaces; дамп информации о табличных пространствах требует глобальной привилегии PROCESS, и --no-tablespaces позволяет без неё обойтись.

На RE:NODE сервер MySQL приходит с тремя сгенерированными учётными данными: паролем root, базой данных приложения и пользователем приложения для неё. Это сразу закрывает первые две строки таблицы. С root вы за минуту создадите остальное операторами выше, а любой, кого вы добавите к серверу в панели как субпользователя, получает права в панели, которые отделены от учётных записей базы данных, - сторону панели описывает статья субпользователи и минимальные привилегии.

Definer, представления и хранимые процедуры#

Представления, хранимые процедуры, функции, триггеры и события по умолчанию выполняются с привилегиями своего DEFINER (SQL SECURITY DEFINER). Два следствия:

• Представление позволяет читать через него даже тому, у кого нет прав на исходную таблицу, и это удобно, чтобы открыть учётной записи для отчётов безопасное подмножество столбцов.
• Когда вы переносите базу данных на другой сервер, каждый объект по-прежнему называет своего исходного definer, часто root@localhost. Если такой учётной записи на новом сервере нет, объекты падают с «The user specified as a definer does not exist».

Чтобы создать объект с definer, отличным от вас самих, в 8.4 нужна SET_ANY_DEFINER (SET_USER_ID или SUPER в 8.0), а для объекта, чей definer не существует, нужна ещё и ALLOW_NONEXISTENT_DEFINER. У пользователя приложения на хостинге их не будет, поэтому дампы, восстанавливаемые обычным пользователем, часто падают на предложениях definer - решение в том, чтобы удалить или переписать их перед импортом либо импортировать под root. SQL SECURITY INVOKER обходит проблему для процедур, которые должны выполняться с правами вызывающего.

Ошибки прав и что они означают#

Коды ошибок конкретны, и каждый указывает на своё решение.

ERROR 1142 (42000): SELECT command denied to user 'app'@'198.51.100.7' for table 'invoices'

Учётная запись совпала и вошла, но у неё нет SELECT на эту таблицу. Проверьте SHOW GRANTS для учётной записи, указанной в сообщении, - а не той, которую вы считаете своей, - и убедитесь, что роль с этой привилегией активна.

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER,SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

Оператору нужна глобальная или динамическая привилегия. Типичные причины - дамп, который задаёт @@GLOBAL.GTID_PURGED, SET GLOBAL в скрипте импорта или предложение DEFINER, называющее кого-то другого. В сообщении перечислены привилегии, которых было бы достаточно; обычно правильнее убрать оператор из скрипта, чем выдавать привилегию.

ERROR 1396 (HY000): Operation CREATE USER failed for 'app'@'%'

Учётная запись уже существует (или, для DROP USER, не существует). Используйте IF NOT EXISTS и IF EXISTS в скриптах, которые могут запуститься дважды.

ERROR 1410 (42000): You are not allowed to create a user with GRANT

GRANT ... IDENTIFIED BY в стиле 5.7 на сервере 8.x или GRANT учётной записи, которой ещё нет. Сначала выполните CREATE USER.

ERROR 1045 (28000): Access denied for user 'app'@'203.0.113.9' (using password: YES)

Аутентификация не прошла: неверный пароль или нет учётной записи, чей шаблон хоста совпадает с этим адресом. Если учётная запись использует mysql_native_password на сервере 8.4, где этот плагин отключён, вход тоже падает здесь. Причины на стороне подключения подробно разобраны в статье удалённые подключения к MySQL.

FAQ#

Нужен ли FLUSH PRIVILEGES после GRANT?

Нет. CREATE USER, GRANT, REVOKE, ALTER USER и DROP USER сразу обновляют кэш привилегий в памяти. FLUSH PRIVILEGES нужен только после прямого редактирования таблиц привилегий, чего делать не следует. Вреда от него нет, но его присутствие в скрипте говорит о том, что скрипт скопирован откуда-то из старых времён.

Почему у пользователя есть доступ с одной машины, но нет с другой?

Потому что учётная запись - это пользователь и шаблон хоста, а адрес другой машины с ним не совпадает. Сообщение об ошибке показывает адрес, который увидел MySQL, - 'app'@'198.51.100.7', - так что сравните его с SELECT user, host FROM mysql.user. Создайте учётную запись для этого хоста или используйте %.

Как дать пользователю доступ ко всем базам данных, чьё имя начинается с префикса?

Исторически - через право на базу данных с подстановочным знаком, заключая имя базы client\_% в обратные кавычки и экранируя подчёркивание, чтобы оно само не было подстановочным знаком. В MySQL 8.2 подстановочные знаки в правах на базы данных объявили устаревшими, и ожидается, что в будущем релизе они станут буквальными. Выдавайте права по каждой базе данных - так в любом случае нагляднее.

Должно ли приложение использовать root?

Нет. Root может читать любую базу данных, менять настройки сервера, создавать пользователей и удалять что угодно. Если приложение скомпрометировано, root превращает плохой день в полную потерю. Используйте учётную запись с правами на собственную базу данных приложения, а root оставьте для обслуживания.

Чем роль отличается от пользователя в MySQL?

Под капотом почти ничем: роль хранится как заблокированная учётная запись без пароля. Разница в использовании. Роли выдаются учётным записям и должны быть активированы (ролью по умолчанию или через SET ROLE), прежде чем их привилегии начнут действовать. Входят же в систему учётные записи.

Можно ли посмотреть, какие привилегии даёт роль?

Да: SHOW GRANTS FOR 'appdb_read'; перечисляет собственные привилегии роли, а SHOW GRANTS FOR 'app'@'%' USING 'appdb_read'; показывает, что получает учётная запись с активной ролью. Подробнее об укреплении учётных записей - в чек-листе безопасности базы данных.