Выбрать команду UPDATE mysql.user SET или ALTER USER? (РЕШЕНО)

У каждого пользователя MySQL и MariaDB могут быть различные настройки, например, разрешение на выполнение тех или иных запросов ко всем или к определённым базам данных. Также у пользователей имеются настройки относящиеся к идентификации (входу) на сервере СУБД, например: пароль пользователя, используемый плагин аутентификации.

Для изменения таких настроек пользователя как пароль и плагин аутентификации вы можете выбрать одну из следующих команд:

UPDATE mysql.user SET plugin = 'caching_sha2_password' WHERE User = 'root';

Или:

ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_password';

Возможно вы уже обращали внимание на эти команды в разных инструкциях.

«ALTER USER» — специализированная команда для изменения настроек пользователей. Но поскольку настройки пользователей хранятся в таблице user в базе данных mysql, то эти настройки можно изменить с помощью универсальной команды «UPDATE». Эти команды не являются идентичными и имеются нюансы при их использовании — для разных ситуаций удобнее выбрать одну из этих двух команд.

Рассмотрим подробнее, чем они различаются и какие из этих операторов следует выбрать.

С помощью ALTER USER удобно менять пароль пользователя

Если вы выбрали ALTER USER для изменения плагина идентификации пользователя, то вам обязательно нужно указать пароль:

ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_password';

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

ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password

И ещё одно важное отличие ALTER USER в том, что вам нужно указать хост. Помните, что в качестве значений хоста могут быть различные значения. Вывести всех пользователей и их хосты вы можете следующим SQL-запросом:

SELECT User,Host,plugin,authentication_string FROM mysql.user;

На самом деле, обычно не требуется менять плагин идентификации и ALTER USER используется для смены пароля:

ALTER USER 'user'@'host' IDENTIFIED BY 'new_password'

UPDATE mysql.user SET — точечное изменение настроек пользователя

Если вы выбрали «UPDATE mysql.user SET», то вы не сможете поменять пароль, но при этом сможете точечно поменять другие настройки пользователя — например, плагин используемый для идентификации, как это показано выше.

Обратите внимание, что если не указать хост, то настройки в «UPDATE mysql.user SET» применяются сразу для всех пользователей с одинаковым именем, но разным хостом. Вы можете изменить это, явно указав хост:

UPDATE mysql.user SET plugin = 'caching_sha2_password' WHERE User = 'root' AND Host = 'localhost';

С помощью UPDATE mysql.user SET можно менять значения полей «*_priv», что приведёт к смене привилегий, но лучше для этого использовать специализированный оператор GRANT.

Полный перечень полей «*_priv» вы найдёте в заметке «Как проверить, какие пользователи MySQL/MariaDB имеются и их характеристики».

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

• Как разрешить пользователю доступ только к определённым базам данных в MySQL (MariaDB)
• Как разрешить доступ пользователю только к определённым таблицам в MySQL (MariaDB)
• Как разрешить доступ пользователю только к определённым столбцам в MySQL (MariaDB)

Заключение

Для изменения пароля пользователя используйте оператор ALTER USER.

Для изменения плагина идентификации пользователя на сервере СУБД удобнее использовать UPDATE mysql.user SET, поскольку можно поменять эту настройку не зная пароли пользователей и не обнуляя их.

Для настройки привилегий пользователя используйте оператор GRANT, поскольку он предоставит вам максимальную гибкость и удобство для настройки доступа к базам данных, таблицам и даже отдельным столбцам.