← Back to branch summary

~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

« back to all changes in this revision

Viewing changes to mysql-test/suite/funcs_1/storedproc/storedproc_06.inc

  • Committer: Bazaar Package Importer
  • Author(s): Norbert Tretkowski
  • Date: 2010-03-17 14:56:02 UTC
  • Revision ID: james.westby@ubuntu.com-20100317145602-x7e30l1b2sb5s6w6
Tags: upstream-5.1.45
Import upstream version 5.1.45

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/suite/funcs_1/storedproc/storedproc_06.inc
 
1
#### suite/funcs_1/storedproc/storedproc_06.inc
 
2
 
 
3
# This test cannot be used for the embedded server because we check here
 
4
# privileges.
 
5
--source include/not_embedded.inc
 
6
 
 
7
--source suite/funcs_1/storedproc/load_sp_tb.inc
 
8
 
 
9
# ==============================================================================
 
10
# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
 
11
#
 
12
# 3.1.6 Privilege checks:
 
13
#
 
14
#  1. Ensure that no user may create a stored procedure without the
 
15
#     GRANT CREATE ROUTINE privilege.
 
16
#  2. Ensure that root always has the GRANT CREATE ROUTINE privilege.
 
17
#  3. Ensure that a user with the GRANT CREATE ROUTINE privilege can always
 
18
#     create both a procedure and a function, on any appropriate database.
 
19
#  4. Ensure that the default security provision of a stored procedure is
 
20
#     SQL SECURITY DEFINER.
 
21
#  5. Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
 
22
#     called/executed by any user, using only the privileges (including
 
23
#     database access privileges) associated with the user who created
 
24
#     the stored procedure.
 
25
#  6. Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
 
26
#     called/executed by any user, using only the privileges (including
 
27
#     database access privileges) associated with the user executing
 
28
#     the stored procedure.
 
29
#
 
30
# ==============================================================================
 
31
let $message= Section 3.1.6 - Privilege Checks:;
 
32
--source include/show_msg80.inc
 
33
 
 
34
 
 
35
connection default;
 
36
USE db_storedproc_1;
 
37
--source suite/funcs_1/include/show_connection.inc
 
38
 
 
39
# ------------------------------------------------------------------------------
 
40
let $message= Testcase 3.1.6.1:
 
41
              -----------------
 
42
Ensure that no user may create a stored procedure without the GRANT CREATE
 
43
ROUTINE privilege.;
 
44
--source include/show_msg80.inc
 
45
 
 
46
create user 'user_1'@'localhost';
 
47
 
 
48
grant all on db_storedproc_1.* to 'user_1'@'localhost';
 
49
revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
 
50
flush privileges;
 
51
 
 
52
--disable_warnings
 
53
DROP PROCEDURE IF EXISTS sp1;
 
54
--enable_warnings
 
55
 
 
56
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
57
connect (user1a, localhost, user_1, , db_storedproc_1);
 
58
--source suite/funcs_1/include/show_connection.inc
 
59
 
 
60
USE db_storedproc_1;
 
61
 
 
62
delimiter //;
 
63
--error ER_DBACCESS_DENIED_ERROR
 
64
CREATE PROCEDURE sp1(v1 char(20))
 
65
BEGIN
 
66
    SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
 
67
END//
 
68
delimiter ;//
 
69
 
 
70
disconnect user1a;
 
71
 
 
72
# add privilege again and check
 
73
connection default;
 
74
USE db_storedproc_1;
 
75
--source suite/funcs_1/include/show_connection.inc
 
76
 
 
77
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
 
78
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
79
connect (user1b, localhost, user_1, , db_storedproc_1);
 
80
--source suite/funcs_1/include/show_connection.inc
 
81
 
 
82
USE db_storedproc_1;
 
83
 
 
84
delimiter //;
 
85
CREATE PROCEDURE sp1(v1 char(20))
 
86
BEGIN
 
87
    SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
 
88
END//
 
89
delimiter ;//
 
90
disconnect user1b;
 
91
 
 
92
# cleanup
 
93
connection default;
 
94
USE db_storedproc_1;
 
95
--source suite/funcs_1/include/show_connection.inc
 
96
 
 
97
DROP USER 'user_1'@'localhost';
 
98
DROP PROCEDURE sp1;
 
99
 
 
100
 
 
101
# ------------------------------------------------------------------------------
 
102
let $message= Testcase 3.1.6.2:
 
103
              -----------------
 
104
Ensure that root always has the GRANT CREATE ROUTINE privilege.
 
105
(checked by other testscases);
 
106
--source include/show_msg80.inc
 
