I have a database with a lot of users, all of them with full read privileges on all the tables but not all the users are reading all the tables. I want to have each user having as little privileges as they need. Each user belongs to a different large legacy application for which auditing the code is not feasible.

How do I go about obtaining this information?


This would have a considerable impact on your system, but you could enable the MySQL General Query Log by running the following:

SET GLOBAL general_log = 'ON';

The default setting for the logs will output to a file that can be identified by running this:

SHOW GLOBAL VARIABLES WHERE Variable_name = 'general_log_file';

Otherwise, you can change the setting to log in a table with the line below, and then you would be able to run queries on the table mysql.general_log

SET GLOBAL log_output = 'TABLE';
  • What tool do I use then to parse the logs and build the graph?
    – bluehallu
    Sep 29 '17 at 16:22
  • 4
    One possible tool is your eyeballs. Another may be pt-query-digest.
    – Rick James
    Sep 30 '17 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.