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

古城微笑少年丶 2022-12-23 00:43 125阅读 0赞

学习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)

--------------------

另可参看[https://www.jianshu.com/p/98a6d42e28c8][https_www.jianshu.com_p_98a6d42e28c8]

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

[https_www.jianshu.com_p_98a6d42e28c8]: https://www.jianshu.com/p/98a6d42e28c8