MySQL道普請便り

第201回MySQLのDEFINERについて

MySQLでTRIGGERやVIEWなどのストアドオブジェクトを作成したあとにSHOW構文で作成したものを確認すると、DEFINER=xxxx@xxxxSQL SECURITY xxxxなどが追加で付与されていることが確認できます。このDEFINER属性とSQL SECURITY特性により、そのストアドオブジェクトを実行するユーザーが変わります。今回は、ストアドオブジェクトの作成時のDEFINERにはどういう特徴があるのかを確認していきます。なお、今回利用しているMySQLのバージョンは8.0.32となります。

MySQLのDEFINER属性

mysql> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE VIEW v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,`t1`.`val` AS `val` from `t1`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

上記のようなVIEWを作ってSHOW構文で確認すると、DEFINER属性が付与されていることが確認できます。このDEFINER属性にはroot@localhostのような、ユーザーアカウントの情報が入ります。

TRIGGERやEVENTが実行された場合は、このDEFINERにあるユーザーアカウントがそのTRIGGERとEVENTを実行します。そのため、もしそのユーザーに適切な権限がないとエラーとなってしまします。

fugaユーザーに適切な権限がたりない場合
mysql> SELECT CURRENT_USER\G
*************************** 1. row ***************************
CURRENT_USER: root@localhost
1 row in set (0.00 sec)

mysql> CREATE DEFINER=`fuga`@`localhost` EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE DO insert into t2 values (1, 'one');
Query OK, 0 rows affected (0.01 sec)

## ログを確認するとroot@localhostで作成したがfuga@localhostで実行されていることがわかる
2023-07-11T20:20:00.305914+09:00 28 [ERROR] [MY-010448] [Server] Event Scheduler: [fuga@localhost].[db1.myevent] execution failed, user no longer has EVENT privilege.

ストアドルーチンとVIEWの場合、DIFINER属性は後述するSQL SECURITY特性との組み合わせにより、そのストアドオブジェクトの実行者をDEFINERに定義されたユーザー、またはストアドオブジェクトの実行を呼び出したユーザーかに決めることになります。

DEFINER属性の定義

このDEFINER属性は、ストアドオブジェクト(ストアドルーチン、TRIGGER、EVENT、VIEW)を作成するときに決めることができます。この属性は省略することもでき、省略した場合は作成したユーザー(たとえばCREATE TRIGGERを実行したときのCURRENT_USER)が入る仕様になっています。

もし、DEFINERに自身(CURRENT_USER)ではない違うユーザーを定義する場合、作成するユーザーがSET_USER_ID権限(またはSUPER権限)を持っている必要があります。さらに、DEFINERにroot@localhostのようなSYSTEM_USER権限をもつアカウントをDEFINERに設定するには、SYSTEM_USER権限が必要になります。

SQL SECURITY特性

SQL SECURITY特性にはDEFINERまたはINVOKERのいずれかの値が入ります。ストアドルーチンやVIEWのストアドオブジェクトを作成する際に、このSQL SECURITY特性を指定しなかった(省略した)場合は、DEFINERになります。

もしSQL SECURITYにDEFINERが設定されているときは、DEFINER属性で定義しているユーザーにより実行されます。SQL SECURITY INVOKERの場合は、ストアドオブジェクトを呼び出したユーザーが実行します。そのため、SQL SECURITY特性がDEFINERの場合はDEFINERに設定したユーザーに権限が必要ですが、INVOKERの場合は実行するユーザーにそのストアドオブジェクトを実行できる権限を付与しなければいけません。

なお、このSQL SECURITY特性はストアドルーチンとVIEWには設定できますが、TRIGGERとEVENTにはありません。TRIGGERとEVENTにはDEFINERに適切な権限を付与するようにしてください。

ここでVIEWを使って簡単な実験をしてみます。

db1にdb2.t1を参照するviewをSQL SECURITY DEFINER/INVOKERでそれぞれ作ってdb1にしか参照できないfuga@localhostでアクセスしてみます。

rootユーザーでDEFINER,INVOKEのviewを作成
mysql> SHOW GRANTS FOR fuga@localhost;
+-----------------------------------------------+
| Grants for fuga@localhost                     |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `fuga`@`localhost`      |
| GRANT SELECT ON `db1`.* TO `fuga`@`localhost` |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> CREATE SQL SECURITY INVOKER VIEW db1.v1_i AS SELECT * FROM db2.t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE SQL SECURITY DEFINER VIEW db1.v1_d AS SELECT * FROM db2.t1;
Query OK, 0 rows affected (0.01 sec)

INVOKERの方はfugaユーザーで実行されるため、権限が足りず実行できませんでしたが、DEFINERのほうがroot@localhostで作成したため、root@localhostで実行されたためdb2内のテーブルを参照することができました。

fuga@localhostでアクセスできるか確認する
mysql> SELECT * FROM v1_i;
ERROR 1356 (HY000): View 'db1.v1_i' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

mysql> SELECT * FROM v1_d;
+------+-------+
| id   | val   |
+------+-------+
|    6 | six   |
|    7 | seven |
+------+-------+
2 rows in set (0.01 sec)

DEFINERのユーザーが存在しない場合

DEFINERには存在しないユーザーを設定してストアドオブジェクトを作成することができてしまいますが、その場合、各ストアドオブジェクトを実行するときにエラーが発生します。そのため、存在しないユーザーを設定しないようにしなければなりません。

また、ユーザーを削除したりRENAMEする際に、元のユーザーがDEFINERに定義されていると、こちらも存在しないユーザーがDEFINERに残って定義されてしまうことになります。MySQL 8.0.22以降、この操作はエラー(SET_USER_ID権限を持っている場合はワーニング)が出力されるようになったため、気づきやすいとは思いますが、実行前にDEFINERに作業対象のユーザーが残っていないか確認することをおすすめします。INFORMATION_SCHEMAの各ストアドオブジェクトのテーブルにDEFINERカラムがあるので、そちらで確認するようにしましょう。詳細については、公式ドキュメントの孤立したストアドオブジェクトをご確認ください。

とくに、マイグレーション作業などでmysqldumpコマンドで出力したストアドオブジェクトを新環境に移行するときに、新環境側にDEFINERのユーザーが存在しないパターンなどが考えられるので、その時はストアドオブジェクトを存在するユーザーで作り直すか、DEFINERを修正するなどの対応をする必要があります。

まとめ

今回はストアドオブジェクトのDEFINER属性について簡単に解説しました。理解しないまま利用すると権限エラーにあったり、DEFINERがroot@localhostのような強い権限を持つユーザーでSQL SECURITYがDEFINERで定義されていると、意図せず他のユーザーからでも実行できてしまうリスクもあるので、きちんと理解してストアドオブジェクトを作成する必要があります。公式ドキュメントには、リスク最小化のガイドラインの項目に、不用意に実行されないようにするためのガイドラインが用意されています。これを参考に、ストアドオブジェクトを設計するとよいでしょう。

おすすめ記事

記事・ニュース一覧