Debian Codegraph @

mysql-test/suite/sys_vars/t/automatic_sp_privileges_func.test

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