CODEWITHSUNDEEP
mysqlpermissionsprivilegessql-grant
thebator
thebator

Reputation: 115

MySQL: User with grant option can't grant create user

I created a user (new_user) with root like this:

GRANT ALL ON labor.* TO 'new_user'@'%' WITH GRANT OPTION;
GRANT ALL ON labor.* TO 'new_user'@'localhost' WITH GRANT OPTION;
GRANT CREATE USER ON *.* TO 'new_user'@'%';
GRANT CREATE USER ON *.* TO 'new_user'@'localhost';
GRANT RELOAD ON *.* TO 'new_user'@'localhost';
GRANT RELOAD ON *.* TO 'new_user'@'%'; 
FLUSH PRIVILEGES;

When I try to create another user the same way but with new_user, I get an access denied error. This error occurs after the GRANT ALL lines.

What else privilege should I add?

Upvotes: 11

Views: 37806

Answers (2)

Promise Preston
Promise Preston

Reputation: 29078

Just to add to Xevelion's answer

I had this issue when I created trying to grant a user access to a database

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

Here's how I fixed it:

This error occurs when you create the user specifying a particular host and try to grant the same user access specifying a different host.

So in my case, I created a user specifying the database host aurora-mysql-instance-0.cgi7whhgca9z.eu-east-1.rds.amazonaws.com:

CREATE USER 'my_user'@'aurora-mysql-instance-0.cgi7whhgca9z.eu-east-1.rds.amazonaws.com' IDENTIFIED BY 'ghy7yyDteh';

And then I tried to grant the user access to a database specifying a different host %:

CREATE DATABASE my_db;
USE my_db;
GRANT ALL ON my_db.* TO 'my_user'@'%';

All I had to do was to delete the existing my_user user and then recreate it using the host I was to grant it access to:

CREATE USER 'my_user'@'%' IDENTIFIED BY 'ghy7yyDteh';

And then I granted the user access to a database specifying the % host:

USE my_db;
GRANT ALL ON my_db.* TO 'my_user'@'%';

This time everything worked fine.

Upvotes: 2

Xevelion
Xevelion

Reputation: 869

The newly create user is missing the grant option on *.* (needed for grant create user on *.* ...)

GRANT GRANT OPTION ON *.* TO 'new_user'@'%';
GRANT GRANT OPTION ON *.* TO 'new_user'@'localhost';

Upvotes: 20

Related Questions