在MySQL中查找表上有授权的用户

Question

我们授予用户以下访问权限

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

现在，就像这样，有很多表，也有很多用户。

我希望找到所有在特定表上有任何授权的用户。

也就是说，给定product_events表，我想得到'product_admin'@'%'

Answer 1

这个混乱的查询应该为您提供每个可以访问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>