Управление удалённым доступом к серверу MySQL

Материал статьи предназначен для начинающих, профессионалы Хабра в большинстве не нуждаются в изложении этого вопроса. Тем не менее, те, кто делает первые шаги в работе с MySQL часто задают подобные вопросы. Статья написан для тех, кто впервые столкнулся с необходимостью установить удаленное соединение с базой данных MySQL. В статье рассказывается о сложностях, которые могут возникнуть при настройке удаленного соединения и о методах их преодоления.

Как установить соединение с удаленной базой?

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

• —host
• —protocol
• —port

Из четырех возможных протоколов удаленное подключение позволяет только TCP/IP, поэтому первым требованием является доступность компьютера из сети по TCP/IP. Далее необходимо добавить имя хоста (или ip адрес машины, на которой расположена база) в строке подключения: mysql —host=host_name Обратите внимание, что указание в качестве имени хоста значения localhost (или же отсутствие такого параметра, что то же самое, так как это дефолтное значение) приводит к обращению на локальную машину. Учтите, что параметры имеют две формы записи: длинную и короткую. В свою очередь, если параметр имеет значение (как например, в случае хоста необходимо указать имя хоста к которому нужно подключиться), то короткая форма записи может использоваться с пробелом или без (исключением из этого правила является пароль). Таким образом, следующие три записи являются эквивалентными:

• mysql —host=myhost.ru
• mysql -h myhost.ru
• mysql -hmyhost.ru

Как настроить соединение с удаленной базой?

В MySQL пользователь характеризуется двумя параметрами: именем и хостом, с которого он может обращаться. По умолчанию доступ разрешен только с локальной машины, т.е. для пользователя user@localhost. Права на доступ пользователям даются с помощью команды GRANT. Команда выполняется под рутом. Например, если я хочу создать юзера, который сможет коннектиться с любого хоста с полными правами, то следует выполнить следующую команду: GRANT ALL PRIVILEGES ON `имя_базы`.* TO myuser@% IDENTIFIED BY ‘пароль’;Примечание. Обратите внимание, что данная команда дает доступ пользователю myuser со всех IP кроме 127.0.0.1, соответсвующего localhost. Для пользователя myuser@localhost необходимо давать права отдельной командой GRANT. Если вы решите обратиться к локальной машине как к удаленной, используя протокол TCP/IP, то не забывайте, что myuser@localhost и myuser@ip_собственного_компа это разные пользователи и каждому из них права нужно давать отдельной командой. Второй пример показывает как дать право читать таблицу time_zone в базе mysql пользователю myuser с машины 192.168.0.76 с паролем mypassy: GRANT SELECT ON mysql.time_zone TO myuser@192.168.0.76 IDENTIFIED BY ‘mypassy’;

Возникающие сложности

