☰

不允许使用 GRANT 创建用户

tag-icon • tag-icon mysql
不允许使用 GRANT 创建用户

我尝试使用链接在 ubuntu 上安装 wordpress，结果 https://ubuntu.com/tutorials/install-and-configure-wordpress#4-configure-database

当我运行此命令时，出现错误-

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
    -> ON wordpress.*
    -> TO wordpress@localhost
    -> IDENTIFIED BY 'root';


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'root'' at line 1

我尝试了几乎所有的语法，通过wordpress@localhost改变'wordpress'@'localhost'

我尝试删除并添加撇'root'号IDENTIFIED BY 'root'，

然后搜索后我也尝试了这个命令 - GRANT ALL PRIVILEGES ON *.* TO 'wordpress'@'localhost' IDENTIFIED BY 'root'并得到了

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

我没有找到任何有用的信息。请帮我看看我哪里出错了。谢谢

PS 数据库已经存在 -

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.01 sec)

答案1

授予权限时不需要包含该IDENTIFIED BY位。如果您需要创建用户和授予权限，这是两个操作：

mysql> CREATE USER 'wordpress'@'localhost' IDENTIFIED WITH mysql_native_password BY '{superSecretPassword!123}';
mysql> GRANT ALL ON `wordpress`.* TO 'wordpress'@'localhost';

确保将该superSecretPassword!123位更改为更好的位。

答案2

  • Ubuntu 22.04.1
  • Mysql 版本 8.0.31-0

我的 root 没有 GRANT 权限，因此无法授予新用户任何权限。解决方案是删除当前 root 用户并使用“mysql_native_password”创建新用户。

命令如下以root身份登录mysql

mysql> DROP USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD' FROM mysql.user;
mysql> CREATE USER 'root'@'%localhost' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

答案3

这是一个较短的选项：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

相关内容