Debian Codegraph @

mysql-test/suite/funcs_1/datadict/is_triggers.inc

raw
1 # suite/funcs_1/datadict/is_triggers.inc
2 #
3 # Check the layout of information_schema.triggers and the impact of
4 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
5 #
6 # Note:
7 # This test is not intended
8 # - to show information about the all time existing triggers
9 # (there are no in the moment) within the databases information_schema
10 # and mysql
11 # - for checking storage engine properties
12 # Therefore please do not alter $engine_type and $other_engine_type.
13 #
14 # Author:
15 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
16 # testsuite funcs_1
17 # Create this script based on older scripts and new code.
18 # Last Change:
19 # 2008-06-11 mleich Move t/is_triggers.test to this file and
20 #
21
22 let $is_table = TRIGGERS;
23
24 # The table INFORMATION_SCHEMA.TRIGGERS must exist
25 eval SHOW TABLES FROM information_schema LIKE '$is_table';
26
27 --echo #######################################################################
28 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
29 --echo #######################################################################
30 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
31 # statement, just as if it were an ordinary user-defined table.
32 #
33 --source suite/funcs_1/datadict/is_table_query.inc
34
35
36 --echo #########################################################################
37 --echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout
38 --echo #########################################################################
39 # Ensure that the INFORMATION_SCHEMA.TRIGGERS table has the following columns,
40 # in the following order:
41 #
42 # TRIGGER_CATALOG NULL
43 # TRIGGER_SCHEMA name of the database in which the trigger occurs
44 # TRIGGER_NAME
45 # EVENT_MANIPULATION event associated with the trigger
46 # ('INSERT', 'DELETE', or 'UPDATE')
47 # EVENT_OBJECT_CATALOG NULL
48 # EVENT_OBJECT_SCHEMA database in which the table associated with the
49 # trigger occurs
50 # EVENT_OBJECT_TABLE name of the table associated with the trigger
51 # ACTION_ORDER 0
52 # ACTION_CONDITION NULL
53 # ACTION_STATEMENT
54 # ACTION_ORIENTATION ROW
55 # ACTION_TIMING 'BEFORE' or 'AFTER'
56 # ACTION_REFERENCE_OLD_TABLE NULL
57 # ACTION_REFERENCE_NEW_TABLE NULL
58 # ACTION_REFERENCE_OLD_ROW OLD
59 # ACTION_REFERENCE_NEW_ROW NEW
60 # CREATED NULL (0)
61 # SQL_MODE server SQL mode that was in effect at the time
62 # when the trigger was created
63 # (also used during trigger execution)
64 # DEFINER who defined the trigger
65 #
66 --source suite/funcs_1/datadict/datadict_bug_12777.inc
67 eval DESCRIBE information_schema.$is_table;
68 --source suite/funcs_1/datadict/datadict_bug_12777.inc
69 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
70 eval SHOW CREATE TABLE information_schema.$is_table;
71 --source suite/funcs_1/datadict/datadict_bug_12777.inc
72 eval SHOW COLUMNS FROM information_schema.$is_table;
73
74
75 # Note: Retrieval of information within information_schema.columns about
76 # information_schema.tables is in is_columns_is.test.
77
78 # Note: Do not use mtr --sorted_result:
79 # the result set contains the trigger body (sql statements),
80 # which itself contains several lines.
81 # This confuses --sorted_result
82
83 --replace_column 17 #
84 SELECT * FROM information_schema.triggers
85 WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL
86 OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL
87 OR action_reference_new_table IS NOT NULL
88 ORDER BY TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME;
89
90
91 --echo ##################################################################################
92 --echo # Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information
93 --echo ##################################################################################
94 # 3.2.18.2: Ensure that the table shows the relevant information on every
95 # trigger on which the current user or PUBLIC has privileges.
96 # 3.2.18.3: Ensure that the table does not show any information on any trigger
97 # on which the current user and public have no privileges.
98 # The SUPER (before 5.1.22) or TRIGGER (since 5.1.22) privilege is required for
99 # - creation of triggers
100 # - retrieval in INFORMATION_SCHEMA.TRIGGERS (affects size of result set)
101 #
102 --disable_warnings
103 DROP DATABASE IF EXISTS db_datadict;
104 --enable_warnings
105 CREATE DATABASE db_datadict charset latin1;
106 --error 0,ER_CANNOT_USER
107 DROP USER 'testuser1'@'localhost';
108 CREATE USER 'testuser1'@'localhost';
109 --error 0,ER_CANNOT_USER
110 DROP USER 'testuser2'@'localhost';
111 CREATE USER 'testuser2'@'localhost';
112 --error 0,ER_CANNOT_USER
113 DROP USER 'testuser3'@'localhost';
114 CREATE USER 'testuser3'@'localhost';
115 --error 0,ER_CANNOT_USER
116 DROP USER 'testuser4'@'localhost';
117 CREATE USER 'testuser4'@'localhost';
118
119 GRANT TRIGGER ON *.* TO 'testuser1'@'localhost';
120 GRANT TRIGGER ON *.* TO 'testuser3'@'localhost';
121 GRANT TRIGGER ON *.* TO 'testuser4'@'localhost';
122 GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
123
124 let $my_select = SELECT * FROM information_schema.triggers
125 WHERE trigger_name = 'trg1';
126 let $my_show = SHOW TRIGGERS FROM db_datadict;
127 --echo # Establish connection testuser1 (user=testuser1)
128 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
129 connect (testuser1, localhost, testuser1, , db_datadict);
130 --replace_result $engine_type <engine_type>
131 eval
132 CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT)
133 ENGINE = $engine_type;
134 CREATE TRIGGER trg1 BEFORE INSERT
135 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
136 GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost';
137 REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost';
138 GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
139 --replace_column 17 #
140 eval $my_select;
141 --replace_column 6 #
142 eval $my_show;
143
144 --echo # Establish connection testuser2 (user=testuser2)
145 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
146 connect (testuser2, localhost, testuser2, , db_datadict);
147 SHOW GRANTS FOR 'testuser2'@'localhost';
148 --echo # No TRIGGER Privilege --> no result for query
149 --replace_column 17 #
150 eval $my_select;
151 --replace_column 6 #
152 eval $my_show;
153
154 --echo # Establish connection testuser3 (user=testuser3)
155 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
156 connect (testuser3, localhost, testuser3, , test);
157 SHOW GRANTS FOR 'testuser3'@'localhost';
158 --echo # TRIGGER Privilege + SELECT Privilege on t1 --> result for query
159 --replace_column 17 #
160 eval $my_select;
161 --replace_column 6 #
162 eval $my_show;
163
164 --echo # Establish connection testuser4 (user=testuser4)
165 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
166 connect (testuser4, localhost, testuser4, , test);
167 SHOW GRANTS FOR 'testuser4'@'localhost';
168 --echo # TRIGGER Privilege + no SELECT Privilege on t1 --> result for query
169 --disable_abort_on_error
170 SELECT * FROM db_datadict.t1;
171 DESC db_datadict.t1;
172 --replace_column 17 #
173 eval $my_select;
174 --replace_column 6 #
175 eval $my_show;
176
177 --echo # Switch to connection default and close connections testuser1 - testuser4
178 connection default;
179 disconnect testuser1;
180 disconnect testuser2;
181 disconnect testuser3;
182 disconnect testuser4;
183 --replace_column 17 #
184 eval $my_select;
185 --replace_column 6 #
186 eval $my_show;
187 DROP USER 'testuser1'@'localhost';
188 DROP USER 'testuser2'@'localhost';
189 DROP USER 'testuser3'@'localhost';
190 DROP USER 'testuser4'@'localhost';
191 DROP DATABASE db_datadict;
192
193
194 --echo #########################################################################
195 --echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
196 --echo #########################################################################
197 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
198 # column) automatically inserts all relevant information on that
199 # object into every appropriate INFORMATION_SCHEMA table.
200 # 3.2.1.14: Ensure that the alteration of any existing database object
201 # automatically updates all relevant information on that object in
202 # every appropriate INFORMATION_SCHEMA table.
203 # 3.2.1.15: Ensure that the dropping of any existing database object
204 # automatically deletes all relevant information on that object from
205 # every appropriate INFORMATION_SCHEMA table.
206 # FIXME: To be implemented
207
208
209 --echo ########################################################################
210 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
211 --echo # DDL on INFORMATION_SCHEMA tables are not supported
212 --echo ########################################################################
213 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
214 # INFORMATION_SCHEMA table.
215 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
216 # INFORMATION_SCHEMA table.
217 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
218 # INFORMATION_SCHEMA table.
219 # 3.2.1.8: Ensure that no user may create an index on an
220 # INFORMATION_SCHEMA table.
221 # 3.2.1.9: Ensure that no user may alter the definition of an
222 # INFORMATION_SCHEMA table.
223 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
224 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
225 # other database.
226 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
227 # in an INFORMATION_SCHEMA table.
228 #
229 --disable_warnings
230 DROP DATABASE IF EXISTS db_datadict;
231 --enable_warnings
232 CREATE DATABASE db_datadict charset latin1;
233 --replace_result $engine_type <engine_type>
234 eval
235 CREATE TABLE db_datadict.t1 (f1 BIGINT)
236 ENGINE = $engine_type;
237 CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
238 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
239
240 --error ER_DBACCESS_DENIED_ERROR
241 INSERT INTO information_schema.triggers
242 SELECT * FROM information_schema.triggers;
243
244 # Note:
245 # 1. We can get different error messages here.
246 # 2. We do not want to unify the individual messages to the far to unspecific
247 # 'Got one of the listed errors'.
248 let $my_error_message =
249 ##### The previous statement must fail ######
250 # Server type | expected error name | expected error message
251 # --------------------------------------------------------------------------------------------------------------------
252 # MySQL | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
253 ;
254 --disable_abort_on_error
255 UPDATE information_schema.triggers SET trigger_schema = 'test'
256 WHERE table_name = 't1';
257 if (!$mysql_errno)
258 {
259 --echo $my_error_message
260 exit;
261 }
262 --enable_abort_on_error
263
264 --error ER_DBACCESS_DENIED_ERROR
265 DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
266 --error ER_DBACCESS_DENIED_ERROR
267 TRUNCATE information_schema.triggers;
268
269 --error ER_DBACCESS_DENIED_ERROR
270 CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
271
272 --error ER_DBACCESS_DENIED_ERROR
273 ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
274 --error ER_DBACCESS_DENIED_ERROR
275 ALTER TABLE information_schema.triggers ADD f1 INT;
276
277 --error ER_DBACCESS_DENIED_ERROR
278 DROP TABLE information_schema.triggers;
279
280 --error ER_DBACCESS_DENIED_ERROR
281 ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
282 --error ER_DBACCESS_DENIED_ERROR
283 ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
284
285 # Cleanup
286 DROP DATABASE db_datadict;
287