107
 
 
108
 
 
109
# ------------------------------------------------------------------------------
 
110
let $message= Testcase 3.1.6.3:
 
111
              -----------------
 
112
Ensure that a user with the GRANT CREATE ROUTINE privilege can always create
 
113
both a procedure and a function, on any appropriate database.
 
114
--source include/show_msg80.inc
 
115
 
 
116
 
 
117
create user 'user_1'@'localhost';
 
118
 
 
119
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
 
120
flush privileges;
 
121
 
 
122
#  disconnect default;
 
123
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
124
connect (user2, localhost, user_1, , db_storedproc_1);
 
125
--source suite/funcs_1/include/show_connection.inc
 
126
 
 
127
--disable_warnings
 
128
DROP PROCEDURE IF EXISTS sp3;
 
129
DROP FUNCTION IF EXISTS fn1;
 
130
--enable_warnings
 
131
 
 
132
delimiter //;
 
133
CREATE PROCEDURE sp3(v1 char(20))
 
134
BEGIN
 
135
   SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
 
136
END//
 
137
delimiter ;//
 
138
 
 
139
delimiter //;
 
140
CREATE FUNCTION fn1(v1 int) returns int
 
141
BEGIN
 
142
   return v1;
 
143
END//
 
144
delimiter ;//
 
145
 
 
146
disconnect user2;
 
147
 
 
148
# cleanup
 
149
connection default;
 
150
USE db_storedproc_1;
 
151
--source suite/funcs_1/include/show_connection.inc
 
152
 
 
153
drop user 'user_1'@'localhost';
 
154
DROP PROCEDURE sp3;
 
155
# This drop function shouldn't generated a warning as the
 
156
# privileges should have been removed when the user was
 
157
# dropped. Reported as Bug#36544 DROP USER does not remove
 
158
# stored function privileges
 
159
DROP FUNCTION fn1;
 
160
 
 
161
 
 
162
# ------------------------------------------------------------------------------
 
163
let $message= Testcase 3.1.6.4:
 
164
              -----------------
 
165
Ensure that the default security provision of a stored procedure is SQL SECURITY
 
166
DEFINER.;
 
167
--source include/show_msg80.inc
 
168
 
 
169
CREATE USER 'user_1'@'localhost';
 
170
 
 
171
grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
 
172
grant execute on db_storedproc_1.* to 'user_1'@'localhost';
 
173
flush privileges;
 
174
 
 
175
USE db_storedproc_1;
 
176
 
 
177
--disable_warnings
 
178
DROP PROCEDURE IF EXISTS sp4;
 
179
--enable_warnings
 
180
 
 
181
delimiter //;
 
182
CREATE PROCEDURE sp4(v1 char(20))
 
183
BEGIN
 
184
   SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
 
185
END//
 
186
delimiter ;//
 
187
 
 
188
#disconnect default;
 
189
 
 
190
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
191
connect (user3, localhost, user_1, , db_storedproc_1);
 
192
--source suite/funcs_1/include/show_connection.inc
 
193
 
 
194
USE db_storedproc_1;
 
195
CALL sp4('a');
 
196
 
 
197
--vertical_results
 
198
SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
 
199
       ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC,
 
200
       SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT
 
201
FROM information_schema.routines
 
202
 WHERE routine_schema LIKE 'db_sto%';
 
203
--horizontal_results
 
204
 
 
205
disconnect user3;
 
206
 
 
207
# cleanup
 
208
connection default;
 
209
--source suite/funcs_1/include/show_connection.inc
 
210
DROP PROCEDURE sp4;
 
211
DROP USER 'user_1'@'localhost';
 
212
 
 
213
 
 
214
# ------------------------------------------------------------------------------
 
215
let $message= Testcase 3.1.6.5:
 
216
              -----------------
 
217
Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
 
218
called/executed by any user, using only the privileges (including database
 
219
access privileges) associated with the user who created the stored procedure.;
 
220
--source include/show_msg80.inc
 
221
 
 
222
USE db_storedproc_1;
 
223
CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date);
 
224
INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL);
 
225
 
 
226
# creates procedures
 
227
create user 'user_1'@'localhost';
 
228
 
 
229
#executes procedure
 
230
create user 'user_2'@'localhost';
 
231
 
 
232
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
 
233
grant SELECT on db_storedproc_1.* to 'user_2'@'localhost';
 
234
grant execute on db_storedproc_1.* to 'user_2'@'localhost';
 
235
flush privileges;
 
236
 
 
237
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
238
connect (user5_1, localhost, user_1, , db_storedproc_1);
 
239
--source suite/funcs_1/include/show_connection.inc
 
240
 
 
241
delimiter //;
 
