Support sql deny/block list on tidb

[sylzd]

Enhancement

When tidb run in production env, we often encounter the situation that the user produces a harmful sql.
in that case, the cluster will become slower and even crash. Further more, the user's retry mechanism may produce more and more similar sql, it will get worse.

For now, we handle it in this way:

1. Using pt-kill tool to keep killing this kind of sql.
2. Using SPM to relieve symptoms.

create global binding for 
SELECT SN FROM XX WHERE PUSH_DATE=? ORDER BY PUSH_DATE  LIMIT ?,10000
using 
SELECT /*+ MAX_EXECUTION_TIME(50) */ SN FROM XX WHERE PUSH_DATE=? ORDER BY PUSH_DATE  LIMIT ?,10000;
# as far as I known, it can't work, when values == 0.

But either of them works after executing sql, when qps > 1000, it also costs a lot. related to #11026

I propose a better way to deal with this situation, we can use the hint like this

SELECT SN FROM XX WHERE PUSH_DATE=? ORDER BY PUSH_DATE  LIMIT ?,10000
using 
SELECT /*+ DISABLE_EXECUTION(true) */ SN FROM XX WHERE PUSH_DATE=? ORDER BY PUSH_DATE  LIMIT ?,10000;

And then we can ban the sql at preprocessing.

If there are other better ways, please comment.

[morgo]

Thank you for the feature request!

Just to comment on how others do this: MySQL supports a feature they call a database firewall. Basically what it does it is record a list of permitted SQL digests and once the server is put in enforcing mode it will refuse to execute anything that is not in the list.

There are pros and cons for each approach: an allow list can help with security (reducing ability for SQL injection), and a deny/block list can help with QoS.

[sylzd]

Thank you for the feature request!

Just to comment on how others do this: MySQL supports a feature they call a database firewall. Basically what it does it is record a list of permitted SQL digests and once the server is put in enforcing mode it will refuse to execute anything that is not in the list.

There are pros and cons for each approach: an allow list can help with security (reducing the ability for SQL injection), and a deny/block list can help with QoS.

Sounds Great to me! I will learn about database firewall.