MySQL8.0でユーザー権限をロールで管理してみる
前回の記事
背景
今回はDBのユーザー権限周りでロールを使ってみました．存在すること自体知らなかったのですが，AWSのIAMみたいなイメージです．実践で使う機会があるかと言われると，ほとんどなさそうですが知識として知っておくと良いかもしれません．
毎度ながら公式ドキュメントを乗っけときます．
それから参考書籍と．
権限付与，権限の取り消し
ロールを使ってみる前に，権限周りの復習をしておきます．
# rootで接続
# 新しいユーザー(ユーザー名:test, パスワード:123)を作成
CREATE USER test IDENTIFIED BY '123';
# 権限を確認
SHOW GRANTS FOR test;
+----------------------------------+
| Grants for test@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
+----------------------------------+
# ここでrootから出て，testユーザーで接続
# データベースを作ろうとするが権限がないので，エラーになる
CREATE DATABASE test1;
ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'test1'権限の種類については，こちらのTable 13.11と13.12にあります．testユーザーのUSAGEはなんの権限もないのでただMySQLに接続できるだけです．
ちなみにTable 13.11はStatic Privilegesで13.12はDynamic Privilegesでなんだこれ？と思い調べてみました．こちらから解釈しますと，Static Privilegesは初期から存在している権限で，Dynamic Privilegesはプラグインなどのインストールによって使えるようになる権限のようでした．
権限を与えてみます．権限の付与はtestユーザーではできないので，rootで行います
# ここでtestユーザーから出て，rootで接続
# testにSELECT, INSERT, CREATE 権限を付与(全てのデータベースとテーブル)
GRANT SELECT, INSERT, CREATE ON *.* TO test;
# 確認
SHOW GRANTS FOR test;
+---------------------------------------------------+
| Grants for test@% |
+---------------------------------------------------+
| GRANT SELECT, INSERT, CREATE ON *.* TO `test`@`%` |
+---------------------------------------------------+
# ここでrootから出て，testユーザーで接続
# データベース作成．今度は成功．
CREATE DATABASE test1;GRANT [権限] ON [データベース名].[テーブル名] TO [ユーザー名]で権限の付与ができます．面倒くさい場合は，[権限]にALLを指定するとGRANT OPTION以外の権限を，[データベース名].[テーブル名]に*.*を指定すると全てのデータベースとそのテーブルを指定することができます．
次に権限を剥奪してみます．
# ここでtestユーザーから出て，rootで接続
# 権限を剥奪
REVOKE SELECT, CREATE ON *.* FROM test;
+-----------------------------------+
| Grants for test@% |
+-----------------------------------+
| GRANT INSERT ON *.* TO `test`@`%` |
+-----------------------------------+REVOKE [権限] ON [データベース名].[テーブル名] FROM [ユーザー名]で権限の剥奪ができます．
ロールとは
先ほどのようにユーザーごとに権限を付与することもできるのですが，複数ユーザーに同じ権限を与えたい場合などにロールを使うと便利です．
A MySQL role is a named collection of privileges.
とドキュメントにあるように，ロールとは権限をまとめたものになります．ロールをユーザーに付与することで，ユーザーはロールに記述された権限を得ることができます．ロールは複数ユーザーに割り当てることができ，ユーザーも複数のロールを割り当てられることができます(↓イメージ図)．
ロールを使うことで，「ユーザーAとユーザーBの両方にある権限を付与したい」となった場合はロール1に権限を追加すればよいだけなので管理が楽になります．
ロールを作成する
# ロールの作成
CREATE ROLE read_only;
# ロールに権限を追加
GRANT SELECT ON *.* TO 'read_only';
# 確認
SHOW GRANTS FOR read_only;
+----------------------------------------+
| Grants for read_only@% |
+----------------------------------------+
| GRANT SELECT ON *.* TO `read_only`@`%` |
+----------------------------------------+ロールへの権限の追加はユーザーのときと全く同じです．
ではtestというユーザーがある状態でtestという同じ名前のロールを作成するとどうなるのか，試してみました．逆のロールがある状態で同じ名前のユーザーも作成してみました．
# ロールの作成失敗
CREATE ROLE test;
ERROR 1396 (HY000): Operation CREATE ROLE failed for 'test'@'%'
# ユーザーの作成失敗
CREATE USER read_only IDENTIFIED BY '123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'read_only'@'%'エラーになり失敗しました．ユーザーとロールで同じ名前のものを作成するのはできないようです．
ロールをユーザーに適用
適当なユーザーを作成して，ロールを適用してみます．
# rootで接続
# 新しいユーザー(ユーザー名:test2, パスワード:123)を作成
CREATE USER test2 IDENTIFIED BY '123';
# 確認
SHOW GRANTS FOR test2;
+-----------------------------------+
| Grants for test2@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%` |
+-----------------------------------+
# test2にロールを適用
GRANT 'read_only' TO 'test2';
# 確認
SHOW GRANTS FOR test2;
+--------------------------------------+
| Grants for test2@% |
+--------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%` |
| GRANT `read_only`@`%` TO `test2`@`%` |
+--------------------------------------+ロールが適用できたので，test2ユーザーで接続して確認してみます．
# ここでrootから出て，test2ユーザーで接続
# ロールをアクティブに
SET ROLE read_only;
# 自分自身のロールを確認
SELECT CURRENT_ROLE();
+-----------------+
| CURRENT_ROLE() |
+-----------------+
| `read_only`@`%` |
+-----------------+ロールはGRANT 'read_only' TO 'test2'; だけでは有効にならず，SET ROLE read_only; をする必要があります．
ロールの取り消しはREVOKE read_only FROM test2; で可能です．またロールの削除はDROP ROLE 'read_only'; でできます．
この記事が気に入ったらサポートをしてみませんか？