Detailed MySQL DEFINER

Foreword:

In the MySQL database, have you paid attention to the definer option when creating views and functions? Whether there is any error report after migrating the view or function, these may actually have something to do with the definer. This article mainly introduces the meaning and function of the definer in MySQL.

1. Brief introduction of DEFINER

Taking the view as an example, let's take a 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 grammar, you find that the definer appears twice, once is DEFINER = user and once is the SQL SECURITY option can be set to DEFINER or INVOKER. Seeing this, have you guessed the role of the definer?

Definer translated into Chinese means "defender". In MySQL, when creating views, functions, procedures, triggers, and events, you can specify the DEFINER = user option, that is, who is the definer of this object, If not explicitly specified, the user who created this object is the definer.

For views, functions, and stored procedures, you can also specify the SQL SECURITY property, and its value can be DEFINER (definer) or INVOKER (caller), which indicates whose authority is used to execute during execution. DEFINER means to execute with the authority of the definer, and INVOKER means to execute with the authority of the caller.

By default, the SQL SECURITY property is DEFINER. When the value is DEFINER, the definer user specified by DEFINER must exist in the database, and the definer user has the corresponding operation authority and the authority of the referenced related object. The executor only needs to have the calling authority to execute successfully. When the SQL SECURITY attribute is INVOKER, the executor needs to have the calling authority and the authority of the referenced related object in order to execute successfully.

To put it simply, suppose a view queries three tables abc, if the SQL SECURITY property of this view is DEFINER, when user u is used to query this view, user u only needs to query this view; if the SQL of this view The SECURITY attribute is INVOKER, then user u needs to have query permissions for this view and query permissions for the three tables abc. The following is a specific demonstration through an example:

# 创建两个视图 定义者都是testuser 查询的是test_tb表
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)

# 只给uview用户查询这两个视图的权限 来进行查询测试
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

# 结果是view_definer查询正常，而view_invoker无法查询 因为uview用户不具有test_tb表的查询权限

Custom functions and stored procedures are similar. If the SQL SECURITY attribute is INVOKER, the caller also needs to have execution permissions and the permissions of the referenced related objects in order to execute successfully.

2. Some considerations

To add some additional knowledge, only users with create permissions and SUPER permissions can create DEFINER = other users' objects. For example, the root account can create a view with DEFINER = testuser, and testuser can only create DEFINER as its own view if there is a view created.

In order to understand the related functions of DEFINER in more detail, let's take the view as an example to illustrate some examples under special circumstances:

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

Assuming that user u2 exists but does not have the permission to query table a, 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, the query time will report a lack of permission error. If the SQL SECURITY attribute is INVOKER, the view can be queried normally with the root account. When using user u2 to log in, creating a view to query table a will directly report the lack of permissions, that is, the view of query table a cannot be created, no matter what the SQL SECURITY attribute of this view is.

After reading the above examples, it is not clear if you have a clearer understanding of DEFINER. Those who are interested can test it by themselves. Combining the author's daily experience, let's talk about the precautions related to DEFINER:

• The SQL SECURITY property recommends using the default DEFINER.
• It is recommended to use a unified DEFINER user for views, functions, and stored procedures in a library.
• Do not easily modify and delete database users, 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 make the difference before and after modification.
• When the database is migrated, pay attention to the definer user of the relevant object in the new environment.
• When doing database migration, it is recommended to first create relevant users and grant permissions in the new environment.

summary:

This article mainly introduces DEFINER related knowledge, which is mainly encountered when creating objects such as views, functions, stored procedures, etc., and is easier to be ignored in normal times. But you should pay attention to these details, learn more about it, so that you can avoid a lot of mistakes when you really use it.