The SHOW GRANTS
statement lists one of the following:
- The roles granted to users in a cluster.
- The privileges granted to users on databases, schemas, tables, or user-defined types.
Syntax
Show privilege grants
Use the following syntax to show the privileges granted to users on database objects:
SHOW GRANTS [ON [DATABASE | SCHEMA | TABLE | TYPE] <targets...>] [FOR <users...>]
When DATABASE
is omitted, the schema, tables, and types in the current database are listed.
Show role grants
Use the following syntax to show the role grants for users in a cluster.
SHOW GRANTS ON ROLE [<roles...>] [FOR <users...>]
Parameters
Parameter | Description |
---|---|
targets |
A comma-separated list of database, schema, table, or user-defined type names. Note: To list the privilege grants for all tables in the current database, you can use SHOW GRANTS ON TABLE * . |
users |
A comma-separated list of the users whose privileges or roles you want to show. |
roles |
A comma-separated list of the roles whose grants you want to show. |
Response
Privilege grants
The SHOW GRANTS ON [DATABASE | SCHEMA | TABLE | TYPE]
statement can return the following fields, depending on the target object specified:
Field | Description |
---|---|
database_name |
The name of the database. |
schema_name |
The name of the schema. |
table_name |
The name of the table. |
type_name |
The name of the user-defined type. |
grantee |
The name of the user or role that was granted the privilege. |
privilege_type |
The name of the privilege. |
Role grants
The SHOW GRANTS ON ROLE
statement returns the following fields:
Field | Description |
---|---|
role_name |
The name of the role. |
member |
The users in the role. |
is_admin |
If true , the role is an admin role. |
Required privileges
No privileges are required to view privileges granted to users.
For
SHOW GRANTS ON ROLES
, the user must have theSELECT
privilege on the system table.
Examples
Show all grants
To list all grants for all users and roles on the current database and its tables:
> SHOW GRANTS;
database_name | schema_name | relation_name | grantee | privilege_type
----------------+--------------------+-----------------------------------+---------+-----------------
movr | crdb_internal | NULL | admin | ALL
movr | crdb_internal | NULL | root | ALL
movr | crdb_internal | backward_dependencies | public | SELECT
movr | crdb_internal | builtin_functions | public | SELECT
...
(365 rows)
Show a specific user or role's grants
> CREATE USER max WITH PASSWORD roach;
> GRANT ALL ON DATABASE movr TO max;
> SHOW GRANTS FOR max;
database_name | schema_name | relation_name | grantee | privilege_type
----------------+--------------------+---------------+---------+-----------------
movr | crdb_internal | NULL | max | ALL
movr | information_schema | NULL | max | ALL
movr | pg_catalog | NULL | max | ALL
movr | pg_extension | NULL | max | ALL
movr | public | NULL | max | ALL
(5 rows)
Show grants on databases
Specific database, all users and roles:
> SHOW GRANTS ON DATABASE movr;
database_name | schema_name | grantee | privilege_type
----------------+--------------------+---------+-----------------
movr | crdb_internal | admin | ALL
movr | crdb_internal | max | ALL
movr | crdb_internal | root | ALL
movr | information_schema | admin | ALL
movr | information_schema | max | ALL
movr | information_schema | root | ALL
movr | pg_catalog | admin | ALL
movr | pg_catalog | max | ALL
movr | pg_catalog | root | ALL
movr | pg_extension | admin | ALL
movr | pg_extension | max | ALL
movr | pg_extension | root | ALL
movr | public | admin | ALL
movr | public | max | ALL
movr | public | root | ALL
(15 rows)
Specific database, specific user or role:
> SHOW GRANTS ON DATABASE movr FOR max;
database_name | schema_name | grantee | privilege_type
----------------+--------------------+---------+-----------------
movr | crdb_internal | max | ALL
movr | information_schema | max | ALL
movr | pg_catalog | max | ALL
movr | pg_extension | max | ALL
movr | public | max | ALL
(5 rows)
Show grants on tables
> GRANT ALL ON TABLE users TO max;
Specific table, all users and roles:
> SHOW GRANTS ON TABLE users;
database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
movr | public | users | admin | ALL
movr | public | users | max | ALL
movr | public | users | root | ALL
(3 rows)
Specific table, specific role or user:
> SHOW GRANTS ON TABLE users FOR max;
database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
movr | public | users | max | ALL
(1 row)
All tables, all users and roles:
> SHOW GRANTS ON TABLE *;
database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+----------------------------+---------+-----------------
movr | public | promo_codes | admin | ALL
movr | public | promo_codes | root | ALL
movr | public | rides | admin | ALL
movr | public | rides | root | ALL
movr | public | user_promo_codes | admin | ALL
movr | public | user_promo_codes | root | ALL
movr | public | users | admin | ALL
movr | public | users | max | ALL
movr | public | users | root | ALL
movr | public | vehicle_location_histories | admin | ALL
movr | public | vehicle_location_histories | root | ALL
movr | public | vehicles | admin | ALL
movr | public | vehicles | root | ALL
(13 rows)
All tables, specific users or roles:
> SHOW GRANTS ON TABLE * FOR max;
database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
movr | public | users | max | ALL
(1 row)
Show grants on schemas
> CREATE SCHEMA cockroach_labs;
> GRANT ALL ON SCHEMA cockroach_labs TO max;
Specific schema, all users or roles:
> SHOW GRANTS ON SCHEMA cockroach_labs;
database_name | schema_name | grantee | privilege_type
----------------+----------------+---------+-----------------
movr | cockroach_labs | admin | ALL
movr | cockroach_labs | max | ALL
movr | cockroach_labs | root | ALL
(3 rows)
Specific schema, specific users or roles:
> SHOW GRANTS ON SCHEMA cockroach_labs FOR max;
database_name | schema_name | grantee | privilege_type
----------------+----------------+---------+-----------------
movr | cockroach_labs | max | ALL
(1 row)
Show grants on user-defined types
> CREATE TYPE status AS ENUM ('available', 'unavailable');
> GRANT ALL ON TYPE status TO max;
Specific type, all users or roles:
> SHOW GRANTS ON TYPE status;
database_name | schema_name | type_name | grantee | privilege_type
----------------+-------------+-----------+---------+-----------------
movr | public | status | admin | ALL
movr | public | status | max | ALL
movr | public | status | public | USAGE
movr | public | status | root | ALL
(4 rows)
Specific type, specific users or roles:
> SHOW GRANTS ON TYPE status FOR max;
database_name | schema_name | type_name | grantee | privilege_type
----------------+-------------+-----------+---------+-----------------
movr | public | status | max | ALL
(1 row)
Show role memberships
> CREATE ROLE moderator;
> GRANT moderator TO max;
All members of all roles:
> SHOW GRANTS ON ROLE;
role_name | member | is_admin
------------+--------+-----------
admin | root | true
moderator | max | false
(2 rows)
Members of a specific role:
> SHOW GRANTS ON ROLE moderator;
role_name | member | is_admin
------------+--------+-----------
moderator | max | false
(1 row)
Roles of a specific user or role:
> SHOW GRANTS ON ROLE FOR max;
role_name | member | is_admin
------------+--------+-----------
moderator | max | false
(1 row)