お題

トリガーってどの権限で動くんだっけ？定義したユーザの権限？トリガーを引いたユーザの権限？
プロシージャと違って、SQL SECURITY の指定がない

TLDR;

トリガーはDEFINERに指定したユーザで動作する
DEFINERをナシにすることはできない、省略した場合はCREATE TRIGGERを実行したユーザがDEFINERに入る
SQL SECURITYは指定できない。トリガーを引いたユーザで、トリガーを実行することはできない。DEFINERで指定したユーザで必ず実行される
SUPERを持っているのに、read_onlyのDBに書き込めない場合は、トリガーを疑うべし

試します

適当にテーブルを作ります。

-- トリガーを仕掛けるテーブル
mysql> CREATE TABLE tbl_p (pk SERIAL PRIMARY KEY, var1 VARCHAR(100), updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)

-- トリガーによって更新するテーブル
mysql> CREATE TABLE tbl_c (pk SERIAL PRIMARY KEY, triggered_by VARCHAR(100), updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)

tbl_p に行をINSERTすると、tbl_c に行をINSERTするトリガーを作ります。このときに、triggered_by カラムにどのユーザで実行されたかを INSERT INTO tbl_c (triggered_by) VALUES(CURRENT_USER()) のようにして、記録しておきます。

delimiter //
 CREATE TRIGGER trg_t BEFORE INSERT ON tbl_p
       FOR EACH ROW
       BEGIN
           INSERT INTO tbl_c (triggered_by) VALUES(CURRENT_USER());
    END; //
delimiter ;

mysql> SHOW CREATE TRIGGER t.trg_t \G
*************************** 1. row ***************************
               Trigger: trg_t
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`user1`@`%` TRIGGER `trg_t` BEFORE INSERT ON `tbl_p` FOR EACH ROW BEGIN
           INSERT INTO tbl_c (triggered_by) VALUES(CURRENT_USER());
    END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_general_ci
               Created: 2020-03-06 16:16:50.90
1 row in set (0.00 sec)

DEFINERを省略すると、CREATE TRIGGERを実行したユーザがDEFINERとなりますね。 DEFINERを省略するということはできないようです。

トリガーの動作確認。トリガーがDEFINERの user1 で動作することが確認できました。

mysql> INSERT INTO tbl_p (var1) VALUES('test');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbl_c;
+----+--------------+---------------------+
| pk | triggered_by | updated_at          |
+----+--------------+---------------------+
|  1 | user1@%      | 2020-03-06 16:18:53 |
+----+--------------+---------------------+
1 row in set (0.00 sec)

念の為、root でトリガーを引いてみます。 root でトリガーを引いても、user1の権限で tbl_c に INSERTが行われたことが分かりました。

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl_p (var1) VALUES('test');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbl_c;
+----+--------------+---------------------+
| pk | triggered_by | updated_at          |
+----+--------------+---------------------+
|  1 | user1@%      | 2020-03-06 16:18:53 |
|  2 | user1@%      | 2020-03-06 16:19:27 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)

read only のときの挙動

read_only でも SUPER 権限があれば書き込むことができます。しかし、上述のようにTRIGGERはDEFINERの権限で動作するため、read_only状態でトリガーのある書き込もうとすると、エラーとなります。

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

-- トリガーのないテーブルには書き込める
mysql> INSERT INTO tx1 VALUES(1);
Query OK, 1 row affected (0.01 sec)

-- superを持たないユーザが作ったトリガーがあると書き込めない
mysql> INSERT INTO tbl_p (var1) VALUES('I am super');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

mita2 database life

主にMySQLに関するメモです

TRIGGERの権限とDEFINER

お題

TLDR;

試します

read only のときの挙動