|
CREATE USER foo@'127.0.0.1';
|
SET GLOBAL userstat=1;
|
|
--connect (con1,127.0.0.1,foo,,,$MASTER_MYPORT)
|
|
--connection default
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
|
--connection con1
|
--error ER_DBACCESS_DENIED_ERROR
|
CREATE DATABASE db;
|
|
--connection default
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
|
CREATE DATABASE db;
|
GRANT SELECT ON db.* TO foo@'127.0.0.1';
|
CREATE TABLE db.t1 (i INT);
|
|
--connection con1
|
--error ER_TABLEACCESS_DENIED_ERROR
|
ALTER TABLE db.t1 ENGINE=MyISAM;
|
|
--connection default
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
|
--connection con1
|
--error ER_TABLEACCESS_DENIED_ERROR
|
ALTER TABLE db.t1 ENGINE=MyISAM;
|
|
--connection default
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
|
--connection con1
|
--error ER_TABLEACCESS_DENIED_ERROR
|
ALTER TABLE db.t1 ENGINE=MyISAM;
|
|
--connection default
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
|
--disconnect con1
|
|
--connection default
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
|
DROP USER foo@'127.0.0.1';
|
SET GLOBAL userstat=DEFAULT;
|
DROP DATABASE db;
|
|
Result on 10.2
|
connect con1,127.0.0.1,foo,,,$MASTER_MYPORT;
|
connection default;
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
USER ACCESS_DENIED DENIED_CONNECTIONS
|
foo 0 0
|
connection con1;
|
CREATE DATABASE db;
|
ERROR 42000: Access denied for user 'foo'@'127.0.0.1' to database 'db'
|
connection default;
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
USER ACCESS_DENIED DENIED_CONNECTIONS
|
root 0 0
|
foo 1 1
|
CREATE DATABASE db;
|
GRANT SELECT ON db.* TO foo@'127.0.0.1';
|
CREATE TABLE db.t1 (i INT);
|
connection con1;
|
ALTER TABLE db.t1 ENGINE=MyISAM;
|
ERROR 42000: ALTER command denied to user 'foo'@'localhost' for table 't1'
|
connection default;
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
USER ACCESS_DENIED DENIED_CONNECTIONS
|
root 0 0
|
foo 2 3
|
connection con1;
|
ALTER TABLE db.t1 ENGINE=MyISAM;
|
ERROR 42000: ALTER command denied to user 'foo'@'localhost' for table 't1'
|
connection default;
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
USER ACCESS_DENIED DENIED_CONNECTIONS
|
root 0 0
|
foo 3 6
|
connection con1;
|
ALTER TABLE db.t1 ENGINE=MyISAM;
|
ERROR 42000: ALTER command denied to user 'foo'@'localhost' for table 't1'
|
connection default;
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
USER ACCESS_DENIED DENIED_CONNECTIONS
|
root 0 0
|
foo 4 10
|
disconnect con1;
|
connection default;
|
SELECT USER, ACCESS_DENIED, DENIED_CONNECTIONS FROM INFORMATION_SCHEMA.USER_STATISTICS;
|
USER ACCESS_DENIED DENIED_CONNECTIONS
|
root 0 0
|
foo 4 14
|
So, the effect I observe is slightly different from the described, but looks equally wrong
The Access denied error does actually increase the value of ACCESS_DENIED variable; but every time it happens, it looks like DENIED_CONNECTIONS i simultaneously increased by the new value of ACCESS_DENIED. And upon disconnect it happens again, the value of ACCESS_DENIED is added to DENIED_CONNECTIONS.
The test users USER_STATISTICS, it seems to be the same for CLIENT_STATISTICS.
On Percona server 5.6/5.7 with the same test case I didn't get any activity in any of the variables, they both remained 0.
|