3

I have inherited a MySQL server that has a user with far too many privileges granted but I believe not all of these are required.

Is there any way to compare the MySQL privileges granted to the MySQL privileges actually used?

Md Haidar Ali Khan
  • 6,523
  • 9
  • 40
  • 62
pkt1975
  • 33
  • 3

1 Answers1

2

This is a demanding task that may not give the exact answer. The only exact way is that you know requirement of applications that use that user privileges to access specific DB.

You must know: does that particular user administrate MySQL server? If not, remove privileges to 'mysql', 'performance_schema' and 'information_schema'.

Second step: If you are uncertain which DBs are used, you may check logs.

There is useful log config that can be put in my.cnf: How to Log All mysql queries into log file? Bear in mind that it will put additional load on your server, but it may give you info about usage. Anyway, you will need to check logs for a week or more to be able to determine which queries are executed by this user and to determine which privileges are required. Bear in mind that some queries will be executed only sporadically, like some monthly tasks (usually procedures), that will not show up so easily.

The best way is to find out which applications use that user and to decide from that which DBs can be excluded from privileges given to that particular user.

Community
  • 1
dotokija
  • 136
  • 2