# SHOW GRANTS

The `SHOW GRANTS` statement lists the privileges granted to users.

## Synopsis

## 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.

## Parameters

Parameter | Description
--- | ---
`role_name` | A comma-separated list of role names.
`table_name` | A comma-separated list of table names. Alternately, to list privileges for all tables, use `*`.
`database_name` | A comma-separated list of database names.
`user_name` | An optional, comma-separated list of grantees.

## Examples

### Show all grants

To list all grants for all users and roles on all databases and tables:

    > SHOW GRANTS;

    +---------------+--------------------+-----------------------------------+---------+----------------+
    | database_name |    schema_name     |            table_name             | grantee | privilege_type |
    +---------------+--------------------+-----------------------------------+---------+----------------+
    | defaultdb     | crdb_internal      | NULL                              | admin   | ALL            |
    | defaultdb     | crdb_internal      | NULL                              | root    | ALL            |
    | defaultdb     | crdb_internal      | backward_dependencies             | public  | SELECT         |
    | defaultdb     | crdb_internal      | builtin_functions                 | public  | SELECT         |
    | defaultdb     | crdb_internal      | cluster_queries                   | public  | SELECT         |
    ...
    +---------------+--------------------+-----------------------------------+---------+----------------+
    (167 rows)

### Show a specific user or role's grants

    > SHOW GRANTS FOR maxroach;

    +---------------+--------------------+-----------------------------------+----------+----------------+
    | database_name |    schema_name     |            table_name             | grantee  | privilege_type |
    +---------------+--------------------+-----------------------------------+----------+----------------+
    | test_roles    | crdb_internal      | NULL                              | maxroach | DELETE         |
    | test_roles    | information_schema | NULL                              | maxroach | DELETE         |
    | test_roles    | pg_catalog         | NULL                              | maxroach | DELETE         |
    | test_roles    | public             | NULL                              | maxroach | DELETE         |
    +---------------+--------------------+-----------------------------------+----------+----------------+

### Show grants on databases

Specific database, all users and roles:

    > SHOW GRANTS ON DATABASE test;

    +---------------+--------------------+----------+----------------+
    | database_name |    schema_name     | grantee  | privilege_type |
    +---------------+--------------------+----------+----------------+
    | test          | crdb_internal      | admin    | ALL            |
    | test          | crdb_internal      | maxroach | CREATE         |
    | test          | crdb_internal      | root     | ALL            |
    | test          | information_schema | admin    | ALL            |
    | test          | information_schema | maxroach | CREATE         |
    | test          | information_schema | root     | ALL            |
    | test          | pg_catalog         | admin    | ALL            |
    | test          | pg_catalog         | maxroach | CREATE         |
    | test          | pg_catalog         | root     | ALL            |
    | test          | public             | admin    | ALL            |
    | test          | public             | maxroach | CREATE         |
    | test          | public             | root     | ALL            |
    +---------------+--------------------+----------+----------------+
    (12 rows)

Specific database, specific user or role:

    > SHOW GRANTS ON DATABASE test FOR maxroach;

    +---------------+--------------------+----------+----------------+
    | database_name |    schema_name     | grantee  | privilege_type |
    +---------------+--------------------+----------+----------------+
    | test          | crdb_internal      | maxroach | CREATE         |
    | test          | information_schema | maxroach | CREATE         |
    | test          | pg_catalog         | maxroach | CREATE         |
    | test          | public             | maxroach | CREATE         |
    +---------------+--------------------+----------+----------------+
    (4 rows)

### Show grants on tables

Specific tables, all users and roles:

    > SHOW GRANTS ON TABLE test.t1;

    +---------------+-------------+------------+----------+----------------+
    | database_name | schema_name | table_name | grantee  | privilege_type |
    +---------------+-------------+------------+----------+----------------+
    | test          | public      | t1         | admin    | ALL            |
    | test          | public      | t1         | maxroach | CREATE         |
    | test          | public      | t1         | root     | ALL            |
    +---------------+-------------+------------+----------+----------------+
    (3 rows)

Specific tables, specific role or user:

    > SHOW GRANTS ON TABLE test.t1 FOR maxroach;

    +---------------+-------------+------------+----------+----------------+
    | database_name | schema_name | table_name | grantee  | privilege_type |
    +---------------+-------------+------------+----------+----------------+
    | test          | public      | t1         | maxroach | CREATE         |
    +---------------+-------------+------------+----------+----------------+
    (1 row)

All tables, all users and roles:

    > SHOW GRANTS ON TABLE test.*;

    +---------------+-------------+------------+----------+----------------+
    | database_name | schema_name | table_name | grantee  | privilege_type |
    +---------------+-------------+------------+----------+----------------+
    | test          | public      | t1         | admin    | ALL            |
    | test          | public      | t1         | maxroach | CREATE         |
    | test          | public      | t1         | root     | ALL            |
    +---------------+-------------+------------+----------+----------------+
    (3 rows)

All tables, specific users or roles:

    > SHOW GRANTS ON TABLE test.* FOR maxroach;

    +---------------+-------------+------------+----------+----------------+
    | database_name | schema_name | table_name | grantee  | privilege_type |
    +---------------+-------------+------------+----------+----------------+
    | test          | public      | t1         | maxroach | CREATE         |
    +---------------+-------------+------------+----------+----------------+
    (1 row)

### Show role memberships

All members of all roles:

    SHOW GRANTS ON ROLE;

    +------------+---------+----------+
    | role_name  | member  | is_admin |
    +------------+---------+----------+
    | admin      | root    | true     |
    | design     | ernie   | false    |
    | design     | lola    | false    |
    | dev        | barkley | false    |
    | dev        | carl    | false    |
    | docs       | carl    | false    |
    | hr         | finance | false    |
    | hr         | lucky   | false    |
    +------------+---------+----------+

Members of a specific role:

    SHOW GRANTS ON ROLE design;

    +------------+---------+----------+
    | role_name  | member  | is_admin |
    +------------+---------+----------+
    | design     | ernie   | false    |
    | design     | lola    | false    |
    +------------+---------+----------+

Roles of a specific user or role:

    SHOW GRANTS ON ROLE FOR carl;

    +------------+---------+----------+
    | role_name  | member  | is_admin |
    +------------+---------+----------+
    | dev        | carl    | false    |
    | docs       | carl    | false    |
    +------------+---------+----------+

## See also

- `CREATE ROLE`
- `DROP ROLE`
- `SHOW ROLES`
- `GRANT <privileges>`
- `GRANT <roles>` (Enterprise)
- `REVOKE <privileges>`
- `REVOKE <roles>` (Enterprise)
- `SHOW GRANTS`
- Manage Users
- Manage Roles
- Authorization
- Other Cockroach Commands
- Information Schema