Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_db_table_mix.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 mix of db and table level of Triggers #
12 #########################################################
13
14 # General setup to be used in all testcases
15 let $message= ####### Testcase for mix of db and table level: #######;
16 --source include/show_msg.inc
17
18 --disable_warnings
19 drop database if exists priv1_db;
20 drop database if exists priv2_db;
21 --enable_warnings
22 create database priv1_db;
23 create database priv2_db;
24 use priv1_db;
25 eval create table t1 (f1 char(20)) engine= $engine_type;
26 eval create table t2 (f1 char(20)) engine= $engine_type;
27 use priv2_db;
28 eval create table t1 (f1 char(20)) engine= $engine_type;
29
30 create User test_yesprivs@localhost;
31 set password for test_yesprivs@localhost = 'PWD';
32 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
33 grant ALL on priv1_db.* to test_yesprivs@localhost;
34 grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost;
35 show grants for test_yesprivs@localhost;
36
37 create User test_noprivs@localhost;
38 set password for test_noprivs@localhost = 'PWD';
39 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
40 grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost;
41 grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost;
42 show grants for test_noprivs@localhost;
43
44 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
45 use priv1_db;
46 connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
47 use priv1_db;
48
49 # trigger priv on db level->create trigger for all tables successful
50
51 let $message= trigger privilege on one db1 db level, not on db2;
52 --source include/show_msg.inc
53 connection yes_privs;
54 select current_user;
55 use priv1_db;
56 create trigger trg1_1 before INSERT on t1 for each row
57 set new.f1 = 'trig 1_1-yes';
58 create trigger trg2_1 before INSERT on t2 for each row
59 set new.f1 = 'trig 2_1-yes';
60 use priv2_db;
61 --error ER_TABLEACCESS_DENIED_ERROR
62 create trigger trg1_1 before INSERT on t1 for each row
63 set new.f1 = 'trig1_1-yes';
64
65 connection no_privs;
66 select current_user;
67 insert into t1 (f1) values ('insert1_no');
68 select f1 from t1 order by f1;
69 insert into t2 (f1) values ('insert1_no');
70 select f1 from t2 order by f1;
71 insert into priv2_db.t1 (f1) values ('insert21-yes');
72 select f1 from priv2_db.t1 order by f1;
73
74 use priv2_db;
75 insert into t1 (f1) values ('insert1_yes');
76 select f1 from t1 order by f1;
77 insert into priv1_db.t1 (f1) values ('insert11-no');
78 select f1 from priv1_db.t1 order by f1;
79 insert into priv1_db.t2 (f1) values ('insert22-no');
80 select f1 from priv1_db.t2 order by f1;
81
82 # revoke trigger priv on table level, that doesn't exists->fail
83
84 let $message= revoke trigger privilege on table level (not existing);
85 --source include/show_msg.inc
86 connection default;
87 select current_user;
88 use priv1_db;
89 --error ER_NONEXISTING_TABLE_GRANT
90 revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost;
91 show grants for test_yesprivs@localhost;
92
93 connection yes_privs;
94 select current_user;
95 drop trigger trg1_1;
96 drop trigger trg2_1;
97 use priv1_db;
98 drop trigger trg1_1;
99 drop trigger trg2_1;
100
101 # revoke the db level->create/drop/use trigger fail
102
103 connection default;
104 select current_user;
105 use priv1_db;
106 revoke TRIGGER on priv1_db.* from test_yesprivs@localhost;
107
108 ################ Section 3.5.3 ############
109 # Check for the table level of Triggers #
110 ###########################################
111 let $message= no trigger privilege on table level for create:;
112 --source include/show_msg.inc
113
114 connection yes_privs;
115 select current_user;
116 use priv1_db;
117 --error ER_TABLEACCESS_DENIED_ERROR
118 create trigger trg1_1 before INSERT on t1 for each row
119 set new.f1 = 'trig 1_1-no';
120
121 connection default;
122 select current_user;
123 --replace_column 6 #
124 show triggers;
125 grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost;
126 show grants for test_yesprivs@localhost;
127
128 let $message= trigger privilege on table level for create:;
129 --source include/show_msg.inc
130 connection yes_privs;
131 select current_user;
132 --replace_column 6 #
133 show triggers;
134 create trigger trg1_2 before INSERT on t1 for each row
135 set new.f1 = 'trig 1_2-yes';
136 --error ER_TABLEACCESS_DENIED_ERROR
137 create trigger trg2_1 before INSERT on t2 for each row
138 set new.f1 = 'trig 2_1-no';
139
140 connection no_privs;
141 select current_user;
142 # need 'use db' to get the newest privileges
143 use priv1_db;
144 insert into t1 (f1) values ('insert2-no');
145 select f1 from t1 order by f1;
146 insert into t2 (f1) values ('insert2-yes');
147 select f1 from t2 order by f1;
148 insert into priv2_db.t1 (f1) values ('insert22-yes');
149 select f1 from priv2_db.t1 order by f1;
150
151 connection default;
152 select current_user;
153 grant TRIGGER on priv1_db.* to test_yesprivs@localhost;
154 show grants for test_yesprivs@localhost;
155
156 # though granted on db level->create trigger fails (no use db)
157
158 connection yes_privs;
159 select current_user;
160 --error ER_TABLEACCESS_DENIED_ERROR
161 create trigger trg2_1 before INSERT on t2 for each row
162 set new.f1 = 'trig 2_1-yes';
163
164 # grant trigger takes effect
165
166 use priv1_db;
167 create trigger trg2_1 before INSERT on t2 for each row
168 set new.f1 = 'trig 2_1-yes';
169
170 connection no_privs;
171 select current_user;
172 use priv1_db;
173 insert into t1 (f1) values ('insert3-no');
174 select f1 from t1 order by f1;
175 insert into t2 (f1) values ('insert3-no');
176 select f1 from t2 order by f1;
177 use priv2_db;
178 insert into priv1_db.t1 (f1) values ('insert12-no');
179 select f1 from priv1_db.t1 order by f1;
180 insert into priv1_db.t2 (f1) values ('insert23-no');
181 select f1 from priv1_db.t2 order by f1;
182
183 disconnect no_privs;
184
185 connection yes_privs;
186 select current_user;
187
188 drop trigger trg1_2;
189 drop trigger trg2_1;
190
191 # Cleanup table level
192 --disable_warnings
193 disconnect yes_privs;
194
195 connection default;
196 select current_user;
197
198
199 # general Cleanup
200 drop database if exists priv1_db;
201 drop database if exists priv2_db;
202 drop user test_yesprivs@localhost;
203 drop user test_noprivs@localhost;
204 --enable_warnings
205