Original link: he Xiaodong blog
The article comes from a question of lemonVersion 8.0 of the document reference in the article
Screenshot of the problem:
The main test scenario is: the K field does not exist, and there is no select permission. See which error is thrown first, and the result is that the error with insufficient permission is thrown. It is a fine-grained permission check in the execution phase. Without permission to open the table, it is impossible to determine whether the K field exists. After a half day’s review of the document, no direct conclusions were made.
There is an example in the event permission section of the official document:
CREATE EVENT e_store_ts ON SCHEDULE EVERY 10 SECOND DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
Assuming that the user has event permission and select permission, and no insert permission, the event does not work at last, and the error message first
INSERT command denied to userAgain
event execution failed., you can make sure that the permissions are not checked at the connector or analyzer stage. If you check at that stage, the entire SQL will not execute. On the other hand, if you want to check fine-grained permissions on the connector, you need to do part of the analyzer’s work. It violates the principle of single responsibility to disassemble SQL and verify the permissions of query fields and tables. When checking in the analyzer, the responsibility of the analyzer is a little out of bounds for checking events, triggers and other permissions. Fine grained checking at the actuator stage is the best option.
For the widely spread figure above (45 columns in the actual battle of MySQL in the geek time, delete the infringement), if you don’t go into the details, you will think that the permissions of tables and fields are checked in the connector, so you can directly return. The reality is:Permission check at MySQL connector level is mainly to check user authentication and connection access database permission. For table, field, trigger, event and other operations, permission check will be carried out in detailed execution stage。
Additional notes: Fine grained checks are only checked when table operations are involvedFor example
select 1+1;Such SQL does not involve table operations and can be executed without select permission. For update and delete operations, not only the corresponding permission but also the select permission is required. For details, please refer to the permission provided by mysql
? original articles. If there is any mistake, please contact [email protected] for discussion
Some data of learning MySQL by the way