SHOW GRANTS
显示用户、角色或对象的授权。对象可以是数据库、架构、表或函数。
语法
SHOW GRANTS ON {DATABASE database_name | FUNCTION function_name | SCHEMA schema_name | TABLE table_name} [FOR {username | ROLE role_name | PUBLIC}] [LIMIT row_limit]
参数
- database_name
-
要显示其授权的数据库的名称。
- function_name
-
要显示其授权的函数的名称。
- schema_name
-
要显示其授权的架构的名称。
- table_name
-
要显示其授权的表的名称。
- FOR username
-
指示显示用户的授权。
- FOR ROLE role_name
-
指示显示角色的授权。
- FOR PUBLIC
-
指示显示 PUBLIC 的授权。
- row_limit
-
要返回的最大行数。row_limit 可以是 0–10000。
示例
以下示例显示对名为 dev 的数据库的所有授权。
SHOW GRANTS ON DATABASE dev;
database_name | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope
---------------+----------------+-------------+---------------+---------------+--------------+-----------------
dev | TRUNCATE | 101 | alice | user | f | TABLES
dev | DROP | 101 | alice | user | f | TABLES
dev | INSERT | 101 | alice | user | f | TABLES
dev | ALTER | 101 | alice | user | f | TABLES
dev | TEMP | 0 | public | public | f | DATABASE
dev | DELETE | 101 | alice | user | f | TABLES
dev | SELECT | 101 | alice | user | f | TABLES
dev | UPDATE | 101 | alice | user | f | TABLES
dev | REFERENCES | 101 | alice | user | f | TABLES
(9 rows)以下命令显示对名为 demo 的架构的所有授权。
SHOW GRANTS ON SCHEMA demo;
schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | admin_option | privilege_scope
-------------+-------------+-------------+----------------+-------------+---------------+---------------+--------------+-----------------
demo | demo | SCHEMA | ALTER | 101 | alice | user | f | SCHEMA
demo | demo | SCHEMA | DROP | 101 | alice | user | f | SCHEMA
demo | demo | SCHEMA | USAGE | 101 | alice | user | f | SCHEMA
demo | demo | SCHEMA | CREATE | 101 | alice | user | f | SCHEMA
(4 rows)以下命令显示名为 alice 的用户的所有授权。
SHOW GRANTS FOR alice;
database_name | schema_name | object_name | object_type | privilege_type | identity_id | identity_name | identity_type | privilege_scope
---------------+-------------+-------------+-------------+----------------+-------------+---------------+---------------+-----------------
dev | | | DATABASE | INSERT | 101 | alice | user | TABLES
dev | | | DATABASE | SELECT | 101 | alice | user | TABLES
dev | | | DATABASE | UPDATE | 101 | alice | user | TABLES
dev | | | DATABASE | DELETE | 101 | alice | user | TABLES
dev | | | DATABASE | REFERENCES | 101 | alice | user | TABLES
dev | | | DATABASE | DROP | 101 | alice | user | TABLES
dev | | | DATABASE | TRUNCATE | 101 | alice | user | TABLES
dev | | | DATABASE | ALTER | 101 | alice | user | TABLES
dev | public | t1 | TABLE | INSERT | 101 | alice | user | TABLE
dev | public | t1 | TABLE | SELECT | 101 | alice | user | TABLE
dev | public | t1 | TABLE | UPDATE | 101 | alice | user | TABLE
dev | public | t1 | TABLE | DELETE | 101 | alice | user | TABLE
dev | public | t1 | TABLE | RULE | 101 | alice | user | TABLE
dev | public | t1 | TABLE | REFERENCES | 101 | alice | user | TABLE
dev | public | t1 | TABLE | TRIGGER | 101 | alice | user | TABLE
dev | public | t1 | TABLE | DROP | 101 | alice | user | TABLE
dev | public | t1 | TABLE | TRUNCATE | 101 | alice | user | TABLE
dev | public | t1 | TABLE | ALTER | 101 | alice | user | TABLE
dev | demo | | SCHEMA | USAGE | 101 | alice | user | SCHEMA
dev | demo | | SCHEMA | CREATE | 101 | alice | user | SCHEMA
dev | demo | | SCHEMA | DROP | 101 | alice | user | SCHEMA
dev | demo | | SCHEMA | ALTER | 101 | alice | user | SCHEMA
(22 rows)