Error using GRANT with IDENTIFIED by password in MySQL

Question: I have been using IDENTIFIED BY ‘password’ along with GRANT statement for years in many MySQL versions. However, the same failed in MySQL version 8.0.26 on my new CentOS Stream release 8. Below is the complete error message. What could be the issue? – Tushar.

mysql> GRANT ALL PRIVILEGES ON qdb.* TO 'quser'@'localhost' IDENTIFIED BY 'qcuser123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near IDENTIFIED BY 'quser123' at line 1

Error while using GRANT with IDENTIFIED by password

Tushar, the use of IDENTIFIED BY password with GRANT statement has been deprecated since MySQL version 5.7.6. It means you need to use IDENTIFIED by password with CREATE USER or ALTER USER and apply GRANT PRIVILEGES as shown below:

CREATE USER and assign a password.

mysql> CREATE USER 'quser'@'localhost' IDENTIFIED BY 'qc123';
Query OK, 0 rows affected (0.12 sec)

Add privileges using GRANT:

mysql> GRANT ALL PRIVILEGES ON qdb.* TO 'quser'@'localhost';
Query OK, 0 rows affected (0.03 sec)

Why this change in the latest MySQL?

The change makes sense for the below reasons:

• Using IDENTIFIED BY password with GRANT privileges will set any password specified as the new password for the account. Assume if there is an account that already exists with a different password, executing GRANT privileges along with IDENTIFIED BY password will overwrite the existing password. So removing IDENTIFIED from GRANT means, the account remains with the password that was set during creation.
• As of MySQL version 5.7.2, if an account already exists, IDENTIFIED by ‘password’ is prohibited and it should be used only during account creation ie., with CREATE USER or ALTER USER.
• GRANT may create a user account if it does not exist.
  • If NO_AUTO_CREATE_USER enabled
    • If an account specified in the GRANT statement does not exist, then GRANT will fail to create an account unless the non-empty password is specified via IDENTIFIED BY or via IDENTIFIED WITH.
  • If NO_AUTO_CREATE_USER disabled
    • If an account specified in the GRANT statement does not exist, then GRANT creates the account. This can be insecure if no password is specified via IDENTIFIED BY.

Finally, GRANT with IDENTIFIED BY password has been deprecated and the user account needs to be created using CREATE USER or ALTER USER.

via StackOverflow.