MySQL DEFINER具体使用详解

吾爱主题阅读：189 2024-04-02 08:04:44 评论：0

前言：

在 MySQL 数据库中，在创建视图及函数的时候，你有注意过 definer 选项吗？在迁移视图或函数后是否有过报错情况，这些其实都可能和 definer 有关系。本篇文章主要介绍下 MySQL 中 definer 的含义及作用。

1.DEFINER简单介绍

以视图为例，我们来看下官方给出的视图创建基础语法：

1 2 3 4 5 6 7 8 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 ]

仔细看上面语法，发现 definer 出现了两次，一次是 DEFINER = user 一次是 SQL SECURITY 选项可以设置为 DEFINER 或 INVOKER ，看到这里，你有猜到 definer 的作用了吗？

definer 翻译成中文是“定义者”的意思。MySQL中，创建视图(view)、函数(function)、存储过程(procedure)、触发器(trigger)、事件(event)时，都可以指定 DEFINER = user 选项，即指定此对象的定义者是谁，若不显式指定，则创建此对象的用户就是定义者。

对于视图、函数及存储过程，还可以指定 SQL SECURITY 属性，其值可以为 DEFINER（定义者）或 INVOKER（调用者），表示在执行过程中，使用谁的权限来执行。DEFINER 表示按定义者拥有的权限来执行，INVOKER 表示用调用者的权限来执行。

默认情况下，SQL SECURITY 属性为 DEFINER 。其值为 DEFINER 时，数据库中必须存在 DEFINER 指定的定义者用户，并且该定义者用户拥有对应的操作权限及引用的相关对象的权限，执行者只需拥有调用权限就能成功执行。当 SQL SECURITY 属性为 INVOKER 时，则需要执行者有调用权限并且有引用的相关对象的权限，才能成功执行。

简单来说，假设一个视图查询了 a b c 三张表，若此视图的 SQL SECURITY 属性为 DEFINER ，当使用用户 u 查询此视图时，用户 u 只需此视图的查询权限即可；若此视图的 SQL SECURITY 属性为 INVOKER ，则用户 u 需要有此视图的查询权限且有 a b c 三张表的查询权限。下面通过示例来具体演示下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 # 创建两个视图定义者都是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表的查询权限

自定义函数及存储过程也是类似，若 SQL SECURITY 属性为 INVOKER ，同样需要调用者有执行权限并且有引用的相关对象的权限，才能成功执行。

2.一些注意事项

额外补充点知识，只有拥有创建权限且有 SUPER 权限的用户才可以建 DEFINER = 其他用户的对象。例如：root 账号可以创建 DEFINER = testuser 的视图，而 testuser 在有创建视图的前提下只能创建 DEFINER 为自己的视图。

为了更细致的了解 DEFINER 相关作用，以视图为例再来说几个特殊情况下的示例：

假设用户 u1 不存在，使用 root 账号可以创建 DEFINER = u1 的视图，若该视图的 SQL SECURITY 属性为 DEFINER ，则查询时会报用户不存在的错误，若该视图的 SQL SECURITY 属性为 INVOKER ，则使用 root 账号可正常查询该视图。

假设用户 u2 存在但不具有查询表 a 的权限，使用 root 账号可以创建 DEFINER = u2 的视图来查询表 a ，若该视图的 SQL SECURITY 属性为 DEFINER ，则查询时报缺少权限的错误，若该视图的 SQL SECURITY 属性为 INVOKER ，则使用 root 账号可正常查询该视图。当使用用户 u2 登录时，则创建视图来查询表 a 会直接报错缺少权限，即创建不了查询表 a 的视图，无论此视图的 SQL SECURITY 属性是什么。

看完上述示例后，不清楚你对 DEFINER 是否有了更清晰的认识，有兴趣的同学可以自己测试看一看。结合笔者日常经验，说下 DEFINER 相关注意事项吧：