MySQL DEFINER具体使用详解

目录
  • 前言:
  • 1.DEFINER简单介绍
  • 2.一些注意事项
  • 总结:

前言:

在 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内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql:The user specified as a definer ('xxx@'%') does not exist的解决方案

    今天项目优化过程中,mysql有个问题The user specified as a definer ('wx_root'@'%') does not exist 查了一下,意思是执行sql无权限.  看了下数据库用户是quoters 而sql没有所属权.那就找原因了.发现sql执行过程中,调用了一个存储过程 而这个存储过程的创建者是wx_root . 所以在执行的时候,quoters没有权限 解决方案和操作: 1.查看存储过程的所属对象:  右键存储过程函数-->选择对象属性-->查看函数创

  • MySQL DEFINER具体使用详解

    目录 前言: 1.DEFINER简单介绍 2.一些注意事项 总结: 前言: 在 MySQL 数据库中,在创建视图及函数的时候,你有注意过 definer 选项吗?在迁移视图或函数后是否有过报错情况,这些其实都可能和 definer 有关系.本篇文章主要介绍下 MySQL 中 definer 的含义及作用. 1.DEFINER简单介绍 以视图为例,我们来看下官方给出的视图创建基础语法: CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TE

  • Mysql中explain作用详解

    一.MYSQL的索引 索引(Index):帮助Mysql高效获取数据的一种数据结构.用于提高查找效率,可以比作字典.可以简单理解为排好序的快速查找的数据结构. 索引的作用:便于查询和排序(所以添加索引会影响where 语句与 order by 排序语句). 在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这样就可以在这些数据结构上实现高级查找算法.这些数据结构就是索引. 索引本身也很大,不可能全部存储在内存中,所以索引往往以索引文件的形式存储在磁盘上. 我们

  • MySQL exists 和in 详解及区别

    MySQL exists 和in 详解及区别 有一个查询如下: SELECT c.CustomerId, CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders o WHERE o.CustomerID = cu.CustomerID) 这里面的EXISTS是如何运作呢?子查询返回的是OrderId字段,可是外面的查询要找的是CustomerID和CompanyName字段,这两个字段肯定不在OrderID里面啊

  • Mysql 5.7.18安装方法及启动MySQL服务的过程详解

    MySQL 是一个非常强大的关系型数据库.但有些初学者在安装配置的时候,遇到种种的困难,在此就不说安装过程了,说一下配置过程.在官网下载的MySQL时候,有msi格式和zip格式.Msi直接运行安装即可,zip则解压在自己喜欢的目录地址即可.在安装这两种的时候,都需要配置才能用.以下介绍主要是msi格式默认的地址:C:\Program Files\ mysql-5.7.18-win32. 一.在安装或者解压后,需要配置环境变量,过程如下:我的电脑->属性->高级系统设置->高级->

  • MySQL 修改密码实例详解

    MySQL 修改密码实例详解 许久不用MySQL了,今天打开HediSQL连接mysql时发现root密码忘记了,修改密码操作捣鼓了一阵子,记录一下,以备后用.(Win10本机操作) 关闭MySQL服务:net stop MySQL 以管理员身份打开cmd控制台窗口,进入MySQL所在目录的bin目录,执行mysqld --skip-grant-tables 再另开一个cmd窗口直接执行mysql命令(由于2操作已经是无需授权即可访问表,所以直接用mysql,不带用户名和密码即可登录) 切换到m

  • Spring整合MyBatis(Maven+MySQL)图文教程详解

    一. 使用Maven创建一个Web项目 为了完成Spring4.x与MyBatis3.X的整合更加顺利,先回顾在Maven环境下创建Web项目并使用MyBatis3.X,第一.二点内容多数是回顾过去的内容 . 1.2.点击"File"->"New"->"Other"->输入"Maven",新建一个"Maven Project",如下图所示: 1.2.请勾选"Create a si

  • MySQL常用聚合函数详解

    一.AVG AVG(col) 返回指定列的平均值 二.COUNT COUNT(col) 返回指定列中非NULL值的个数 三.MIN/MAX MIN(col):返回指定列的最小值 MAX(col):返回指定列的最大值 四.SUM SUM(col) 返回指定列的所有值之和 五.GROUP_CONCAT GROUP_CONCAT([DISTINCT] expr [,expr ...]              [ORDER BY {unsigned_integer | col_name | expr}

  • php mysql insert into 结合详解及实例代码

    php mysql insert into 结合详解 ySQL INSERT INTO语句在实际应用中是经常使用到的语句,所以对其相关的内容还是多多掌握为好. 向数据库表插入数据 INSERT INTO 语句用于向数据库表添加新记录. 语法 INSERT INTO table_name VALUES (value1, value2,....) 您还可以规定希望在其中插入数据的列: INSERT INTO table_name (column1, column2,...) VALUES (valu

  • MySQL常用时间函数详解(推荐)

    2.6 DATE_SUB/DATE_ADD DATE_SUB(date,INTERVAL expr type) date 参数是合法的日期表达式.expr 参数是您希望添加的时间间隔. SELECT id FROM my_table WHERE create_time >= date_sub(now(), INTERVAL 3 HOUR) AND create_time < now(); Type 值 •MICROSECOND •SECOND •MINUTE •HOUR •DAY •WEEK

  • Mysql Limit 分页查询优化详解

    select * from table LIMIT 5,10; #返回第6-15行数据 select * from table LIMIT 5; #返回前5行 select * from table LIMIT 0,5; #返回前5行 我们来写分页 物理分页 select * from table LIMIT (当前页-1)*每页显示条数,每页显示条数; MySQL之Limit简单优化.md 同样是取90000条后100条记录,传统方式还是改造方式? 传统方式是先取了前90001条记录,取其中最

随机推荐