SHOW GRANTSで権限を確認する
MySQLで指定したユーザーに付与されている権限を確認するにはSHOW GRANTS文を使う。
SHOW GRANTS FOR 'ユーザー名'@'ホスト名';
例えばapp_userユーザーの権限を確認するには以下のように実行する。
mysql> SHOW GRANTS FOR 'app_user'@'%';
+----------------------------------------------------------------------+
| Grants for app_user@% |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shopdb`.* TO `app_user`@`%` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `app_user`@`%` |
+----------------------------------------------------------------------+
1行目のGRANT USAGE ON *.* TO ...は「権限がない」ことを表す慣習的な表示であり、実害はない。
2行目以降が実際に付与されている権限とロールである。
自分自身の権限を確認する場合は、ユーザー名の代わりにCURRENT_USER()を指定する。
SHOW GRANTS FOR CURRENT_USER();
権限とロールが分けて表示される
MySQL 8.0からロール機能が使えるようになった。
ロールを付与しているユーザーの場合、SHOW GRANTSの結果にはユーザーに直接付与した権限とは別に、付与されているロール名も表示される。
上記のapp_userの例では、3行目のGRANT app_read@%,app_write@%TOapp_user@%`` が、app_userにapp_readロールとapp_writeロールが付与されていることを表す。
この時点では、ロールに含まれる権限はSHOW GRANTSの結果に直接表示されない。
ロールに含まれる権限も表示する
ロール自体に付与されている権限は、ロール名を指定してSHOW GRANTSを実行すると確認できる。
mysql> SHOW GRANTS FOR 'app_read';
+------------------------------------------------+
| Grants for app_read@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%` |
| GRANT SELECT ON `shopdb`.* TO `app_read`@`%` |
+------------------------------------------------+
ユーザーに付与されたロールを含めた実質的な権限をまとめて確認したい場合は、USING句でロールを指定する。
例えばapp_readロールだけを付与されたrole_only_userユーザーの場合、通常のSHOW GRANTSではロール名しか表示されない。
mysql> SHOW GRANTS FOR 'role_only_user'@'%';
+----------------------------------------------+
| Grants for role_only_user@% |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `role_only_user`@`%` |
| GRANT `app_read`@`%` TO `role_only_user`@`%` |
+----------------------------------------------+
USING句を付けると、ロールに含まれるSELECT権限が展開されて表示される。
mysql> SHOW GRANTS FOR 'role_only_user'@'%' USING 'app_read';
+------------------------------------------------------+
| Grants for role_only_user@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `role_only_user`@`%` |
| GRANT SELECT ON `shopdb`.* TO `role_only_user`@`%` |
| GRANT `app_read`@`%` TO `role_only_user`@`%` |
+------------------------------------------------------+
ただしロールが有効化されていない状態で接続した場合、実際のクエリ実行時にはロールの権限は適用されない。
ロールを常に有効化しておく場合は、SET DEFAULT ROLEで既定のロールとして設定しておく。
SET DEFAULT ROLE ALL TO 'app_user'@'%';
app_userで接続してCURRENT_ROLE()を確認すると、既定のロールが有効になっていることが分かる。
mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE() |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+
SET DEFAULT ROLEを設定していないユーザーのCURRENT_ROLE()はNONEになる。
mysql.userテーブルでユーザー一覧を確認する
ユーザー一覧を確認する場合はmysql.userテーブルを参照する。
mysql> SELECT User, Host, plugin, account_locked
-> FROM mysql.user
-> WHERE User IN ('app_user', 'readonly_user', 'app_read', 'app_write', 'root');
+---------------+-----------+-----------------------+----------------+
| User | Host | plugin | account_locked |
+---------------+-----------+-----------------------+----------------+
| app_read | % | caching_sha2_password | Y |
| app_user | % | caching_sha2_password | N |
| app_write | % | caching_sha2_password | Y |
| readonly_user | % | caching_sha2_password | N |
| root | % | caching_sha2_password | N |
| root | localhost | caching_sha2_password | N |
+---------------+-----------+-----------------------+----------------+
account_lockedがYのユーザーはパスワード認証でログインできない。CREATE ROLEで作成したロールは、既定でログイン不可のアカウントとして作成されるためaccount_lockedがYになる。
pluginは認証方式を表し、MySQL 8.0のデフォルトはcaching_sha2_passwordである。
mysql.userの権限カラムは注意が必要
mysql.userにはSelect_priv・Insert_privなどの権限カラムも存在するが、これらはデータベース全体（GRANT ... ON *.*）に対するグローバル権限のみを表す。
mysql> SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv
-> FROM mysql.user WHERE User IN ('app_user', 'readonly_user');
+---------------+------+-------------+-------------+-------------+-------------+
| User | Host | Select_priv | Insert_priv | Update_priv | Delete_priv |
+---------------+------+-------------+-------------+-------------+-------------+
| app_user | % | N | N | N | N |
| readonly_user | % | N | N | N | N |
+---------------+------+-------------+-------------+-------------+-------------+
app_userとreadonly_userにはshopdbデータベースに対する権限しか付与していないため、すべてNになっている。
データベース単位の権限はmysql.dbテーブルに格納される。
mysql> SELECT Db, User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv
-> FROM mysql.db WHERE User IN ('app_user', 'readonly_user');
+--------+---------------+------+-------------+-------------+-------------+-------------+
| Db | User | Host | Select_priv | Insert_priv | Update_priv | Delete_priv |
+--------+---------------+------+-------------+-------------+-------------+-------------+
| shopdb | app_user | % | Y | Y | Y | Y |
| shopdb | readonly_user | % | Y | N | N | N |
+--------+---------------+------+-------------+-------------+-------------+-------------+
権限は*.*（グローバル）・db.*（データベース）・db.table（テーブル）・db.table.column（カラム）の階層で管理されており、それぞれmysql.user・mysql.db・mysql.tables_priv・mysql.columns_privテーブルに格納される。
どの階層に権限があるか分からない場合は、mysql.*を直接見るよりもSHOW GRANTSを使うほうが確実である。
ロール間の関係を確認する
どのロールがどのユーザーに付与されているかをまとめて確認する場合は、mysql.role_edgesテーブルを参照する。
mysql> SELECT * FROM mysql.role_edges;
+-----------+-----------+---------+----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+----------+-------------------+
| % | app_read | % | app_user | N |
| % | app_write | % | app_user | N |
+-----------+-----------+---------+----------+-------------------+
FROM_USER・FROM_HOSTがロール、TO_USER・TO_HOSTがロールの付与先ユーザーを表す。WITH_ADMIN_OPTIONがYの場合、付与先ユーザーはそのロールを他のユーザーにさらに付与できる。
参考
- MySQL Documentation: SHOW GRANTS Statement
- MySQL Documentation: Roles
- MySQL Documentation: Grant Tables
