MySQL root授权报错ERROR 1045 (28000)

2021-12-20 23:14 abce 阅读(1935) 评论(0) 编辑收藏举报

创建用户后授权的时候报错，显示没有访问权限：

1 2	`mysql>` `grant` `select, process, super, replication client, reload` `on` `.` `to` `'abc'@'127.0.0.1';` `ERROR 1045 (28000): Access denied` `for` `user` `'root'@'%'` `(using` `password: YES)`

先看看系统中，有哪些数据库用户：

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| abc           | 127.0.0.1 |
| root          | %         |
+---------------+-----------+

这里的abc就是刚才创建的用户。

查看一下当前登录的用户信息：(status命令看到不一定是真的)

mysql> status;       
--------------
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.7.29, for linux-glibc2.12 (x86_64) using  EditLine wrapper
 
Connection id:          74969
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.29-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /tmp/mysql.sock
Uptime:                 49 days 4 hours 12 min 24 sec
 
Threads: 63  Questions: 325324823  Slow queries: 66  Opens: 34378  Flush tables: 99  Open tables: 476  Queries per second avg: 76.569
--------------
 
mysql> select user(), current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@%         |
+----------------+----------------+
1 row in set (0.00 sec)

user()表示当前的登录用户；current_user()表示对应于mysql.user表里对应的账号。

看下当前用户权限；

mysql> show grants;
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
 
mysql> 

可以看到root用户是没有with grant option权限的。正确的应该是这样的：

1	`GRANT` `ALL` `PRIVILEGES` `ON` `.` `TO` `'root'@'%'` `WITH` `GRANT` `OPTION`

修改方法：

mysql> select user,host,Grant_priv from mysql.user;
+---------------+-----------+------------+
| user          | host      | Grant_priv |
+---------------+-----------+------------+
| abc           | 127.0.0.1 | N          | 
| root          | %         | N          | 
+---------------+-----------+------------+
 
mysql> update mysql.user set Grant_priv="Y" where user="root" and host="%";
mysql> flush privileges;