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