1
############# mysql-test\t\automatic_sp_privileges_func.test ############################
3
# Variable Name: automatic_sp_privileges #
5
# Access Type: Dynamic #
7
# Default Value: 1 TRUE #
8
# Values: 1 TRUE, 0 FALSE #
11
# Creation Date: 2008-03-04 #
12
# Author: Sharique Abdullah #
14
# Description: Test Cases of Dynamic System Variable "automatic_sp_privileges" #
15
# that checks behavior of this variable in the following ways #
16
# * Functionality based on different values #
18
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
19
# server-system-variables.html#option_mysqld_automatic_sp_privileges #
21
#########################################################################################
27
--source include/not_embedded.inc
30
SET @global_automatic_sp_privileges = @@GLOBAL.automatic_sp_privileges;
32
CREATE TABLE t1 (a varchar(200));
34
INSERT INTO t1 VALUES('Procedure Executed.');
40
CREATE USER 'userTest'@'localhost';
41
CREATE USER 'userTest1'@'localhost';
46
--echo ** Connection default **
48
SET GLOBAL automatic_sp_privileges = TRUE;
50
--echo ** Connecting using userTest **
51
connect (conUser,localhost,userTest,,);
52
--echo ** Connection conUser **
57
CREATE PROCEDURE testProc ()
65
--echo Expecting SELECT executed
70
--echo ** Connection default**
72
SET GLOBAL automatic_sp_privileges = FALSE;
74
--echo ** Connecting using userTest1 **
75
connect (conUser1,localhost,userTest1,,);
76
--echo ** Connection conUser1 **
81
CREATE PROCEDURE testProc1 ()
89
--echo Expected error access denied
90
--error ER_PROCACCESS_DENIED_ERROR
94
--echo Expected error access denied
95
--error ER_PROCACCESS_DENIED_ERROR
96
ALTER PROCEDURE testProc1 COMMENT 'My Comment';
99
--echo Expected error access denied
100
--error ER_PROCACCESS_DENIED_ERROR
101
DROP PROCEDURE testProc1;
103
--echo ** Connection default **
106
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE testProc1 TO 'userTest1'@'localhost';
108
--echo ** Connection conUser1 **
112
--echo Expecting seelect executed
114
ALTER PROCEDURE testProc1 COMMENT 'My Comment';
121
--echo ** Connection default **
124
--echo disconnecting connections
128
SET GLOBAL automatic_sp_privileges = @global_automatic_sp_privileges;
130
# Disabled due to differences in results: Bug#35384
131
#SHOW GRANTS FOR 'userTest'@'localhost';
133
# on Linux (5.1.24) successful, on Windows (5.1.23) error
134
--error 0,ER_NONEXISTING_PROC_GRANT
135
REVOKE EXECUTE, ALTER ROUTINE ON PROCEDURE testProc FROM 'userTest'@'localhost';
137
--error 0,ER_NONEXISTING_PROC_GRANT
138
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest'@'localhost';
140
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest1'@'localhost';
143
DROP PROCEDURE testProc;
145
DROP PROCEDURE testProc1;
147
DROP USER 'userTest'@'localhost';
148
DROP USER 'userTest1'@'localhost';