Создание пользователей MySQL/MariaDB и предоставление прав доступа

Обновлено: 31.01.2023

Опубликовано: 01.08.2016

Тематические термины: MySQL, MariaDB.

Учетные записи в СУБД MySQL/MariaDB представлены в виде связки <имя пользователя>@<удаленный хост, с которого можно подключаться>. Это может вызвать путаницу, поэтому необходимо быть внимательнее, например, учетные записи root@localhost и root@192.168.0.15 — разные.

В инструкции мы рассмотрим базовые аспекты работы с пользователями и рассмотрим некоторые примеры.

Создать пользователя
  Командой CREATE USER
Командой GRANT
Посмотреть пользователей
Сменить пароль
  MySQL
  MariaDB
  Суперпользователю
Сброс пароля для root
Сетевой доступ
Примеры
  Особые права
  Удаленное подключение
  Права на резервное копирование
  С правами подключения к определенной таблице
  Удаление пользователя
Права на управление пользователями
Возможные ошибки

Создание пользователя и выдача прав

Рассмотрим два варианта создания учетных записей в СУБД MySQL/MariaDB.

1. С помощью команды CREATE USER.

Данный метод является универсальным. Он позволяет создать пользователя в системе без каких либо прав. После права назначаются командой GRANT.

Пример создания учетной записи:

> CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password';

* в данном примере будет создана учетная запись dbuser@localhost (доступ разрешен только с локального компьютера) и паролем password.

После можно задать права командой:

> GRANT <тип привилегий> ON <объект> TO <пользователь> <дополнительные опции>;

Например:

> GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'localhost';

* где:

ALL PRIVILEGES — предоставляет полные права на использование данных.
*.* — права предоставляются на все базы и все таблицы.
dbuser — имя учетной записи.
localhost — доступ для учетной записи будет предоставлен только с локального компьютера.

2. С помощью команды GRANT.

Данный метод позволяет одной командой сразу и создать пользователя, и дать ему права. Но в более новых версиях MariaDB/MySQL, она возвращает ошибку — разработчики запретили ее использование и сначала требуется создать пользователя (с помощью CREATE USER).

Синтаксис:

> GRANT <тип привилегий> ON <объект> TO <пользователь> [IDENTIFIED BY <пароль>] <дополнительные опции>;

Например, эта команда предоставляет права доступа пользователю и, если его не существует, создает его:

> GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Описание команды:

* где:

ALL PRIVILEGES — предоставляет полные права на использование данных.
*.* — права предоставляются на все базы и все таблицы.
dbuser — имя учетной записи.
localhost — доступ для учетной записи будет предоставлен только с локального компьютера.
password — пароль, который будет задан пользователю.
WITH GRANT OPTION — будут предоставлены дополнительные права на изменение структуры баз и таблиц.

Посмотреть существующих пользователей и их привилегии

Список пользователей:

> SELECT user, host FROM mysql.user;

Список привилегий (для кажого пользователя смотряться отдельно):

> SHOW GRANTS FOR 'root'@'localhost';

* где 'root'@'localhost' — учетная запись, для которой смотрим привилегии; если упустить FOR, команда выдаст результат для пользователя, под которым выполнено подключение к СУБД.

Смена пароля

Команды отличаются в зависимости от версии СУБД.

MySQL

Версия 5.7.6 и более современная:

> ALTER USER 'root'@'localhost' IDENTIFIED BY 'New_Password';

Версия 5.7.5 и древнее:

> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('New_Password');

* посмотреть версию СУБД можно командой mysql -V.

MariaDB

В MariaDB команда для смены пароля одна, независимо от версии:

> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('New_Password');

* в данном примере будет задан пароль New_Password для пользователя root.

Смена пароля пользователю root после установки

Для старых версий СУБД и новых процедуры различаются.

MySQL < 8 или MariaDB

Первый раз пароль задается из командной строки операционной системы (для MySQL/MariaDB):

> mysqladmin -u root password

Для смены пароля root необходимо выполнить команду в оболочке mysql по инструкции, описанной выше.

MySQL 8 и выше

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

grep 'password' /var/log/mysqld.log

Мы увидим что-то на подобие:

2021-08-16T20:14:13.173577Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: oi25?wznro%W

* где oi25?wznro%W — пароль для пользователя root.

