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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
# ******************************************************************************
# This testcase is to test
# with SUPER user privileges can change the limit of sysvar innodb_open_files
# with SYSTEM_VARIABLES_ADMI can change the limit of sysvar innodb_open_files
# with both above 2 privileges can change the limit of sysvar innodb_open_files
# without both privileges can can change the limit of sysvar innodb_open_files
# ******************************************************************************
# This test requires a small amount of not easily closeable InnoDB files. The
# UNDO tablespaces are such not easily closeable files, so we limit runs to ones
# that use the default amount of such.
--source include/have_innodb_default_undo_tablespaces.inc
SET @start_global_value = @@GLOBAL.innodb_open_files;
SELECT @start_global_value;
# Check default user root privileges
SHOW STATUS LIKE 'SYSTEM_VARIABLES_ADMIN';
SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
# Check Default setting
SELECT @@innodb_open_files;
# Test SUPER privileges can change the limit
# create a user
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
# Issue Grant SUPER Priv to user1
GRANT SUPER ON *.* to 'user1'@'localhost';
# Connect with user1
--connect (con1,'localhost','user1','123',)
# Check priv of user1
SHOW GRANTS FOR 'user1'@'localhost';
# Check SYSTEM_VARIABLES_ADMIN priv to user1
SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
# Check Default value
SELECT @@innodb_open_files ;
# Set new value with Min
SELECT innodb_set_open_files_limit(10);
# Check value is set
SELECT @@innodb_open_files ;
--connection default
--disconnect con1
# clean up
DROP USER 'user1'@'localhost';
# Test user with SYSTEM_VARIABLES_ADMIN privileges can change the limit
# create a user
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
# Issue Grant SYSTEM_VARIABLES_ADMIN Priv to user1
GRANT SYSTEM_VARIABLES_ADMIN ON *.* to 'user1'@'localhost';
# Connect with user1
--connect (con1,'localhost','user1','123',)
# Check priv of user1
SHOW GRANTS FOR 'user1'@'localhost';
# Check SYSTEM_VARIABLES_ADMIN to user1
SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
# Check value 10 is existing
SELECT @@innodb_open_files ;
# Set new value with Max
SELECT innodb_set_open_files_limit(2147483647);
# Check value is set
SELECT @@innodb_open_files ;
--connection default
--disconnect con1
# clean up
DROP USER 'user1'@'localhost';
# Test user with SUPER and SYSTEM_VARIABLES_ADMIN privileges can change the limit
# create a user
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
# Grant SUPER Priv to user1
GRANT SUPER ON *.* to 'user1'@'localhost';
# Grant SYSTEM_VARIABLES_ADMI Priv to user1
GRANT SYSTEM_VARIABLES_ADMIN ON *.* to 'user1'@'localhost';
# Connect with user1
--connect (con1,'localhost','user1','123',)
# Check priv of user1
SHOW GRANTS FOR 'user1'@'localhost';
# Check SYSTEM_VARIABLES_ADMIN to user1
SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
# Check 2147483647 value exist
SELECT @@innodb_open_files ;
# Set new value with Default
SELECT innodb_set_open_files_limit(4000);
# Check value is set
SELECT @@innodb_open_files ;
--connection default
--disconnect con1
# clean up
DROP USER 'user1'@'localhost';
# Test user without SUPER and SYSTEM_VARIABLES_ADMIN privileges can change the limit
# create a user
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
# Grant Priv to user1
GRANT CREATE, SELECT, UPDATE, DELETE ON *.* TO 'user1'@'localhost';
# Connect with user1
--connect (con1,'localhost','user1','123',)
# Check No such priv of user1
SHOW GRANTS FOR 'user1'@'localhost';
# Check No such Priv SYSTEM_VARIABLES_ADMIN to user1
SELECT * FROM performance_schema.global_status WHERE variable_name like 'SYSTEM_VARIABLES_ADMIN';
# Check value 4000 is exist
SELECT @@innodb_open_files ;
# Set new value with Default
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SELECT innodb_set_open_files_limit(6000);
# Check value is set
SELECT @@innodb_open_files ;
--connection default
--disconnect con1
# clean up
DROP USER 'user1'@'localhost';
SELECT innodb_set_open_files_limit(@start_global_value);
SELECT @@GLOBAL.innodb_open_files;
|
