SHOW GRANTS

On this page

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    |
+------------+---------+----------+

Pricing

Contact us

Sign In