Introduction/TLDR:
We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools. We would like to get feedback from the Community about the different approaches for achieving this.
The problem:
Running EXPLAIN is a great way to understand how complex SQL statements are executed. So it is natural that monitoring and query analysis tools utilize EXPLAIN for these purposes.
However, there is a problem for cases when INSERT/UPDATE/DELETE statements need to be explained. Running EXPLAIN for these statements, a read-only operation, requires the same privileges as running the original statements which would change data! This means that to be able to run EXPLAIN for any DML statement which was executed in the system users employed by monitoring and query analysis tools need to be granted global INSERT/UPDATE/DELETE privileges along with global SELECT privilege.
Granting such broad privileges to a monitoring and query analysis tool user account sounds like a bad idea from a security point of view. Many users would be wary of doing this. Also, for example, it is not recommended by the Percona Monitoring and Management manual. But without these privileges it becomes impossible for these tools to analyze problematic data-change statements using EXPLAIN.
In Percona, we are thinking about solving this problem by making EXPLAIN for statements like INSERT/UPDATE/DELETE to require fewer privileges than the original statements being explained. In principle, requiring only SELECT privilege for explaining INSERT/UPDATE/DELETE should be sufficient to solve the problem. But ideally, there should be a separate EXPLAIN_PLAN privilege that will be more restrictive than SELECT.
Possible solutions:
We are considering several approaches to implementing this, each with their own benefits and drawbacks. But before proceeding to describe them, let me mention a couple of complications to keep in mind:
- By its nature, an EXPLAIN statement might disclose information about the contents of the tables (e.g., how many rows are there for a certain key value). This can be a problem for some sensitive data. Currently, this can be avoided by using partial revokes feature to revoke any privileges on schemas in which sensitive data are stored from Monitoring/QuAn users.
- The introduction of an EXPLAIN_PLAN privilege which supports levels other than global (e.g., can be granted/revoked or partially revoked on schema level) will break binary compatibility of ACL tables with upstream. This results in a more complicated migration process from upstream Oracle MySQL to Percona Server for MySQL. Moreover, it might cause more issues in the future if upstream decides to change ACL tables in some other direction. So we try to avoid solutions which require incompatible changes like this.
Here are the approaches we are thinking about at the moment:
1. We introduce a new global dynamic EXPLAIN_PLAN privilege to be granted to monitoring and query analysis tool users. The EXPLAIN code is changed to require either this global EXPLAIN_PLAN privilege OR privileges that explained statement requires.
+) The patch implementing this approach is fairly simple and minimally intrusive. So it is less likely to introduce bugs and cause merge troubles with upstream.
+) Existing user’s code/scenarios continue to work (but see comment about partial revokes below!).
-) Since the new privilege is global, it won’t work with partial revokes (or making it work will significantly increase complexity and intrusiveness of this change). So there won’t be a way to avoid disclosure of data from sensitive tables to monitoring and query analysis tool users through EXPLAIN.
2. We introduce a new global dynamic EXPLAIN_DML privilege to be granted to monitoring and query analysis tool users. Unlike in the previous case, it is interpreted like a flag (there is precedent for this, see NDB_STORED_USER privilege). This flag indicates that EXPLAIN for DML statements run by user require SELECT privilege instead of appropriate INSERT/UPDATE/DELETE privileges.
+) This code change can be fairly straightforward, except the code to handle special cases of performance_schema and information_schema tables.
+) This approach works with partial revokes, so sensitive data can be protected.
+) All scenarios for existing users continue to work as is, as only users with a new EXPLAIN_DML flag are affected.
-) The patch is fairly big and intrusive (as we need to touch places where DML currently checks if users have INSERT/UPDATE/DELETE privileges).
There is a variant of this approach which uses a start-up option to identify users requiring special treatment instead of privilege.
3. We introduce a new global dynamic EXPLAIN_ONLY privilege. This privilege is “negative” – meaning that it blocks execution of any non-EXPLAIN statements while active. EXPLAIN statements still require the same privileges for their execution as before. The idea is that with EXPLAIN_ONLY it becomes possible to create a role, to be used by monitoring and query analysis tools, with all necessary “strong” privileges for INSERT/ UPDATE/DELETE statements, but restricted to the execution of EXPLAIN statements only.
+) The patch implementing this approach is fairly simple and non-intrusive.
+) Existing user’s code/scenarios continue to work.
+) This approach works with partial revokes, so sensitive data can be protected.
-) To be able to execute EXPLAIN statements, without sacrificing access to P_S and I_S tables, monitoring and query analysis tools will have to constantly activate and deactivate this special role to which EXPLAIN_ONLY was granted.
-) We are introducing a new “negative” privilege concept which is not in line with existing privileges, so it will require additional documentation and explanations.
The optimal choice between these approaches is not straightforward and depends on what use-cases are considered to be important.
Our request:
We would like to get feedback from the Community about the suggested approaches. What is your preference and why? Will they work for you or not? Or perhaps you see some problems we have missed? Maybe you have some great alternative idea? Please let us know what you think about them by leaving comments below this post.
We would especially appreciate feedback from people working on and with monitoring and query analysis tools! Would you see value in this change?
It would be also great to hear feedback from the Oracle MySQL Team, as we plan to contribute upstream these changes once they are implemented! We have created feature request #110132 “Allow EXPLAIN for DML to be run with lower/read-only privileges” for the upstream for this.
Thank you in advance!
That would be nice to have, but extremely dangerous and hard to manage: explain analyze, function calls inside the explain (#67632)… IMO I think we don’t really need it, if you want to explain a “delete” likely you are writing it and will run it
If this is for/because PMM, then the monitoring user should have those privileges… but better than explain a plan after the fact, it would be if PMM could read and save the real optimizer data during execution and show it
Hello Francisco!
Thank you for your feedback! The plan was not to apply new privilege/rules to EXPLAIN ANALYZE. Indeed, it is different from ordinary EXPLAIN and allowing it for DML to users without INSERT/UPDATE/DELETE privilege looks more risky.
Regarding argument that PMM and similar tools should have those privileges. This is exactly the problem. Many users are reluctant to grant such privileges to monitoring users, which makes it harder to use PMM/other similar tools for DML.
Storing real execution plan/optimizer data somewhere and using it in PMM sounds like a good idea, but has its own tradeoffs (where to store, for how long and so on).