The SHOW GRANTS statement lists the privileges granted to users.

Synopsis

SHOW GRANTS ON TABLE table_name , DATABASE database_name , FOR user_name ,

Required Privileges

No privileges are required to view privileges granted to users.

Parameters

Parameter Description
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 grants on databases

Specific database, all users:

> SHOW GRANTS ON DATABASE db2:
+----------+------------+------------+
| Database |    User    | Privileges |
+----------+------------+------------+
| db2      | betsyroach | CREATE     |
| db2      | root       | ALL        |
+----------+------------+------------+
(2 rows)

Specific database, specific user:

> SHOW GRANTS ON DATABASE db2 FOR betsyroach;
+----------+------------+------------+
| Database |    User    | Privileges |
+----------+------------+------------+
| db2      | betsyroach | CREATE     |
+----------+------------+------------+
(1 row)

Show grants on tables

Specific tables, all users:

> SHOW GRANTS ON TABLE db1.t1, db1.t2*;
+-------+------------+------------+
| Table |    User    | Privileges |
+-------+------------+------------+
| t1    | betsyroach | DELETE     |
| t1    | henryroach | DELETE     |
| t1    | maxroach   | DELETE     |
| t1    | root       | ALL        |
| t1    | sallyroach | DELETE     |
| t2    | betsyroach | DELETE     |
| t2    | henryroach | DELETE     |
| t2    | maxroach   | DELETE     |
| t2    | root       | ALL        |
| t2    | sallyroach | DELETE     |
+-------+------------+------------+
(10 rows)

Specific tables, specific users:

> SHOW GRANTS ON TABLE db.t1, db.t2 FOR maxroach, betsyroach;
+-------+------------+------------+
| Table |    User    | Privileges |
+-------+------------+------------+
| t1    | betsyroach | DELETE     |
| t1    | maxroach   | DELETE     |
| t2    | betsyroach | DELETE     |
| t2    | maxroach   | DELETE     |
+-------+------------+------------+
(4 rows)

All tables, all users:

> SHOW GRANTS ON TABLE db1.*;
+-------+------------+------------+
| Table |    User    | Privileges |
+-------+------------+------------+
| t1    | betsyroach | DELETE     |
| t1    | henryroach | DELETE     |
| t1    | maxroach   | DELETE     |
| t1    | root       | ALL        |
| t1    | sallyroach | DELETE     |
| t2    | betsyroach | DELETE     |
| t2    | henryroach | DELETE     |
| t2    | maxroach   | DELETE     |
| t2    | root       | ALL        |
| t2    | sallyroach | DELETE     |
| t3    | root       | ALL        |
| t4    | maxroach   | CREATE     |
| t4    | root       | ALL        |
| t5    | maxroach   | CREATE     |
| t5    | root       | ALL        |
+-------+------------+------------+
(15 rows)

All tables, specific users:

> SHOW GRANTS ON TABLE db1.* FOR maxroach, betsyroach;
+-------+------------+------------+
| Table |    User    | Privileges |
+-------+------------+------------+
| t1    | betsyroach | DELETE     |
| t1    | maxroach   | DELETE     |
| t2    | betsyroach | DELETE     |
| t2    | maxroach   | DELETE     |
| t4    | maxroach   | CREATE     |
| t5    | maxroach   | CREATE     |
+-------+------------+------------+
(6 rows)

See Also



Yes No