summaryrefslogtreecommitdiff
path: root/mysql-test/r/user_limits.result
blob: bbf73d7c617cb7845a4edcf0be420b2c1a9c6e0f (plain)
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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
set @my_max_user_connections= @@global.max_user_connections;
drop table if exists t1;
create table t1 (i int);
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;
create user mysqltest_1@localhost;
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 2;
flush user_resources;
connect  mqph, localhost, mysqltest_1,,;
connection mqph;
select * from t1;
i
select * from t1;
i
select * from t1;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_queries_per_hour' resource (current value: 2)
connect  mqph2, localhost, mysqltest_1,,;
connection mqph2;
select * from t1;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_queries_per_hour' resource (current value: 2)
connection default;
drop user mysqltest_1@localhost;
disconnect mqph;
disconnect mqph2;
create user mysqltest_1@localhost;
grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 2;
flush user_resources;
connect  muph, localhost, mysqltest_1,,;
connection muph;
select * from t1;
i
select * from t1;
i
select * from t1;
i
delete from t1;
delete from t1;
delete from t1;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_updates_per_hour' resource (current value: 2)
select * from t1;
i
connect  muph2, localhost, mysqltest_1,,;
connection muph2;
delete from t1;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_updates_per_hour' resource (current value: 2)
select * from t1;
i
connection default;
drop user mysqltest_1@localhost;
disconnect muph;
disconnect muph2;
create user mysqltest_1@localhost;
grant usage on *.* to mysqltest_1@localhost with max_connections_per_hour 2;
flush user_resources;
connect  mcph1, localhost, mysqltest_1,,;
connection mcph1;
select * from t1;
i
connect  mcph2, localhost, mysqltest_1,,;
connection mcph2;
select * from t1;
i
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  mcph3, localhost, mysqltest_1,,;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_connections_per_hour' resource (current value: 2)
select * from t1;
i
disconnect mcph1;
disconnect mcph2;
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  mcph3, localhost, mysqltest_1,,;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_connections_per_hour' resource (current value: 2)
connection default;
drop user mysqltest_1@localhost;
flush privileges;
create user mysqltest_1@localhost;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 2;
flush user_resources;
connect  muc1, localhost, mysqltest_1,,;
connection muc1;
select * from t1;
i
connect  muc2, localhost, mysqltest_1,,;
connection muc2;
select * from t1;
i
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  muc3, localhost, mysqltest_1,,;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: 2)
disconnect muc1;
connect  muc3, localhost, mysqltest_1,,;
select * from t1;
i
connection default;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 3;
flush user_resources;
connect  muc4, localhost, mysqltest_1,,;
connection muc4;
select * from t1;
i
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  muc5, localhost, mysqltest_1,,;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: 3)
connection default;
grant usage on *.* to mysqltest_1@localhost with max_user_connections -1;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_USER_CONNECTIONS -1
flush user_resources;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_USER_CONNECTIONS -1
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  muc5, localhost, mysqltest_1,,;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: -1)
disconnect muc2;
disconnect muc3;
disconnect muc4;
drop user mysqltest_1@localhost;
select @@session.max_user_connections, @@global.max_user_connections;
@@session.max_user_connections	@@global.max_user_connections
1000	1000
set session max_user_connections= 2;
ERROR HY000: SESSION variable 'max_user_connections' is read-only. Use SET GLOBAL to assign the value
set global max_user_connections= 2;
select @@session.max_user_connections, @@global.max_user_connections;
@@session.max_user_connections	@@global.max_user_connections
2	2
create user mysqltest_1@localhost;
grant usage on *.* to mysqltest_1@localhost;
flush user_resources;
connect  muca1, localhost, mysqltest_1,,;
connection muca1;
select @@session.max_user_connections, @@global.max_user_connections;
@@session.max_user_connections	@@global.max_user_connections
2	2
connect  muca2, localhost, mysqltest_1,,;
connection muca2;
select * from t1;
i
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  muca3, localhost, mysqltest_1,,;
ERROR 42000: User mysqltest_1 already has more than 'max_user_connections' active connections
connection default;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 3;
flush user_resources;
connect  muca3, localhost, mysqltest_1,,;
connection muca3;
select @@session.max_user_connections, @@global.max_user_connections;
@@session.max_user_connections	@@global.max_user_connections
3	2
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  muca4, localhost, mysqltest_1,,;
ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: 3)
connection default;
disconnect muca1;
disconnect muca2;
disconnect muca3;
set global max_user_connections= 0;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 0;
set global max_user_connections=-1;
show variables like "max_user_user_connections";
Variable_name	Value
select @@max_user_connections;
@@max_user_connections
-1
select @@global.max_user_connections;
@@global.max_user_connections
-1
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  muca2, localhost, mysqltest_1,,;
ERROR 42000: User mysqltest_1 already has more than 'max_user_connections' active connections
connect  muca2, localhost, root,,;
disconnect muca2;
connection default;
set global max_user_connections=1;
connect  muca2, localhost, mysqltest_1,,;
connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
connect  muca3, localhost, mysqltest_1,,;
ERROR 42000: User mysqltest_1 already has more than 'max_user_connections' active connections
disconnect muca2;
connection default;
drop user mysqltest_1@localhost;
drop table t1;
set global max_user_connections= @my_max_user_connections;