之前在查看用户权限的时候，发现我自己创建的用户都没有 grant_priv 权限
root@demo [(none)]> select user,host, grant_priv from mysql.user;
+------------------+-----------+------------+
| user | host | grant_priv |
+------------------+-----------+------------+
| dbsync | % | N |
| glue | % | N |
| root | % | N |
| test | % | N |
| mysql.infoschema | localhost | N |
| mysql.session | localhost | N |
| mysql.sys | localhost | N |
| root | localhost | Y |
+------------------+-----------+------------+
我们看到上面的root用户是没有权限的，我尝试重新授权
root@demo [(none)]> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.01 sec)
再去查询，发现权限不变
root@demo [(none)]> select user,host, grant_priv from mysql.user;
+------------------+-----------+------------+
| user | host | grant_priv |
+------------------+-----------+------------+
| dbsync | % | N |
| glue | % | N |
| root | % | N |
| test | % | N |
| mysql.infoschema | localhost | N |
| mysql.session | localhost | N |
| mysql.sys | localhost | N |
| root | localhost | Y |
+------------------+-----------+------------+
一开始有些困惑，我不是给全部权限了吗，怎么这个 root@% 还没有 grant 权限啊
原因是我们语句用错了，mysql8.0中 grant权限已经不能用 all privileges去包含啦。
正确的做法是 使用 with grant option:
root@demo [(none)]> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
root@demo [(none)]> select user,host, grant_priv from mysql.user;
+------------------+-----------+------------+
| user | host | grant_priv |
+------------------+-----------+------------+
| dbsync | % | N |
| glue | % | N |
| root | % | Y |
| test | % | N |
| mysql.infoschema | localhost | N |
| mysql.session | localhost | N |
| mysql.sys | localhost | N |
| root | localhost | Y |
+------------------+-----------+------------+
8 rows in set (0.00 sec)
经过上面的操作，我们发现 root@% 这个用户已经有grant 权限啦。
