Тематические термины: MySQL , MariaDB .
Учетные записи в СУБД MySQL/MariaDB представлены в виде связки <имя пользователя>@<удаленный хост, с которого можно подключаться>. Это может вызвать путаницу, поэтому необходимо быть внимательнее, например, учетные записи root@localhost и root@192.168.0.15 — разные.
В инструкции мы рассмотрим базовые аспекты работы с пользователями и рассмотрим некоторые примеры.
Создать пользователя
Командой CREATE USER
Командой GRANT
Посмотреть пользователей
Сменить пароль
MySQL
MariaDB
Суперпользователю
Сброс пароля для root
Сетевой доступ
Примеры
Особые права
Удаленное подключение
Права на резервное копирование
С правами подключения к определенной таблице
Удаление пользователя
Права на управление пользователями
Возможные ошибки
Рассмотрим два варианта создания учетных записей в СУБД MySQL/MariaDB.
Данный метод является универсальным. Он позволяет создать пользователя в системе без каких либо прав. После права назначаются командой GRANT.
Пример создания учетной записи:
> CREATE USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;
* в данном примере будет создана учетная запись dbuser@localhost (доступ разрешен только с локального компьютера) и паролем password .
После можно задать права командой:
> GRANT <тип привилегий> ON <объект> TO <пользователь> <дополнительные опции>;
Например:
> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’localhost’;
* где:
Данный метод позволяет одной командой сразу и создать пользователя, и дать ему права. Но в более новых версиях MariaDB/MySQL, она возвращает ошибку — разработчики запретили ее использование и сначала требуется создать пользователя (с помощью CREATE USER ).
Синтаксис:
> GRANT <тип привилегий> ON <объект> TO <пользователь> [IDENTIFIED BY <пароль>] <дополнительные опции>;
Например, эта команда предоставляет права доступа пользователю и, если его не существует, создает его:
> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
Описание команды:
* где:
Список пользователей:
> SELECT user, host FROM mysql.user;
Список привилегий (для кажого пользователя смотряться отдельно):
> SHOW GRANTS FOR ‘root’@’localhost’;
* где ‘root’@’localhost’ — учетная запись, для которой смотрим привилегии; если упустить FOR, команда выдаст результат для пользователя, под которым выполнено подключение к СУБД.
Команды отличаются в зависимости от версии СУБД.
Версия 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 команда для смены пароля одна, независимо от версии:
> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘New_Password’);
* в данном примере будет задан пароль New_Password для пользователя root .
Для старых версий СУБД и новых процедуры различаются.
Первый раз пароль задается из командной строки операционной системы (для MySQL/MariaDB):
> mysqladmin -u root password
Для смены пароля root необходимо выполнить команду в оболочке mysql по инструкции, описанной выше.
Пароль создается автоматически при установке системы. Его можно посмотреть командой:
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
и меняем его .
При необходимости, мы можем сбросить пароль для суперпользователя mysql. Для этого необходимо запустить сервер баз данных в безопасном режиме и ввести команду на смену пароля.
Мы рассмотрим процесс подробнее как для СУБД, установленной в виде пакета, так и запущенное как процесс docker.
Для начала необходимо остановить сервис одной из команд (в зависимости от установленной СУБД):
systemctl stop mysql
systemctl stop mysqld
systemctl stop mariadb
Создаем каталог /var/run/mysqld и зададим для него нужные права:
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
* в некоторых системах данного каталога может не быть, однако, для безопасного режима mysql он необходим.
Запускаем в безопасном режиме СУБД:
mysqld_safe —skip-grant-tables &
Если мы получим ошибку:
mysqld_safe: command not found
… нам нужно выполнить команду:
systemctl set-environment MYSQLD_OPTS=»—skip-grant-tables —skip-networking»
И запускаем службу MySQL:
systemctl start mysql
Запускаем оболочку sql — система не потребует пароль:
mysql -uroot
Для MySQL 8:
sudo -u mysql mysql -uroot
Выполняем запрос FLUSH PRIVILEGES:
> FLUSH PRIVILEGES;
Меняем пароль вышеописанным способом .
Выходим из оболочки:
> quit
Уничтожаем процесс, который запустили ранее:
kill %1
Если мы запускали безопасный режим с помощью systemctl set-environment, то сначала удаляем заданную системную переменную:
systemctl stop mysql
systemctl unset-environment MYSQLD_OPTS
После, запускаем mysql в нормальном режиме:
systemctl start mysql
systemctl start mysqld
systemctl start mariadb
Нам необходимо перезапустить контейнер с добавлением к команде запуска опции —skip-grant-tables .
Если используем docker-compose, то в yml файл добавляем:
services:
…
command: —skip-grant-tables
…
Если docker запускается с помощью docker run, то перезапускаем его с опцией
docker run -d —entrypoint: mysqld_safe —skip-grant-tables —user=mysql …
После мы можем подключиться к СУБД от пользователя root без ввода пароля.
Вводим что-то на подобие:
docker exec -it db_container mysql
* где db_container — имя контейнера с базой данных.
Мы должны зайти в SQL-shell. Выполняем запрос FLUSH PRIVILEGES:
> FLUSH PRIVILEGES;
Меняем пароль вышеописанным способом .
После мы должны перезапустить контейнет без опции skip-grant-tables .
Для успешного подключения к СУБД по сети нам нужно выполнить 3 условия:
Рассмотрим выполнение данных условий по-очереди.
Как было сказано в начале, имя учетной записи состоит из частей <имя пользователя> + @ + <удаленный хост, с которого можно подключаться>.
Таким образом, если нам нужно разрешить подключение пользователю 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 .
Рассмотрим часто встречаемые операции по работе с пользователями сервера баз данных.
Предоставление особых прав пользователю:
> 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
Разрешение на удаленное подключение и использование базы 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’;
Создание учетной записи MySQL с правами создания резервных копий:
> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO ‘backup’@’localhost’ IDENTIFIED BY ‘backup’;
По сути, это такое же предоставление прав, но с указанием конкретной таблицы после базы:
> GRANT ALL PRIVILEGES ON base1.table1 TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;
* в данном примере предоставлены все права на таблицу table1 в базе base1 .
Нам может также понадобиться удалить ранее созданного пользователя. Это делается в два этапа:
> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘dbuser’@’localhost’;
> DROP USER ‘dbuser’@’localhost’;
* в данном примере мы первой командой отняли все права у пользователя dbuser ( localhost ) и второй — удалили его.
Чтобы наш пользователь мог создавать других пользоватлей, задаем права:
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 |
+—————————————+———+
* где:
Решение:
> 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
Готово.
Zulip — программное обеспечение для реализации корпоративного чата. Разработан в 2012 году, в 2014 был…
Zookeeper — cервис-координатор, который позволяет обеспечить контроль синхронизации данных. Разработан на Java компанией Apache Software…
Zimbra — программное обеспечение для реализации почтового сервиса или, если сказать точнее, автоматизации совместной деятельности…
Zabbix — бесплатная система мониторинга. Позволяет отслеживать состояние сетевых узлов, компьютеров и серверов. Возможности: Поддержка…
YouTube — компания-владелец одноименного портала для просмотра и хранения видео. Чтобы пользоваться данным порталом достаточно…
Yota — провайдер, предоставляющий доступ к сети Интернет по беспроводной связи. Впервые, сервис начал работать…