MySQL root授权报错ERROR 1045 (28000)

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

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权限的。正确的应该是这样的：

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;

　　

退出重新登录，再次授权即可。