MySQL権限一覧をきれいに作る方法と、rootユーザー以外で棚卸しする方法

information_schemaを利用した権限リスト作成

MySQLで、存在するユーザーと権限の一覧が欲しいとき、
rootユーザーで以下のコマンドを実行すると簡単に確認できます。

シンプルなクエリ

SELECT * FROM information_schema.user_privileges;   -- グローバルレベル権限のリスト
SELECT * FROM information_schema.schema_privileges; -- データベースレベル権限のリスト
SELECT * FROM information_schema.table_privileges;  -- テーブルレベル権限のリスト
SELECT * FROM information_schema.column_privileges; -- カラムレベル権限のリスト

ユーザーごとに権限を並べて表示したいなら、

-- グローバルレベル権限のリスト
SELECT
  grantee user, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges
FROM
  information_schema.user_privileges
GROUP BY
  grantee;

-- データベースレベル権限のリスト
SELECT
  grantee user, table_schema db, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges
FROM
  information_schema.schema_privileges
GROUP BY
  grantee, table_schema;

-- テーブルレベル権限のリスト
SELECT
  grantee user, table_schema db, table_name tbl, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges
FROM
  information_schema.table_privileges
GROUP BY
  grantee, table_schema, table_name;

-- カラムレベル権限のリスト
SELECT
  grantee user, table_schema db, table_name tbl, column_name clmn, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges
FROM
  information_schema.column_privileges
GROUP BY
  grantee, table_schema, table_name, column_name;

-- GROUP_CONCAT関数はANSI SQL非標準

一括でリストに出力するなら

(SELECT grantee user, '*'          db, '*'        tbl, '*'         clmn, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges FROM information_schema.user_privileges   GROUP BY grantee)
UNION
(SELECT grantee user, table_schema db, '*'        tbl, '*'         clmn, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges FROM information_schema.schema_privileges GROUP BY grantee, table_schema)
UNION
(SELECT grantee user, table_schema db, table_name tbl, '*'         clmn, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges FROM information_schema.table_privileges  GROUP BY grantee, table_schema, table_name)
UNION
(SELECT grantee user, table_schema db, table_name tbl, column_name clmn, GROUP_CONCAT(privilege_type ORDER BY privilege_type) privileges FROM information_schema.column_privileges GROUP BY grantee, table_schema, table_name, column_name)
ORDER BY user, db, tbl, clmn;

-- 結果例
+----------------+-------+------+------------------+---------------+
| user           | db    | tbl  | clmn             | privileges    |
+----------------+-------+------+------------------+---------------+
| 'testuser'@'%' | *     | *    | *                | FILE,PROCESS  |
| 'testuser'@'%' | mysql | *    | *                | SELECT,INSERT |
| 'testuser'@'%' | mysql | user | *                | DELETE        |
| 'testuser'@'%' | mysql | user | password_expired | UPDATE        |
+----------------+-------+------+------------------+---------------+

percona-tool-kitのpt-show-grantsを使っても簡単に権限リストが出力できるようです。

root以外での実行

上記はrootで実行しましたが、内部統制の兼ね合いなどで、
できれば最小権限で動かしたいところです。

mysqlデータベースにデータベースレベルのSELECT権限があれば、
information_schemaのユーザー権限系テーブルで全ユーザーの権限が表示可能です。
上記の権限がないと、表示はSHOW GRANTS相当だけになります。

-- 棚卸し用ユーザー
-- テーブルレベル(mysql.userなど)では効果がなく、データベースレベルが必要
GRANT SELECT ON mysql.* to listmaker; 

ただし、このユーザーはmysql.userテーブルが参照できてしまうので、
全ユーザーのパスワードハッシュが見えてしまいます。
より細かく管理するなら、プログラムで上記のコマンドを実行させるといった方法で、
このユーザー自体の管理にも気をつける必要があります。

その他検討した方法

mysql.userにカラムレベルで権限をつける。

mysql.userにカラム単位で権限をつけていき、
パスワードハッシュは除外することを検討しました。
mysql.user, mysql.db, mysql.tables_priv, mysql.columns_privをSELECTする方法です。
ただ、information_schemaを利用したほうがリスト作成が簡単なのと、
カラム単位での権限制御は避けたかったので除外しました。

information_schemaに対してViewを作成する

Viewはdefinerによって実行権限が決まるので、definerをrootにすれば、
Viewを見るユーザーがroot相当でinformation_schemaを参照できそうだったので試しました。

CREATE SQL SECURITY DEFINER VIEW sampledb.db_privileges AS
  SELECT grantee, table_schema, priviledge_type
  FROM information_schema.schema_privileges; -- Viewを作る

残念ながら、実行ユーザー自身の権限しか閲覧できませんでした。
information_schemaは仮想的なテーブルなので、
実行ユーザーの権限でテーブルの中身があらかじめ動的に作成されるためと思われます。