Debian Codegraph @

mysql-test/suite/innodb/t/innodb_open_files_priv.test

raw
1 # ******************************************************************************
2 # This testcase is to test
3 # with SUPER user privileges can change the limit of sysvar innodb_open_files
4 # with SYSTEM_VARIABLES_ADMI can change the limit of sysvar innodb_open_files
5 # with both above 2 privileges can change the limit of sysvar innodb_open_files
6 # without both privileges can can change the limit of sysvar innodb_open_files
7 # ******************************************************************************
8
9 # This test requires a small amount of not easily closeable InnoDB files. The
10 # UNDO tablespaces are such not easily closeable files, so we limit runs to ones
11 # that use the default amount of such.
12 --source include/have_innodb_default_undo_tablespaces.inc
13
14
15 SET @start_global_value = @@GLOBAL.innodb_open_files;
16 SELECT @start_global_value;
17
18 # Check default user root privileges
19 SHOW STATUS LIKE 'SYSTEM_VARIABLES_ADMIN';
20
21 SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
22
23 # Check Default setting
24 SELECT @@innodb_open_files;
25
26 # Test SUPER privileges can change the limit
27
28 # create a user
29 CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
30
31 # Issue Grant SUPER Priv to user1
32 GRANT SUPER ON *.* to 'user1'@'localhost';
33
34 # Connect with user1
35 --connect (con1,'localhost','user1','123',)
36
37 # Check priv of user1
38 SHOW GRANTS FOR 'user1'@'localhost';
39
40 # Check SYSTEM_VARIABLES_ADMIN priv to user1
41 SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
42
43 # Check Default value
44 SELECT @@innodb_open_files ;
45
46 # Set new value with Min
47 SELECT innodb_set_open_files_limit(10);
48
49 # Check value is set
50 SELECT @@innodb_open_files ;
51
52 --connection default
53
54 --disconnect con1
55
56 # clean up
57 DROP USER 'user1'@'localhost';
58
59 # Test user with SYSTEM_VARIABLES_ADMIN privileges can change the limit
60
61 # create a user
62 CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
63
64 # Issue Grant SYSTEM_VARIABLES_ADMIN Priv to user1
65 GRANT SYSTEM_VARIABLES_ADMIN ON *.* to 'user1'@'localhost';
66
67 # Connect with user1
68 --connect (con1,'localhost','user1','123',)
69
70 # Check priv of user1
71 SHOW GRANTS FOR 'user1'@'localhost';
72
73 # Check SYSTEM_VARIABLES_ADMIN to user1
74 SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
75
76 # Check value 10 is existing
77 SELECT @@innodb_open_files ;
78
79 # Set new value with Max
80 SELECT innodb_set_open_files_limit(2147483647);
81
82 # Check value is set
83 SELECT @@innodb_open_files ;
84
85 --connection default
86
87 --disconnect con1
88
89 # clean up
90 DROP USER 'user1'@'localhost';
91
92 # Test user with SUPER and SYSTEM_VARIABLES_ADMIN privileges can change the limit
93
94 # create a user
95 CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
96
97 # Grant SUPER Priv to user1
98 GRANT SUPER ON *.* to 'user1'@'localhost';
99
100 # Grant SYSTEM_VARIABLES_ADMI Priv to user1
101 GRANT SYSTEM_VARIABLES_ADMIN ON *.* to 'user1'@'localhost';
102
103 # Connect with user1
104 --connect (con1,'localhost','user1','123',)
105
106 # Check priv of user1
107 SHOW GRANTS FOR 'user1'@'localhost';
108
109 # Check SYSTEM_VARIABLES_ADMIN to user1
110 SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
111
112 # Check 2147483647 value exist
113 SELECT @@innodb_open_files ;
114
115 # Set new value with Default
116 SELECT innodb_set_open_files_limit(4000);
117
118 # Check value is set
119 SELECT @@innodb_open_files ;
120
121 --connection default
122
123 --disconnect con1
124
125 # clean up
126 DROP USER 'user1'@'localhost';
127
128 # Test user without SUPER and SYSTEM_VARIABLES_ADMIN privileges can change the limit
129
130 # create a user
131 CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
132
133 # Grant Priv to user1
134 GRANT CREATE, SELECT, UPDATE, DELETE ON *.* TO 'user1'@'localhost';
135
136 # Connect with user1
137 --connect (con1,'localhost','user1','123',)
138
139 # Check No such priv of user1
140 SHOW GRANTS FOR 'user1'@'localhost';
141
142 # Check No such Priv SYSTEM_VARIABLES_ADMIN to user1
143 SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
144
145 # Check value 4000 is exist
146 SELECT @@innodb_open_files ;
147
148 # Set new value with Default
149 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
150 SELECT innodb_set_open_files_limit(6000);
151
152 # Check value is set
153 SELECT @@innodb_open_files ;
154
155 --connection default
156
157 --disconnect con1
158
159 # clean up
160 DROP USER 'user1'@'localhost';
161
162 SELECT innodb_set_open_files_limit(@start_global_value);
163 SELECT @@GLOBAL.innodb_open_files;