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 = password('PWD');
29 create User test_useprivs@localhost;
30 set password for test_useprivs@localhost = password('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,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK);
36 # next is to check that we connected above
37 connection yes_privs;
38
39 connection default;
40 select current_user;
41 --replace_column 6 #
42 show triggers;
43 grant select, insert, update ,trigger
44 on priv_db.t1 to test_yesprivs@localhost
45 with grant option;
46 grant select
47 on priv_db.t1 to test_useprivs@localhost;
48 show grants for test_yesprivs@localhost;
49
50 connection yes_privs;
51 select current_user;
52 use priv_db;
53 create trigger trg1_1 before INSERT on t1 for each row
54 set new.f1 = 'trig 1_1-yes';
55 grant insert on t1 to test_useprivs@localhost;
56 prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')';
57 execute ins1;
58 select f1 from t1 order by f1;
59 prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')';
60
61 connect (use_privs,localhost,test_useprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK);
62 # next is to check that we connected above
63 connection use_privs;
64 select current_user;
65 prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')';
66 execute ins1;
67 select f1 from t1 order by f1;
68
69 connection default;
70 select current_user;
71 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
72 show grants for test_yesprivs@localhost;
73
74 connection yes_privs;
75 select current_user;
76 --error ER_TABLEACCESS_DENIED_ERROR
77 execute ins1;
78 select f1 from t1 order by f1;
79 prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')';
80
81 connection use_privs;
82 select current_user;
83 prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')';
84 --error ER_TABLEACCESS_DENIED_ERROR
85 execute ins1;
86 select f1 from t1 order by f1;
87
88 connection default;
89 select current_user;
90 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
91 show grants for test_yesprivs@localhost;
92
93 connection yes_privs;
94 select current_user;
95 execute ins1;
96 select f1 from t1 order by f1;
97 prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')';
98
99 connection use_privs;
100 select current_user;
101 execute ins1;
102 select f1 from t1 order by f1;
103 prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')';
104
105 connection default;
106 select current_user;
107 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
108 show grants for test_yesprivs@localhost;
109
110 connection yes_privs;
111 select current_user;
112 --error ER_TABLEACCESS_DENIED_ERROR
113 execute ins1;
114 select f1 from t1 order by f1;
115
116 connection use_privs;
117 select current_user;
118 --error ER_TABLEACCESS_DENIED_ERROR
119 execute ins1;
120 select f1 from t1 order by f1;
121
122 connection default;
123 select current_user;
124 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
125 show grants for test_yesprivs@localhost;
126
127 connection yes_privs;
128 select current_user;
129 execute ins1;
130 select f1 from t1 order by f1;
131
132 connection use_privs;
133 select current_user;
134 execute ins1;
135 select f1 from t1 order by f1;
136
137 connection default;
138 select current_user;
139 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
140 show grants for test_yesprivs@localhost;
141
142 connection yes_privs;
143 select current_user;
144 execute ins1;
145 select f1 from t1 order by f1;
146 deallocate prepare ins1;
147
148 connection use_privs;
149 select current_user;
150 execute ins1;
151 select f1 from t1 order by f1;
152 deallocate prepare ins1;
153
154 connection default;
155 select current_user;
156 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
157 show grants for test_yesprivs@localhost;
158
159 connection yes_privs;
160 select current_user;
161 drop trigger trg1_1;
162
163 connection default;
164 select current_user;
165
166 # Cleanup prepare
167 --disable_warnings
168 disconnect yes_privs;
169
170 connection default;
171 select current_user;
172 --enable_warnings
173
174
175 # general Cleanup
176 --disable_warnings
177 drop database if exists priv_db;
178 drop user test_yesprivs@localhost;
179 drop user test_useprivs@localhost;
180 --enable_warnings
181