I am trying to add an existing user to another database but I am keep getting the error:

ERROR 1410 (42000): You are not allowed to create a user with GRANT

I tried the following, none worked:

GRANT ALL ON db.* TO 'user'@'localhost';

GRANT ALL ON db.* TO 'user'@'localhost' WITH GRANT OPTION;

GRANT ALL  ON db.* TO 'user';

I am using MySQL on digitalocean

MySQL version: 8.0.31-0ubuntu2

Update: I am using root user.

CodePudding user response：

According to this oracle blog, it is not possible to create a user from the GRANT command in mysql 8:

To start, let’s highlight the fact that in MySQL 8.0 it’s not any more possible to create a user directly from the GRANT command:

It states you have to create a user first, before granting privileges. Have you created the user first? If not, try that!

CodePudding user response：

You need to create a user 1st. Follow the below steps.

1. CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password'; or CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password'; if the 1st command throws an error.
2. Grant privilege. GRANT PRIVILEGE ON database.table TO 'username'@'host';
3. FLUSH PRIVILEGES;

You can find the full reference in this blog