SHOW GRANTS 语句 用于列出授予用户的 权限

概要

图片 图片

所需权限

权限 要求。 但是,对于 SHOW GRANTS ON ROLES, 用户需要系统表的 SELECT 权限

参数

Parameter Description
role_name 多个由逗号隔开的role名字
table_name 多个由逗号隔开的表名。如果想显示所有表的权限,使用 *
database_name 多个由逗号隔开的数据库名字
user_name 可选项,多个由逗号隔开的授权用户名

例子

显示所有权限 New in v2.0

显示所有用户和角色在所有数据库与表上的权限:

> SHOW GRANTS;
+------------+--------------------+------------------+------------+------------+
|  Database  |       Schema       |      Table       |    User    | Privileges |
+------------+--------------------+------------------+------------+------------+
| system     | crdb_internal      | NULL             | admin      | GRANT      |
| system     | crdb_internal      | NULL             | admin      | SELECT     |
| system     | crdb_internal      | NULL             | root       | GRANT      |
...
| test_roles | public             | employees        | system_ops | CREATE     |
+------------+--------------------+------------------+------------+------------+
(167 rows)

显示特定的一个用户或角色的权限New in v2.0

> SHOW GRANTS FOR maxroach;
+------------+--------------------+-------+----------+------------+
|  Database  |       Schema       | Table |   User   | Privileges |
+------------+--------------------+-------+----------+------------+
| test_roles | crdb_internal      | NULL  | maxroach | DELETE     |
| test_roles | information_schema | NULL  | maxroach | DELETE     |
| test_roles | pg_catalog         | NULL  | maxroach | DELETE     |
| test_roles | public             | NULL  | maxroach | DELETE     |
+------------+--------------------+-------+----------+------------+

显示一个数据库上的权限

所有用户与角色在特定的数据库的权限:

> SHOW GRANTS ON DATABASE db2:
+----------+--------------------+------------+------------+
| Database |       Schema       |    User    | Privileges |
+----------+--------------------+------------+------------+
| db2      | crdb_internal      | admin      | ALL        |
| db2      | crdb_internal      | betsyroach | CREATE     |
| db2      | crdb_internal      | root       | ALL        |
| db2      | information_schema | admin      | ALL        |
| db2      | information_schema | betsyroach | CREATE     |
| db2      | information_schema | root       | ALL        |
| db2      | pg_catalog         | admin      | ALL        |
| db2      | pg_catalog         | betsyroach | CREATE     |
| db2      | pg_catalog         | root       | ALL        |
| db2      | public             | admin      | ALL        |
| db2      | public             | betsyroach | CREATE     |
| db2      | public             | root       | ALL        |
+----------+--------------------+------------+------------+

特定的用户与角色在具体数据库上的权限:

> SHOW GRANTS ON DATABASE db2 FOR betsyroach;
+----------+--------------------+------------+------------+
| Database |       Schema       |    User    | Privileges |
+----------+--------------------+------------+------------+
| db2      | crdb_internal      | betsyroach | CREATE     |
| db2      | information_schema | betsyroach | CREATE     |
| db2      | pg_catalog         | betsyroach | CREATE     |
| db2      | public             | betsyroach | CREATE     |
+----------+--------------------+------------+------------+

显示表上的权限

所有用户与角色在特定表上的权限:

> SHOW GRANTS ON TABLE test_roles.employees;
+------------+--------+-----------+------------+------------+
|  Database  | Schema |   Table   |    User    | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | admin      | ALL        |
| test_roles | public | employees | root       | ALL        |
| test_roles | public | employees | system_ops | CREATE     |
+------------+--------+-----------+------------+------------+

特定的用户或角色在具体表上的权限:

> SHOW GRANTS ON TABLE test_roles.employees FOR system_ops;
+------------+--------+-----------+------------+------------+
|  Database  | Schema |   Table   |    User    | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | system_ops | CREATE     |
+------------+--------+-----------+------------+------------+

所有用户与角色在所有表上的权限:

> SHOW GRANTS ON TABLE test_roles.*;
+------------+--------+-----------+------------+------------+
|  Database  | Schema |   Table   |    User    | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | admin      | ALL        |
| test_roles | public | employees | root       | ALL        |
| test_roles | public | employees | system_ops | CREATE     |
+------------+--------+-----------+------------+------------+

特定用户在所有表上是权限:

> SHOW GRANTS ON TABLE test_roles.* FOR system_ops;
+------------+--------+-----------+------------+------------+
|  Database  | Schema |   Table   |    User    | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | system_ops | CREATE     |
+------------+--------+-----------+------------+------------+

查看角色中用户 New in v2.0

查看所有角色里的所有用户:

SHOW GRANTS ON ROLE;
+--------+---------+---------+
|  role  | member  | isAdmin |
+--------+---------+---------+
| admin  | root    | true    |
| design | ernie   | false   |
| design | lola    | false   |
| dev    | barkley | false   |
| dev    | carl    | false   |
| docs   | carl    | false   |
| hr     | finance | false   |
| hr     | lucky   | false   |
+--------+---------+---------+

查看一个角色里的用户:

SHOW GRANTS ON ROLE design;
+--------+--------+---------+
|  role  | member | isAdmin |
+--------+--------+---------+
| design | ernie  | false   |
| design | lola   | false   |
+--------+--------+---------+

查看用户所在的所有角色

SHOW GRANTS ON ROLE FOR carl;
+------+--------+---------+
| role | member | isAdmin |
+------+--------+---------+
| dev  | carl   | false   |
| docs | carl   | false   |
+------+--------+---------+

See Also