Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_table_level.inc

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # test cases for TRIGGER privilege on db, table and column level
5 #======================================================================
6
7 --disable_abort_on_error
8
9 ###########################################
10 ################ Section 3.5.3 ############
11 # Check for the db level of Triggers #
12 ###########################################
13
14 # General setup to be used in all testcases
15 let $message= ######### Testcase for table level: ########;
16 --source include/show_msg.inc
17
18 --disable_warnings
19 drop database if exists priv_db;
20 --enable_warnings
21 create database priv_db;
22 use priv_db;
23 eval create table t1 (f1 char(20)) engine= $engine_type;
24
25 create User test_yesprivs@localhost;
26 set password for test_yesprivs@localhost = password('PWD');
27 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
28
29 create User test_noprivs@localhost;
30 set password for test_noprivs@localhost = password('PWD');
31 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
32
33 connect (yes_privs,localhost,test_yesprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK);
34 # next is to check that we connected above
35 connection yes_privs;
36
37 connect (no_privs,localhost,test_noprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK);
38 # next is to check that we connected above
39 connection no_privs;
40
41 ################ Section 3.5.3 ############
42 # Check for the table level of Triggers #
43 ###########################################
44
45 # user has no trigger privilege->create trigger fail
46
47 let $message= no trigger privilege on table level for create:;
48 --source include/show_msg.inc
49 connection default;
50 select current_user;
51 --replace_column 6 #
52 show triggers;
53 grant select, insert, update on priv_db.t1 to test_yesprivs@localhost;
54 show grants for test_yesprivs@localhost;
55 grant select, update, insert on priv_db.t1 to test_noprivs@localhost;
56 show grants for test_noprivs@localhost;
57
58 connection yes_privs;
59 select current_user;
60 use priv_db;
61 show tables;
62 --error ER_TABLEACCESS_DENIED_ERROR
63 create trigger trg1_1 before INSERT on t1 for each row
64 set new.f1 = 'trig 1_1-no';
65
66 # no trigger execution, as trigger does'nt exist
67
68 connection no_privs;
69 select current_user;
70 use priv_db;
71 insert into t1 (f1) values ('insert1-yes');
72 select f1 from t1 order by f1;
73
74 connection default;
75 select current_user;
76 --replace_column 6 #
77 show triggers;
78 show tables;
79 insert into t1 (f1) values ('insert2-yes');
80 select f1 from t1 order by f1;
81 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
82 show grants for test_yesprivs@localhost;
83
84 # user got trigger privilege->create trigger successful
85
86 let $message= trigger privilege on table level for create:;
87 --source include/show_msg.inc
88 connection yes_privs;
89 select current_user;
90 --replace_column 6 #
91 show triggers;
92 create trigger trg1_2 before INSERT on t1 for each row
93 set new.f1 = 'trig 1_2-yes';
94
95 # insert now executes the trigger
96
97 connection no_privs;
98 select current_user;
99 insert into t1 (f1) values ('insert3-no');
100 select f1 from t1 order by f1;
101
102 connection default;
103 select current_user;
104 insert into t1 (f1) values ('insert4-no');
105 select f1 from t1 order by f1;
106 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
107 show grants for test_yesprivs@localhost;
108
109 # revoke triggerprivilege->drop trigger fail
110
111 let $message= no trigger privilege on table level for drop:;
112 --source include/show_msg.inc
113 connection yes_privs;
114 select current_user;
115
116 --error ER_TABLEACCESS_DENIED_ERROR
117 drop trigger trg1_2;
118
119 # no trigger priv at activation time->insert fails
120
121 let $message= no trigger privilege at activation time:;
122 --source include/show_msg.inc
123 connection no_privs;
124 select current_user;
125 --error ER_TABLEACCESS_DENIED_ERROR
126 insert into t1 (f1) values ('insert5-no');
127 select f1 from t1 order by f1;
128
129 connection default;
130 select current_user;
131 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
132
133 # trigger privilege at activation time->insert with trigger successful
134
135 let $message= trigger privilege at activation time:;
136 --source include/show_msg.inc
137 connection no_privs;
138 select current_user;
139 insert into t1 (f1) values ('insert6-no');
140 select f1 from t1 order by f1;
141
142 # trigger privilege->drop trigger successful
143 let $message= trigger privilege on table level for drop:;
144 --source include/show_msg.inc
145 connection yes_privs;
146 select current_user;
147 show grants for test_yesprivs@localhost;
148 drop trigger trg1_2;
149
150 # inserts without trigger
151
152 connection no_privs;
153 select current_user;
154 insert into t1 (f1) values ('insert7-yes');
155 select f1 from t1 order by f1;
156
157 connection default;
158 select current_user;
159 insert into t1 (f1) values ('insert8-yes');
160 select f1 from t1 order by f1;
161
162 # trigger privilege must be keep when mixinf tables with and without
163 # trigger privilege
164
165 let $message= switch to table without having trigger priv for it:;
166 --source include/show_msg.inc
167 eval create table t2 (f1 char(20)) engine= $engine_type;
168 # Adding the minimal priv to be able to set to the db
169 grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost;
170 show grants for test_yesprivs@localhost;
171
172 grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost;
173 show grants for test_noprivs@localhost;
174
175 let $message= use table with trigger privilege and without...:;
176 --source include/show_msg.inc
177 connection yes_privs;
178 select current_user;
179 --error ER_TABLEACCESS_DENIED_ERROR
180 create trigger trg2_1 before INSERT on t2 for each row
181 set new.f1 = 'trig 2_1-no';
182 create trigger trg1_3 before INSERT on t1 for each row
183 set new.f1 = 'trig 1_3-yes';
184 --error ER_TABLEACCESS_DENIED_ERROR
185 create trigger trg2_2 before UPDATE on t2 for each row
186 set new.f1 = 'trig 2_2-no';
187 create trigger trg1_4 before UPDATE on t1 for each row
188 set new.f1 = 'trig 1_4-yes';
189 --replace_column 6 #
190 show triggers;
191 connection no_privs;
192 select current_user;
193 insert into t2 (f1) values ('insert9-yes');
194 select f1 from t2 order by f1;
195 insert into t1 (f1) values ('insert10-no');
196 select f1 from t1 order by f1;
197 disconnect no_privs;
198
199 connection yes_privs;
200 select current_user;
201 --error ER_TRG_DOES_NOT_EXIST
202 drop trigger trg2_1;
203 drop trigger trg1_3;
204 --error ER_TRG_DOES_NOT_EXIST
205 drop trigger trg2_2;
206 drop trigger trg1_4;
207
208
209 # Cleanup table level
210 --disable_warnings
211 disconnect yes_privs;
212
213 connection default;
214 select current_user;
215 --enable_warnings
216
217
218 # general Cleanup
219 --disable_warnings
220 drop database if exists priv_db;
221 drop user test_yesprivs@localhost;
222 drop user test_noprivs@localhost;
223 --enable_warnings
224