MySQL DEFINER具体使用详解

目录
  • 1.definer简单介绍

前言:

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

1.definer简单介绍

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

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 三张表的查询权限。下面通过示例来具体演示下:

# 创建两个视图 定义者都是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 相关注意事项吧:

  • sql security 属性建议使用默认的 definer 。
  • 某个库内的视图、函数、存储过程建议使用统一的 definer 用户。
  • 不要轻易修改及删除数据库用户,因为此用户可能是相关对象的定义者。
  • 若要修改 sql security 属性,请做好测试,清楚修改前后的区别。
  • 数据库迁移时,要注意新环境存在相关对象的定义者用户。
  • 做数据库迁移时,建议首先在新环境创建相关用户及赋予权限。

总结:

本篇文章主要介绍了 definer 相关知识,这些主要在创建视图、函数、存储过程等对象时会遇到,平时比较容易被忽略。但这些细节还是应该注意的,多了解多学习下,这样到真正用到的时候可以避免很多错误。

到此这篇关于mysql definer具体使用详解的文章就介绍到这了,更多相关mysql definer内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