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

Введение

MySQL - это система управления реляционными базами данных с открытым исходным кодом. Она обычно используется как часть стека LAMP (который расшифровывается как Linux, Apache, MySQL и PHP) и на момент написания этой статьи является самой популярной базой данных с открытым исходным кодом в мире.

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

Подготовка

Чтобы следовать этому руководству, вам понадобится доступ к базе данных MySQL. В этом руководстве предполагается, что эта база данных установлена на виртуальном частном сервере под управлением Debian 12, хотя принципы, изложенные в нем, должны применяться независимо от того, как вы получаете доступ к своей базе данных.

Если у вас нет доступа к базе данных MySQL и вы хотели бы настроить ее самостоятельно, вы можете воспользоваться руководством по установке MySQL. Опять же, независимо от операционной системы, используемой на вашем сервере, методы создания нового пользователя MySQL и предоставления ему разрешений, как правило, будут одинаковыми.

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

После установки MySQL создает учетную запись пользователя root, которую вы можете использовать для управления своей базой данных. Этот пользователь имеет полные привилегии на сервере MySQL, что означает, что он имеет полный контроль над каждой базой данных, таблицей, пользователем и так далее. Из-за этого лучше избегать использования этой учетной записи вне административных функций. На этом шаге описывается, как с помощью root создать новую учетную запись пользователя и предоставить ей привилегии.

В системах на базе Debian, работающих под управлением MySQL 5.7 (и более поздних версий), пользователь root MySQL по умолчанию проходит аутентификацию с помощью плагина auth_socket, а не с помощью пароля. Этот плагин требует, чтобы имя пользователя операционной системы, вызывающего клиент MySQL, совпадало с именем пользователя MySQL, указанным в команде. Это означает, что вам нужно перед командой mysql с ставить sudo, чтобы вызвать ее с привилегиями пользователя root, чтобы получить доступ к соответствующему пользователю MySQL:

$ sudo mysql

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

$ mysql -u root -p

Как только у вас появится доступ к командной строке MySQL, вы сможете создать нового пользователя с помощью команды CREATE USER. Общий синтаксис:

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

После CREATE USER вы указываете имя пользователя. Сразу после этого следует знак @, а затем имя хоста, с которого этот пользователь будет подключаться. Если вы планируете получать доступ к этому пользователю только локально с вашего сервера Ubuntu, вы можете указать localhost. Заключать имя пользователя и хост в одинарные кавычки не всегда необходимо, но это может помочь предотвратить ошибки.

У вас есть несколько вариантов выбора плагина аутентификации вашего пользователя. Упомянутый ранее плагин auth_socket может быть удобен, поскольку он обеспечивает надежную защиту, не требуя от действительных пользователей вводить пароль для доступа к базе данных. Но это также предотвращает удаленные подключения, что может усложнить ситуацию, когда внешним программам необходимо взаимодействовать с MySQL.

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

Выполните следующую команду, чтобы создать пользователя, который будет проходить аутентификацию с помощью caching_sha2_password. Обязательно измените имя пользователя denis на ваше имя пользователя и пароль на надежный пароль по вашему выбору:

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

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

CREATE USER 'denis'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Если вы не уверены, вы всегда можете создать пользователя, который проходит проверку подлинности с помощью caching_sha2_plugin, а затем изменить его позже с помощью этой команды:

ALTER USER 'denis'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

После создания нового пользователя вы можете предоставить ему соответствующие привилегии.

Предоставление прав доступа

Общий синтаксис для предоставления привилегий пользователя выглядит следующим образом:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

Значение PRIVILEGE в этом примере синтаксиса определяет, какие действия пользователю разрешено выполнять с указанными database и table. Вы можете предоставить несколько привилегий одному и тому же пользователю в одной команде, разделив их запятой. Вы также можете предоставить пользователю глобальные привилегии, указав звездочки * вместо имен базы данных и таблиц. В SQL звездочки - это специальные символы, используемые для обозначения любых баз данных или таблиц.

Для иллюстрации приведенная ниже команда предоставляет пользователю глобальные привилегии для CREATE, ALTER и DROP создания, изменения и удаления баз данных, таблиц и пользователей, а также возможность INSERT, UPDATE и DELETE вставлять, обновлять и удалять данные из любой таблицы на сервере. Это также предоставляет пользователю возможность запрашивать данные с помощью SELECT, создавать внешние ключи с помощью ключевого слова REFERENCES и выполнять операции очистки FLUSH с привилегией RELOAD. Однако вы должны предоставлять пользователям только те разрешения, которые им нужны, поэтому не стесняйтесь изменять свои собственные привилегии по мере необходимости.

Вы можете найти полный список доступных привилегий в официальной документации по MySQL.

Запустите эту инструкцию GRANT, заменив denis на ваше имя пользователя MySQL, чтобы предоставить эти привилегии вашему пользователю:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'denis'@'localhost' WITH GRANT OPTION;

Обратите внимание, что это утверждение также включает параметр WITH GRANT. Это позволит вашему пользователю MySQL предоставлять любые разрешения, которые у него есть, другим пользователям в системе.

Предупреждение: Некоторые пользователи могут захотеть предоставить своему пользователю MySQL привилегию ALL PRIVILEGES, которая предоставит им широкие привилегии суперпользователя, аналогичные привилегиям пользователя root, например:

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

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

Во многих руководствах предлагается выполнить команду FLUSH PRIVILEGES сразу после команды CREATE USER или GRANT, чтобы перезагрузить таблицы предоставления и убедиться, что новые привилегии вступили в силу:

FLUSH PRIVILEGES;

Однако, согласно официальной документации MySQL, когда вы косвенно изменяете права таблицы с помощью инструкции по управлению учетными записями, такой как GRANT, база данных немедленно загружает таблицы в память, что означает, что команда FLUSH PRIVILEGES в нашем случае не требуется. С другой стороны, его запуск не окажет никакого негативного влияния на работу системы.

Если вам нужно отозвать разрешение, структура практически идентична процедуре его предоставления:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

Обратите внимание, что при отзыве разрешений синтаксис требует, чтобы вы использовали FROM вместо TO, который вы использовали при предоставлении разрешений.

Вы можете просмотреть текущие разрешения пользователя, выполнив команду SHOW GRANTS:

SHOW GRANTS FOR 'username'@'host';

Точно так же, как вы можете удалять базы данных с помощью DROP, вы можете использовать DROP для удаления пользователя:

DROP USER 'username'@'localhost';

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

exit

В будущем, чтобы войти в систему в качестве нового пользователя MySQL, вам следует использовать команду, подобную следующей:

mysql -u denis -p

Флаг -p приведет к тому, что клиент MySQL запросит у вас пароль вашего пользователя MySQL для аутентификации.

Заключение

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