How to create a user and grant privileges in MySQL 8.x using GRANT

Written by Claudio Kuenzler - 0 comments

Published on November 23rd 2020 - Listed in MySQL Database Database

---

Since MySQL 8.x the way MySQL users are created an privileges are granted has changed compared to previous versions.

In previous versions (up to MySQL 5.7), a user could be created at the same time of granting privileges using GRANT in combination with IDENTIFIED BY:

mysql > GRANT ALL ON database.* TO 'sqluser'@'localhost' IDENTIFIED BY 'secret';

Since MySQL 8.x, this doesn't work anymore and an error message appears:

mysql > GRANT ALL ON database.* TO 'sqluser'@'localhost' IDENTIFIED BY 'secret';
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 'secret'' at line 1

In this case, the user must be created first (using CREATE USER) and then the privileges must be assigned to the existing user:

mysql > CREATE USER 'sqluser'@'localhost' IDENTIFIED BY 'secret';
mysql > GRANT ALL ON database.* TO 'sqluser'@'localhost';

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.