在 MySQL 中查找對錶有授權的使用者

我們授予以下使用者訪問權限

grant select, insert, update, delete on product_events to 'product_admin'@'%' ;

現在，像這樣有很多表和很多使用者。

我想找到對特定表有任何授權的所有使用者

也就是說，給定product_events表，我想得到'product_admin'@'%'

這個凌亂的查詢應該給你每個可以訪問的使用者product_db.product_events

SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

如果您正在尋找具有所有四 (4) 項權限（、、和授權）的使用者SELECT，請INSERT使用它（基本上使用代替）UPDATE``DELETE``AND``OR

SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

注意：表級權限檢查起來更加麻煩，因為授權被列為一組而不是單獨的列

mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                                                                                                              | Null | Key | Default           | Extra                       |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                                                                                                          | NO   | PRI |                   |                             |
| Db          | char(64)                                                                                                                          | NO   | PRI |                   |                             |
| User        | char(16)                                                                                                                          | NO   | PRI |                   |                             |
| Table_name  | char(64)                                                                                                                          | NO   | PRI |                   |                             |
| Grantor     | char(77)                                                                                                                          | NO   | MUL |                   |                             |
| Timestamp   | timestamp                                                                                                                         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   |     |                   |                             |
| Column_priv | set('Select','Insert','Update','References')                                                                                      | NO   |     |                   |                             |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql>

引用自：https://dba.stackexchange.com/questions/287135