Debian Codegraph @

mysql-test/suite/connection_control/t/connection_delay_info_schema_view.test

raw
1
2 # Make sure that connection_control plugin can be loaded
3 --source ../inc/have_connection_control_plugin.inc
4
5 # Save the initial number of concurrent sessions
6 --source include/count_sessions.inc
7
8 --echo # Connection delay tests for valid user accounts
9
10 --echo
11 --echo # ----------------------------------------------------------------------
12 --echo
13
14 --echo # Setup
15 --echo # Install connection_control plugin
16 --source ../inc/install_connection_control_plugin.inc
17
18 --echo # Performance schema
19 --echo # Verify the instrumentation provided
20
21 SELECT * FROM performance_schema.setup_instruments
22 WHERE NAME LIKE "%/conn_delay/%"
23 ORDER BY NAME;
24
25 --echo # Create user accounts for testing
26 CREATE USER u1@localhost IDENTIFIED BY 'abcd';
27 CREATE USER u2@localhost IDENTIFIED BY 'abcd';
28 CREATE USER u3@localhost IDENTIFIED BY 'abcd';
29
30 --echo # Save original values of connection_control variables
31 SET @saved_connections_threshold = @@global.connection_control_failed_connections_threshold;
32
33 -- echo # Avoid triggering delay
34 SET @@global.connection_control_failed_connections_threshold = 100;
35
36 # We don't need to use client side authentication plugin for this test.
37 let $USE_AUTH_PLUGIN= 0;
38
39 --echo
40 --echo # ----------------------------------------------------------------------
41 --echo
42
43 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
44 --error ER_ACCESS_DENIED_ERROR
45 connect(fail_con_u1, localhost, u1,,,,,SSL);
46 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
47 --error ER_ACCESS_DENIED_ERROR
48 connect(fail_con_u1, localhost, u1,haha,,,,SSL);
49 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
50 --error ER_ACCESS_DENIED_ERROR
51 connect(fail_con_u2, localhost, u2,efgh,,,,SSL);
52
53 -- echo # connection_control_failed_login_attempts should contain entries
54 -- echo # for u1@localhost and u2@localhost
55
56 --sorted_result
57 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
58
59 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
60 --error ER_ACCESS_DENIED_ERROR
61 connect(fail_con_u3, localhost, u3,,,,,SSL);
62 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
63 --error ER_ACCESS_DENIED_ERROR
64 connect(fail_con_u2, localhost, u2,haha,,,,SSL);
65 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
66 --error ER_ACCESS_DENIED_ERROR
67 connect(fail_con_u3, localhost, u3,efgh,,,,SSL);
68
69 -- echo # connection_control_failed_login_attempts should contain entries
70 -- echo # for u1@localhost, u2@localhost and u3@localhost
71
72 --sorted_result
73 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
74
75 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
76 --error ER_ACCESS_DENIED_ERROR
77 connect(fail_con_u2, localhost, u2,,,,,SSL);
78 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
79 --error ER_ACCESS_DENIED_ERROR
80 connect(fail_con_u1, localhost, u1,haha,,,,SSL);
81 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
82 --error ER_ACCESS_DENIED_ERROR
83 connect(fail_con_u3, localhost, u3,efgh,,,,SSL);
84
85 -- echo # failed connection counts should have increased for all users
86
87 --sorted_result
88 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
89
90 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
91 --error ER_ACCESS_DENIED_ERROR
92 connect(fail_con_u2, localhost, u2,,,,,SSL);
93 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
94 --error ER_ACCESS_DENIED_ERROR
95 connect(fail_con_u1, localhost, u1,haha,,,,SSL);
96 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
97 --error ER_ACCESS_DENIED_ERROR
98 connect(fail_con_u3, localhost, u3,efgh,,,,SSL);
99
100 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
101 --error ER_ACCESS_DENIED_ERROR
102 connect(fail_con_u1, localhost, u1,haha,,,,SSL);
103 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
104 --error ER_ACCESS_DENIED_ERROR
105 connect(fail_con_u3, localhost, u3,efgh,,,,SSL);
106
107 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
108 --error ER_ACCESS_DENIED_ERROR
109 connect(fail_con_u1, localhost, u1,haha,,,,SSL);
110
111 -- echo # Try various queries
112
113 --sorted_result
114 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
115 WHERE USERHOST = '\'u1\'@\'localhost\'';
116
117 --sorted_result
118 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
119 WHERE USERHOST LIKE '%u2%';
120
121 --sorted_result
122 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
123 WHERE FAILED_ATTEMPTS > 4;
124
125 --sorted_result
126 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
127 WHERE FAILED_ATTEMPTS > 5;
128
129 --sorted_result
130 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
131 WHERE FAILED_ATTEMPTS < 5;
132
133 connect(con_u1, localhost, u1,abcd,,,,SSL);
134 connect(con_u2, localhost, u2,abcd,,,,SSL);
135 connection default;
136 --disconnect con_u1
137 --disconnect con_u2
138
139 CREATE USER viewer@localhost IDENTIFIED BY 'abcd';
140 GRANT CONNECTION_ADMIN ON *.* TO viewer@localhost;
141 connect(con_viewer, localhost, viewer,abcd,,,,SSL);
142 --echo # After successful login, corresponding entry should disappear
143 --echo # from the view
144 --sorted_result
145 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
146 connection default;
147 disconnect con_viewer;
148 DROP USER viewer@localhost;
149
150 connect(con_u3, localhost, u3,abcd,,,,SSL);
151 --echo # A user without privileges should not be able to any information
152 --sorted_result
153 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
154
155 connection default;
156 --disconnect con_u3
157
158 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
159 --error ER_ACCESS_DENIED_ERROR
160 connect(fail_con_u1, localhost, u1,,,,,SSL);
161 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
162 --error ER_ACCESS_DENIED_ERROR
163 connect(fail_con_u3, localhost, u3,haha,,,,SSL);
164 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
165 --error ER_ACCESS_DENIED_ERROR
166 connect(fail_con_u2, localhost, u2,efgh,,,,SSL);
167
168 -- echo # Subsequent failed attempts should put entries back in the view
169 --sorted_result
170 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
171
172 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
173 --error ER_ACCESS_DENIED_ERROR
174 connect(fail_con_u4, localhost, u4,,,,,SSL);
175 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
176 --error ER_ACCESS_DENIED_ERROR
177 connect(fail_con_u5, localhost, u5,haha,,,,SSL);
178 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
179 --error ER_ACCESS_DENIED_ERROR
180 connect(fail_con_u6, localhost, u6,efgh,,,,SSL);
181
182 -- echo # Attempts by invalid accounts should be shown in the view
183 --sorted_result
184 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
185
186 --echo
187 --echo # ----------------------------------------------------------------------
188 --echo
189
190 --echo # Cleanup
191
192 connection default;
193
194 --echo # Restore original values of conenction_control variables
195 SET @@global.connection_control_failed_connections_threshold = @saved_connections_threshold;
196
197 --echo # Remove user accounts created for the test
198 DROP USER u1@localhost;
199 DROP USER u2@localhost;
200 DROP USER u3@localhost;
201
202 --echo # Uninstall connection_control plugin
203 --source ../inc/uninstall_connection_control_plugin.inc
204
205 # Wait till all disconnects are completed.
206 --source include/wait_until_count_sessions.inc
207
208 --echo
209 --echo # ----------------------------------------------------------------------