Как создать нового пользователя и предоставить разрешения в MySQL
Введение
самая популярная база данных с открытым исходным кодом в мире.
В этом руководстве описывается, как создать нового пользователя MySQL и предоставить ему права, необходимые для выполнения различных действий.
Предпосылки
Чтобы следовать этому руководству, вам потребуется доступ к базе данных MySQL. В этом руководстве предполагается, что эта база данных установлена на виртуальном частном сервере под управлением Ubuntu 20.04, хотя изложенные в нем принципы должны применяться независимо от того, как вы получаете доступ к базе данных.
Если у вас нет доступа к базе данных MySQL и вы хотите настроить ее самостоятельно, вы можете следовать одному из наших руководств по установке MySQL. Опять же, независимо от базовой операционной системы вашего сервера, методы создания нового пользователя MySQL и предоставления ему разрешений, как правило, будут одинаковыми.
В качестве альтернативы вы можете развернуть базу данных MySQL, управляемую облачным провайдером. Подробную информацию о том, как развернуть управляемую базу данных DigitalOcean, см. в документации по нашему продукту.
Обратите внимание, что любые части примеров команд, которые вам нужно изменить или настроить, будут выделены подобным образом в этом руководстве.
Создание нового пользователя
После установки MySQL создает учетную запись пользователя root, которую вы можете использовать для управления своей базой данных. Этот пользователь имеет полные привилегии над сервером MySQL, что означает, что он имеет полный контроль над каждой базой данных, таблицей, пользователем и так далее. Из-за этого лучше избегать использования этой учетной записи вне административных функций. Этот шаг описывает, как использовать пользователя root MySQL для создания новой учетной записи пользователя и предоставления ему привилегий.
В системах Ubuntu, работающих под управлением MySQL 5.7 (и более поздних версий), пользователь root MySQL по умолчанию настроен на аутентификацию с использованием подключаемого модуля auth_socket, а не с помощью пароля. Этот подключаемый модуль требует, чтобы имя пользователя операционной системы, вызывающего клиент MySQL, совпадало с именем пользователя MySQL, указанным в команде. Это означает, что вам нужно предварить команду mysql sudo, чтобы вызвать ее с привилегиями пользователя root Ubuntu, чтобы получить доступ к пользователю root MySQL:
- sudo mysql
Примечание. Если ваш корневой пользователь 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. Обязательно измените sammy на предпочитаемое имя пользователя и пароль на надежный пароль по вашему выбору:
- CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
Примечание. В некоторых версиях PHP существует известная проблема, вызывающая проблемы с caching_sha2_password. Если вы планируете использовать эту базу данных с приложением PHP — например, phpMyAdmin — вы можете создать пользователя, который будет аутентифицироваться с помощью старого, хотя и безопасного плагина mysql_native_password:
- CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Если вы не уверены, вы всегда можете создать пользователя, который проходит аутентификацию с помощью caching_sha2_plugin, а затем ИЗМЕНИТЬ его позже с помощью этой команды:
- ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
После создания нового пользователя вы можете предоставить ему соответствующие привилегии.
Предоставление разрешений пользователю
Общий синтаксис для предоставления привилегий пользователя следующий:
- GRANT PRIVILEGE ON database.table TO 'username'@'host';
Значение PRIVILEGE в этом примере синтаксиса определяет, какие действия разрешено выполнять пользователю в указанной базе данных и <таблица. Вы можете предоставить несколько привилегий одному и тому же пользователю в одной команде, разделяя их запятой. Вы также можете предоставить пользователю глобальные привилегии, введя звездочки (*) вместо имен баз данных и таблиц. В SQL звездочки — это специальные символы, используемые для обозначения «всех» баз данных или таблиц.
Для иллюстрации следующая команда предоставляет пользователю глобальные привилегии для CREATE, ALTER и DROP баз данных, таблиц и пользователей, а также право на INSERT, UPDATE и DELETE данные из любой таблицы на сервере. Он также предоставляет пользователю возможность запрашивать данные с помощью SELECT, создавать внешние ключи с помощью ключевого слова REFERENCES и выполнять операции FLUSH с помощью RELOAD привилегия. Однако вы должны предоставлять пользователям только те разрешения, которые им нужны, поэтому не стесняйтесь настраивать привилегии своих пользователей по мере необходимости.
Вы можете найти полный список доступных привилегий в официальной документации MySQL.
Запустите этот оператор GRANT, заменив sammy своим собственным именем пользователя MySQL, чтобы предоставить следующие привилегии вашему пользователю:
- GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
Обратите внимание, что этот оператор также включает WITH GRANT OPTION. Это позволит вашему пользователю MySQL предоставлять любые разрешения, которые он имеет, другим пользователям в системе.
Предупреждение: некоторые пользователи могут захотеть предоставить своим пользователям MySQL привилегию ALL PRIVILEGES, которая предоставит им широкие привилегии суперпользователя, аналогичные привилегиям пользователя root, например:
- GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'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 sammy -p
Флаг -p заставит клиент MySQL запросить у вас пароль пользователя MySQL для аутентификации.
Заключение
Следуя этому руководству, вы узнали, как добавлять новых пользователей и предоставлять им различные разрешения в базе данных MySQL. Отсюда вы можете продолжать исследовать и экспериментировать с различными настройками разрешений для вашего пользователя MySQL, или вы можете узнать больше о некоторых конфигурациях MySQL более высокого уровня.
Для получения дополнительной информации об основах MySQL вы можете ознакомиться со следующими руководствами: