Debian Codegraph @

mysql-test/suite/connection_control/r/connection_delay_info_schema_view.result

raw
1 # Connection delay tests for valid user accounts
2
3 # ----------------------------------------------------------------------
4
5 # Setup
6 # Install connection_control plugin
7 INSTALL PLUGIN connection_control SONAME 'CONNECTION_CONTROL_LIB';
8 INSTALL PLUGIN connection_control_failed_login_attempts SONAME 'CONNECTION_CONTROL_LIB';
9 # Performance schema
10 # Verify the instrumentation provided
11 SELECT * FROM performance_schema.setup_instruments
12 WHERE NAME LIKE "%/conn_delay/%"
13 ORDER BY NAME;
14 NAME ENABLED TIMED PROPERTIES FLAGS VOLATILITY DOCUMENTATION
15 stage/conn_delay/Waiting in connection_control plugin YES YES NULL 0 NULL
16 wait/synch/cond/conn_delay/connection_delay_wait_condition YES YES NULL 0 NULL
17 wait/synch/mutex/conn_delay/connection_delay_mutex YES YES NULL 0 NULL
18 wait/synch/rwlock/conn_delay/connection_event_delay_lock YES YES singleton NULL 0 NULL
19 # Create user accounts for testing
20 CREATE USER u1@localhost IDENTIFIED BY 'abcd';
21 CREATE USER u2@localhost IDENTIFIED BY 'abcd';
22 CREATE USER u3@localhost IDENTIFIED BY 'abcd';
23 # Save original values of connection_control variables
24 SET @saved_connections_threshold = @@global.connection_control_failed_connections_threshold;
25 # Avoid triggering delay
26 SET @@global.connection_control_failed_connections_threshold = 100;
27
28 # ----------------------------------------------------------------------
29
30 connect(localhost,u1,,test,MASTER_PORT,MASTER_SOCKET);
31 ERROR 28000: Access denied for user 'u1'@'localhost' (using password: NO)
32 connect(localhost,u1,haha,test,MASTER_PORT,MASTER_SOCKET);
33 ERROR 28000: Access denied for user 'u1'@'localhost' (using password: YES)
34 connect(localhost,u2,efgh,test,MASTER_PORT,MASTER_SOCKET);
35 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: YES)
36 # connection_control_failed_login_attempts should contain entries
37 # for u1@localhost and u2@localhost
38 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
39 USERHOST FAILED_ATTEMPTS
40 'u1'@'localhost' 2
41 'u2'@'localhost' 1
42 connect(localhost,u3,,test,MASTER_PORT,MASTER_SOCKET);
43 ERROR 28000: Access denied for user 'u3'@'localhost' (using password: NO)
44 connect(localhost,u2,haha,test,MASTER_PORT,MASTER_SOCKET);
45 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: YES)
46 connect(localhost,u3,efgh,test,MASTER_PORT,MASTER_SOCKET);
47 ERROR 28000: Access denied for user 'u3'@'localhost' (using password: YES)
48 # connection_control_failed_login_attempts should contain entries
49 # for u1@localhost, u2@localhost and u3@localhost
50 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
51 USERHOST FAILED_ATTEMPTS
52 'u1'@'localhost' 2
53 'u2'@'localhost' 2
54 'u3'@'localhost' 2
55 connect(localhost,u2,,test,MASTER_PORT,MASTER_SOCKET);
56 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: NO)
57 connect(localhost,u1,haha,test,MASTER_PORT,MASTER_SOCKET);
58 ERROR 28000: Access denied for user 'u1'@'localhost' (using password: YES)
59 connect(localhost,u3,efgh,test,MASTER_PORT,MASTER_SOCKET);
60 ERROR 28000: Access denied for user 'u3'@'localhost' (using password: YES)
61 # failed connection counts should have increased for all users
62 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
63 USERHOST FAILED_ATTEMPTS
64 'u1'@'localhost' 3
65 'u2'@'localhost' 3
66 'u3'@'localhost' 3
67 connect(localhost,u2,,test,MASTER_PORT,MASTER_SOCKET);
68 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: NO)
69 connect(localhost,u1,haha,test,MASTER_PORT,MASTER_SOCKET);
70 ERROR 28000: Access denied for user 'u1'@'localhost' (using password: YES)
71 connect(localhost,u3,efgh,test,MASTER_PORT,MASTER_SOCKET);
72 ERROR 28000: Access denied for user 'u3'@'localhost' (using password: YES)
73 connect(localhost,u1,haha,test,MASTER_PORT,MASTER_SOCKET);
74 ERROR 28000: Access denied for user 'u1'@'localhost' (using password: YES)
75 connect(localhost,u3,efgh,test,MASTER_PORT,MASTER_SOCKET);
76 ERROR 28000: Access denied for user 'u3'@'localhost' (using password: YES)
77 connect(localhost,u1,haha,test,MASTER_PORT,MASTER_SOCKET);
78 ERROR 28000: Access denied for user 'u1'@'localhost' (using password: YES)
79 # Try various queries
80 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
81 WHERE USERHOST = '\'u1\'@\'localhost\'';
82 USERHOST FAILED_ATTEMPTS
83 'u1'@'localhost' 6
84 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
85 WHERE USERHOST LIKE '%u2%';
86 USERHOST FAILED_ATTEMPTS
87 'u2'@'localhost' 4
88 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
89 WHERE FAILED_ATTEMPTS > 4;
90 USERHOST FAILED_ATTEMPTS
91 'u1'@'localhost' 6
92 'u3'@'localhost' 5
93 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
94 WHERE FAILED_ATTEMPTS > 5;
95 USERHOST FAILED_ATTEMPTS
96 'u1'@'localhost' 6
97 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts
98 WHERE FAILED_ATTEMPTS < 5;
99 USERHOST FAILED_ATTEMPTS
100 'u2'@'localhost' 4
101 CREATE USER viewer@localhost IDENTIFIED BY 'abcd';
102 GRANT CONNECTION_ADMIN ON *.* TO viewer@localhost;
103 # After successful login, corresponding entry should disappear
104 # from the view
105 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
106 USERHOST FAILED_ATTEMPTS
107 'u3'@'localhost' 5
108 DROP USER viewer@localhost;
109 # A user without privileges should not be able to any information
110 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
111 USERHOST FAILED_ATTEMPTS
112 connect(localhost,u1,,test,MASTER_PORT,MASTER_SOCKET);
113 ERROR 28000: Access denied for user 'u1'@'localhost' (using password: NO)
114 connect(localhost,u3,haha,test,MASTER_PORT,MASTER_SOCKET);
115 ERROR 28000: Access denied for user 'u3'@'localhost' (using password: YES)
116 connect(localhost,u2,efgh,test,MASTER_PORT,MASTER_SOCKET);
117 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: YES)
118 # Subsequent failed attempts should put entries back in the view
119 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
120 USERHOST FAILED_ATTEMPTS
121 'u1'@'localhost' 1
122 'u2'@'localhost' 1
123 'u3'@'localhost' 1
124 connect(localhost,u4,,test,MASTER_PORT,MASTER_SOCKET);
125 ERROR 28000: Access denied for user 'u4'@'localhost' (using password: NO)
126 connect(localhost,u5,haha,test,MASTER_PORT,MASTER_SOCKET);
127 ERROR 28000: Access denied for user 'u5'@'localhost' (using password: YES)
128 connect(localhost,u6,efgh,test,MASTER_PORT,MASTER_SOCKET);
129 ERROR 28000: Access denied for user 'u6'@'localhost' (using password: YES)
130 # Attempts by invalid accounts should be shown in the view
131 SELECT * FROM INFORMATION_SCHEMA.connection_control_failed_login_attempts;
132 USERHOST FAILED_ATTEMPTS
133 'u1'@'localhost' 1
134 'u2'@'localhost' 1
135 'u3'@'localhost' 1
136 'u4'@'localhost' 1
137 'u5'@'localhost' 1
138 'u6'@'localhost' 1
139
140 # ----------------------------------------------------------------------
141
142 # Cleanup
143 # Restore original values of conenction_control variables
144 SET @@global.connection_control_failed_connections_threshold = @saved_connections_threshold;
145 # Remove user accounts created for the test
146 DROP USER u1@localhost;
147 DROP USER u2@localhost;
148 DROP USER u3@localhost;
149 # Uninstall connection_control plugin
150 UNINSTALL PLUGIN connection_control;
151 UNINSTALL PLUGIN connection_control_failed_login_attempts;
152
153 # ----------------------------------------------------------------------