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

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

MySQLで、存在するユーザーと権限の一覧が欲しいとき、
rootユーザーで以下のコマンドを実行すると簡単に確認できます。
### シンプルなクエリ
```mysql
SELECT * FROM information_schema.user_privileges;   -- グローバルレベル権限のリスト
SELECT * FROM information_schema.schema_privileges; -- データベースレベル権限のリスト
SELECT * FROM information_schema.table_privileges;  -- テーブルレベル権限のリスト
SELECT * FROM information_schema.column_privileges; -- カラムレベル権限のリスト
```
### ユーザーごとに権限を並べて表示したいなら、

```mysql
-- グローバルレベル権限のリスト
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非標準
```

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

```mysql
(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
-- 棚卸し用ユーザー
-- テーブルレベル(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を参照できそうだったので試しました。

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

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