MySQLのView, Stored Procedureのセキュリティ設定、デフォルトはDEFINER

DEFINER= rootでSQL SECURITY DEFINERの話です。

論より実験。

d2user> SHOW GRANTS;
+------------------------------------------------+
| Grants for d2user@%                            |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'd2user'@'%'             |
| GRANT ALL PRIVILEGES ON `d2`.* TO 'd2user'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

d2データベースにだけ権限を持ったd2userがいます。
ここにrootで、おとなりd1データベースのテーブルをSELECTするViewを作ります。

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

root> SELECT * FROM d2.v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

こんな感じで。
さてオチは読めると思いますがこのd2.v1はd2userで

d2user> SELECT * FROM d2.v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

参照できます。
どんなロジックが働いているかというと、

d2user> SHOW CREATE VIEW d2.v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val` from `d1`.`t1`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

さっきのCREATE VIEWで設定しなかったところが暗黙のデフォルトで補完されてますね。

ALGORITHMはViewを参照するときのベーステーブルからの取り出し方に関わるものなので今回は関係なく、DEFINER= root@localhost と SQL SECURITY DEFINERがキモです。

DEFINERはこのViewに紐付けられたユーザー属性で、暗黙のデフォルトはCURRENT_USERです。CREATE VIEWを叩いたのはroot@localhostだったのでその値が来ています。DEFINER= でCURRENT_USER以外の値を設定できるのはSuper_priv持ちのユーザーだけです。

SQL SECURITY DEFINERは「このビューを操作する時はDEFINERの権限を使ってViewの中身を処理する」という属性です。つまり、このビューに対するあらゆる操作はroot@localhostの権限で行われます。つまりが、

d2user> INSERT INTO v1 VALUES (4, 'four');
Query OK, 1 row affected (0.01 sec)

d2user> SELECT * FROM v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
|   4 | four  |
+-----+-------+
4 rows in set (0.00 sec)

d2user> DELETE FROM v1 WHERE num= 2;
Query OK, 1 row affected (0.02 sec)

d2user> SELECT * FROM v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   3 | three |
|   4 | four  |
+-----+-------+
3 rows in set (0.01 sec)

なんでもできます。
これを防ぐにはDEFINERをテキトーなユーザー権限に変えるか、SQL SECURITYをINVOKERにするかのどちらかです。

DEFINERを書き換えるのはSuper_priv持ちのユーザーでないといけない、また、結局「操作したユーザーではないユーザー権限でベーステーブルにアクセスする」可能性が捨てきれないので、SQL SECURITY INVOKERにすることが多いんじゃないでしょうか。

root> ALTER SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM d1.t1;
Query OK, 0 rows affected (0.00 sec)

d2user> SELECT * FROM v1;
ERROR 1356 (HY000): View 'd2.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Stored Procedure, Stored FunctiomもSQL SECURITY {DEFINER|INVOKER}を持っていて、同じような動き方をします。
RDSでは逆にSQL SECURITY DEFINERを上手く使って、mysql.slow_logテーブルを「rootだろうと直接操作させず、rdsadmin(でしたっけ？)の権限で操作する」ことでローテーションさせたりできますね。

ちなみにTRIGGERはDEFINER項目はありますがSQL SECURITY {DEFINER|INVOKER}は無く、常にDEFINER権限で操作されます。

root> CREATE TRIGGER t1_ins_trigger AFTER INSERT ON d2.t1 FOR EACH ROW INSERT INTO d1.t2 VALUES (new.num, new.val)//
Query OK, 0 rows affected (0.01 sec)

root> SHOW CREATE TRIGGER t1_ins_trigger\G
*************************** 1. row ***************************
               Trigger: t1_ins_trigger
              sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER t1_ins_trigger AFTER INSERT ON d2.t1 FOR EACH ROW INSERT INTO d1.t2 VALUES (new.num, new.val)
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8_general_ci
1 row in set (0.00 sec)

d2user> INSERT INTO d2.t1 VALUES (10, 'ten');
Query OK, 1 row affected (0.01 sec)

root> SELECT * FROM d1.t2;
+-----+------+
| num | val  |
+-----+------+
|  10 | ten  |
+-----+------+
1 row in set (0.00 sec)

ちなみに、存在しないユーザーをDEFINER= で設定してSQL SECURITY DEFINERすると、誰からも操作できないViewが出来上がります。mysqldumpでビュー定義だけ引っ張り出してテスト環境にリストアした時にたまにやりますね。