Detailed explanation of MySQL DEFINER usage

Preface:

In MySQL database, when creating views and functions, have you ever paid attention to the definer option? Whether there are any errors after migrating views or functions, these may actually be related to the definer. This article mainly introduces the meaning and function of definer in MySQL.

1.Brief introduction to DEFINER

Taking views as an example, let's look at the official basic syntax for creating views:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Looking carefully at the above syntax, we can see that definer appears twice, once in DEFINER = user and once in the SQL SECURITY option, which can be set to DEFINER or INVOKER. Have you guessed the function of definer?

Definer translated into Chinese means "definer". In MySQL, when creating a view, function, stored procedure, trigger, or event, you can specify the DEFINER = user option, which specifies who defines the object. If it is not explicitly specified, the user who creates the object is the definer.

For views, functions, and stored procedures, you can also specify the SQL SECURITY attribute, whose value can be DEFINER (definer) or INVOKER (invoker), indicating whose permissions are used during execution. DEFINER means that the program is executed with the permissions of the definer, and INVOKER means that the program is executed with the permissions of the caller.

By default, the SQL SECURITY attribute is DEFINER. When its value is DEFINER, the definer user specified by DEFINER must exist in the database, and the definer user has the corresponding operation permissions and permissions for the referenced related objects. The executor only needs to have the call permission to successfully execute. When the SQL SECURITY attribute is INVOKER, the execution can be successful only if the executor has the calling permission and the permission of the referenced related objects.

Simply put, suppose a view queries three tables abc. If the SQL SECURITY attribute of this view is DEFINER, when user u queries this view, user u only needs the query permission of this view; if the SQL SECURITY attribute of this view is INVOKER, user u needs to have the query permission of this view and the query permission of the three tables abc. The following example demonstrates this in detail:

# Create two views. Both are defined by testuser. The query is on the test_tb table. mysql> show grants for 'testuser'@'%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@% |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE VIEW, SHOW VIEW ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create view view_definer\G
*************************** 1. row ***************************
                View: view_definer
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`testuser`@`%` SQL SECURITY DEFINER VIEW `view_definer` AS select `test_tb`.`stu_id` AS `stu_id`,`test_tb`.`stu_name` AS `stu_name` from `test_tb`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> show create view view_invoker\G
*************************** 1. row ***************************
                View: view_invoker
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`testuser`@`%` SQL SECURITY INVOKER VIEW `view_invoker` AS select `test_tb`.`stu_id` AS `stu_id`,`test_tb`.`stu_name` AS `stu_name` from `test_tb`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)

# Only grant uview user the permission to query these two views to perform query test mysql> select user();
+-----------------+
| user() |
+-----------------+
| uview@localhost |
+-----------------+
1 row in set (0.00 sec)

mysql> show grants;
+--------------------------------------------------------+
| Grants for uview@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'uview'@'%' |
| GRANT SELECT ON `testdb`.`view_definer` TO 'uview'@'%' |
| GRANT SELECT ON `testdb`.`view_invoker` TO 'uview'@'%' |
+--------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from view_definer;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1001 | from1 |
| 1002 | dfsfd |
| 1003 | fdgfg |
+--------+----------+
9 rows in set (0.00 sec)

mysql> select * from view_invoker;
ERROR 1356 (HY000): View 'testdb.view_invoker' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

# The result is that the view_definer query is normal, but the view_invoker query cannot be performed because the uview user does not have the query permission for the test_tb table

Custom functions and stored procedures are similar. If the SQL SECURITY attribute is INVOKER, the caller must have execution permissions and permissions on the referenced objects to execute successfully.

2. Some notes

An additional point of knowledge: only users with the create permission and the SUPER permission can create objects with DEFINER = other users. For example, the root account can create a view with DEFINER = testuser, but testuser can only create a view with its own DEFINER if the view has been created.

In order to understand the role of DEFINER in more detail, let's take views as an example to show some examples in special cases:

Assume that user u1 does not exist. You can use the root account to create a view with DEFINER = u1. If the SQL SECURITY attribute of the view is DEFINER, an error indicating that the user does not exist will be reported when querying. If the SQL SECURITY attribute of the view is INVOKER, the root account can query the view normally.

Assume that user u2 exists but does not have permission to query table a. You can use the root account to create a view with DEFINER = u2 to query table a. If the SQL SECURITY attribute of the view is DEFINER, an error indicating lack of permission will be reported during the query. If the SQL SECURITY attribute of the view is INVOKER, the view can be queried normally using the root account. When logging in as user u2, creating a view to query table a will directly report a lack of permissions error, that is, the view to query table a cannot be created, no matter what the SQL SECURITY attribute of this view is.

After reading the above examples, I don’t know if you have a clearer understanding of DEFINER. Students who are interested can test it themselves. Based on my daily experience, let me talk about the precautions related to DEFINER:

• The SQL SECURITY attribute recommends using the default DEFINER.
• It is recommended that a unified DEFINER user be used for views, functions, and stored procedures within a library.
• Do not modify or delete the database user easily, because this user may be the definer of related objects.
• If you want to modify the SQL SECURITY property, please do a good job of testing and clearly understand the difference before and after the modification.
• When migrating the database, pay attention to the definer users of related objects in the new environment.
• When migrating the database, it is recommended to first create relevant users in the new environment and grant permissions.

Summarize:

This article mainly introduces the knowledge related to DEFINER, which is mainly encountered when creating views, functions, stored procedures and other objects, and is usually easily overlooked. But these details should still be paid attention to, and you should learn more about them so that you can avoid many mistakes when you actually use them.

This is the end of this article about the detailed use of MySQL DEFINER. For more relevant MySQL DEFINER content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!