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

学习mysql授权,遇到问题:

mysql> grant select on test.* to user6;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

查到网上解决办法,很多是update user set host='%' ,,

也能体验到授权成功的感觉,感谢他们的分享.

mysql> update user set host='%' where user='user6';    更改主机
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;    //刷新
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on test.* to user6;  //授权成功
Query OK, 0 rows affected (0.00 sec)

但初学者感觉没有讲透问题的产生根源,难道其它主机就不行么?--怎么可能不行!

实质问题是,授权语法不严谨导致.所授权的用户及其所在主机名,要与实际存在的用户及其主机名一致.

也就是说,如果已建用户是:user22@localhost,你要授权给user22@%,那肯定不行.必须一样才行.

举例说明:

mysql> create user user11 identified by '123';   //新建用户时没有指定主机,则默认为%
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on test.* to user11;    //授权时没有指定主机,也是默认为%,所以可以
Query OK, 0 rows affected (0.00 sec)

建用户时用的啥,授权时要指定啥:
mysql> create user user12@localhost identified by '123';  //建用户时指定用户名及主机
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on test.* to user12@localhost;    //授权时也要指明用户名和主机
Query OK, 0 rows affected (0.00 sec)

ok 

---

另可参看https://www.jianshu.com/p/98a6d42e28c8

欢迎留言,共同探讨/进步.