| 1 |
set @save_sql_mode=@@global.sql_mode; |
| 2 |
set global sql_mode=""; |
| 3 |
SHOW TABLES FROM information_schema LIKE 'TRIGGERS'; |
| 4 |
Tables_in_information_schema (TRIGGERS) |
| 5 |
TRIGGERS |
| 6 |
####################################################################### |
| 7 |
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT |
| 8 |
####################################################################### |
| 9 |
DROP VIEW IF EXISTS test.v1; |
| 10 |
DROP PROCEDURE IF EXISTS test.p1; |
| 11 |
DROP FUNCTION IF EXISTS test.f1; |
| 12 |
CREATE VIEW test.v1 AS SELECT * FROM information_schema.TRIGGERS; |
| 13 |
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TRIGGERS; |
| 14 |
CREATE FUNCTION test.f1() returns BIGINT |
| 15 |
BEGIN |
| 16 |
DECLARE counter BIGINT DEFAULT NULL; |
| 17 |
SELECT COUNT(*) INTO counter FROM information_schema.TRIGGERS; |
| 18 |
RETURN counter; |
| 19 |
END// |
| 20 |
# Attention: The printing of the next result sets is disabled. |
| 21 |
SELECT * FROM information_schema.TRIGGERS; |
| 22 |
SELECT * FROM test.v1; |
| 23 |
CALL test.p1; |
| 24 |
SELECT test.f1(); |
| 25 |
DROP VIEW test.v1; |
| 26 |
DROP PROCEDURE test.p1; |
| 27 |
DROP FUNCTION test.f1; |
| 28 |
######################################################################### |
| 29 |
# Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout |
| 30 |
######################################################################### |
| 31 |
DESCRIBE information_schema.TRIGGERS; |
| 32 |
Field Type Null Key Default Extra |
| 33 |
TRIGGER_CATALOG varchar(512) NO NULL |
| 34 |
TRIGGER_SCHEMA varchar(64) NO NULL |
| 35 |
TRIGGER_NAME varchar(64) NO NULL |
| 36 |
EVENT_MANIPULATION varchar(6) NO NULL |
| 37 |
EVENT_OBJECT_CATALOG varchar(512) NO NULL |
| 38 |
EVENT_OBJECT_SCHEMA varchar(64) NO NULL |
| 39 |
EVENT_OBJECT_TABLE varchar(64) NO NULL |
| 40 |
ACTION_ORDER bigint(4) NO NULL |
| 41 |
ACTION_CONDITION longtext YES NULL |
| 42 |
ACTION_STATEMENT longtext YES NULL |
| 43 |
ACTION_ORIENTATION varchar(9) NO NULL |
| 44 |
ACTION_TIMING varchar(6) NO NULL |
| 45 |
ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL |
| 46 |
ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL |
| 47 |
ACTION_REFERENCE_OLD_ROW varchar(3) NO NULL |
| 48 |
ACTION_REFERENCE_NEW_ROW varchar(3) NO NULL |
| 49 |
CREATED datetime(2) YES NULL |
| 50 |
SQL_MODE varchar(8192) NO NULL |
| 51 |
DEFINER varchar(384) YES NULL |
| 52 |
CHARACTER_SET_CLIENT varchar(32) NO NULL |
| 53 |
COLLATION_CONNECTION varchar(64) NO NULL |
| 54 |
DATABASE_COLLATION varchar(64) NO NULL |
| 55 |
SHOW CREATE TABLE information_schema.TRIGGERS; |
| 56 |
Table Create Table |
| 57 |
TRIGGERS CREATE TEMPORARY TABLE `TRIGGERS` ( |
| 58 |
`TRIGGER_CATALOG` varchar(512) NOT NULL, |
| 59 |
`TRIGGER_SCHEMA` varchar(64) NOT NULL, |
| 60 |
`TRIGGER_NAME` varchar(64) NOT NULL, |
| 61 |
`EVENT_MANIPULATION` varchar(6) NOT NULL, |
| 62 |
`EVENT_OBJECT_CATALOG` varchar(512) NOT NULL, |
| 63 |
`EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL, |
| 64 |
`EVENT_OBJECT_TABLE` varchar(64) NOT NULL, |
| 65 |
`ACTION_ORDER` bigint(4) NOT NULL, |
| 66 |
`ACTION_CONDITION` longtext, |
| 67 |
`ACTION_STATEMENT` longtext, |
| 68 |
`ACTION_ORIENTATION` varchar(9) NOT NULL, |
| 69 |
`ACTION_TIMING` varchar(6) NOT NULL, |
| 70 |
`ACTION_REFERENCE_OLD_TABLE` varchar(64), |
| 71 |
`ACTION_REFERENCE_NEW_TABLE` varchar(64), |
| 72 |
`ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL, |
| 73 |
`ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL, |
| 74 |
`CREATED` datetime(2), |
| 75 |
`SQL_MODE` varchar(8192) NOT NULL, |
| 76 |
`DEFINER` varchar(384), |
| 77 |
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL, |
| 78 |
`COLLATION_CONNECTION` varchar(64) NOT NULL, |
| 79 |
`DATABASE_COLLATION` varchar(64) NOT NULL |
| 80 |
) DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
| 81 |
SHOW COLUMNS FROM information_schema.TRIGGERS; |
| 82 |
Field Type Null Key Default Extra |
| 83 |
TRIGGER_CATALOG varchar(512) NO NULL |
| 84 |
TRIGGER_SCHEMA varchar(64) NO NULL |
| 85 |
TRIGGER_NAME varchar(64) NO NULL |
| 86 |
EVENT_MANIPULATION varchar(6) NO NULL |
| 87 |
EVENT_OBJECT_CATALOG varchar(512) NO NULL |
| 88 |
EVENT_OBJECT_SCHEMA varchar(64) NO NULL |
| 89 |
EVENT_OBJECT_TABLE varchar(64) NO NULL |
| 90 |
ACTION_ORDER bigint(4) NO NULL |
| 91 |
ACTION_CONDITION longtext YES NULL |
| 92 |
ACTION_STATEMENT longtext YES NULL |
| 93 |
ACTION_ORIENTATION varchar(9) NO NULL |
| 94 |
ACTION_TIMING varchar(6) NO NULL |
| 95 |
ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL |
| 96 |
ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL |
| 97 |
ACTION_REFERENCE_OLD_ROW varchar(3) NO NULL |
| 98 |
ACTION_REFERENCE_NEW_ROW varchar(3) NO NULL |
| 99 |
CREATED datetime(2) YES NULL |
| 100 |
SQL_MODE varchar(8192) NO NULL |
| 101 |
DEFINER varchar(384) YES NULL |
| 102 |
CHARACTER_SET_CLIENT varchar(32) NO NULL |
| 103 |
COLLATION_CONNECTION varchar(64) NO NULL |
| 104 |
DATABASE_COLLATION varchar(64) NO NULL |
| 105 |
SELECT * FROM information_schema.triggers |
| 106 |
WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL |
| 107 |
OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL |
| 108 |
OR action_reference_new_table IS NOT NULL |
| 109 |
ORDER BY trigger_schema, trigger_name; |
| 110 |
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 |
| 111 |
def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 112 |
def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 113 |
################################################################################## |
| 114 |
# Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information |
| 115 |
################################################################################## |
| 116 |
DROP DATABASE IF EXISTS db_datadict; |
| 117 |
CREATE DATABASE db_datadict; |
| 118 |
DROP USER 'testuser1'@'localhost'; |
| 119 |
CREATE USER 'testuser1'@'localhost'; |
| 120 |
DROP USER 'testuser2'@'localhost'; |
| 121 |
CREATE USER 'testuser2'@'localhost'; |
| 122 |
DROP USER 'testuser3'@'localhost'; |
| 123 |
CREATE USER 'testuser3'@'localhost'; |
| 124 |
DROP USER 'testuser4'@'localhost'; |
| 125 |
CREATE USER 'testuser4'@'localhost'; |
| 126 |
GRANT TRIGGER ON *.* TO 'testuser1'@'localhost'; |
| 127 |
GRANT TRIGGER ON *.* TO 'testuser3'@'localhost'; |
| 128 |
GRANT TRIGGER ON *.* TO 'testuser4'@'localhost'; |
| 129 |
GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; |
| 130 |
connect testuser1, localhost, testuser1, , db_datadict; |
| 131 |
CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT) |
| 132 |
ENGINE = <engine_type>; |
| 133 |
CREATE TRIGGER trg1 BEFORE INSERT |
| 134 |
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before; |
| 135 |
GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost'; |
| 136 |
REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost'; |
| 137 |
GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'; |
| 138 |
SELECT * FROM information_schema.triggers |
| 139 |
WHERE trigger_name = 'trg1'; |
| 140 |
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 |
| 141 |
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 # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 142 |
SHOW TRIGGERS FROM db_datadict; |
| 143 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 144 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 145 |
connect testuser2, localhost, testuser2, , db_datadict; |
| 146 |
SHOW GRANTS FOR 'testuser2'@'localhost'; |
| 147 |
# No TRIGGER Privilege --> no result for query |
| 148 |
SELECT * FROM information_schema.triggers |
| 149 |
WHERE trigger_name = 'trg1'; |
| 150 |
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 |
| 151 |
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 # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 152 |
SHOW TRIGGERS FROM db_datadict; |
| 153 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 154 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 155 |
connect testuser3, localhost, testuser3, , test; |
| 156 |
SHOW GRANTS FOR 'testuser3'@'localhost'; |
| 157 |
# TRIGGER Privilege + SELECT Privilege on t1 --> result for query |
| 158 |
SELECT * FROM information_schema.triggers |
| 159 |
WHERE trigger_name = 'trg1'; |
| 160 |
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 |
| 161 |
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 # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 162 |
SHOW TRIGGERS FROM db_datadict; |
| 163 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 164 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 165 |
connect testuser4, localhost, testuser4, , test; |
| 166 |
SHOW GRANTS FOR 'testuser4'@'localhost'; |
| 167 |
# TRIGGER Privilege + no SELECT Privilege on t1 --> result for query |
| 168 |
SELECT * FROM db_datadict.t1; |
| 169 |
f1 f2 f3 |
| 170 |
DESC db_datadict.t1; |
| 171 |
Field Type Null Key Default Extra |
| 172 |
f1 int(11) YES NULL |
| 173 |
f2 int(11) YES NULL |
| 174 |
f3 int(11) YES NULL |
| 175 |
SELECT * FROM information_schema.triggers |
| 176 |
WHERE trigger_name = 'trg1'; |
| 177 |
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 |
| 178 |
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 # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 179 |
SHOW TRIGGERS FROM db_datadict; |
| 180 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 181 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 182 |
connection default; |
| 183 |
disconnect testuser1; |
| 184 |
disconnect testuser2; |
| 185 |
disconnect testuser3; |
| 186 |
disconnect testuser4; |
| 187 |
SELECT * FROM information_schema.triggers |
| 188 |
WHERE trigger_name = 'trg1'; |
| 189 |
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 |
| 190 |
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 # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 191 |
SHOW TRIGGERS FROM db_datadict; |
| 192 |
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation |
| 193 |
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci |
| 194 |
DROP USER 'testuser1'@'localhost'; |
| 195 |
DROP USER 'testuser2'@'localhost'; |
| 196 |
DROP USER 'testuser3'@'localhost'; |
| 197 |
DROP USER 'testuser4'@'localhost'; |
| 198 |
DROP DATABASE db_datadict; |
| 199 |
######################################################################### |
| 200 |
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications |
| 201 |
######################################################################### |
| 202 |
######################################################################## |
| 203 |
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and |
| 204 |
# DDL on INFORMATION_SCHEMA tables are not supported |
| 205 |
######################################################################## |
| 206 |
DROP DATABASE IF EXISTS db_datadict; |
| 207 |
CREATE DATABASE db_datadict; |
| 208 |
CREATE TABLE db_datadict.t1 (f1 BIGINT) |
| 209 |
ENGINE = <engine_type>; |
| 210 |
CREATE TRIGGER db_datadict.trg1 BEFORE INSERT |
| 211 |
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before; |
| 212 |
INSERT INTO information_schema.triggers |
| 213 |
SELECT * FROM information_schema.triggers; |
| 214 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 215 |
UPDATE information_schema.triggers SET trigger_schema = 'test' |
| 216 |
WHERE table_name = 't1'; |
| 217 |
Got one of the listed errors |
| 218 |
DELETE FROM information_schema.triggers WHERE trigger_name = 't1'; |
| 219 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 220 |
TRUNCATE information_schema.triggers; |
| 221 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 222 |
CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema); |
| 223 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 224 |
ALTER TABLE information_schema.triggers DROP PRIMARY KEY; |
| 225 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 226 |
ALTER TABLE information_schema.triggers ADD f1 INT; |
| 227 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 228 |
DROP TABLE information_schema.triggers; |
| 229 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 230 |
ALTER TABLE information_schema.triggers RENAME db_datadict.triggers; |
| 231 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 232 |
ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers; |
| 233 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 234 |
DROP DATABASE db_datadict; |
| 235 |
set global sql_mode=@save_sql_mode; |