7

I want to grant select privilege to user from every host, but getting an error, you're not allow to create a user

mysql>GRANT SELECT ON *.* TO 'alice'@'%' WITH GRANT OPTION;

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

I don't want to create a new user, for that I used command CREATE USER

I tried to create a new user but the grant command also failed.

mrakona
  • 81
  • 1
  • 4
  • You have to login to MySQL with an account that is allowed to execute GRANT's. Normally that would be the `root` account unless you have created others – RiggsFolly Aug 12 '19 at 11:20
  • Ohh does the `alic` user account already exist? – RiggsFolly Aug 12 '19 at 11:24
  • Yes, alice exists and I login as root – mrakona Aug 12 '19 at 11:32
  • Give a try on `GRANT SELECT ON *.* TO 'alic'@'%';` – James Aug 12 '19 at 11:34
  • `mysql> GRANT SELECT ON *.* TO 'alice'@'%'; ERROR 1410 (42000): You are not allowed to create a user with GRANT`. I updated the user name to alice, as in the DB – mrakona Aug 12 '19 at 11:38
  • Try this `select * from mysql.user;` and check do you have `Grant_priv` as `Y` – James Aug 12 '19 at 11:44
  • Or this also works `show grants for current_user;` – James Aug 12 '19 at 11:45
  • `There is no such grant defined for user 'alice' on host '%'` and Grant_prive = N for alice and Y for root – mrakona Aug 12 '19 at 11:48
  • btw you are actually in root? and do you have privilege in `select`? and *Or this also works show grants for current_user;* what is the result set you are getting? – James Aug 12 '19 at 11:52
  • Yes I'm root `GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION | | GRANT SYSTEM_USER ON *.* TO `root`@`%` | GRANT AUDIT_ADMIN,CLONE_ADMIN,INNODB_REDO_LOG_ARCHIVE,TABLE_ENCRYPTION_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION ` – mrakona Aug 12 '19 at 11:56
  • So now you have created a new user called alice? or it's yet to create? – James Aug 12 '19 at 12:03
  • yes, the user is already created – mrakona Aug 12 '19 at 12:03
  • then run ` FLUSH PRIVILEGES` and then try again – James Aug 12 '19 at 12:05
  • There is no such grant defined for user 'alice' on host '%' – mrakona Aug 12 '19 at 12:07
  • I tried to update the host to % and select_priv to Y, but cannot login from remote host and show grants return no such grant defined – mrakona Aug 12 '19 at 12:07
  • reopen the session again. Have you tried flush privileges? – James Aug 12 '19 at 12:08
  • same.. also tried to restart mysqld and it's not helped. Do you have any suggestions? – mrakona Aug 12 '19 at 12:36
  • Nope, but refer https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0/50197630 this might help I guess – James Aug 12 '19 at 12:43
  • I've deleted mysql.users and backed to default MySQL configuration, the issue is resolved now. Thanks for your answers – mrakona Aug 14 '19 at 07:57
  • The FLUSH PRIVILEGES is not aplicable in this case, see my answer below @James – Alvimar Oct 15 '20 at 14:57

2 Answers2

3

A good idea (and a good practice) in this MySql Version (8 and above) is to config ROLES and then set the users for it, like this good article (read it, is very good!).

You'll can set your role to many users.

Something like this:

1 - Create the role:

create ROLE name_of_your_role;

2 - Set the necessary privileges to the ROLE, and remember, in this MySql version you dont need of Flush Privileges. Example (change mydatabase expression for your database name or wildcard, like you need):

grant alter,create,delete,drop,index,insert,select,update,trigger,alter
 routine,create routine, execute, create temporary tables 
on mydatabase.* to 'name_of_your_role';

3 - Grant the role for your user:

grant 'name_of_your_role' to 'alice';

4 - Set this role as default to your user:

set default role 'name_of_your_role' to 'alice';
Alvimar
  • 488
  • 3
  • 12
3

I just had the same issue.

But my problem was that I made a mistake in CREATE USER query. I wanted to create grafana user, but executed the following query: CREATE USER grafan IDENTIFIED BY '<password>'; (Note that I created grafan user here instead of grafana)

Then, when I executed query GRANT SELECT ON mydb.* TO grafana@'%'; I got an error ERROR 1410 (42000): You are not allowed to create a user with GRANT. There was no mention that user grafana does not exist.

I spent about 20 minutes to figure out what I was doing wrong.

So if you are reading this, just check that you had correctly created your user. Hope this will help someone.

hungl
  • 101
  • 8
  • well done @hungl . For me it was an error on `localhost`. The query `SELECT user, host FROM user;` showed me `?localhost?` instead of `localhost` because of a typo when creating the concerned user – Abpostman1 Mar 22 '23 at 10:27