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

Введение

MySQL — это реляционная система управления базами данных с открытым исходным кодом. Она широко используется в составе стека LAMP (Linux, Apache, MySQL и PHP) и является самой популярной СУБД с открытым исходным кодом в мире на момент написания этой статьи. В этом руководстве объясняется, как создать нового пользователя MySQL и предоставить ему необходимые права для выполнения различных действий.

Предпосылки

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

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

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

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

sudo mysql

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

mysql -u root -p

Получив доступ к командной строке MySQL, вы можете создать нового пользователя с помощью фразы СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ Создать. Они следуют следующему общему синтаксису:

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

После СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ Вы указываете имя пользователя. Сразу за ним следует символ @, а затем имя хоста, с которого этот пользователь будет подключаться. Если вы планируете подключаться к этому пользователю только локально с вашего сервера Ubuntu, можно указать localhost. Заключать имя пользователя и имя хоста в кавычки не всегда обязательно, но это может помочь избежать ошибок.

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

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

Чтобы создать пользователя, аутентифицирующегося с помощью caching_sha2_password, выполните следующую команду. Убедитесь, что: Сэмми Выберите надежный пароль для предпочитаемого вами имени пользователя и пароля:

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 звёздочки — это специальные символы, используемые для обозначения “всех” баз данных или таблиц.

Для иллюстрации, следующая команда предоставляет пользователю глобальные привилегии на создание, изменение и удаление баз данных, таблиц и пользователей, а также право вставлять, обновлять и удалять данные из любой таблицы на сервере. Она также позволяет пользователю запрашивать данные с помощью ВЫБИРАТЬ Поиск, внешние ключи с ключевым словом ССЫЛКИ Создавать и эксплуатировать РУМЯНЕЦ с точками ПЕРЕЗАГРУЗКА Однако вам следует предоставлять пользователям только те разрешения, которые им необходимы, чтобы вы могли при необходимости корректировать свои пользовательские привилегии.

Полный список доступных точек вы можете увидеть на сайте Официальная документация MySQL Найди это.

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

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

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

Некоторые пользователи могут захотеть предоставить своему пользователю MySQL ВСЕ ПРИВИЛЕГИИ, что даст им широкие права суперпользователя, аналогичные правам пользователя 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';

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

Выполнив команду ШОУ ГРАНТЫ Вы можете проверить текущие разрешения пользователя:

SHOW GRANTS FOR 'username'@'host';

Поскольку вы можете использовать базы данных с УРОНИТЬ Удалить, можно из УРОНИТЬ Чтобы удалить пользователя, используйте:

DROP USER 'username'@'localhost';

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

exit

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

mysql -u sammy -p

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

Результат

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