目次
はじめに
ビューやストアドプロシージャについて、ロールで権限が付与されているように見えて、エラーになるケースがあります。
以下の全ての条件を満たすとエラーになります。
- SQL SECURITY特性がDEFINERである
- DEFINER属性で指定されているユーザがロールで権限付与されている
- ロールがデフォルトロールに設定されていない
解決策としては、ロールを使用せずに直接権限を付与するか、適切なデフォルトロールが設定されている必要があります。
ドキュメントにも間違った説明がされており、実際の挙動とは異なるので注意が必要です。
検証環境
執筆時点でLTSの最新リリースとなるMySQL 8.4 LTSを使用します。
|
1 2 |
$ mysqld --version /usr/sbin/mysqld Ver 8.4.2 for Linux on x86_64 (MySQL Community Server - GPL) |
検証準備
検証用ユーザを作成します。
定義側ユーザとしてtestユーザ
起動側ユーザとしてcallerユーザを作成します。
|
1 2 3 |
$ mysql -u root -p mysql> CREATE USER test@localhost IDENTIFIED BY 'your_password'; mysql> CREATE USER caller@localhost IDENTIFIED BY 'your_password'; |
検証用テーブルをtable_dbスキーマに作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
mysql> CREATE DATABASE table_db; mysql> USE table_db; mysql> CREATE TABLE country ( country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, country VARCHAR(50) NOT NULL, PRIMARY KEY (country_id) ); mysql> CREATE TABLE city ( city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, city VARCHAR(50) NOT NULL, country_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY (city_id), CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE ); mysql> INSERT INTO country (country) VALUES ('Japan'); mysql> INSERT INTO city (city, country_id) VALUES ('Tokyo', 1), ('Osaka', 1); mysql> SELECT * FROM country; +------------+---------+ | country_id | country | +------------+---------+ | 1 | Japan | +------------+---------+ 1 row in set (0.00 sec) mysql> SELECT * FROM city; +---------+-------+------------+ | city_id | city | country_id | +---------+-------+------------+ | 1 | Tokyo | 1 | | 2 | Osaka | 1 | +---------+-------+------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM country NATURAL JOIN city; +------------+---------+---------+-------+ | country_id | country | city_id | city | +------------+---------+---------+-------+ | 1 | Japan | 1 | Tokyo | | 1 | Japan | 2 | Osaka | +------------+---------+---------+-------+ 2 rows in set (0.00 sec) |
次に、上記で作成したテーブルを参照するビューとストアドプロシージャを別スキーマviewproc_dbに作成します。
- 単純なSELECT文と、JOINを使用したSELECT文と二通り作成します。
- DEFINER属性をtestユーザに設定します。
補足
- DEFINER 属性
- ビューやストアドプロシージャなどの、オブジェクトの定義者を指定することができます。未指定の場合はそれを作成したユーザーが定義者となります。後述のSQL SECURITY 特性と組み合わせて使用することで、起動側の権限ではなく、定義側の権限で実行されるよう指定することができます。
- SQL SECURITY 特性
- ビューやストアドプロシージャなどの、オブジェクトが定義側の権限で実行されるか起動側の権限で実行されるかを指定できます。デフォルトは定義側です。
- 詳細は以下ドキュメントをご参照ください。
viewproc_dbスキーマにビューを作成します。
|
1 2 3 4 5 6 |
mysql> CREATE DATABASE viewproc_db; mysql> USE viewproc_db; mysql> CREATE DEFINER=test@localhost VIEW viewproc_db.v_country AS SELECT * FROM table_db.country; mysql> CREATE DEFINER=test@localhost VIEW viewproc_db.v_country_city AS SELECT * FROM table_db.country NATURAL JOIN table_db.city; |
ストアドプロシージャを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> DELIMITER // mysql> CREATE DEFINER=test@localhost PROCEDURE viewproc_db.p_country() BEGIN SELECT * FROM table_db.country; END // mysql> CREATE DEFINER=test@localhost PROCEDURE viewproc_db.p_country_city() BEGIN SELECT * FROM table_db.country NATURAL JOIN table_db.city; END // mysql> DELIMITER ; |
DEFINER属性にtestユーザを指定したので、起動側ユーザが誰であれ、定義側のtestユーザの権限で実行されることになります。
今回はcallerユーザで実行したいので、作成したビューやストアドプロシージャを参照するには、
- testユーザに対して、元テーブルへの参照権限
- testユーザに対して、ビューの参照権限とストアドプロシージャの実行権限
- callerユーザに対して、ビューの参照権限とストアドプロシージャの実行権限
以上が必要になります。
（検証の結果、2のtestユーザに対するビューの参照権限は不要でした。見やすさの都合上付与してしまっています。）
①権限を直接付与する
直接GRANTステートメントで権限付与する場合は以下になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> GRANT SELECT ON table_db.* TO test@localhost; mysql> GRANT SELECT, EXECUTE ON viewproc_db.* TO test@localhost; mysql> GRANT SELECT, EXECUTE ON viewproc_db.* TO caller@localhost; mysql> SHOW GRANTS FOR test@localhost; +----------------------------------------------------------------+ | Grants for test@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT SELECT ON 'table_db'.* TO 'test'@'localhost' | | GRANT SELECT, EXECUTE ON 'viewproc_db'.* TO 'test'@'localhost' | +----------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW GRANTS FOR caller@localhost; +------------------------------------------------------------------+ | Grants for caller@localhost | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'caller'@'localhost' | | GRANT SELECT, EXECUTE ON 'viewproc_db'.* TO 'caller'@'localhost' | +------------------------------------------------------------------+ 2 rows in set (0.00 sec) |
必要な権限が付与されたので、ビューやストアドプロシージャを参照できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
mysql> exit $ mysql -u caller -p mysql> USE viewproc_db; mysql> SELECT * FROM v_country; +------------+---------+ | country_id | country | +------------+---------+ | 1 | Japan | +------------+---------+ 1 row in set (0.00 sec) mysql> SELECT * FROM v_country_city; +------------+---------+---------+-------+ | country_id | country | city_id | city | +------------+---------+---------+-------+ | 1 | Japan | 1 | Tokyo | | 1 | Japan | 2 | Osaka | +------------+---------+---------+-------+ 2 rows in set (0.00 sec) mysql> CALL p_country(); +------------+---------+ | country_id | country | +------------+---------+ | 1 | Japan | +------------+---------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql> CALL p_country_city(); +------------+---------+---------+-------+ | country_id | country | city_id | city | +------------+---------+---------+-------+ | 1 | Japan | 1 | Tokyo | | 1 | Japan | 2 | Osaka | +------------+---------+---------+-------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
②権限をロールで付与する
権限管理をロールでする場合は以下のようになります。
前提として、activate_all_roles_on_loginを有効にしておきます。
ログインの度にSET ROLEステートメントでロールを有効にする手間が省けます。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> exit $ mysql -u root -p mysql> SET GLOBAL activate_all_roles_on_login=1; mysql> SELECT @@global.activate_all_roles_on_login; +--------------------------------------+ | @@global.activate_all_roles_on_login | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec) |
※先ほどの検証で付与した権限をリセットしておきましょう。
|
1 2 3 |
mysql> REVOKE SELECT ON table_db.* FROM test@localhost; mysql> REVOKE SELECT, EXECUTE ON viewproc_db.* FROM test@localhost; mysql> REVOKE SELECT, EXECUTE ON viewproc_db.* FROM caller@localhost; |
ロールを作成し、ロールに権限を付与し、ロールをユーザに付与します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> CREATE ROLE r_table_db@'%'; mysql> GRANT SELECT ON table_db.* TO r_table_db@'%'; mysql> CREATE ROLE r_viewproc_db@'%'; mysql> GRANT SELECT, EXECUTE ON viewproc_db.* TO r_viewproc_db@'%'; mysql> GRANT r_table_db@'%', r_viewproc_db@'%' TO test@localhost; mysql> GRANT r_viewproc_db@'%' TO caller@localhost; mysql> SHOW GRANTS FOR test@localhost; +------------------------------------------------------------------+ | Grants for test@localhost | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT 'r_table_db'@'%','r_viewproc_db'@'%' TO 'test'@'localhost' | +------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> SHOW GRANTS FOR caller@localhost; +---------------------------------------------------+ | Grants for caller@localhost | +---------------------------------------------------+ | GRANT USAGE ON *.* TO 'caller'@'localhost' | | GRANT 'r_viewproc_db'@'%' TO 'caller'@'localhost' | +---------------------------------------------------+ 2 rows in set (0.00 sec) |
一旦testユーザでログインしてみて、必要な権限やロールが付与されていることを確認します。
activate_all_roles_on_loginが有効に設定されているので、SET ROLEステートメントを発行せずともロールが有効化されているのがCURRENT_ROLE()から確認できます。
また、SHOW GRANTSステートメントの結果を見ると、ロールによって権限が付与されているのが確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> exit mysql -u test -p mysql> SELECT CURRENT_ROLE(); +--------------------------------------+ | CURRENT_ROLE() | +--------------------------------------+ | 'r_table_db'@'%','r_viewproc_db'@'%' | +--------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS; +------------------------------------------------------------------+ | Grants for test@localhost | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT SELECT ON 'table_db'.* TO 'test'@'localhost' | | GRANT SELECT, EXECUTE ON 'viewproc_db'.* TO 'test'@'localhost' | | GRANT 'r_table_db'@'%','r_viewproc_db'@'%' TO 'test'@'localhost' | +------------------------------------------------------------------+ 4 rows in set (0.00 sec) |
callerユーザに切り替えて、ビューやストアドプロシージャを参照できるかと思いきや、エラーとなってしまいました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> exit mysql -u caller -p mysql> SELECT CURRENT_ROLE(); +---------------------+ | CURRENT_ROLE() | +---------------------+ | 'r_viewproc_db'@'%' | +---------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS; +------------------------------------------------------------------+ | Grants for caller@localhost | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'caller'@'localhost' | | GRANT SELECT, EXECUTE ON 'viewproc_db'.* TO 'caller'@'localhost' | | GRANT 'r_viewproc_db'@'%' TO 'caller'@'localhost' | +------------------------------------------------------------------+ 3 rows in set (0.01 sec) mysql> USE viewproc_db; mysql> SELECT * FROM v_country; ERROR 1356 (HY000): View 'viewproc_db.v_country' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> SELECT * FROM v_country_city; ERROR 1143 (42000): SELECT command denied to user 'test'@'localhost' for column 'country_id' in table 'country' mysql> CALL p_country(); ERROR 1370 (42000): execute command denied to user 'test'@'localhost' for routine 'viewproc_db.p_country' mysql> CALL p_country_city(); ERROR 1370 (42000): execute command denied to user 'test'@'localhost' for routine 'viewproc_db.p_country_city' |
原因：ロールが効いていない
MySQL Bugs: #104732: In the case of using the role, there is a problem with the acl check of the view
上記MySQLバグレポートによれば、以下の問題が指摘されています。
Privileges associated with a role activated via activate_all_roles_on_login is not checked when a view is accessed.
（activate_all_roles_on_login でアクティブ化されたロールに関連付けられた権限は、ビューへのアクセス時にチェックされません。）
しかし、これはドキュメントの以下の記述と矛盾します。
Stored program and view objects that execute in definer context execute with the default roles of the user named in their DEFINER attribute. If activate_all_roles_on_login is enabled, such objects execute with all roles granted to the DEFINER user, including mandatory roles.
（定義者コンテキストで実行されるストアド プログラムおよびビュー オブジェクトは、DEFINER 属性で指定されたユーザーのデフォルト ロールで実行されます。activate_all_roles_on_login が有効になっている場合、このようなオブジェクトは、必須ロールを含む、DEFINER ユーザーに付与されたすべてのロールで実行されます。）
https://dev.mysql.com/doc/refman/8.4/en/roles.html#roles-activating
正しい挙動に即した記述に修正するには、
次のようにドキュメントを書き換える必要があります。
Roles activated via activate_all_roles_on_login or including mandatory roles privileges should not be considered for stored program and view objects.
（activate_all_roles_on_login を介してアクティブ化されたロール、または必須のロール権限を含むロールは、ストアド プログラムおよびビュー オブジェクトでは考慮されません。）
以上をまとめると、次のことがわかります。
- ビューやストアドプロシージャでは、DEFINER属性で指定されたユーザのデフォルトロールで実行される。
- 「activate_all_roles_on_login=1の場合は、DEFINER属性で指定されたユーザの全てのロールで実行される」とドキュメントに書かれているが、その記述は正しくない。デフォルトロールのみで実行されるというのが正しい。
③ロールをデフォルトロールに設定する
そこで、ロールをデフォルトロールに設定して、ビューを参照できるかどうか試してみます。
デフォルトロールの設定方法は、二通りあります。
SET DEFAULT ROLE ... TO ～;ALTER USER ～ DEFAULT ROLE ...;
デフォルトロールの確認方法は、SHOW CREATE USERステートメントで確認ができます。
今回はtestユーザに付与したロールをデフォルトロールに設定すれば、エラー回避ができました。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> exit $ mysql -u root -p -- 設定コマンドその① mysql> SET DEFAULT ROLE r_table_db@'%', r_viewproc_db@'%' TO test@localhost; -- 設定コマンドその② mysql> ALTER USER test@localhost DEFAULT ROLE r_table_db@'%', r_viewproc_db@'%'; mysql> SHOW CREATE USER test@localhost\G *************************** 1. row *************************** CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '*****' DEFAULT ROLE 'r_table_db'@'%','r_viewproc_db'@'%' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 1 row in set (0.00 sec) |
callerユーザで実行して、エラーなく参照できるようになりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
mysql> exit $ mysql -u caller -p mysql> USE viewproc_db; mysql> SELECT * FROM v_country; +------------+---------+ | country_id | country | +------------+---------+ | 1 | Japan | +------------+---------+ 1 row in set (0.00 sec) mysql> SELECT * FROM v_country_city; +------------+---------+---------+-------+ | country_id | country | city_id | city | +------------+---------+---------+-------+ | 1 | Japan | 1 | Tokyo | | 1 | Japan | 2 | Osaka | +------------+---------+---------+-------+ 2 rows in set (0.00 sec) mysql> CALL p_country(); +------------+---------+ | country_id | country | +------------+---------+ | 1 | Japan | +------------+---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> CALL p_country_city(); +------------+---------+---------+-------+ | country_id | country | city_id | city | +------------+---------+---------+-------+ | 1 | Japan | 1 | Tokyo | | 1 | Japan | 2 | Osaka | +------------+---------+---------+-------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
まとめ
結果を表にまとめると以下の通りです。
| ①権限を直接付与 | ②ロールで付与 | ③デフォルトロールに設定 | |
|---|---|---|---|
| ビュー | 〇 | references invalid ERROR 1356 | 〇 |
| ビュー (結合テーブル) | 〇 | SELECT command denied ERROR 1143 | 〇 |
| ストアドプロシージャ | 〇 | execute command denied ERROR 1370 | 〇 |
| ストアドプロシージャ (結合テーブル) | 〇 | execute command denied ERROR 1370 | 〇 |
ドキュメントにも記載が無く、エラーメッセージも結合テーブルか否かで異なるため、エラーになったときに躓き易いです。
ロールを使用する場合は、デフォルトロールの設定が不足していないか注意を払いましょう。
