Description:
It is possible to run EXPLAIN for DML statements like UPDATE,DELETE or INSERT to get their execution plans.
This operation, despite being read-only, requires the same privileges as the original DML statement.
Such an approach works fine for cases when user which runs EXPLAIN statement is the same one which runs DML statement. However, it becomes problematic if EXPLAIN statement to be run by some separate Performance Monitoring or Query Analysis tools. For such tools separate users often created with reduced set of privileges, typically read-only. Naturally, administrators are reluctant to give those users read-write rights to database. And this makes it impossible for such tool to run EXPLAIN against DMLs in these cases...
It would be great if some solution for this problem is implemented:
- Changing EXPLAIN for DML to require only SELECT privileges would solve the problem
- Alternatively new EXPLAIN privilege can be introduced to allow execution of EXPLAIN statement for DMLs (there are multiple options here depending on how much code you are ready to change and how much you are concerned about data disclosure by EXPLAIN statements).
- Another alternative is adding an option/flag which will restrict certain users to read-only operations even if they have privileges which would normally allow them to run data-change statements.
How to repeat:
N/A
