Создание нового пользователя и настройка прав в MySQL

Как работать с пользователями в MySQL: создавать и удалять учетные записи, предоставлять и отзывать привилегии, а также просматривать права доступа.

Введение

Есть несколько способов работы с БД MySQL: через графические phpMyAdmin, MySQL WorkBench и т.д. Поскольку работа с пользователями задача больше административная и нерегулярная, рассмотрим наиболее надежный способ — через консоль.

Для этого понадобится минимум — консольный клиент mysql. Запускать его можно на своей рабочей станции (mysql –host=<адрес сервера> [–user=<name>] [–password=<pass>] [database]) или через ssh на самом сервере (в случае ОС Linux).

Зачем нужны пользователи

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

Администратору в работе требуется создавать учетные записи «обычных» пользователей с ограниченным доступом к данным, определять права доступа, при необходимости — создавать дополнительных (привилегированных) суперпользователей. Также важно проводить аудит — просматривать выданные полномочия и корректировать их по мере необходимости.

Пользователи MySQL

Имя пользователя MySQL

В MySQL имя пользователя состоит из 2-х частей: имени пользователя (обязательно) и хоста (может быть опущена, тогда она означает ‘%’):

‘someuser’@’somehost’

Похоже на адрес электронной почты: первая часть — имя пользователя, вторая — место, откуда разрешено подключение. Хостовая часть может содержать DNS-имя, IP-адрес или символ подстановки ‘%’, который обозначает любой хост.

Например:

‘someuser’@’localhost’ — подключение только с самого сервера;
‘someuser’@’ip’ — подключение с конкретного IP-адреса;
‘someuser’@’%’ — подключение с любого хоста (при условии, что это также разрешено настройками сети и MySQL).

Важно, что ‘someuser’@’localhost’ и ‘someuser’@’%’ — это разные учетные записи MySQL, даже если имя пользователя у них одинаковое. Поэтому суперпользователь root также является не просто именем root, а учетной записью с определенной хостовой частью, например ‘root’@’localhost’.

Проверить существующие учетные записи можно командой:

SELECT host, user FROM mysql.user

Просмотр всех пользователей

Давайте проверим, какие пользователи есть в нашей БД. Выведем основную информацию о пользователях:

SELECT host, user FROM mysql.user;

Если список пользователей большой, его можно отфильтровать по хосту. Например, чтобы найти пользователей, подключение которых разрешено с localhost:

SELECT host, user, password FROM mysql.user WHERE host LIKE 'msk%';

Или использовать в конце модификатор \G, оптимизирующий вывод для отображения в консоли:

SELECT host, user FROM mysql.user\G

Подробная информация:

SELECT * FROM mysql.user\G

Создание нового пользователя MySQL

Новый пользователь в MySQL добавляется командой:

CREATE USER 'some_user'@'somehost.somedomain' IDENTIFIED BY 'some_password';

Разберем по частям:

CREATE USER — создает новую учётную запись MySQL;
‘some_user’@‘somehost.somedomain’ — имя пользователя + хост, с которого ему разрешено подключаться;
IDENTIFIED BY ‘some_password’ — задает пароль для этого пользователя.

Теперь давайте создадим нашего первого пользователя:

CREATE USER 'test'@'localhost' IDENTIFIED BY 'secret';

Полезная возможность — добавление комментария:

CREATE USER 'test'@'localhost' COMMENT 'My 1st user for app';

Проверим, что пользователь появился:

SELECT host, user
FROM mysql.user
WHERE user = 'test';

FLUSH PRIVILEGES

FLUSH PRIVILEGES заставляет MySQL перечитать таблицы привилегий.

В современных версиях MySQL эта команда не требуется после штатных операторов управления учетными записями и привилегиями, таких как CREATE USER, GRANT, REVOKE, SET PASSWORD и RENAME USER. Эти команды сами применяют изменения.

Но она нужна, если таблицы привилегий изменялись напрямую, например с помощью INSERT, UPDATE или DELETE.

Поэтому FLUSH PRIVILEGES не нужно выполнять после команд:

CREATE USER 'test'@'localhost' IDENTIFIED BY 'пароль';
GRANT SELECT ON my_db_cli.* TO 'test'@'localhost';

Удаление пользователя MySQL

Для удаления пользователя используется команда

DROP USER 'some_user'@'somehost.somedomain';

На нашем предыдущем примере:

DROP USER 'test'@'localhost';

И проверим результат:

SELECT host, user
FROM mysql.user
WHERE user = 'test';

Создание дополнительного суперпользователя

Это не лучшая практика, но бывают ситуации, когда у СУБД несколько хозяев и всем нужно быть суперпользователями. Если нескольким администраторам действительно нужен полный доступ к MySQL, это можно сделать с помощью GRANT.

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

ALL PRIVILEGES ON *.* предоставляет пользователю привилегии на все базы данных и таблицы сервера.
WITH GRANT OPTION позволяет пользователю самостоятельно выдавать предоставленные ему привилегии другим пользователям.

Хостовая часть ‘admin’@’localhost’ означает, что подключение разрешено только с самого сервера. Если использовать ‘admin’@’%’, подключение будет разрешено с любого хоста, если это также допускают настройки сети и MySQL. Такой вариант требует особой осторожности.

Отзыв полномочий у пользователя

Команда отзыва привилегий функционально обратна GRANT, “TO” заменяется на “FROM”:

REVOKE SELECT ON `somedb`.* FROM 'someuser'@'somehost';
REVOKE ALL PRIVILEGES ON `somedb`.* FROM 'someuser'@'somehost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'somehost';

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

Вторая отзывает все привилегии для указанной базы данных.

Третья отзывает все глобальные привилегии пользователя, а также право выдавать привилегии другим пользователям (GRANT OPTION).

Важно: REVOKE не удаляет пользователя. Учетная запись продолжает существовать, но лишается отозванных привилегий. Для полного удаления учетной записи используется команда DROP USER.

После REVOKE выполнять FLUSH PRIVILEGES не нужно: MySQL применяет изменения автоматически.

Смена пароля

Для изменения пароля учетной записи пользователя применяется команда ALTER USER:

ALTER USER 'test_user'@'localhost' IDENTIFIED BY 'new_password';

Предоставление доступа пользователю MySQL

Доступ предоставляется командой:

GRANT SELECT ON `some_db`.* TO 'some_user'@'somehost.somedomain';

Она означает:

GRANT SELECT — дать право читать данные;
`some_db`.* — во всех таблицах базы some_db;
‘some_user’@’somehost.somedomain’ — конкретному пользователю с конкретным хостом.

Допустим, наше ПО использует базу данных test_db. Для его работы мы создали пользователя test_user, а FQDN хоста, где работает ПО — наш локальный хост (localhost). Наше приложение только считывает данные из БД — выполняет SELECT.

Создадим пользователя и БД (часто БД называют схемой, в терминах MySQL):

CREATE SCHEMA test_DB;
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'secret';

Команда для предоставления доступа будет выглядеть так:

Наследование привилегий

В предыдущем примере наш пользователь сможет только читать данные из базы test_db, но передать свои права другому пользователю не сможет. Используя GRANT OPTION, мы можем позволить ему сделать это. Тогда пользователь получит возможность передавать другим то, что разрешено ему самому.

Создаем пользователя:

CREATE USER 'some_user'@'somehost' IDENTIFIED BY ‘secret';

И выдаем ему права:

GRANT SELECT, INSERT, UPDATE, DELETE
ON `some_db`.* TO 'some_user'@'somehost'
WITH GRANT OPTION;

Из соображений безопасности использовать GRANT OPTION небезопасно! В случае компрометации учетной записи злоумышленник сможет не только получить доступ к данным, но и сделать закладку в виде копии учетной записи.

Доступ к таблице

Примеры выше дают доступ ко всей БД. Часто доступ должен быть ограничен строго определенным набором таблиц:

GRANT SELECT
ON `test_db`.`table_users`
TO 'test_user'@'localhost';

Доступ к столбцу

Предоставляется перечислением столбцов:

GRANT SELECT (id, user_name),
      UPDATE (user_name)
ON `test_db`.`table_users`
TO 'test_user'@'localhost';

Она означает:

SELECT (id, user_name) — пользователь может читать только столбцы id и user_name;
UPDATE (user_name) — пользователь может изменять только столбец user_name;
все это относится только к таблице test_db.table_users.

Просмотр привилегий пользователей MySQL

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

Проверка текущих полномочий пользователя

Нам пригодится команда:

SHOW GRANTS FOR 'test_user'@'localhost';

Пример:

+-----------------------------------------------+
| Grants for test_user@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`localhost` |
+-----------------------------------------------+
1 row in set (0.00 sec)

Проверка полномочий к данным

Через read-only БД information_schema доступно множество метаданных — системную информацию о MySQL.

Информация о привилегиях на уровне схем, таблиц и столбцов доступна в представлениях SCHEMA_PRIVILEGES, TABLE_PRIVILEGES и COLUMN_PRIVILEGES.

SELECT * FROM information_schema.schema_privileges;
SELECT * FROM information_schema.table_privileges;
SELECT * FROM information_schema.column_privileges;

Можно отфильтровать информацию по конкретному пользователю:

SELECT *
FROM information_schema.column_privileges
WHERE GRANTEE = "'test_user'@'localhost'";

Для простой проверки всех привилегий конкретного пользователя удобнее использовать:

SHOW GRANTS FOR 'test_user'@'localhost';

information_schema особенно полезна, когда требуется анализировать привилегии с помощью SQL-запросов.

Просмотр привилегий через системную БД mysql

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

Информация о пользователях:

SELECT * FROM mysql.user;

Привилегии на уровне базы данных:

SELECT * FROM mysql.db;

Права, назначенные на таблицы:

SELECT * FROM mysql.tables_priv;

И на столбцы:

 SELECT * FROM mysql.columns_priv;

Просмотр глобальных привилегий

Глобальные полномочия смотрим здесь:

SELECT * FROM information_schema.user_privileges;

Заключение

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

Шпаргалка по командам MySQL

CREATE USER → создать пользователя;
GRANT → выдать права;
SHOW GRANTS → посмотреть права;
REVOKE → отозвать права;
ALTER USER → изменить пароль;
DROP USER → удалить.

При выдаче прав избегайте избыточности. Права не нужно выдавать с запасом, часто выполнение GRANT ALL PRIVILEGES ON *.* TO ‘myUser’@’%’ — не лучший выход. Другой важный момент, часто упускаемый из виду новичками, — наличие в имени хостовой части. Игнорирование хоста может привести к ошибкам.

Всем высоких скоростей, безаварийной работы и долгого аптайма!

Базы данных