Mysql – Unsuccessfully granting privileges

MySQLpermissions

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.

Best 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';
Related Question