SHOW GRANTS

On this page

Warning:

As of November 24, 2023, CockroachDB v22.1 is no longer supported. For more details, refer to the Release Support Policy.

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 the 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.
`is_grantable`	New in v22.1: `TRUE` if the grantee has the grant option on the object; `FALSE` if not.

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:

> SHOW GRANTS;

  database_name |    schema_name     |           relation_name           | grantee | privilege_type  | is_grantable
----------------+--------------------+-----------------------------------+---------+-----------------+--------------
  movr          | crdb_internal      | NULL                              | admin   | ALL             | true
  movr          | crdb_internal      | NULL                              | root    | ALL             | true
  movr          | crdb_internal      | backward_dependencies             | public  | SELECT          | false
  movr          | crdb_internal      | builtin_functions                 | public  | SELECT          | false
...
(365 rows)

Show a specific user or role's grants

> CREATE USER max WITH PASSWORD roach;

> GRANT ALL ON DATABASE movr TO max WITH GRANT OPTION;

> SHOW GRANTS FOR max;

  database_name |    schema_name     | relation_name | grantee | privilege_type  | is_grantable
----------------+--------------------+---------------+---------+-----------------+--------------
  movr          | crdb_internal      | NULL          | max     | ALL             | true
  movr          | information_schema | NULL          | max     | ALL             | true
  movr          | pg_catalog         | NULL          | max     | ALL             | true
  movr          | pg_extension       | NULL          | max     | ALL             | true
  movr          | public             | NULL          | max     | ALL             | true
(5 rows)

Show grants on databases

Specific database, all users and roles:

> SHOW GRANTS ON DATABASE movr;

  database_name |    schema_name     | grantee | privilege_type  | is_grantable
----------------+--------------------+---------+-----------------+--------------
  movr          | crdb_internal      | admin   | ALL             | true
  movr          | crdb_internal      | max     | ALL             | false
  movr          | crdb_internal      | root    | ALL             | true
  movr          | information_schema | admin   | ALL             | true
  movr          | information_schema | max     | ALL             | false
  movr          | information_schema | root    | ALL             | true
  movr          | pg_catalog         | admin   | ALL             | true
  movr          | pg_catalog         | max     | ALL             | false
  movr          | pg_catalog         | root    | ALL             | true
  movr          | pg_extension       | admin   | ALL             | true
  movr          | pg_extension       | max     | ALL             | false
  movr          | pg_extension       | root    | ALL             | true
  movr          | public             | admin   | ALL             | true
  movr          | public             | max     | ALL             | false
  movr          | public             | root    | ALL             | true
(15 rows)

Specific database, specific user or role:

> SHOW GRANTS ON DATABASE movr FOR max;

  database_name |    schema_name     | grantee | privilege_type  | is_grantable
----------------+--------------------+---------+-----------------+--------------
  movr          | crdb_internal      | max     | ALL             | false
  movr          | information_schema | max     | ALL             | false
  movr          | pg_catalog         | max     | ALL             | false
  movr          | pg_extension       | max     | ALL             | false
  movr          | public             | max     | ALL             | false
(5 rows)

Show grants on tables

> GRANT ALL ON TABLE users TO max WITH GRANT OPTION;

Specific table, all users and roles:

> SHOW GRANTS ON TABLE users;

  database_name | schema_name | table_name | grantee | privilege_type  | is_grantable
----------------+-------------+------------+---------+-----------------+---------------
  movr          | public      | users      | admin   | ALL             | true
  movr          | public      | users      | max     | ALL             | true
  movr          | public      | users      | root    | ALL             | true
(3 rows)

Specific table, specific role or user:

> SHOW GRANTS ON TABLE users FOR max;

  database_name | schema_name | table_name | grantee | privilege_type  | is_grantable
----------------+-------------+------------+---------+-----------------+---------------
  movr          | public      | users      | max     | ALL             | true
(1 row)

