GreatSQL用户手册

§ 扩展显示授权

在 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

以及创建grantee匿名用户

greatsql> CREATE USER grantee IDENTIFIED BY 'grantee2';
Query OK, 0 rows affected (0.06 sec)
1
2

再创建一个DATABASE

greatsql> CREATE DATABASE db2;
Query OK, 1 row affected (0.06 sec)
1
2

授权grantee用户

greatsql> GRANT ALL PRIVILEGES ON db2.* TO grantee WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
1
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

尽管未显示对 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

更改后 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

greatsql-wx