mysql.user - 列出列(权限)值作为记录

Question

需要帮助从 select * from mysql.user; 结果中列出用户拥有的所有权限(意味着列权限上带有“Y”)，并将其作为 1 列(如user_priv)以及主机和用户列

query test user and its privileges details

想将上面的结果转换为这个:

+------+------+-------------+
| user | host | user_priv   |
+------+------+-------------+
| test | %    | Create_priv |
+------+------+-------------+
| test | %    | Reload_priv |
+------+------+-------------+
| test | %    | ...         |

Answer 1

您可以通过查询 information_schema.USER_PRIVILEGES 表来获取所需的数据，该表从 mysql.user 派生数据 (documentation)

虽然如果没有一些字符串操作来分隔用户和主机，您将无法获得所需的确切格式，但您将获得权限类型的更易读的输出。

例如

SELECT `GRANTEE` as 'USER@HOST', `PRIVILEGE_TYPE` as 'PRIVILEGE' 
FROM `information_schema`.`USER_PRIVILEGES` WHERE `GRANTEE` LIKE '%paul%';
+--------------------+-------------------------+
| USER@HOST          | PRIVILEGE               |
+--------------------+-------------------------+
| 'paul'@'localhost' | SELECT                  |
| 'paul'@'localhost' | INSERT                  |
| 'paul'@'localhost' | UPDATE                  |
| 'paul'@'localhost' | DELETE                  |
| 'paul'@'localhost' | CREATE                  |

...

| 'paul'@'localhost' | DROP                    |
| 'paul'@'localhost' | RELOAD                  |
| 'paul'@'localhost' | EVENT                   |
| 'paul'@'localhost' | TRIGGER                 |
| 'paul'@'localhost' | CREATE TABLESPACE       |
+--------------------+-------------------------+

如果您想知道用户是否可以授予权限，请将 IS_GRANTABLE 列添加到您的查询中。