242
CREATE PROCEDURE sp5_s_i () sql security definer
 
243
BEGIN
 
244
   SELECT * from db_storedproc_1.t3165;
 
245
   insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
 
246
END//
 
247
 
 
248
CREATE PROCEDURE sp5_sel () sql security definer
 
249
BEGIN
 
250
   SELECT * from db_storedproc_1.t3165;
 
251
END//
 
252
 
 
253
CREATE PROCEDURE sp5_ins () sql security definer
 
254
BEGIN
 
255
   insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
 
256
END//
 
257
delimiter ;//
 
258
 
 
259
disconnect user5_1;
 
260
 
 
261
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
262
connect (user5_2, localhost, user_2, , db_storedproc_1);
 
263
--source suite/funcs_1/include/show_connection.inc
 
264
 
 
265
--error ER_TABLEACCESS_DENIED_ERROR
 
266
CALL sp5_s_i();
 
267
--error ER_TABLEACCESS_DENIED_ERROR
 
268
CALL sp5_ins();
 
269
--error ER_TABLEACCESS_DENIED_ERROR
 
270
CALL sp5_sel();
 
271
 
 
272
# now 'add' INSERT to DEFINER
 
273
connection default;
 
274
--source suite/funcs_1/include/show_connection.inc
 
275
--error ER_TABLEACCESS_DENIED_ERROR
 
276
CALL sp5_sel();
 
277
grant insert on db_storedproc_1.* to 'user_1'@'localhost';
 
278
flush privileges;
 
279
 
 
280
connection user5_2;
 
281
--source suite/funcs_1/include/show_connection.inc
 
282
--error ER_TABLEACCESS_DENIED_ERROR
 
283
CALL sp5_s_i();
 
284
CALL sp5_ins();
 
285
--error ER_TABLEACCESS_DENIED_ERROR
 
286
CALL sp5_sel();
 
287
 
 
288
# now 'add' SELECT to DEFINER
 
289
connection default;
 
290
--source suite/funcs_1/include/show_connection.inc
 
291
--error ER_TABLEACCESS_DENIED_ERROR
 
292
CALL sp5_sel();
 
293
grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
 
294
#grant execute on db_storedproc_1.* to 'user_2'@'localhost';
 
295
flush privileges;
 
296
 
 
297
connection user5_2;
 
298
--source suite/funcs_1/include/show_connection.inc
 
299
CALL sp5_s_i();
 
300
CALL sp5_ins();
 
301
CALL sp5_sel();
 
302
 
 
303
# now revoke INSERT FROM DEFINER
 
304
connection default;
 
305
--source suite/funcs_1/include/show_connection.inc
 
306
REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
 
307
flush privileges;
 
308
 
 
309
connection user5_2;
 
310
--source suite/funcs_1/include/show_connection.inc
 
311
--error ER_TABLEACCESS_DENIED_ERROR
 
312
CALL sp5_s_i();
 
313
--error ER_TABLEACCESS_DENIED_ERROR
 
314
CALL sp5_ins();
 
315
CALL sp5_sel();
 
316
 
 
317
# now revoke SELECT FROM DEFINER
 
318
connection default;
 
319
--source suite/funcs_1/include/show_connection.inc
 
320
REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
 
321
flush privileges;
 
322
 
 
323
connection user5_2;
 
324
--source suite/funcs_1/include/show_connection.inc
 
325
--error ER_TABLEACCESS_DENIED_ERROR
 
326
CALL sp5_s_i();
 
327
--error ER_TABLEACCESS_DENIED_ERROR
 
328
CALL sp5_ins();
 
329
--error ER_TABLEACCESS_DENIED_ERROR
 
330
CALL sp5_sel();
 
331
 
 
332
# cleanup
 
333
disconnect user5_2;
 
334
connection default;
 
335
--source suite/funcs_1/include/show_connection.inc
 
336
 
 
337
DROP PROCEDURE sp5_s_i;
 
338
DROP PROCEDURE sp5_sel;
 
339
DROP PROCEDURE sp5_ins;
 
340
DROP TABLE t3165;
 
341
DROP USER 'user_1'@'localhost';
 
342
DROP USER 'user_2'@'localhost';
 
343
 
 
344
 
 
345
# ------------------------------------------------------------------------------
 
346
let $message= Testcase 3.1.6.6:
 
347
              -----------------
 
348
Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
 
349
called/executed by any user, using only the privileges (including database
 
350
access privileges) associated with the user executing the stored procedure.;
 
351
--source include/show_msg80.inc
 
352
 
 
353
USE db_storedproc_1;
 