Заходим в оболочку sql с помощью данного пароля:

mysql -p

и меняем его.

Сброс пароля для root

При необходимости, мы можем сбросить пароль для суперпользователя mysql. Для этого необходимо запустить сервер баз данных в безопасном режиме и ввести команду на смену пароля.

Для начала необходимо остановить сервис:

systemctl stop mysql

... или:

systemctl stop mariadb

Создаем каталог /var/run/mysqld и зададим для него нужные права:

mkdir -p /var/run/mysqld

chown mysql:mysql /var/run/mysqld

* в некоторых системах данного каталога может не быть, однако, для безопасного режима mysql он необходим.

Запускаем в безопасном режиме СУБД:

mysqld_safe --skip-grant-tables &

Запускаем оболочку sql — система не потребует пароль:

mysql -uroot

Выполняем запрос FLUSH PRIVILEGES:

> FLUSH PRIVILEGES;

Меняем пароль вышеописанным способом.

Выходим из оболочки:

> quit

Уничтожаем процесс, который запустили ранее:

kill %1

После, запускаем mysql в нормальном режиме:

systemctl start mysql

... или:

systemctl start mariadb

Доступ по сети

Для успешного подключения к СУБД по сети нам нужно выполнить 3 условия:

Создать правильную учетную запись.
Сервер баз данных должен слушать сетевые запросы.
Правила брандмауэра не должны блокировать доступ по порту mysql (по умолчанию 3306).

Рассмотрим выполнение данных условий по-очереди.

Создание учетной записи

Как было сказано в начале, имя учетной записи состоит из частей <имя пользователя> + @ + <удаленный хост, с которого можно подключаться>.

Таким образом, если нам нужно разрешить подключение пользователю dbuser с компьютера 192.168.0.15, создаем:

> CREATE USER 'dbuser'@'192.168.0.15' IDENTIFIED BY 'password';

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

Для этого используем вместо IP знак %:

> CREATE USER 'dbuser'@'%' IDENTIFIED BY 'password';

Также мы можем разрешить подключение для подсети, заменив октет знаком %, например:

> CREATE USER 'dbuser'@'192.168.0.%' IDENTIFIED BY 'password';

* в этом случае мы получим доступ с любого компьютера в сети 192.168.0.0/24.

Не забываем данному пользователю предоставить привилении:

> GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'192.168.0.15';

Настройка сервера для работы по сети

Проверить, на каком сетевом интерфейсе слушает сервер можно командой:

ss -tunlp | grep 3306

Если мы увидели, что запросы выполняются только на локальных адресах:

tcp LISTEN 0 50 127.0.0.1:3306 ...

* обратите внимание, что наш сервер слушает на адресе 127.0.0.1, что означает обработку только локальных запросов.

... нам нужно настроить сервер.

Открываем конфигурационный файл. Раньше он находился по пути /etc/my.cnf, но теперь данный файл не содержит настроек, а просто подгружает конфигурационные файлы из каталога /etc/my.cnf.d. В зависимости от СУБД и ее версии, файлы настройки могут находиться в разных местах последнего, например:

vi /etc/my.cnf.d/server.cnf

vi /etc/my.cnf.d/mariadb-server.cnf.

Откроем его и задачим значение для опции bind-address:

bind-address = 0.0.0.0

* в данном примере мы разрешаем серверу слушать на любом адресе (0.0.0.0). Если нужно ограничить конкретным, то просто впишите его.

Перезапускаем сервис

systemctl restart mysql

Настройка брандмауэра

В зависимости от утилиты управления правилами фаервола, наши действия будут отличаться.

а) Для iptables (как правило, в системах на основе deb):

iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

Чтобы сохранить правила, можно использовать iptables-persistent:

apt install iptables-persistent

netfilter-persistent save

б) Для firewalld (как правило, в системах на основе rpm):

firewall-cmd --permanent --add-port=3306/tcp

firewall-cmd --reload

Проверка

Можно пробовать подключаться. Пример команды для подключения клиентом mysql к удаленному серверу:

mysql -udbuser -p -h192.168.0.10

* в данном примере мы попробуем подключиться к серверу 192.168.0.10 пользователем dbuser. Система выведет запрос на ввод пароля.
** стоит напомнить, что в нашем примере подключиться можно будет пользователем dbuser с компьютера 192.168.0.15.

