§ 扩展显示授权
在 MySQL 的SHOW GRANTS 中，仅显示显式授予指定帐户的授权。该帐户可能具有其他授权，但不会显示。例如，如果存在匿名帐户，则指定帐户可能能够使用其授权，但 SHOW GRANTS 将不会显示它们。GreatSQL 提供 SHOW EFFECTIVE GRANTS 命令来显示帐户的所有有效可用授权，包括授予其他帐户的授权。
§ 例子
如果我们创建以下用户：
greatsql> CREATE USER grantee@localhost IDENTIFIED BY 'grantee1';
Query OK, 0 rows affected (0.06 sec)
1
2
2
以及创建grantee匿名用户
greatsql> CREATE USER grantee IDENTIFIED BY 'grantee2';
Query OK, 0 rows affected (0.06 sec)
1
2
2
再创建一个DATABASE
greatsql> CREATE DATABASE db2;
Query OK, 1 row affected (0.06 sec)
1
2
2
授权grantee用户
greatsql> GRANT ALL PRIVILEGES ON db2.* TO grantee WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
1
2
2
若只使用SHOW GRANTS查看不会显示用户的所有授权：
greatsql> SHOW GRANTS FOR 'grantee'@'localhost';
+---------------------------------------------+
| Grants for grantee@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost` |
+---------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
2
3
4
5
6
7
尽管未显示对 db2 数据库的授权，但 grantee@localhost 用户具有足够的授权在该数据库中创建表：
$ mysql -ugrantee -pgrantee1 -h localhost
greatsql> CREATE TABLE db2.t1(a int);
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
2
3
4
更改后 SHOW EFFECTIVE GRANTS 的输出显示了 grantee 用户的所有授权：
greatsql> SHOW EFFECTIVE GRANTS FOR grantee@localhost;
+----------------------------------------------------------------------------+
| Effective grants for grantee@localhost |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost` |
| GRANT ALL PRIVILEGES ON `db2`.* TO `grantee`@`localhost` WITH GRANT OPTION |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
greatsql> SHOW EFFECTIVE GRANTS FOR grantee;
+--------------------------------------------------------------------+
| Effective grants for grantee@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`%` |
| GRANT ALL PRIVILEGES ON `db2`.* TO `grantee`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
扫码关注微信公众号
