Displays grants for a user, role, or object. The object can be a database, a schema, a table, or a function.
Syntax
SHOW GRANTS ON {DATABASE database_name | FUNCTION function_name | SCHEMA schema_name | TABLE table_name} [FOR {username | ROLE role_name | PUBLIC}] [LIMIT row_limit]
Parameters
- database_name
-
The name of the database to show grants on.
- function_name
-
The name of the function to show grants on.
- schema_name
-
The name of the schema to show grants on.
- table_name
-
The name of the table to show grants on.
- FOR username
-
Indicates showing grants for a user.
- FOR ROLE role_name
-
Indicates showing grants for a role.
- FOR PUBLIC
-
Indicates showing grants for PUBLIC.
- row_limit
-
The maximum number of rows to return. The row_limit can be 0–10,000.
Examples
The following example displays all grants on a database named
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)The following command shows all grants on a schema named 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)The following command shows all grants for a user named 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)