It's really hard to find out if a table in an instance has access.(
A common method is to open a tcpdump or tshark persistent packet to filter suspected table names.(
However, if we were using MySQL from the Percona branch, this would be much easier.
Under Percona MySQL, we can open the userstat parameter and query the system statistics tables to get access to the user, client, and library tables. Note that this function is only available in the Percona distribution of MySQL, not in the community version.
(none) > status -------------- mysql Ver 14.14 Distrib 5.6.38-83.0, for Linux (x86_64) using 6.2 Connection id:1222143 Current database: Current user:root@localhost SSL:Not in use Current pager:stdout Using outfile:'/root/audit.log' Using delimiter:; Server version:5.6.38-83.0-log Percona Server (GPL), Release 83.0, Revision dc97471bd40 Protocol version:10 Connection:Localhost via UNIX socket Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 UNIX socket:/tmp/mysql.sock Uptime:110 days 8 hours 34 min 25 sec Threads: 32 Questions: 1074250447 Slow queries: 1474 Opens: 136 Flush tables: 1 Open tables: 129 Queries per second avg: 112.665 -------------- (none) > use INFORMATION_SCHEMA (INFORMATION_SCHEMA) > show tables like '%_STATISTICS'; +---------------------------------------------+ | Tables_in_information_schema (%_STATISTICS) | +---------------------------------------------+ | CLIENT_STATISTICS | | INDEX_STATISTICS | | TABLE_STATISTICS | | THREAD_STATISTICS | | USER_STATISTICS | +---------------------------------------------+ 5 rows in set (0.00 sec) set global userstat=ON; select * from INFORMATION_SCHEMA.CLIENT_STATISTICS; select * from INFORMATION_SCHEMA.INDEX_STATISTICS; select * from INFORMATION_SCHEMA.TABLE_STATISTICS; select * from INFORMATION_SCHEMA.THREAD_STATISTICS; select * from INFORMATION_SCHEMA.USER_STATISTICS;
To clean up statistics, you can use the following commands:
FLUSH CLIENT_STATISTICS ; FLUSH INDEX_STATISTICS ; FLUSH TABLE_STATISTICS ; FLUSH THREAD_STATISTICS ; FLUSH USER_STATISTICS ;