6

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.

8

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';
  • It does appear to be deprecated and your solution does indeed work. Many thanks – Brennan Jun 20 '18 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.