Description:
There is no warning or error given when a user who is a trigger definer has their permissions are revoked.
example below done in 5.0.90
How to repeat:
- session 1
mysql> show grants for 'trig_test'@'localhost';
+-------------------------------------------------------------+
| Grants for trig_test@localhost |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'trig_test'@'localhost' |
| GRANT ALL PRIVILEGES ON `trig`.* TO 'trig_test'@'localhost' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> GRANT SUPER ON *.* to 'trig_test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
- session 2 under the trig_test user
mysql> use trig;
Database changed
mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test4(
-> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> b4 INT DEFAULT 0
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter |
mysql>
mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
-> FOR EACH ROW BEGIN
-> INSERT INTO test2 SET a2 = NEW.a1;
-> DELETE FROM test3 WHERE a3 = NEW.a1;
-> UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
-> END;
-> |
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> exit;
Bye
- session 1
mysql> drop user 'trig-test'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'trig-test'@'localhost'
mysql>
mysql>
mysql> revoke all on trig.* from 'trig_test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'trig_test'@'localhost';
+-----------------------------------------------+
| Grants for trig_test@localhost |
+-----------------------------------------------+
| GRANT SUPER ON *.* TO 'trig_test'@'localhost' |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from Information_schema.triggers\G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: trig
TRIGGER_NAME: testref
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: trig
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: trig_test@localhost
1 row in set (0.02 sec)
mysql> use trig
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);
ERROR 1142 (42000): INSERT command denied to user 'trig_test'@'localhost' for table 'test2'
- session 2
mysql> use trig;
ERROR 1044 (42000): Access denied for user 'trig_test'@'localhost' to database 'trig'
Suggested fix:
having some kind of warning or error would be nice. and/or maybe changing the definer in the information_schema rather then leaving the orphaned trigger hanging out there.
Description: There is no warning or error given when a user who is a trigger definer has their permissions are revoked. example below done in 5.0.90 How to repeat: - session 1 mysql> show grants for 'trig_test'@'localhost'; +-------------------------------------------------------------+ | Grants for trig_test@localhost | +-------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'trig_test'@'localhost' | | GRANT ALL PRIVILEGES ON `trig`.* TO 'trig_test'@'localhost' | +-------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> GRANT SUPER ON *.* to 'trig_test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye - session 2 under the trig_test user mysql> use trig; Database changed mysql> CREATE TABLE test1(a1 INT); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE test2(a2 INT); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE test4( -> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> b4 INT DEFAULT 0 -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter | mysql> mysql> CREATE TRIGGER testref BEFORE INSERT ON test1 -> FOR EACH ROW BEGIN -> INSERT INTO test2 SET a2 = NEW.a1; -> DELETE FROM test3 WHERE a3 = NEW.a1; -> UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; -> END; -> | Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> exit; Bye - session 1 mysql> drop user 'trig-test'@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for 'trig-test'@'localhost' mysql> mysql> mysql> revoke all on trig.* from 'trig_test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'trig_test'@'localhost'; +-----------------------------------------------+ | Grants for trig_test@localhost | +-----------------------------------------------+ | GRANT SUPER ON *.* TO 'trig_test'@'localhost' | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from Information_schema.triggers\G *************************** 1. row *************************** TRIGGER_CATALOG: NULL TRIGGER_SCHEMA: trig TRIGGER_NAME: testref EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: NULL EVENT_OBJECT_SCHEMA: trig EVENT_OBJECT_TABLE: test1 ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: DEFINER: trig_test@localhost 1 row in set (0.02 sec) mysql> use trig Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4); ERROR 1142 (42000): INSERT command denied to user 'trig_test'@'localhost' for table 'test2' - session 2 mysql> use trig; ERROR 1044 (42000): Access denied for user 'trig_test'@'localhost' to database 'trig' Suggested fix: having some kind of warning or error would be nice. and/or maybe changing the definer in the information_schema rather then leaving the orphaned trigger hanging out there.