Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

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-kitpt-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は仮想的なテーブルなので、
実行ユーザーの権限でテーブルの中身があらかじめ動的に作成されるためと思われます。

yoshi-taka
QiitaにはWeb(特に日本語)に少ない情報を書いています。 得意なのは要件定義とスケジューリングとSRE周りと各種パイプライン改善。 最近の関心事は組織設計、組織改革、チェンジマネジメント、5年後の予測とそれに向けたコンセプト作成。FinBizDevSecTestOpsエンジニアとして会社全体のパイプライン最適化を目指す
https://twitter.com/ask_yoshi_taka/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away