Choose UPDATE mysql.user SET or ALTER USER? (SOLVED)

Each MySQL and MariaDB user can have various settings, such as permission (grants) to execute certain queries to all or specific databases. Users also have settings related to identification (login) on the DBMS server, such as: user password, used authentication plugin.

To change user settings such as password and authentication plugin, you can choose one of the following commands:

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

Or:

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

You may have already noticed these commands in different instructions.

“ALTER USER” is a specialized command for changing user settings. But since user settings are stored in the “user” table in the “mysql” database, these settings can be changed using the universal “UPDATE” command. These commands are not identical and there are nuances in their use – for different situations, it is more convenient to choose one of these two commands.

Let's take a closer look at how they differ and which of these operators should be selected.

ALTER USER is convenient for changing the user password

If you selected ALTER USER to change the user identification plugin, then you must specify the password:

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

If you do not specify the password, then instead of saving the old password (as you might assume), a reset to login without a password will be made:

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

And another important difference of ALTER USER is that you need to specify the host. Remember that the host values ​​can have different values. You can display all users and their hosts with the following SQL query:

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

In fact, there is usually no need to change the authentication plugin and ALTER USER is used to change the password:

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

UPDATE mysql.user SET – fine-grained change of user settings

If you select “UPDATE mysql.user SET”, you will not be able to change the password, but you will be able to fine-grainedly change other user settings – for example, the plugin used for authentication, as shown above.

Please note that if you do not specify the host, the settings in “UPDATE mysql.user SET” are applied immediately to all users with the same name but different hosts. You can change this by explicitly specifying the host:

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

Using UPDATE mysql.user SET you can change the values ​​of the “*_priv” fields, which will change the privileges, but it is better to use the specialized GRANT operator for this.

You will find a full list of the “*_priv” fields in the note “How to check what MySQL/MariaDB users exist and their characteristics”.

For fine-tuning of user privileges, see the following articles:

• How to allow a user to access only certain databases in MySQL (MariaDB)
• How to allow a user to access only certain tables in MySQL (MariaDB)
• How to allow a user to access only certain columns in MySQL (MariaDB)

Conclusion

To change a user's password, use the ALTER USER statement.

To change the user authentication plugin on the DBMS server, it is more convenient to use UPDATE mysql.user SET, since you can change this setting without knowing the user passwords and without resetting them.

To set up user privileges, use the GRANT statement, since it will give you maximum flexibility and convenience for setting up access to databases, tables, and even individual columns.