All tables, all users and roles:

> SHOW GRANTS ON TABLE *;

  database_name | schema_name |         table_name         | grantee | privilege_type  | is_grantable
----------------+-------------+----------------------------+---------+-----------------+---------------
  movr          | public      | promo_codes                | admin   | ALL             | true
  movr          | public      | promo_codes                | root    | ALL             | true
  movr          | public      | rides                      | admin   | ALL             | true
  movr          | public      | rides                      | root    | ALL             | true
  movr          | public      | user_promo_codes           | admin   | ALL             | true
  movr          | public      | user_promo_codes           | root    | ALL             | true
  movr          | public      | users                      | admin   | ALL             | true
  movr          | public      | users                      | max     | ALL             | true
  movr          | public      | users                      | root    | ALL             | true
  movr          | public      | vehicle_location_histories | admin   | ALL             | true
  movr          | public      | vehicle_location_histories | root    | ALL             | true
  movr          | public      | vehicles                   | admin   | ALL             | true
  movr          | public      | vehicles                   | root    | ALL             | true
(13 rows)

All tables, specific users or roles:

> SHOW GRANTS ON TABLE * FOR max;

  database_name | schema_name | table_name | grantee | privilege_type  | is_grantable
----------------+-------------+------------+---------+-----------------+---------------
  movr          | public      | users      | max     | ALL             | true
(1 row)

Show grants on schemas

> CREATE SCHEMA cockroach_labs;

> GRANT ALL ON SCHEMA cockroach_labs TO max WITH GRANT OPTION;

Specific schema, all users or roles:

> SHOW GRANTS ON SCHEMA cockroach_labs;

  database_name |  schema_name   | grantee | privilege_type  | is_grantable
----------------+----------------+---------+-----------------+---------------
  movr          | cockroach_labs | admin   | ALL             | true
  movr          | cockroach_labs | max     | ALL             | true
  movr          | cockroach_labs | root    | ALL             | true
(3 rows)

Specific schema, specific users or roles:

> SHOW GRANTS ON SCHEMA cockroach_labs FOR max;

  database_name |  schema_name   | grantee | privilege_type  | is_grantable
----------------+----------------+---------+-----------------+---------------
  movr          | cockroach_labs | max     | ALL             | true
(1 row)

Show grants on user-defined types

> CREATE TYPE status AS ENUM ('available', 'unavailable');

> GRANT ALL ON TYPE status TO max WITH GRANT OPTION;

Specific type, all users or roles:

> SHOW GRANTS ON TYPE status;

  database_name | schema_name | type_name | grantee | privilege_type  | is_grantable
----------------+-------------+-----------+---------+-----------------+---------------
  movr          | public      | status    | admin   | ALL             | true
  movr          | public      | status    | max     | ALL             | true
  movr          | public      | status    | public  | USAGE           | true
  movr          | public      | status    | root    | ALL             | true
(4 rows)

Specific type, specific users or roles:

> SHOW GRANTS ON TYPE status FOR max;

  database_name | schema_name | type_name | grantee | privilege_type  | is_grantable
----------------+-------------+-----------+---------+-----------------+---------------
  movr          | public      | status    | max     | ALL             | true
(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)

Cockroach
University

Docs Hub

SHOW GRANTS

Syntax

Show privilege grants

Show role grants

Parameters

Response

Privilege grants

Role grants

Required privileges

Examples

Show all grants

Show a specific user or role's grants

Show grants on databases

Show grants on tables

Show grants on schemas

Show grants on user-defined types

Show role memberships

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

SHOW GRANTS

Syntax

Show privilege grants

Show role grants

Parameters

Response

Privilege grants

Role grants

Required privileges

Examples

Show all grants

Show a specific user or role's grants

Show grants on databases

Show grants on tables

Show grants on schemas

Show grants on user-defined types

Show role memberships

See also

Cockroach
University

Cockroach
University