Skip to content

Latest commit

 

History

History
245 lines (205 loc) · 7.93 KB

show-grants.md

File metadata and controls

245 lines (205 loc) · 7.93 KB
title summary keywords toc
SHOW GRANTS
The SHOW GRANTS statement lists the privileges granted to users.
reflection
false

The SHOW GRANTS statement lists the privileges granted to users.

Synopsis

{% include sql/{{ page.version.version }}/diagrams/show_grants.html %}

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:

{% include copy-clipboard.html %}

> 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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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:

{% include copy-clipboard.html %}

SHOW GRANTS ON ROLE design;
+--------+--------+---------+
|  role  | member | isAdmin |
+--------+--------+---------+
| design | ernie  | false   |
| design | lola   | false   |
+--------+--------+---------+

Roles of a specific user or role:

{% include copy-clipboard.html %}

SHOW GRANTS ON ROLE FOR carl;
+------+--------+---------+
| role | member | isAdmin |
+------+--------+---------+
| dev  | carl   | false   |
| docs | carl   | false   |
+------+--------+---------+

See Also