４－５．MySQLユーザーへのロールの割り当てと有効化

2019年7月15日

１．ロールを定義する方法

ロールを作成するには「CRATE ROLE」で定義します。

１）構文

CREATE ROLE [ロール名];

２）実行例

mysql> CREATE ROLE ROLE01;
Query OK, 0 rows affected (0.17 sec)

スポンサードサーチ

２．ロールに権限を付与する方法

ロールに権限を付与するにはGRANTを使用します。

１）構文

GRANT　権限 TO ロール;

２）実行例

mysql> GRANT SELECT ON TABLE01 TO ROLE01;
Query OK, 0 rows affected (0.88 sec)

mysql>

 

スポンサードサーチ

３．ロールをユーザーアカウントに付与する

ロールをユーザーアカウントに付与するにはGRANTを使用します。

１）構文

GRANT ロール TO [ユーザーアカウント];

２）実行例

mysql> GRANT ROLE01 TO USER01@localhost;
Query OK, 0 rows affected (0.34 sec)

mysql>

 

４．ユーザーアカウントへロールを付与する流れ

１）権限の状況確認

まずユーザー「USER01」の権限付与の状況を確認します。

ユーザーアカウント「USER01@localhost」には権限が付いていない状態です。

mysql> SHOW GRANTS FOR USER01@localhost;
+--------------------------------------------+
| Grants for USER01@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost` |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql>

 

２）権限の付与

ユーザー「USER01」にロール「ROLE01」を付与します。

mysql> GRANT ROLE01 TO USER01@localhost;
Query OK, 0 rows affected (0.84 sec)

mysql>

 

３）付与後の権限の状況確認

権限を付与後のユーザーアカウント「USER01」の状況を確認します。

mysql> SHOW GRANTS FOR USER01@localhost;
+--------------------------------------------+
| Grants for USER01@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost` |
| GRANT `ROLE01`@`%` TO `USER01`@`localhost` |
+--------------------------------------------+
2 rows in set (0.00 sec)

mysql>

「USER01@localhost」に「ROLE01」が付与されていることが確認できます。

しかし、これだけでは実は作業は足りていないのです。事項のようにロールを有効かする必要があります。

スポンサードサーチ

５．ロールを有効化する。

ロールは付与しただけでは有効になりません。ユーザーに対して有効化する必要があります。
方法は３通りあります。

１）セッション内でロール有効化する方法
２）ユーザーアカウントのデフォルトロールに設定する
３）システム変数「activate_all_roles_on_login」を「on」にする

１）セッション内でロール有効化する方法

この方法ではセッション内で付与されたロールを有効にします。

（１）設定方法

以下の構文でロールを有効にします。
【構文】
SET ROLE [ロール名];

（２）実行例

①データベースを設定してみます。

mysql> USE studydb;
ERROR 1044 (42000): Access denied for user 'USER01'@'localhost' to database 'studydb'

USE文でデータベースを設定できませんでした。これはユーザーアカウント「USER01@localhost」にはデータベース「studydb」でまだアクセス許可されているテーブルが無いためです。

②権限割り当て状況を確認してみます。
ロールは付与されているのにアクセス出来ない事が確認できます。

mysql> SHOW GRANTS;
+--------------------------------------------+
| Grants for USER01@localhost |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost` |
| GRANT `ROLE01`@`%` TO `USER01`@`localhost` |←ロールは割り当てられている
+--------------------------------------------+
2 rows in set (0.01 sec)

ロール「ROLE01」がちゃんと割り当てられています。エラーになるのはロールが有効になっていないためです。

③ロールを有効にします。

mysql> set ROLE ROLE01;
Query OK, 0 rows affected (0.01 sec)

有効になりました。再度SELECTを試してみましょう。

mysql> USE studydb
Database changed
mysql> select count(*) from TABLE01;
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set (0.26 sec)

mysql>

ロールが有効になってデータベースの設定もテーブルへのアクセスもうまくいきました。

２）ユーザーアカウントのデフォルトロールに設定する

ユーザーアカウントのデフォルトロールに設定します。
これによりセッション開始と共にロールが割り当てられます。

（１）設定方法

【構文】

CREATE USER [ユーザーアカウント] IDENTIFIED BY '[パスワード]’ DEFAULT ROLE [ロール名];

ALTER USER [ユーザーアカウント] DEFAULT ROLE [ロール名];

（２）実行例

①rootユーザーでユーザー「USER01」のデフォルトロールを「ROLE01」に設定します。

mysql> ALTER USER USER01@localhost DEFAULT ROLE ROLE01;
Query OK, 0 rows affected (0.04 sec)

②「USER01」でログインして権限付与状況を確認します。

mysql> SHOW GRANTS;
+--------------------------------------------+
| Grants for USER01@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost` |
| GRANT `ROLE01`@`%` TO `USER01`@`localhost` |←ロールが付与されています。
+--------------------------------------------+
2 rows in set (0.00 sec)

③データベースを「studydb」に設定して「TABLE01」をSELECTしてみます。

mysql> USE studydb;
Database changed
mysql> select count(*) from TABLE01;
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

↑ログインした時点でデフォルトロールが有効になっている為改めて有効化することなくSELECTがうまくいきました。

３）システム変数「activate_all_roles_on_login」を「on」にする

システム変数「activate_all_roles_on_login」を「on」にするとGRANTで権限を割り当てた時点で有効になります。

①現在の「activate_all_roles_on_login」の設定値を確認します。
以下のコマンドで値を確認できます。
SHOW VARIABLES LIKE 'activate_all_roles_on_login’;

mysql> SHOW VARIABLES LIKE 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set, 1 warning (0.13 sec)

mysql>

②「activate_all_roles_on_login」の値を「ON」に設定します。

SET GLOBAL activate_all_roles_on_login = on;

③ユーザーアカウント「USER01」の権限付与状況を確認します。

mysql> SHOW GRANTS FOR USER01@localhost;
+--------------------------------------------+
| Grants for USER01@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost` |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql>

④ユーザーアカウント「USER01」に「ROLE01」を付与します。

mysql> GRANT ROLE01 TO USER01@localhost;
Query OK, 0 rows affected (0.01 sec)

⑤ユーザーアカウント「USER01」の権限付与状況を確認します。

mysql> SHOW GRANTS FOR USER01@localhost;
+--------------------------------------------+
| Grants for USER01@localhost 　　　　　　　 |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `USER01`@`localhost` |
| GRANT `ROLE01`@`%` TO `USER01`@`localhost` |
+--------------------------------------------+
2 rows in set (0.00 sec)

mysql>

⑥ユーザーアカウント「USER01」に接続して「TABLE01」にアクセスしてみます。

mysql> USE studydb;
Database changed
mysql> SELECT COUNT(*) FROM TABLE01;
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.02 sec)

mysql>

SETで有効にしなくてもアクセス出来ました。

４_ユーザー、権限,MySQL

Posted by garnet