MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
triggers_03e_global_db_mix.inc
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 user and db level of Triggers #
12 #########################################################
13 
14 # General setup to be used in all testcases
15 let $message= #### Testcase for mix of user(global) and db level: ####;
16 --source include/show_msg.inc
17 
18  --disable_warnings
19  drop database if exists priv_db;
20  drop database if exists no_priv_db;
21  --enable_warnings
22  create database priv_db;
23  create database no_priv_db;
24  use priv_db;
25  eval create table t1 (f1 char(20)) engine= $engine_type;
26  use no_priv_db;
27  eval create table t1 (f1 char(20)) engine= $engine_type;
28 
29  create User test_yesprivs@localhost;
30  set password for test_yesprivs@localhost = password('PWD');
31  revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
32  grant ALL on *.* to test_yesprivs@localhost;
33  show grants for test_yesprivs@localhost;
34 
35  create User test_noprivs@localhost;
36  set password for test_noprivs@localhost = password('PWD');
37  revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
38  grant SELECT,INSERT on *.* to test_noprivs@localhost;
39  show grants for test_noprivs@localhost;
40 
41  connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
42 
43  connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
44 
45  connection yes_privs;
46  select current_user;
47 let $message= trigger privilege on user level for create:;
48 --source include/show_msg.inc
49  use priv_db;
50 
51  create trigger trg1_1 before INSERT on t1 for each row
52  set new.f1 = 'trig 1_1-yes';
53  insert into t1 (f1) values ('insert-no');
54  select f1 from t1 order by f1;
55 
56  use no_priv_db;
57  create trigger priv_db.trg1_5 before UPDATE on priv_db.t1
58  for each row
59  set new.f1 = 'trig 1_5-yes';
60  insert into priv_db.t1 (f1) values ('insert-no');
61  select f1 from priv_db.t1 order by f1;
62  drop trigger priv_db.trg1_5;
63 
64  connection no_privs;
65  select current_user;
66  use priv_db;
67  insert into t1 (f1) values ('insert-no');
68  select f1 from t1 order by f1;
69 
70  connection default;
71  select current_user;
72  use priv_db;
73  insert into t1 (f1) values ('insert-no');
74  select f1 from t1 order by f1;
75 
76  revoke TRIGGER on *.* from test_yesprivs@localhost;
77  show grants for test_yesprivs@localhost;
78 
79 # change of privilege only active after reconnecting the session
80 
81  --disable_warnings
82  disconnect yes_privs;
83  --enable_warnings
84  connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
85  select current_user;
86  use priv_db;
87  show triggers;
88  select * from information_schema.triggers;
89  --error ER_TABLEACCESS_DENIED_ERROR
90  drop trigger trg1_1;
91 
92  connection default;
93  select current_user;
94  show grants;
95  drop trigger trg1_1;
96  use priv_db;
97 
98 ################ Section 3.5.3 ############
99 # Check for the db level of Triggers #
100 ###########################################
101 let $message= no trigger privilege on db level for create:;
102 --source include/show_msg.inc
103 
104  connection yes_privs;
105  select current_user;
106  --error ER_TABLEACCESS_DENIED_ERROR
107  create trigger trg1_1 before INSERT on t1 for each row
108  set new.f1 = 'trig 1_1-no';
109 
110  connection no_privs;
111  select current_user;
112  use priv_db;
113  insert into t1 (f1) values ('insert-yes');
114  select f1 from t1 order by f1;
115 
116  connection default;
117  select current_user;
118  grant TRIGGER on priv_db.* to test_yesprivs@localhost;
119  show grants for test_yesprivs@localhost;
120 
121 let $message= trigger privilege on db level for create:;
122 --source include/show_msg.inc
123  connection yes_privs;
124  select current_user;
125 
126 # active after 'use db'
127 
128  use priv_db;
129  create trigger trg1_2 before INSERT on t1 for each row
130  set new.f1 = 'trig 1_2-yes';
131  --error ER_TABLEACCESS_DENIED_ERROR
132  create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
133  for each row
134  set new.f1 = 'trig 1_9-yes';
135  use no_priv_db;
136  --error ER_TABLEACCESS_DENIED_ERROR
137  create trigger trg1_2 before INSERT on t1 for each row
138  set new.f1 = 'trig 1_2-no';
139  create trigger priv_db.trg1_9 before UPDATE on priv_db.t1
140  for each row
141  set new.f1 = 'trig 1_9-yes';
142 
143  connection no_privs;
144  select current_user;
145  use priv_db;
146  insert into t1 (f1) values ('insert-yes');
147  select f1 from t1 order by f1;
148  use no_priv_db;
149  insert into t1 (f1) values ('insert-yes');
150  select f1 from t1 order by f1;
151  --error ER_TABLEACCESS_DENIED_ERROR
152  drop trigger priv_db.trg1_9;
153 
154  connection default;
155  select current_user;
156  drop trigger priv_db.trg1_9;
157  revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
158  use priv_db;
159  --error ER_TABLEACCESS_DENIED_ERROR
160  insert into t1 (f1) values ('insert-yes');
161  select f1 from t1 order by f1;
162  grant TRIGGER on *.* to test_yesprivs@localhost;
163  show grants for test_yesprivs@localhost;
164 
165  connection yes_privs;
166  select current_user;
167  use no_priv_db;
168  --error ER_TABLEACCESS_DENIED_ERROR
169  create trigger trg1_2 before INSERT on t1 for each row
170  set new.f1 = 'trig 1_2-no';
171 
172  connection no_privs;
173  select current_user;
174  use priv_db;
175  insert into t1 (f1) values ('insert-no');
176  select f1 from t1 order by f1;
177  use no_priv_db;
178  insert into t1 (f1) values ('insert-yes');
179  select f1 from t1 order by f1;
180 
181  --disable_warnings
182  disconnect yes_privs;
183  --enable_warnings
184  connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
185  select current_user;
186  use no_priv_db;
187  create trigger trg1_2 before INSERT on t1 for each row
188  set new.f1 = 'trig 1_2-yes';
189  --disable_warnings
190  disconnect yes_privs;
191  --enable_warnings
192 
193  connection no_privs;
194  select current_user;
195  use priv_db;
196  insert into t1 (f1) values ('insert-no');
197  select f1 from t1 order by f1;
198  use no_priv_db;
199  insert into t1 (f1) values ('insert-no');
200  select f1 from t1 order by f1;
201  --disable_warnings
202  disconnect no_privs;
203 
204 # Cleanup table level
205  connection default;
206  select current_user;
207 
208 # general Cleanup
209  drop database if exists priv_db;
210  drop database if exists no_priv_db;
211  drop database if exists h1;
212  drop user test_yesprivs@localhost;
213  drop user test_noprivs@localhost;
214  --enable_warnings
215