Примеры работы с пользователями

Рассмотрим часто встречаемые операции по работе с пользователями сервера баз данных.

1. Особые права

Предоставление особых прав пользователю:

> GRANT SELECT, UPDATE ON base1.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';

* права на выборку и обновление данных во всех таблицах базы base1 для пользователя dbuser
** список всех возможных прав: all privileges, alter, create, create temporary tables, delete, drop, execute, file, index, insert, lock tables, process, references, reload, replication client, replication slave, select, show databases, shutdown, super, update, usage

2. Удаленное подключение

Разрешение на удаленное подключение и использование базы MySQL:

> GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'192.168.0.55' IDENTIFIED BY 'password';

* предоставит права пользователю dbuser, который будет подключаться с компьютера с IP-адресом 192.168.0.55.

Мы можем сделать менее жесткое ограничение, разрешив доступ из подсети (особенно удобно при использовании динамических адресов):

> GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'192.168.0.%' IDENTIFIED BY 'password';

* в данном примере мы разрешим подключение для любого узла из сети 192.168.0.0/24.

Или мы можем, вовсе, разрешить водключение с любого хоста (не безопасно):

> GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'%' IDENTIFIED BY 'password';

3. Права на резервное копирование

Создание учетной записи MySQL с правами создания резервных копий:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'backup';

4. Права доступа к определенной таблице

По сути, это такое же предоставление прав, но с указанием конкретной таблицы после базы:

> GRANT ALL PRIVILEGES ON base1.table1 TO 'dbuser'@'localhost' IDENTIFIED BY 'password';

* в данном примере предоставлены все права на таблицу table1 в базе base1.

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

Нам может также понадобиться удалить ранее созданного пользователя. Это делается в два этапа:

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dbuser'@'localhost';

> DROP USER 'dbuser'@'localhost';

* в данном примере мы первой командой отняли все права у пользователя dbuser (localhost) и второй — удалили его.

6. Права на создание других пользователей

Чтобы наш пользователь мог создавать других пользоватлей, задаем права:

GRANT CREATE USER ON *.* TO 'creator'@'localhost';

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

Если нужно, чтобы из под данной учетной записи можно было также назначать права, добавим:

GRANT CREATE USER ON *.* TO 'creator'@'localhost' WITH GRANT OPTION;

* обратите внимание, что мы просто добавили WITH GRANT OPTION.

Возможные ошибки

1. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Причина: в новых версиях по умолчанию активированы политики на проверку сложности пароля. Их список можно посмотреть командой:

> SHOW VARIABLES LIKE 'validate_password%';

Вывод команды будет, примерно, следующим:

+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+

* где:

validate_password_check_user_name — пароль не должен совпадать с именем пользователя.
validate_password_dictionary_file — использовать специальный файл со словарем запрещенных паролей.
validate_password_length — минимальная длина пароля.
validate_password_mixed_case_count — сколько, как минимум, должно быть символов в малой и большой раскладках.
validate_password_number_count — какое минимальное количество цифр использовать в пароле.
validate_password_policy — позволяет задать определенный набор правил. Доступны значения LOW (или 0), MEDIUM (1), STRONG (2).
validate_password_special_char_count — минимальное количество специальных символов (например, # или !).

Решение:

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

> SET GLOBAL validate_password_number_count = 0;

2. ERROR 1728 (HY000): Cannot load from mysql.tables_priv. The table is probably corrupted

Причина: система считает, что таблица tables_priv в базе mysql неисправна.

Решение: чаще всего, ошибка возникает при переносе баз с одного сервера в другой. Если версии СУБД разные, таблица tables_priv может работать со сбоями. Для исправления необходимо выполнить команду mysql_upgrade — она проверяет все таблицы на совместимость с текущей версией MySQL/MariaDB и вносит исправления. Применение команды:

mysql_upgrade -u root -p

3. ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

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

Причина: СУБД загружена в режиме skip-grant-tables — без загрузки таблиц разрешений.

Решение: открываем конфигурационный файл СУБД. Это может быть:

vi /etc/my.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

... и так далее. Ориентироваться можно по содержимому /etc/my.cnf — в нем будет либо конфигурация, либо include.

Находим строку:

skip-grant-tables

И комментируем ее:

#skip-grant-tables

Перезапускаем сервис:

systemctl restart mysql

Готово.