MySQLでTRIGGERやVIEWなどのストアドオブジェクトを作成したあとにSHOW構文で作成したものを確認すると、DEFINER=xxxx@xxxxやSQL SECURITY xxxxなどが追加で付与されていることが確認できます。このDEFINER属性とSQL SECURITY特性により、そのストアドオブジェクトを実行するユーザーが変わります。今回は、ストアドオブジェクトの作成時のDEFINERにはどういう特徴があるのかを確認していきます。なお、今回利用しているMySQLのバージョンは8.
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を実行します。そのため、もしそのユーザーに適切な権限がないとエラーとなってしまします。
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属性は、ストアドオブジェクト
もし、DEFINERに自身
SQL SECURITY特性
SQL SECURITY特性にはDEFINERまたはINVOKERのいずれかの値が入ります。ストアドルーチンやVIEWのストアドオブジェクトを作成する際に、このSQL SECURITY特性を指定しなかった
もしSQL SECURITYにDEFINERが設定されているときは、DEFINER属性で定義しているユーザーにより実行されます。SQL SECURITY INVOKERの場合は、ストアドオブジェクトを呼び出したユーザーが実行します。そのため、SQL SECURITY特性がDEFINERの場合はDEFINERに設定したユーザーに権限が必要ですが、INVOKERの場合は実行するユーザーにそのストアドオブジェクトを実行できる権限を付与しなければいけません。
なお、このSQL SECURITY特性はストアドルーチンとVIEWには設定できますが、TRIGGERとEVENTにはありません。TRIGGERとEVENTにはDEFINERに適切な権限を付与するようにしてください。
ここでVIEWを使って簡単な実験をしてみます。
db1にdb2.
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内のテーブルを参照することができました。
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.
とくに、マイグレーション作業などでmysqldumpコマンドで出力したストアドオブジェクトを新環境に移行するときに、新環境側にDEFINERのユーザーが存在しないパターンなどが考えられるので、その時はストアドオブジェクトを存在するユーザーで作り直すか、DEFINERを修正するなどの対応をする必要があります。
まとめ
今回はストアドオブジェクトのDEFINER属性について簡単に解説しました。理解しないまま利用すると権限エラーにあったり、DEFINERがroot@localhostのような強い権限を持つユーザーでSQL SECURITYがDEFINERで定義されていると、意図せず他のユーザーからでも実行できてしまうリスクもあるので、きちんと理解してストアドオブジェクトを作成する必要があります。公式ドキュメントには、リスク最小化のガイドラインの項目に、不用意に実行されないようにするためのガイドラインが用意されています。これを参考に、ストアドオブジェクトを設計するとよいでしょう。