354
CREATE TABLE t3166 ( c1 char(30) );
 
355
INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
 
356
 
 
357
# DEFINER
 
358
create user 'user_1'@'localhost';
 
359
 
 
360
# INVOKER
 
361
create user 'user_2'@'localhost';
 
362
 
 
363
GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
 
364
GRANT SELECT  ON db_storedproc_1.* TO 'user_2'@'localhost';
 
365
GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
 
366
FLUSH PRIVILEGES;
 
367
 
 
368
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
369
connect (user6_1, localhost, user_1, , db_storedproc_1);
 
370
--source suite/funcs_1/include/show_connection.inc
 
371
 
 
372
delimiter //;
 
373
CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
 
374
BEGIN
 
375
   SELECT * from db_storedproc_1.t3166;
 
376
   insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
 
377
END//
 
378
 
 
379
CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
 
380
BEGIN
 
381
   SELECT * from db_storedproc_1.t3166;
 
382
END//
 
383
 
 
384
CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
 
385
BEGIN
 
386
   insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
 
387
END//
 
388
delimiter ;//
 
389
 
 
390
disconnect user6_1;
 
391
 
 
392
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
393
connect (user6_2, localhost, user_2, , db_storedproc_1);
 
394
--source suite/funcs_1/include/show_connection.inc
 
395
 
 
396
--error ER_TABLEACCESS_DENIED_ERROR
 
397
CALL sp3166_s_i();
 
398
--error ER_TABLEACCESS_DENIED_ERROR
 
399
CALL sp3166_ins();
 
400
CALL sp3166_sel();
 
401
 
 
402
# now 'add' INSERT to INVOKER
 
403
connection default;
 
404
--source suite/funcs_1/include/show_connection.inc
 
405
CALL sp3166_sel();
 
406
GRANT INSERT  ON db_storedproc_1.* TO 'user_2'@'localhost';
 
407
FLUSH PRIVILEGES;
 
408
disconnect user6_2;
 
409
 
 
410
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
411
connect (user6_3, localhost, user_2, , db_storedproc_1);
 
412
--source suite/funcs_1/include/show_connection.inc
 
413
CALL sp3166_s_i();
 
414
CALL sp3166_ins();
 
415
CALL sp3166_sel();
 
416
disconnect user6_3;
 
417
 
 
418
# now 'remove' SELECT from INVOKER
 
419
connection default;
 
420
--source suite/funcs_1/include/show_connection.inc
 
421
CALL sp3166_sel();
 
422
REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
 
423
FLUSH PRIVILEGES;
 
424
 
 
425
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
426
connect (user6_4, localhost, user_2, , db_storedproc_1);
 
427
--source suite/funcs_1/include/show_connection.inc
 
428
--error ER_TABLEACCESS_DENIED_ERROR
 
429
CALL sp3166_s_i();
 
430
CALL sp3166_ins();
 
431
--error ER_TABLEACCESS_DENIED_ERROR
 
432
CALL sp3166_sel();
 
433
disconnect user6_4;
 
434
 
 
435
# now 'remove' EXECUTE FROM INVOKER
 
436
connection default;
 
437
CALL sp3166_s_i();
 
438
--source suite/funcs_1/include/show_connection.inc
 
439
REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
 
440
FLUSH PRIVILEGES;
 
441
 
 
442
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
 
443
connect (user6_5, localhost, user_2, , db_storedproc_1);
 
444
--source suite/funcs_1/include/show_connection.inc
 
445
--error ER_PROCACCESS_DENIED_ERROR
 
446
CALL sp3166_s_i();
 
447
--error ER_PROCACCESS_DENIED_ERROR
 
448
CALL sp3166_ins();
 
449
--error ER_PROCACCESS_DENIED_ERROR
 
450
CALL sp3166_sel();
 
451
disconnect user6_5;
 
452
 
 
453
# cleanup
 
454
connection default;
 
455
--source suite/funcs_1/include/show_connection.inc
 
456
 
 
457
DROP PROCEDURE sp3166_s_i;
 
458
DROP PROCEDURE sp3166_sel;
 
459
DROP PROCEDURE sp3166_ins;
 
460
DROP TABLE t3166;
 
461
DROP USER 'user_1'@'localhost';
 
462
DROP USER 'user_2'@'localhost';
 
463
 
 
464
 
 
465
# ==============================================================================
 
466
# USE the same .inc to cleanup before and after the test
 
467
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
 
468
 
 
469
# ==============================================================================
 
470
--echo
 
471
--echo .                               +++ END OF SCRIPT +++
 
472
--echo --------------------------------------------------------------------------------
 
473
# ==============================================================================