On this page
Warning:
As of October 4, 2019, CockroachDB v2.0 is no longer supported. For more details, refer to the Release Support Policy.
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 New in v2.0
To list all grants for all users and roles on all databases and tables:
> SHOW GRANTS;
+------------+--------------------+------------------+------------+------------+
| Database | Schema | Table | User | Privileges |
+------------+--------------------+------------------+------------+------------+
| system | crdb_internal | NULL | admin | GRANT |
| system | crdb_internal | NULL | admin | SELECT |
| system | crdb_internal | NULL | root | GRANT |
...
| test_roles | public | employees | system_ops | CREATE |
+------------+--------------------+------------------+------------+------------+
(167 rows)
Show a Specific User or Role's Grants New in v2.0
> SHOW GRANTS FOR maxroach;
+------------+--------------------+-------+----------+------------+
| Database | Schema | Table | User | Privileges |
+------------+--------------------+-------+----------+------------+
| 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 db2:
+----------+--------------------+------------+------------+
| Database | Schema | User | Privileges |
+----------+--------------------+------------+------------+
| db2 | crdb_internal | admin | ALL |
| db2 | crdb_internal | betsyroach | CREATE |
| db2 | crdb_internal | root | ALL |
| db2 | information_schema | admin | ALL |
| db2 | information_schema | betsyroach | CREATE |
| db2 | information_schema | root | ALL |
| db2 | pg_catalog | admin | ALL |
| db2 | pg_catalog | betsyroach | CREATE |
| db2 | pg_catalog | root | ALL |
| db2 | public | admin | ALL |
| db2 | public | betsyroach | CREATE |
| db2 | public | root | ALL |
+----------+--------------------+------------+------------+
Specific database, specific user or role:
> SHOW GRANTS ON DATABASE db2 FOR betsyroach;
+----------+--------------------+------------+------------+
| Database | Schema | User | Privileges |
+----------+--------------------+------------+------------+
| db2 | crdb_internal | betsyroach | CREATE |
| db2 | information_schema | betsyroach | CREATE |
| db2 | pg_catalog | betsyroach | CREATE |
| db2 | public | betsyroach | CREATE |
+----------+--------------------+------------+------------+
Show Grants on Tables
Specific tables, all users and roles:
> SHOW GRANTS ON TABLE test_roles.employees;
+------------+--------+-----------+------------+------------+
| Database | Schema | Table | User | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | admin | ALL |
| test_roles | public | employees | root | ALL |
| test_roles | public | employees | system_ops | CREATE |
+------------+--------+-----------+------------+------------+
Specific tables, specific role or user:
> SHOW GRANTS ON TABLE test_roles.employees FOR system_ops;
+------------+--------+-----------+------------+------------+
| Database | Schema | Table | User | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | system_ops | CREATE |
+------------+--------+-----------+------------+------------+
All tables, all users and roles:
> SHOW GRANTS ON TABLE test_roles.*;
+------------+--------+-----------+------------+------------+
| Database | Schema | Table | User | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | admin | ALL |
| test_roles | public | employees | root | ALL |
| test_roles | public | employees | system_ops | CREATE |
+------------+--------+-----------+------------+------------+
All tables, specific users or roles:
> SHOW GRANTS ON TABLE test_roles.* FOR system_ops;
+------------+--------+-----------+------------+------------+
| Database | Schema | Table | User | Privileges |
+------------+--------+-----------+------------+------------+
| test_roles | public | employees | system_ops | CREATE |
+------------+--------+-----------+------------+------------+
Show Role Memberships New in v2.0
All members of all roles:
SHOW GRANTS ON ROLE;
+--------+---------+---------+
| role | member | isAdmin |
+--------+---------+---------+
| 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 | member | isAdmin |
+--------+--------+---------+
| design | ernie | false |
| design | lola | false |
+--------+--------+---------+
Roles of a specific user or role:
SHOW GRANTS ON ROLE FOR carl;
+------+--------+---------+
| role | member | isAdmin |
+------+--------+---------+
| dev | carl | false |
| docs | carl | false |
+------+--------+---------+