Question :
I’m currently attempting to define a new user using the following:
mysql> GRANT ALL PRIVILEGES ON simple_cms_development.* TO 'br'@'localhost' IDENTIFIED BY 'password';
However, it is unsuccessful and outputting:
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 ‘password” at line 1
I am running MySQL 8.0.11.
Answer :
According to the MySQL Documentation, this feature was deprecated: https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-deprecations
Changes to account-management statements make the following features obsolete. They are now deprecated:
Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is deprecated.
Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.
The solution is to use two separate statements. One to ALTER USER, then a second to GRANT privs.
ALTER USER 'br' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON simple_cms_development.* TO 'br'@'localhost';