STATEMENT_DIGEST関数 はSQLステートメントから定数をノーマライズしたもの（ダイジェスト）をハッシュ化して返してくれる関数。
MySQL 5.6とそれ以降の
performance_schema.events_statements_summary_by_digest なんかで使われているアレを関数で引くことができる。
パッと思いつく感じだと、「今まではダイジェストの値を直接計算できなかったから QUERY_SAMPLE_TEXT カラムの値とかから何となく探していたけど、これからは直接 WHERE digest = STATEMENT_DIGEST('SELECT ..') とかで検索できる」というのがメリットとしてあるんだけれど、クエリーをノーマライズして一元化できるってことはつまりホワイトリストっぽいものが作れるんじゃないかなと思ったので軽くテスト。
まずはホワイトリストを登録するためのテーブルを作る。
大事なのは digest だけであって、 digest_text は単なるおまけ（後々見るのに楽かなって）
mysql80 7> SHOW CREATE TABLE myeval.whitelist\G
*************************** 1. row ***************************
Table: whitelist
Create Table: CREATE TABLE `whitelist` (
`digest` varchar(64) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
`digest_text` text COLLATE utf8mb4_ja_0900_as_cs
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.01 sec)
( ´-｀).oO（utf8mb4_binのがいいかな
これに例えばこんなクエリーでホワイトリスト登録する。
mysql80 7> INSERT INTO myeval.whitelist VALUES (STATEMENT_DIGEST('SELECT * FROM d1.t1 WHERE num = 1'), STATEMENT_DIGEST_TEXT('SELECT * FROM d1.t1 WHERE num = 1'));
Query OK, 1 row affected (0.00 sec)
mysql80 7> SELECT * FROM myeval.whitelist;
+------------------------------------------------------------------+--------------------------------------------+
| digest | digest_text |
+------------------------------------------------------------------+--------------------------------------------+
| d214d5d8f31ce686d36be01a22bc7cfff76dd8b7b131644c7fcad28e76f78489 | SELECT * FROM `d1` . `t1` WHERE `num` = ? |
+------------------------------------------------------------------+--------------------------------------------+
1 row in set (0.01 sec)
num = 1 の部分はどうせノーマライズされるのでテキトーな値。
これに、「 myeval.whitelist に登録があればそのクエリーを実行、なければError: 1142をレイズする」ストアドプロシージャを用意する。
delimiter //
CREATE PROCEDURE myeval.eval_query (IN sql_statement TEXT)
BEGIN
DECLARE is_white TINYINT;
SELECT (digest IS NOT NULL) FROM myeval.whitelist WHERE digest = STATEMENT_DIGEST(sql_statement) INTO is_white;
IF is_white = 1 THEN
SET @sql_statement := sql_statement;
PREPARE st FROM @sql_statement;
EXECUTE st;
DEALLOCATE PREPARE st;
ELSE
SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = "Query isn't registored in myeval.whitelist", MYSQL_ERRNO = 1142;
END IF;
END
//
delimiter ;
このプロシージャーを実行する権限だけを持たせたユーザーを用意して
mysql80 7> CREATE USER yoku0825;
Query OK, 0 rows affected (0.00 sec)
mysql80 7> GRANT EXECUTE ON PROCEDURE myeval.eval_query TO yoku0825;
Query OK, 0 rows affected (0.03 sec)
そのアカウントでログイン。
mysql80 9> SHOW GRANTS;
+--------------------------------------------------------------------+
| Grants for yoku0825@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%` |
| GRANT EXECUTE ON PROCEDURE `myeval`.`eval_query` TO `yoku0825`@`%` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
当然このアカウントでは直接 d1.t1 に対するアクセスはできないけれども、 SQL SECURITY DEFINER なストアドプロシージャを通せば、ストアドを作ったアカウントの権限でそのSQLが実行できるようになる。
mysql80 9> SELECT * FROM d1.t1 WHERE num = 1;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 't1'
mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num = 1");
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num = 2");
+-----+------+
| num | val |
+-----+------+
| 2 | two |
+-----+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ダイジェストが一致すれば通すので、細かい定数部分が違っても問題なく。
mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE val = 'one'");
ERROR 1142 (42000): Query isn't registored in myeval.whitelist
mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num < 2");
ERROR 1142 (42000): Query isn't registored in myeval.whitelist
mysql80 9> CALL myeval.eval_query("INSERT INTO d1.t1 VALUES (3, 'three')");
ERROR 1142 (42000): Query isn't registored in myeval.whitelist
ただしステートメントがそもそも違うものや、対象カラム、演算子が違うとダイジェストが変わるので弾かれる。
mysql80 7> INSERT INTO myeval.whitelist VALUES (STATEMENT_DIGEST('INSERT INTO d1.t1 VALUES (1, "one")'), STATEMENT_DIGEST_TEXT('INSERT INTO d1.t1 VALUES (1, "one")'));
Query OK, 1 row affected (0.01 sec)
mysql80 7> SELECT * FROM myeval.whitelist;
+------------------------------------------------------------------+--------------------------------------------+
| digest | digest_text |
+------------------------------------------------------------------+--------------------------------------------+
| d214d5d8f31ce686d36be01a22bc7cfff76dd8b7b131644c7fcad28e76f78489 | SELECT * FROM `d1` . `t1` WHERE `num` = ? |
| bcaf175197bfc4753d6de62d76dcd05484a9cb5ca65f4cb2f4b1b065c5e6ae0d | INSERT INTO `d1` . `t1` VALUES (...) |
+------------------------------------------------------------------+--------------------------------------------+
2 rows in set (0.00 sec)
テキトーに myeval.whitelist に登録してやれば
mysql80 9> CALL myeval.eval_query("INSERT INTO d1.t1 VALUES (3, 'three')");
Query OK, 0 rows affected (0.01 sec)
mysql80 9> CALL myeval.eval_query("SELECT * FROM d1.t1 WHERE num = 3");
+-----+-------+
| num | val |
+-----+-------+
| 3 | three |
+-----+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
特に再読み込みとかせずに実行できるようにできる。
SQLだけでできるってことは、某RDSとかでも8.0が来たら出来るかも？ とか :-P
