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