SHOW GRANTS

On this page Carat arrow pointing down
Warning:
CockroachDB v21.1 is no longer supported as of November 18, 2022. For more details, refer to the Release Support Policy.

The SHOW GRANTS statement lists one of the following:

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 the SELECT 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:

icon/buttons/copy
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

icon/buttons/copy
CREATE USER max WITH PASSWORD roach;
icon/buttons/copy
GRANT ALL ON DATABASE movr TO max;
icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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

icon/buttons/copy
GRANT ALL ON TABLE users TO max;

Specific table, all users and roles:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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

icon/buttons/copy
CREATE SCHEMA cockroach_labs;
icon/buttons/copy
GRANT ALL ON SCHEMA cockroach_labs TO max;

Specific schema, all users or roles:

icon/buttons/copy
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:

icon/buttons/copy
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

icon/buttons/copy
CREATE TYPE status AS ENUM ('available', 'unavailable');
icon/buttons/copy
GRANT ALL ON TYPE status TO max;

Specific type, all users or roles:

icon/buttons/copy
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:

icon/buttons/copy
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

icon/buttons/copy
CREATE ROLE moderator;
icon/buttons/copy
GRANT moderator TO max;

All members of all roles:

icon/buttons/copy
SHOW GRANTS ON ROLE;
  role_name | member | is_admin
------------+--------+-----------
  admin     | root   |   true
  moderator | max    |  false
(2 rows)

Members of a specific role:

icon/buttons/copy
SHOW GRANTS ON ROLE moderator;
  role_name | member | is_admin
------------+--------+-----------
  moderator | max    |  false
(1 row)

Roles of a specific user or role:

icon/buttons/copy
SHOW GRANTS ON ROLE FOR max;
  role_name | member | is_admin
------------+--------+-----------
  moderator | max    |  false
(1 row)

See also


Yes No
On this page

Yes No