| 1 |
SHOW TABLES FROM information_schema LIKE 'TRIGGERS'; |
| 2 |
Tables_in_information_schema (TRIGGERS) |
| 3 |
TRIGGERS |
| 4 |
####################################################################### |
| 5 |
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT |
| 6 |
####################################################################### |
| 7 |
DROP VIEW IF EXISTS test.v1; |
| 8 |
DROP PROCEDURE IF EXISTS test.p1; |
| 9 |
DROP FUNCTION IF EXISTS test.f1; |
| 10 |
CREATE VIEW test.v1 AS SELECT * FROM information_schema.TRIGGERS; |
| 11 |
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TRIGGERS; |
| 12 |
CREATE FUNCTION test.f1() returns BIGINT |
| 13 |
BEGIN |
| 14 |
DECLARE counter BIGINT DEFAULT NULL; |
| 15 |
SELECT COUNT(*) INTO counter FROM information_schema.TRIGGERS; |
| 16 |
RETURN counter; |
| 17 |
END// |
| 18 |
# Attention: The printing of the next result sets is disabled. |
| 19 |
SELECT * FROM information_schema.TRIGGERS; |
| 20 |
SELECT * FROM test.v1; |
| 21 |
CALL test.p1; |
| 22 |
SELECT test.f1(); |
| 23 |
DROP VIEW test.v1; |
| 24 |
DROP PROCEDURE test.p1; |
| 25 |
DROP FUNCTION test.f1; |
| 26 |
######################################################################### |
| 27 |
# Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout |
| 28 |
######################################################################### |
| 29 |
DESCRIBE information_schema.TRIGGERS; |
| 30 |
Field Type Null Key Default Extra |
| 31 |
TRIGGER_CATALOG varchar(64) YES NULL |
| 32 |
TRIGGER_SCHEMA varchar(64) YES NULL |
| 33 |
TRIGGER_NAME varchar(64) NO NULL |
| 34 |
EVENT_MANIPULATION enum('INSERT','UPDATE','DELETE') NO NULL |
| 35 |
EVENT_OBJECT_CATALOG varchar(64) YES NULL |
| 36 |
EVENT_OBJECT_SCHEMA varchar(64) YES NULL |
| 37 |
EVENT_OBJECT_TABLE varchar(64) YES NULL |
| 38 |
ACTION_ORDER int unsigned NO NULL |
| 39 |
ACTION_CONDITION binary(0) YES NULL |
| 40 |
ACTION_STATEMENT longtext NO NULL |
| 41 |
ACTION_ORIENTATION varchar(3) NO |
| 42 |
ACTION_TIMING enum('BEFORE','AFTER') NO NULL |
| 43 |
ACTION_REFERENCE_OLD_TABLE binary(0) YES NULL |
| 44 |
ACTION_REFERENCE_NEW_TABLE binary(0) YES NULL |
| 45 |
ACTION_REFERENCE_OLD_ROW varchar(3) NO |
| 46 |
ACTION_REFERENCE_NEW_ROW varchar(3) NO |
| 47 |
CREATED timestamp(2) NO NULL |
| 48 |
SQL_MODE set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ALLOW_INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','TIME_TRUNCATE_FRACTIONAL') NO NULL |
| 49 |
DEFINER varchar(288) NO NULL |
| 50 |
CHARACTER_SET_CLIENT varchar(64) NO NULL |
| 51 |
COLLATION_CONNECTION varchar(64) NO NULL |
| 52 |
DATABASE_COLLATION varchar(64) NO NULL |
| 53 |
SHOW CREATE TABLE information_schema.TRIGGERS; |
| 54 |
View Create View character_set_client collation_connection |
| 55 |
TRIGGERS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TRIGGERS` AS select (`cat`.`name` collate utf8mb3_tolower_ci) AS `TRIGGER_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `TRIGGER_SCHEMA`,`trg`.`name` AS `TRIGGER_NAME`,`trg`.`event_type` AS `EVENT_MANIPULATION`,(`cat`.`name` collate utf8mb3_tolower_ci) AS `EVENT_OBJECT_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `EVENT_OBJECT_SCHEMA`,(`tbl`.`name` collate utf8mb3_tolower_ci) AS `EVENT_OBJECT_TABLE`,`trg`.`action_order` AS `ACTION_ORDER`,NULL AS `ACTION_CONDITION`,`trg`.`action_statement_utf8` AS `ACTION_STATEMENT`,'ROW' AS `ACTION_ORIENTATION`,`trg`.`action_timing` AS `ACTION_TIMING`,NULL AS `ACTION_REFERENCE_OLD_TABLE`,NULL AS `ACTION_REFERENCE_NEW_TABLE`,'OLD' AS `ACTION_REFERENCE_OLD_ROW`,'NEW' AS `ACTION_REFERENCE_NEW_ROW`,`trg`.`created` AS `CREATED`,`trg`.`sql_mode` AS `SQL_MODE`,`trg`.`definer` AS `DEFINER`,`cs_client`.`name` AS `CHARACTER_SET_CLIENT`,`coll_conn`.`name` AS `COLLATION_CONNECTION`,`coll_db`.`name` AS `DATABASE_COLLATION` from (((((((`mysql`.`triggers` `trg` join `mysql`.`tables` `tbl` on((`tbl`.`id` = `trg`.`table_id`))) join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `coll_client` on((`coll_client`.`id` = `trg`.`client_collation_id`))) join `mysql`.`character_sets` `cs_client` on((`cs_client`.`id` = `coll_client`.`character_set_id`))) join `mysql`.`collations` `coll_conn` on((`coll_conn`.`id` = `trg`.`connection_collation_id`))) join `mysql`.`collations` `coll_db` on((`coll_db`.`id` = `trg`.`schema_collation_id`))) where ((`tbl`.`type` <> 'VIEW') and (0 <> can_access_trigger(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`))) utf8mb3 utf8mb3_general_ci |
| 56 |
SHOW COLUMNS FROM information_schema.TRIGGERS; |
| 57 |
Field Type Null Key Default Extra |
| 58 |
TRIGGER_CATALOG varchar(64) YES NULL |
| 59 |
TRIGGER_SCHEMA varchar(64) YES NULL |
| 60 |
TRIGGER_NAME varchar(64) NO NULL |
| 61 |
EVENT_MANIPULATION enum('INSERT','UPDATE','DELETE') NO NULL |
| 62 |
EVENT_OBJECT_CATALOG varchar(64) YES NULL |
| 63 |
EVENT_OBJECT_SCHEMA varchar(64) YES NULL |
| 64 |
EVENT_OBJECT_TABLE varchar(64) YES NULL |
| 65 |
ACTION_ORDER int unsigned NO NULL |
| 66 |
ACTION_CONDITION binary(0) YES NULL |
| 67 |
ACTION_STATEMENT longtext NO NULL |
| 68 |
ACTION_ORIENTATION varchar(3) NO |
| 69 |
ACTION_TIMING enum('BEFORE','AFTER') NO NULL |
| 70 |
ACTION_REFERENCE_OLD_TABLE binary(0) YES NULL |
| 71 |
ACTION_REFERENCE_NEW_TABLE binary(0) YES NULL |
| 72 |
ACTION_REFERENCE_OLD_ROW varchar(3) NO |
| 73 |
ACTION_REFERENCE_NEW_ROW varchar(3) NO |
| 74 |
CREATED timestamp(2) NO NULL |
| 75 |
SQL_MODE set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ALLOW_INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','TIME_TRUNCATE_FRACTIONAL') NO NULL |
| 76 |
DEFINER varchar(288) NO NULL |
| 77 |
CHARACTER_SET_CLIENT varchar(64) NO NULL |
| 78 |
COLLATION_CONNECTION varchar(64) NO NULL |
| 79 |
DATABASE_COLLATION varchar(64) NO NULL |
| 80 |
SELECT * FROM information_schema.triggers |
| 81 |
WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL |
| 82 |
OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL |
| 83 |
OR action_reference_new_table IS NOT NULL |
| 84 |
ORDER BY TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME; |
| 85 |
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION |
| 86 |
def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN |
| 87 |
DECLARE dummy INT; |
| 88 |
SET GLOBAL regexp_time_limit = 0; |
| 89 |
SELECT "" REGEXP NEW.pattern INTO dummy; |
| 90 |
SET GLOBAL regexp_time_limit = DEFAULT; |
| 91 |
END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_0900_ai_ci |
| 92 |
def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN |
| 93 |
DECLARE dummy INT; |
| 94 |
SET GLOBAL regexp_time_limit = 0; |
| 95 |
SELECT "" REGEXP NEW.pattern INTO dummy; |
| 96 |
SET GLOBAL regexp_time_limit = DEFAULT; |
| 97 |
END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_0900_ai_ci |
| 98 |
def sys sys_config_insert_set_user INSERT def sys sys_config 1 NULL BEGIN |
| 99 |
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN |
| 100 |
SET NEW.set_by = USER(); |
| 101 |
END IF; |
| 102 |
END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION mysql.sys@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci |
| 103 |
def sys sys_config_update_set_user UPDATE def sys sys_config 1 NULL BEGIN |
| 104 |
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN |
| 105 |
SET NEW.set_by = USER(); |
| 106 |
END IF; |
| 107 |
END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION mysql.sys@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci |
| 108 |
################################################################################## |
| 109 |
# Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information |
| 110 |
################################################################################## |
| 111 |
DROP DATABASE IF EXISTS db_datadict; |
| 112 |
CREATE DATABASE db_datadict charset latin1; |
| 113 |
DROP USER 'testuser1'@'localhost'; |
| 114 |
CREATE USER 'testuser1'@'localhost'; |
| 115 |
DROP USER 'testuser2'@'localhost'; |
| 116 |
CREATE USER 'testuser2'@'localhost'; |
| 117 |
DROP USER 'testuser3'@'localhost'; |
| 118 |
CREATE USER 'testuser3'@'localhost'; |
| 119 |
DROP USER 'testuser4'@'localhost'; |
| 120 |
CREATE USER 'testuser4'@'localhost'; |
| 121 |
GRANT TRIGGER ON *.* TO 'testuser1'@'localhost'; |
| 122 |
GRANT TRIGGER ON *.* TO 'testuser3'@'localhost'; |
| 123 |
GRANT TRIGGER ON *.* TO 'testuser4'@'localhost'; |
| 124 |
GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; |
| 125 |
# Establish connection testuser1 (user=testuser1) |
| 126 |
CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT) |
| 127 |
ENGINE = <engine_type>; |
| 128 |
CREATE TRIGGER trg1 BEFORE INSERT |
| 129 |
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before; |
| 130 |
GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost'; |
| 131 |
REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost'; |
| 132 |
GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'; |
| 133 |
SELECT * FROM information_schema.triggers |
| 134 |
WHERE trigger_name = 'trg1'; |
| 135 |
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION |
| 136 |
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 137 |
SHOW TRIGGERS FROM db_datadict; |
| 138 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 139 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 140 |
# Establish connection testuser2 (user=testuser2) |
| 141 |
SHOW GRANTS FOR 'testuser2'@'localhost'; |
| 142 |
Grants for testuser2@localhost |
| 143 |
GRANT USAGE ON *.* TO `testuser2`@`localhost` |
| 144 |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `db_datadict`.`t1` TO `testuser2`@`localhost` |
| 145 |
# No TRIGGER Privilege --> no result for query |
| 146 |
SELECT * FROM information_schema.triggers |
| 147 |
WHERE trigger_name = 'trg1'; |
| 148 |
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION |
| 149 |
SHOW TRIGGERS FROM db_datadict; |
| 150 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 151 |
# Establish connection testuser3 (user=testuser3) |
| 152 |
SHOW GRANTS FOR 'testuser3'@'localhost'; |
| 153 |
Grants for testuser3@localhost |
| 154 |
GRANT TRIGGER ON *.* TO `testuser3`@`localhost` |
| 155 |
GRANT SELECT ON `db_datadict`.`t1` TO `testuser3`@`localhost` |
| 156 |
# TRIGGER Privilege + SELECT Privilege on t1 --> result for query |
| 157 |
SELECT * FROM information_schema.triggers |
| 158 |
WHERE trigger_name = 'trg1'; |
| 159 |
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION |
| 160 |
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 161 |
SHOW TRIGGERS FROM db_datadict; |
| 162 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 163 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 164 |
# Establish connection testuser4 (user=testuser4) |
| 165 |
SHOW GRANTS FOR 'testuser4'@'localhost'; |
| 166 |
Grants for testuser4@localhost |
| 167 |
GRANT TRIGGER ON *.* TO `testuser4`@`localhost` |
| 168 |
# TRIGGER Privilege + no SELECT Privilege on t1 --> result for query |
| 169 |
SELECT * FROM db_datadict.t1; |
| 170 |
ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table 't1' |
| 171 |
DESC db_datadict.t1; |
| 172 |
ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table 't1' |
| 173 |
SELECT * FROM information_schema.triggers |
| 174 |
WHERE trigger_name = 'trg1'; |
| 175 |
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION |
| 176 |
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 177 |
SHOW TRIGGERS FROM db_datadict; |
| 178 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 179 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 180 |
# Switch to connection default and close connections testuser1 - testuser4 |
| 181 |
SELECT * FROM information_schema.triggers |
| 182 |
WHERE trigger_name = 'trg1'; |
| 183 |
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION |
| 184 |
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 185 |
SHOW TRIGGERS FROM db_datadict; |
| 186 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 187 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci |
| 188 |
DROP USER 'testuser1'@'localhost'; |
| 189 |
Warnings: |
| 190 |
Warning 4005 User 'testuser1'@'localhost' is referenced as a definer account in a trigger. |
| 191 |
DROP USER 'testuser2'@'localhost'; |
| 192 |
DROP USER 'testuser3'@'localhost'; |
| 193 |
DROP USER 'testuser4'@'localhost'; |
| 194 |
DROP DATABASE db_datadict; |
| 195 |
######################################################################### |
| 196 |
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications |
| 197 |
######################################################################### |
| 198 |
######################################################################## |
| 199 |
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and |
| 200 |
# DDL on INFORMATION_SCHEMA tables are not supported |
| 201 |
######################################################################## |
| 202 |
DROP DATABASE IF EXISTS db_datadict; |
| 203 |
CREATE DATABASE db_datadict charset latin1; |
| 204 |
CREATE TABLE db_datadict.t1 (f1 BIGINT) |
| 205 |
ENGINE = <engine_type>; |
| 206 |
CREATE TRIGGER db_datadict.trg1 BEFORE INSERT |
| 207 |
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before; |
| 208 |
INSERT INTO information_schema.triggers |
| 209 |
SELECT * FROM information_schema.triggers; |
| 210 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 211 |
UPDATE information_schema.triggers SET trigger_schema = 'test' |
| 212 |
WHERE table_name = 't1'; |
| 213 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 214 |
DELETE FROM information_schema.triggers WHERE trigger_name = 't1'; |
| 215 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 216 |
TRUNCATE information_schema.triggers; |
| 217 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 218 |
CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema); |
| 219 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 220 |
ALTER TABLE information_schema.triggers DROP PRIMARY KEY; |
| 221 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 222 |
ALTER TABLE information_schema.triggers ADD f1 INT; |
| 223 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 224 |
DROP TABLE information_schema.triggers; |
| 225 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 226 |
ALTER TABLE information_schema.triggers RENAME db_datadict.triggers; |
| 227 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 228 |
ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers; |
| 229 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 230 |
DROP DATABASE db_datadict; |