SHOW GRANTS

The SHOW GRANTS statement lists the privileges granted to users.

Synopsis

SHOWGRANTSONROLErole_name,TABLEtable_name,DATABASEdatabase_name,FORuser_name,

Required privileges

No privileges are required to view privileges granted to users. For SHOW GRANTS ON ROLES, the user must have the SELECTprivilege on the system table.

Parameters

ParameterDescription
role_nameA comma-separated list of role names.
table_nameA comma-separated list of table names. Alternately, to list privileges for all tables, use *.
database_nameA comma-separated list of database names.
user_nameAn optional, comma-separated list of grantees.

Examples

Show all grants

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

  1. > SHOW GRANTS;
  1. +---------------+--------------------+-----------------------------------+---------+----------------+
  2. | database_name |    schema_name     |            table_name             | grantee | privilege_type |
  3. +---------------+--------------------+-----------------------------------+---------+----------------+
  4. | defaultdb     | crdb_internal      | NULL                              | admin   | ALL            |
  5. | defaultdb     | crdb_internal      | NULL                              | root    | ALL            |
  6. | defaultdb     | crdb_internal      | backward_dependencies             | public  | SELECT         |
  7. | defaultdb     | crdb_internal      | builtin_functions                 | public  | SELECT         |
  8. | defaultdb     | crdb_internal      | cluster_queries                   | public  | SELECT         |
  9. ...
  10. +---------------+--------------------+-----------------------------------+---------+----------------+
  11. (167 rows)

Show a specific user or role's grants

  1. > SHOW GRANTS FOR maxroach;
  1. +---------------+--------------------+-----------------------------------+----------+----------------+
  2. | database_name |    schema_name     |            table_name             | grantee  | privilege_type |
  3. +---------------+--------------------+-----------------------------------+----------+----------------+
  4. | test_roles    | crdb_internal      | NULL                              | maxroach | DELETE         |
  5. | test_roles    | information_schema | NULL                              | maxroach | DELETE         |
  6. | test_roles    | pg_catalog         | NULL                              | maxroach | DELETE         |
  7. | test_roles    | public             | NULL                              | maxroach | DELETE         |
  8. +---------------+--------------------+-----------------------------------+----------+----------------+

Show grants on databases

Specific database, all users and roles:

  1. > SHOW GRANTS ON DATABASE test;
  1. +---------------+--------------------+----------+----------------+
  2. | database_name |    schema_name     | grantee  | privilege_type |
  3. +---------------+--------------------+----------+----------------+
  4. | test          | crdb_internal      | admin    | ALL            |
  5. | test          | crdb_internal      | maxroach | CREATE         |
  6. | test          | crdb_internal      | root     | ALL            |
  7. | test          | information_schema | admin    | ALL            |
  8. | test          | information_schema | maxroach | CREATE         |
  9. | test          | information_schema | root     | ALL            |
  10. | test          | pg_catalog         | admin    | ALL            |
  11. | test          | pg_catalog         | maxroach | CREATE         |
  12. | test          | pg_catalog         | root     | ALL            |
  13. | test          | public             | admin    | ALL            |
  14. | test          | public             | maxroach | CREATE         |
  15. | test          | public             | root     | ALL            |
  16. +---------------+--------------------+----------+----------------+
  17. (12 rows)

Specific database, specific user or role:

  1. > SHOW GRANTS ON DATABASE test FOR maxroach;
  1. +---------------+--------------------+----------+----------------+
  2. | database_name |    schema_name     | grantee  | privilege_type |
  3. +---------------+--------------------+----------+----------------+
  4. | test          | crdb_internal      | maxroach | CREATE         |
  5. | test          | information_schema | maxroach | CREATE         |
  6. | test          | pg_catalog         | maxroach | CREATE         |
  7. | test          | public             | maxroach | CREATE         |
  8. +---------------+--------------------+----------+----------------+
  9. (4 rows)

Show grants on tables

Specific tables, all users and roles:

  1. > SHOW GRANTS ON TABLE test.t1;
  1. +---------------+-------------+------------+----------+----------------+
  2. | database_name | schema_name | table_name | grantee  | privilege_type |
  3. +---------------+-------------+------------+----------+----------------+
  4. | test          | public      | t1         | admin    | ALL            |
  5. | test          | public      | t1         | maxroach | CREATE         |
  6. | test          | public      | t1         | root     | ALL            |
  7. +---------------+-------------+------------+----------+----------------+
  8. (3 rows)

Specific tables, specific role or user:

  1. > SHOW GRANTS ON TABLE test.t1 FOR maxroach;
  1. +---------------+-------------+------------+----------+----------------+
  2. | database_name | schema_name | table_name | grantee  | privilege_type |
  3. +---------------+-------------+------------+----------+----------------+
  4. | test          | public      | t1         | maxroach | CREATE         |
  5. +---------------+-------------+------------+----------+----------------+
  6. (1 row)

All tables, all users and roles:

  1. > SHOW GRANTS ON TABLE test.*;
  1. +---------------+-------------+------------+----------+----------------+
  2. | database_name | schema_name | table_name | grantee  | privilege_type |
  3. +---------------+-------------+------------+----------+----------------+
  4. | test          | public      | t1         | admin    | ALL            |
  5. | test          | public      | t1         | maxroach | CREATE         |
  6. | test          | public      | t1         | root     | ALL            |
  7. +---------------+-------------+------------+----------+----------------+
  8. (3 rows)

All tables, specific users or roles:

  1. > SHOW GRANTS ON TABLE test.* FOR maxroach;
  1. +---------------+-------------+------------+----------+----------------+
  2. | database_name | schema_name | table_name | grantee  | privilege_type |
  3. +---------------+-------------+------------+----------+----------------+
  4. | test          | public      | t1         | maxroach | CREATE         |
  5. +---------------+-------------+------------+----------+----------------+
  6. (1 row)

Show role memberships

All members of all roles:

  1. SHOW GRANTS ON ROLE;
  1. +------------+---------+----------+
  2. | role_name  | member  | is_admin |
  3. +------------+---------+----------+
  4. | admin      | root    | true     |
  5. | design     | ernie   | false    |
  6. | design     | lola    | false    |
  7. | dev        | barkley | false    |
  8. | dev        | carl    | false    |
  9. | docs       | carl    | false    |
  10. | hr         | finance | false    |
  11. | hr         | lucky   | false    |
  12. +------------+---------+----------+

Members of a specific role:

  1. SHOW GRANTS ON ROLE design;
  1. +------------+---------+----------+
  2. | role_name  | member  | is_admin |
  3. +------------+---------+----------+
  4. | design     | ernie   | false    |
  5. | design     | lola    | false    |
  6. +------------+---------+----------+

Roles of a specific user or role:

  1. SHOW GRANTS ON ROLE FOR carl;
  1. +------------+---------+----------+
  2. | role_name  | member  | is_admin |
  3. +------------+---------+----------+
  4. | dev        | carl    | false    |
  5. | docs       | carl    | false    |
  6. +------------+---------+----------+

See also

Was this page helpful?
YesNo