Если после всех действий у вас возникает ошибка (Can’t connect to Mysql Server on ‘ваш IP'(10061)), значит соединение блокирует firewall (windows firewall, outpost, антивирус или еще что-то). Если возникает ошибка (Client does not support autentification protocol request by server; consider upgrading MySQL client), клиент (то есть ваша программа) не поддерживает требуемый сервером протокол. Эту ошибку можно исправить, установив пароль старого формата: SET PASSWORD FOR user@host = OLD_PASSWORD(‘password’); Кросспост Настройка удаленного подключения к MySQL с Webew.

Нередко, удалённый доступ к серверу MySQL — это единственный способ управления своими базами данных (БД). Для хостинг-провайдеров подобная техническая возможность является обязательной для качественного предоставления услуг клиентам. Для веб-разработчиков (вообще для разработчиков) удалённый доступ позволяет использовать один общий ресурс для обслуживания БД. В любом случае, просто так по-умолчанию сервер MySQL удалённые подключения не допускает. Для включения удалённого доступа к MySQL необходимо конфигурирование определённых параметров как самого сервера, так и обслуживаемых БД и их пользователей. В данной статье будут рассмотрены основные принципы предоставления удалённого доступа к серверу MySQL.

Содержание

Что для этого нужно?

Сервер MySQL по-умолчанию в целях безопасности сконфигурирован таким образом, что может принимать только локальные соединения, поступающие через локальный сокет с той же самой машины, на которой работает и сам сервер. Эта настройка обслуживается опцией «bind-address» в конфигурационном файле MySQL-сервера

/etc/mysql/mysql.conf.d/mysqld.cnf. Изменив значение указанной опции и перезапустив сервер MySQL можно предоставить доступ для удалённых клиентов. Кроме самой возможности удалённого подключения к серверу MySQL необходимо также и позаботиться о предоставлении соответствующих привилегий удалённым пользователям. Это также очень важно как для комфортной работы, так и для безопасности. Привилегии пользователей MySQL предоставляются как для подключений, так и для работы

непосредственно с БД. В первом случае для пользователя могут быть заданы привилегии, ограничивающие его действия для конкретного типа подключения. Например, удалённо пользователь не может удалять БД. При этом неважно, что сервер корректно настроен для удалённого доступа. Во втором случае у пользователя могут быть заданы ограничения на уровне самих БД, таблиц и процедур, действующие только при удалённом подключении и отсутствующие при локальном. Например, так бывает, когда системный администратор MySQL не хочет, чтобы БД, таблицы, данные можно было удалить работая с сервером через удалённое подключение, а только локально.

Таким образом, существует возможность задания привилегий, т. е. управления доступом не только для самих пользователей и групп. Но и для характера их подключения к серверу — удалённого или локального. Это необходимо учитывать при организации и управлении удалённым доступом к MySQL-серверу.

Настройка сервера MySQL

По-умолчанию для опции «bind-address» в файле /etc/mysql/mysql.conf.d/mysqld.cnf задано значение 127.0.0.1. И подключиться к серверу можно только локально:

. . .  lc-messages-dir = /usr/share/mysql  skip-external-locking  #  # Instead of skip-networking the default is now to listen only on  # localhost which is more compatible and is not less secure.  bind-address = 127.0.0.1  . . .

Для того, чтобы иметь доступ к MySQL-серверу с любого IP-адреса, т. е. с любого компьютера в сети, нужно значение 127.0.0.1 изменить на 0.0.0.0:

. . .  lc-messages-dir = /usr/share/mysql  skip-external-locking  #  # Instead of skip-networking the default is now to listen only on  # localhost which is more compatible and is not less secure.  bind-address = 0.0.0.0  . . .

Сделав изменения в и сохранив файл нужно перезапустить сам сервер MySQL:

$ sudo systemctl restart mysql

Далее можно проверить, что удалённое подключение возможно, выполнив его с другого компьютера в сети:

$ mysql -u user_name -h db_host -p

Здесь «db_host» – это адрес (IP или имя хоста) компьютера, на котором работает сервер MySQL. При удачном подключении пользователь будет переведён в интерфейс командной консоли MySQL.

Настройка привилегий БД

На сервере MySQL может быть несколько пользователей с одинаковым именем, но разным режимом доступа. Например, пользователь с именем john, которому доступно только локальные подключения. На самом деле распознается сервером MySQL как пользователь с именем «john@localhost». Для того, чтобы пользователь john мог подключаться ещё и удалённо с любого IP. На сервере должна быть создана ещё одна учётная запись с именем «john@%». Вот как выглядит создание такой учётной записи в веб-интерфейсе phpMyAdmin:

Как можно видеть, в системе управления доступом MySQL для учётной записи пользователя существует отдельный атрибут «имя хоста», регламентирующий режим подключения. Это сделано как для повышения самой безопасности, так и для более гибкого управления и администрирования. Тот случай, когда одно другому не только не мешает. Но в совокупности поднимает эффективность и безопасность всего сервиса.

Поскольку учётные записи john@localhost и john@% являются разными (хотя и принадлежат одному пользователю), то для них можно отдельно настраивать привилегии. Например, для учётной записи john@% в phpMyAdmin это выглядит следующим образом:

Как можно видеть, администратор запретил пользователю john удалять БД через удалённое подключение. Таким же образом можно устанавливать (или убирать) ограничения на уровне БД. Т. е. при работе с таблицами, а также на уровне таблиц — при работе непосредственно с данными.

Заключение

В заключение стоит ещё раз отметить, что предоставление удалённого доступа к БД путём изменения параметра bind-address в значение 0.0.0.0 должно проводиться с учётом принятых мер по обеспечению дополнительной безопасности сервера.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

После того, как вы установили и настроили MySQL, вам необходимо создать базы данных, таблицы и пользователей. Конечно, вы можете сделать это от имени  суперпользователя root, но это не безопасно. Да и большинство приложений не позволят вам такой вольности, например, Phpmyadmin не даст авториrзоваться от имени суперпользователя.

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

Теперь можно работать. Для создания пользователя используется команда CREATE USER, её синтаксис такой:

CREATE USER‘имя_пользователя’@‘хост’IDENTIFIED BY‘пароль’;

Кроме имени пользователя, здесь нужно задать хост, с которого может авторизоваться этот пользователь. Здесь может быть доменное имя, IP-адрес, адрес подсети или знак «%», который означает все возможные хосты. Это очень удобно, потому что вы можете создать пользователя, к которому можно будет подключится только локально или настроить отдельно права для локального или удалённого пользователя.

Например, давайте создадим локального пользователя test_user с паролем password:

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';

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

CREATE USER 'test_user'@'%' IDENTIFIED BY 'password';

Смотрим наших пользователей:

SELECT User,Host FROM mysql.user;

Все пользователи созданы.

Создать пользователя — это ещё далеко не всё, теперь нужно назначить ему права. Для этого используется команда GRAT. У неё такой синтаксис:

GRANTпривилегии ONбаза_данных.таблицаTO ‘имя_пользователя‘@‘хост‘;

Здесь можем дать определённые привилегии для опредёленного пользователя к базе данных или таблице. Если нужно дать права для всех баз данных или таблиц, используйте вместо названия звездочку — «*». Вот основные права пользователя MySQL, которые вы можете использовать:

• ALL PRIVILEGES — все, кроме GRANT;
• USAGE PRIVILEGES — никаких привилегий;
• SELECT — делать выборки из таблиц;
• INSERT — вставлять данные в таблицу;
• UPDATE — обновлять данные в таблице;
• DELETE — удалять данные из таблицы;
• FILE — разрешает читать файлы на сервере;
• CREATE — создавать таблицы или базы данных;
• ALTER — изменять структуру таблиц;
• INDEX — создавать индексы для таблиц;
• DROP — удалять таблицы;
• EVENT — обработка событий;
• TRIGGER — создание триггеров.

Также доступны такие привилегии администрирования баз данных:

• GRANT — изменять права пользователей;
• SUPER — суперпользователь;
• PROCESS — получение информации о состоянии MySQL;
• RELOAD — позволяет перезагружать таблицы привилегий;
• SHUTDOWN — позволяет отключать или перезапускать базу данных;
• SHOW DATABASES — просмотр списка баз данных;
• LOCK TABLES — блокирование таблиц при использовании SELECT;
• REFERENCES — создание внешних ключей для связывания таблиц;
• CREATE USER — создание пользователей;

Чтобы дать права пользователю MySQL на обновление и добавление записей для базы данных test_database, выполните:

GRANT SELECT,UPDATE,INSERT ON test_database . * TO 'test_user'@'localhost';

Дальше дадим этому же пользователю все права над этой базой данных:

GRANT ALL PRIVILEGES ON test_database . * TO 'test_user'@'localhost';

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

SHOW GRANTS FOR 'test_user'@'localhost';

Мы видим, что для всех баз данных и таблиц привелегий нет, но зато есть все привилегии для базы данных test_database. Вот так это работает. После обновления прав пользователя необходимо обновить таблицу прав пользователей MySQL  в памяти. Для этого выполните:

FLUSH PRIVILEGES;

Чтобы отозвать права у пользователя MySQL, используйте команду REVOKE вместо GRANT. Её синтаксис похож на GRANT:

REVOKEпривилегии ONбаза_данных.таблицаFROM ‘имя_пользователя‘@‘хост‘;

Например, заберём все права на базу данных test_database у нашего пользователя:

REVOKE ALL PRIVILEGES ON test_database . * FROM 'test_user'@'localhost';

Если вам необходимо создать пользователя со всеми правами MySQL на замену для root, то можно использовать такую конструкцию:

GRANT ALL PRIVILEGES ON * . * TO 'test_user'@'localhost';

Даём все привилегии для пользователя test_user над всеми базами данными и всеми таблицами. Но наш пользователь не сможет давать права другим пользователям. Чтобы это исправить, нужно дать ему привилегию GRANT, а для этого используется такая команда:

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

Теперь этот пользователь является суперпользователем для MySQL и, авторизовавшись от его имени в PhpMyAdmin, вы можете делать всё то же самое, что и с  помощью root.

В этой статье мы рассмотрели, как создать пользователя MySQL в  Ubuntu или в любом другом дистрибутиве. Работа в командной строке с MySQL может показаться довольно сложной. Но сделать это надо всего один раз -после установки базы данных. А дальше можно выполнять все действия в Phpmyadmin.

Используемые источники:

• https://habr.com/post/25554/
• https://itproffi.ru/upravlenie-udalyonnym-dostupom-k-serveru-mysql/
• https://losst.ru/sozdanie-polzovatelya-mysql