The difference of two-step permission check in MySQL execution

Time:2020-1-21

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 difference of two-step permission check in MySQL execution

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 firstINSERT command denied to userAgainevent 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.

The difference of two-step permission check in MySQL execution

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 exampleselect 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