Description:
As part of connection_control plugin, we install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS to implement table named CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS. This table contain information of user, host and number of failed login attempts (as counter). This counter will reset soon after the successful attempt.
The main purpose of this table is to monitor the number of failed login attempts. Compared to host_cache table in performance schema, this table will not have any additional secured details. However, this table seems like require super privilege to access the contents.
Also, when super privilege is not present for the DB user, its showing empty results instead of giving access denied error whcih is misleading sometimes.
How to repeat:
1. Install connection_control plugins
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
2. Verify plugins installed as expected
SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY,PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%';
show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
3. Create failed connection attempts for root user and check whether able to see stats from root user (which has all privileges)
select user(), current_user();
show grants;
show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
4. Create new user and provide all privileges to it apart from SUPER privilege
create user vidya identified by '************';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE,
ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, FILE, SHUTDOWN ON *.* TO 'vidya'@'%' WITH GRANT OPTION;
5. Now, connect with vidya user in different session and check whether you are able to access the metrics in information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table. (we see 'empty set' even its not empty)
> connect to mysql using vidya user
select user(), current_user();
show grants;
show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
6. Provide SUPER privilege to vidya DB user and reconnect to DB using vidya user and access table contents (you should be able to see the data)
grant super on *.* to vidya;
show grants for vidya;
> reconnect to DB using vidya DB user
select user(), current_user();
show grants;
show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
7. Consider to revoke all privileges apart from SUPER from vidya user and try to check whether table accessible (We can access table only with super privilege)
revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* from vidya;
show grants for vidya;
> reconnect to DB using vidya DB user
select user(), current_user();
show grants;
show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Suggested fix:
Please remove SUPER privilege requirement to access the table - information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS.
It would be nice to protect this table with PROCESS privilege to access the contents of the table. Also, consider to through an error when appropriate privileges are not present to the DB user instead of giving empty results.
