A customer's server is set up with only one user, that has global permissions to do everything. We want to change this to one user per application, with the minimum necessary permissions.
To do so, without breaking anything, we would like to analyze the query log, to find out what type of queries are executed. Is there such a tool?
Preferably it would say that only SELECT, INSERT, UPDATE and DELETE have been executed, and we could remove all other permissions.
There is no tool that can analyze a query log and tell you what will be run now and for all time based on it, simply because there is no way to know if the query log is an exhaustive list of all the queries which may be generated by the software.
You need to look at the actual code doing the querying to make this determination.
If you don't have access to the actual code the best thing to do in your situation is to create per-application accounts which only have access to the database(s) required for the specific application, and can not run any DDL statements -- this will at least eliminate the Little Bobby Tables problem (and frankly any system in which the code is running DDL statements is probably fundamentally broken and needs to be carefully re-evaluated for proper permissions fencing).
Do this in your development environment (you do have one, right?) and test to be sure your applications function correctly. Be especially wary of hard-coded usernames/passwords that you might not catch.
If you are not fortunate enough to have a development environment test during off-hours, with access to the affected applications disabled for all users except your internal development/testing team.
After that you can decide if it makes sense to put further restrictions on some parts of the app (e.g. read-only accounts, accounts which can only
INSERTorUPDATEbut notDELETE, etc.)