1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
############# mysql-test\t\automatic_sp_privileges_func.test ############################
# #
# Variable Name: automatic_sp_privileges #
# Scope: GLOBAL #
# Access Type: Dynamic #
# Data Type: BOOLEAN #
# Default Value: 1 TRUE #
# Values: 1 TRUE, 0 FALSE #
# #
# #
# Creation Date: 2008-03-04 #
# Author: Sharique Abdullah #
# #
# Description: Test Cases of Dynamic System Variable "automatic_sp_privileges" #
# that checks behavior of this variable in the following ways #
# * Functionality based on different values #
# #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
# server-system-variables.html#option_mysqld_automatic_sp_privileges #
# #
#########################################################################################
#
# Setup
#
--source include/not_embedded.inc
--echo ** Setup **
SET @global_automatic_sp_privileges = @@GLOBAL.automatic_sp_privileges;
CREATE TABLE t1 (a varchar(200));
INSERT INTO t1 VALUES('Procedure Executed.');
#
# Creating test user
#
CREATE USER 'userTest'@'localhost';
GRANT CREATE ROUTINE, SELECT ON test.* to 'userTest'@'localhost';
CREATE USER 'userTest1'@'localhost';
GRANT CREATE ROUTINE, SELECT ON test.* to 'userTest1'@'localhost';
#
# Value TRUE
#
connection default;
SET GLOBAL automatic_sp_privileges = TRUE;
connect (conUser,localhost,userTest,,);
connection conUser;
delimiter |;
CREATE PROCEDURE testProc ()
BEGIN
SELECT * FROM t1;
END;|
delimiter ;|
CALL testProc();
--echo Expecting SELECT executed
#
# Value FALSE
#
connection default;
SET GLOBAL automatic_sp_privileges = FALSE;
connect (conUser1,localhost,userTest1,,);
connection conUser1;
delimiter |;
CREATE PROCEDURE testProc1 ()
BEGIN
SELECT * FROM t1;
END;|
delimiter ;|
--echo
--echo Expected error access denied
--error ER_PROCACCESS_DENIED_ERROR
CALL testProc1();
--echo
--echo Expected error access denied
--error ER_PROCACCESS_DENIED_ERROR
ALTER PROCEDURE testProc1 COMMENT 'My Comment';
--echo
--echo Expected error access denied
--error ER_PROCACCESS_DENIED_ERROR
DROP PROCEDURE testProc1;
connection default;
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE testProc1 TO 'userTest1'@'localhost';
connection conUser1;
CALL testProc1();
--echo Expecting seelect executed
ALTER PROCEDURE testProc1 COMMENT 'My Comment';
--echo
#
# Cleanup
#
--echo ** Cleanup **
connection default;
disconnect conUser;
disconnect conUser1;
SET GLOBAL automatic_sp_privileges = @global_automatic_sp_privileges;
# Disabled due to differences in results: Bug#35384
#SHOW GRANTS FOR 'userTest'@'localhost';
# on Linux (5.1.24) successful, on Windows (5.1.23) error
--error 0,ER_NONEXISTING_PROC_GRANT
REVOKE EXECUTE, ALTER ROUTINE ON PROCEDURE testProc FROM 'userTest'@'localhost';
--error 0,ER_NONEXISTING_PROC_GRANT
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest1'@'localhost';
--disable_warnings
DROP PROCEDURE testProc;
--enable_warnings
DROP PROCEDURE testProc1;
DROP USER 'userTest'@'localhost';
DROP USER 'userTest1'@'localhost';
DROP TABLE t1;
|
