Debian Codegraph @

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

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # test cases for TRIGGER privilege on db, table and column level
5 # These tests ensure that at activation time (execute statement)
6 # the user must have trigger privilege.
7 #======================================================================
8
9 --disable_abort_on_error
10
11 ###########################################################
12 ################ Section 3.5.3 ############################
13 # Check for the trigger privilege in case of prepare/exec #
14 ###########################################################
15
16 # General setup to be used in all testcases
17 let $message= #### Testcase for trigger privilege on execution time ########;
18 --source include/show_msg.inc
19
20 --disable_warnings
21 drop database if exists priv_db;
22 --enable_warnings
23 create database priv_db;
24 use priv_db;
25 eval create table t1 (f1 char(20)) engine= $engine_type;
26
27 create User test_yesprivs@localhost;
28 set password for test_yesprivs@localhost = 'PWD';
29 create User test_useprivs@localhost;
30 set password for test_useprivs@localhost = 'PWD';
31
32 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
33 revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost;
34
35 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
36
37 connection default;
38 select current_user;
39 --replace_column 6 #
40 show triggers;
41 grant select, insert, update ,trigger
42 on priv_db.t1 to test_yesprivs@localhost
43 with grant option;
44 grant select
45 on priv_db.t1 to test_useprivs@localhost;
46 show grants for test_yesprivs@localhost;
47
48 connection yes_privs;
49 select current_user;
50 use priv_db;
51 create trigger trg1_1 before INSERT on t1 for each row
52 set new.f1 = 'trig 1_1-yes';
53 grant insert on t1 to test_useprivs@localhost;
54 prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')';
55 execute ins1;
56 select f1 from t1 order by f1;
57 prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')';
58
59 connect (use_privs,localhost,test_useprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
60 select current_user;
61 use priv_db;
62 prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')';
63 execute ins1;
64 select f1 from t1 order by f1;
65
66 connection default;
67 select current_user;
68 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
69 show grants for test_yesprivs@localhost;
70
71 connection yes_privs;
72 select current_user;
73 --error ER_TABLEACCESS_DENIED_ERROR
74 execute ins1;
75 select f1 from t1 order by f1;
76 prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')';
77
78 connection use_privs;
79 select current_user;
80 prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')';
81 --error ER_TABLEACCESS_DENIED_ERROR
82 execute ins1;
83 select f1 from t1 order by f1;
84
85 connection default;
86 select current_user;
87 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
88 show grants for test_yesprivs@localhost;
89
90 connection yes_privs;
91 select current_user;
92 execute ins1;
93 select f1 from t1 order by f1;
94 prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')';
95
96 connection use_privs;
97 select current_user;
98 execute ins1;
99 select f1 from t1 order by f1;
100 prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')';
101
102 connection default;
103 select current_user;
104 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
105 show grants for test_yesprivs@localhost;
106
107 connection yes_privs;
108 select current_user;
109 --error ER_TABLEACCESS_DENIED_ERROR
110 execute ins1;
111 select f1 from t1 order by f1;
112
113 connection use_privs;
114 select current_user;
115 --error ER_TABLEACCESS_DENIED_ERROR
116 execute ins1;
117 select f1 from t1 order by f1;
118
119 connection default;
120 select current_user;
121 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
122 show grants for test_yesprivs@localhost;
123
124 connection yes_privs;
125 select current_user;
126 execute ins1;
127 select f1 from t1 order by f1;
128
129 connection use_privs;
130 select current_user;
131 execute ins1;
132 select f1 from t1 order by f1;
133
134 connection default;
135 select current_user;
136 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
137 show grants for test_yesprivs@localhost;
138
139 connection yes_privs;
140 select current_user;
141 execute ins1;
142 select f1 from t1 order by f1;
143 deallocate prepare ins1;
144
145 connection use_privs;
146 select current_user;
147 execute ins1;
148 select f1 from t1 order by f1;
149 deallocate prepare ins1;
150
151 connection default;
152 select current_user;
153 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
154 show grants for test_yesprivs@localhost;
155
156 connection yes_privs;
157 select current_user;
158 drop trigger trg1_1;
159
160 connection default;
161 select current_user;
162
163 # Cleanup prepare
164 --disable_warnings
165 disconnect yes_privs;
166
167 connection default;
168 select current_user;
169 --enable_warnings
170
171
172 # general Cleanup
173 --disable_warnings
174 drop database if exists priv_db;
175 drop user test_yesprivs@localhost;
176 drop user test_useprivs@localhost;
177 --enable_warnings
178