• 背景
• 排查原因
• 错误提示一的原因
  • definer
  • invoker
  • 错误提示二的原因
    • log_bin_trust_function_creators
• 解决方案
  • 错误提示一的方案
    • 方案一
    • 方案二
  • 错误提示二的方案
• 总结

背景

在AWS RDS环境下，使用mysqldump备份了一个MySQL数据库数据库，然后想把它用mysql的命令还原到另外一个数据库下面，结果在还原的过程中遇到的下面的错误提示信息，此时的MySQL实例是没有开启binlog。

ERROR 1227 (42000) at line 1163: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

如果在开启了binlog的情况下，把mysqldump命令备份的SQL文件导入到新的schema下面，则会出现如下的错误提示：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

下面研究了一下出现上面两个错误的原因分别是什么。

排查原因

错误提示一的原因

先看第一个错误是什么原因导致的

根据第一个错误提示信息，我找到了导出来的SQL文件的第1163行，发现这一行的代码如下所示，在这一行定义了一个trigger触发器。

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`app_user`@`10.10.%`*/ /*!50003 trigger SEC_RESOURCE_ROLE_DELETE_TS_NN_UPDATE_TRIGGER before update on SEC_RESOURCE_ROLE
            for each row
            begin
                    if not(NEW.DELETE_TS <=> OLD.DELETE_TS) then
                            set NEW.DELETE_TS_NN = if (NEW.DELETE_TS is null, '1000-01-01 00:00:00.000', NEW.DELETE_TS);
                    end if;
            end */;;
DELIMITER ;

根据错误提示信息，再加上定位到的SQL语句，我猜测是当前我执行导入操作的用户，没有权限吗？

为了验证我的猜测，我把同样的SQL文件，在本地的MySQL数据库中，使用root用户导入了一次，结果成功了。而使用了AWS RDS for MySQL的管理员用户想RDS中导入却失败，确实是这个权限的问题。

我们都知道AWS RDS中的用户是没有root权限的，但是没有想到它的权限是这么低。RDS中权限最大的用户就是这个了，如果这个权限都不够的话，那就没有任何其他用户可以用了。只能继续想办法看下具体是需要什么样的权限才可以执行上面的创建触发器的语句。

我把上面创建触发器的SQL语句，稍做修改，发现把里面的DEFINER选项/*!50017 DEFINER=app_user@10.10.%*/给去掉就可以创建成功。改为如下的语句来执行是OK的：

DELIMITER ;;
/*!50003 CREATE*/ /*!50003 trigger SEC_RESOURCE_ROLE_DELETE_TS_NN_UPDATE_TRIGGER before update on SEC_RESOURCE_ROLE
            for each row
            begin
                    if not(NEW.DELETE_TS <=> OLD.DELETE_TS) then
                            set NEW.DELETE_TS_NN = if (NEW.DELETE_TS is null, '1000-01-01 00:00:00.000', NEW.DELETE_TS);
                    end if;
            end */;;
DELIMITER ;

这说明是definer的语句导致了上面的触发器不能正常在MySQL版本的RDS中执行。

在MySQL中，创建函数、存储过程、视图、事件、触发器的时候，可以为其指定definer属性，但是只能指定执行当前创建函数、存储过程、视图、事件、触发器对象DDL语句的用户才可以，如果要指定为其他用户作为definer，则需要使用超级用户才可以。

definer

这里说明一下definer关键字的作用。

definer关键字的作用是用来指定当前的函数、存储过程、视图、事件、触发器等数据库对象是由哪个用户创建的。这里在指定的时候可以为某一个数据库对象指定其他用户作为definer，但前提是拥有super权限的用户才可以在创建数据库对象的时候指定其他用户作为definer，非super权限的用户，不可以这么做，只能指定它自己作为definer。如果在创建对象的时候没有显示的声明definer，会用当前执行创建对象DDL语句的用户来作为默认的definer。

如下是定义了definer的存储过程p1的示例。它使用了SQL SECURITY DEFINER属性，并且指定了definer为u3@%这个用户，存储过程里面是对数据库procedure_test下面的表t1的counter字段执行加1的一个update操作。也就是说，这个存储过程p1每被调用一次，数据库procedure_test下面的表t1的counter字段的值就会被加1。

use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 1;
END;;
delimiter ;

/*上面的存储过程，等价于下面的存储过程*/
use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p1()
-- SQL SECURITY DEFINER /*可以省略该行，不指定的时候，默认就是使用sql security definer*/
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 1;
END;;
delimiter ;

对于上面的存储过程p1，不管调用这个存储过程的用户是否对procedure_test数据库下面的表t1是否有select、update的权限(注意：修改一个表，前提是可以查询表中的数据，查询出来后才可以修改，所以不仅仅需要update的权限，还需要select的权限)，只要这个用户在procedure_test数据库下面有EXECUTE的权限，这个用户就可以调用procedure_test数据库下面的p1这个存储过程。

它是以definer中指定的用户u3@%来执行这个存储过程中的命令的。如果definer中定义的用户u3@%对存储过程中使用的数据库对象procedure_test.t1没有对应的select、update的权限，那么这个调用存储过程p1的用户在以definer用户u3@%去调用该存储过程的时候，也会失败。

invoker

说道了definer属性，就要提一下invoker属性。

在定义函数、存储过程、视图对象的时候，除了可以指定definer属性之外，还可以为其指定invoker属性。invoker属性的含义是，哪个用户可以调用这个数据库对象。

当前一个对象没有在begin前面显示的声明SQL SECURITY DEFINER或SQL SECURITY INVOKER的时候，默认是使用SQL SECURITY DEFINER。当一个对象显示的声明了SQL SECURITY INVOKER则会覆盖definer属性的定义，真正在执行对应的对象的时候，会按照invoker的权限去判断是否可以执行对应的命令。

如下是一个定义了invoker的存储过程p2。这个存储过程，和前面不同的是，这里使用了SQL SECURITY INVOKER属性。

use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 100;
END;;
delimiter ;

对于上面的存储过程p2，虽然定义的时候指定了definer为u3@%，但是由于还指定了SQL SECURITY INVOKER，所以在这个存储过程被调用的时候，会根据invoker的属性去判断是否可以成功调用该存储过程，忽略definer属性的约束。这个存储过程p2是否可以调用成功，取决于调用者是否对数据库procedure_test下面的t1表拥有select、update的权限。不会判断调用者对数据库procedure_test是否有execute权限。

**注意：**触发器、事件这两个对象是没有invoker属性，不能为其指定哪些用户可以调用执行它们，它们的调用执行由MySQL自己决定什么时候调用，最多只能为其指定definer属性，标识是哪个用户创建的触发器、事件。其他的几个像：存储过程、函数、视图是可以为其指定invoker属性的，标识哪个用户可以调用该对象。

错误提示二的原因

接下来我们再看第二个错误是什么原因导致的。

根据错误二的提示信息，我尝试使用root用户在我本地的MySQL中执行导入操作，在开启binlog的情况下，是可以导入成功的。但是如果使用一个非root用户，在本地MySQL开启binlog的情况下， 导入确实会出现下面的错误提示：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

错误提示给出了关键的解决方式：那就通过参数
log_bin_trust_function_creators的配置可以解决这个问题。

log_bin_trust_function_creators

该参数只有在binlog开启的情况下才会生效，如果binlog没有开启，这个参数不管配置成什么值，都不会生效，不起任何作用。所以，接下来我们讨论的这个参数配置为off或者on的前提是：binlog已经是开启的状态。

参数
log_bin_trust_function_creators的取值范围是0或1，对应着off或者on，其默认值为off。它是用来控制MySQL是否信任函数、存储过程、触发器的创建者所创建的这些数据库对象。

• off，表示不信任。在创建函数、存储过程、触发器之前，MySQL会验证这些对象是否可以被创建。
• on，表示信任。在创建函数、存储过程、触发器之前，MySQL不会去验证这些对象是否可以被创建，待创建的对象只要语法上没有问题，就可以创建成功。

那么参数
log_bin_trust_function_creators到底是对待创建的数据库对象(function、procedure、trigger)做什么验证呢？

如果待创建的数据库对象，在定义过程中引用了非确定性因素的函数事件，比如在SQL语句中引用了rand()、uuid()、now()等MySQL内置的函数，就认为这个待创建的数据库对象是不安全的。因为每次调用或执行这个数据库对象后，它所产生的结果是不确定的。此时MySQL就认为这个对象是安全的数据库对象。

• 如果参数log_bin_trust_function_creators=off的时候，MySQL就会对待创建的数据库对象进行上面我们描述的检查和验证。是安全的对象，则可以创建成功，如果是不安全的对象则不能创建成功(拥有超级权限的用户除外，超级权限的用户在log_bin_trust_function_creators=off的情况下，即便是非安全的数据库对象，也可以创建成功)。会抛出一个异常信息如下：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

• 如果参数log_bin_trust_function_creators=on的时候，MySQL就不会对待创建的数据库对象进行安全性的检查，在语法没有错误的前提下，可以直接创建成功。

题外话：为什么说引用rand()、uuid()、now()等不确定结果函数的数据库对象就是不安全的呢？

这要从MySQL数据库binlog和主从复制说起。

我们知道在binlog开启的情况下，我们对数据库中数据的任何变更操作都会被记录在binlog中，从库在同步主库的数据的时候，就是读取主库中binlog的记录，然后再从库在执行一遍，来达到从库数据库和主库数据一致的要求。

但是如果我们调用或执行一些数据库对象的时候，它们引用了MySQL内置的不确定性的函数如uuid()。那么binlog中记录的这个操作的SQL语句在不同的MySQL实例上重放的时候，将会得到不同的结果。

例如一个存储过程中对某个表的某一行进行了修改，其语句为update t set a=uuid() where id = 1;。那么在binlog的记录格式是statement格式的时候，binlog中就会记录这样的一个SQL语句，并不是把调用uuid()之后的得到的数据行修改后的内容记录在binlog中。那么这样的binlog在同步到从库之后，从库重放binlog中的这样的记录时，在从库上执行的update t set a=uuid() where id = 1;这个SQL语句后的结果得到的uuid()的值，和主库上的值是相同的可能性几乎为零。这就导致了从库上面id=1的数据行，和主库上面id=1的数据行的内容不一致，进而可能导致主从同步中断。这就是为什么引用不确定性内置函数的数据库对象被视为不安全的原因。

如果在binlog格式是row格式的情况下，就不会出现上面我们描述这样因为不安全的内置函数引用而导致的主从数据不一致的情况，因为row格式的binlog记录的是id=1这一行数据修改后的数据内容，这样的binlog在从库中重放的时候，就直接把修改后的数据内容应用到从库上，而不是把在主库上执行是SQL语句在从库上重新执行一遍。所以，在binlog开启并且格式为row的前提下，即便是把参数
log_bin_trust_function_creators=on，表示在创建数据库对象的时候不做验证，也不会导致主从数据不一致的问题。

如果开启了binlog之后并且搭建了主从，同时binlog的格式为statement，参数
log_bin_trust_function_creators改为on之后，表示不会对函数、存储过程、触发器等对象在创建的时候，进行验证它们的安全性。就很有可能导致主从数据不一致。

当然，如果没有开启binlog，或者开启了binlog但没有配置主从同步，只是一个单实例的MySQL服务，设置这个参数为on，不会导致主从不同步。但是如果开启了binlog，并且格式为statement，在使用binlog做数据恢复还原的时候，也是有可能导致还原后的数据和原先的数据不一致。所以，只要开启了binlog，格式请一定设置为row格式。

对于binlog开启与否，以及binlog的格式，主从链路的状态和
log_bin_trust_function_creators参数之间的影响关系如下：

解决方案

错误提示一的方案

了解了definer和revoker的作用之后，根据MySQL给抛出来的错误信息，结合AWS RDS版本的MySQL用户是没有超级用户的权限的，我们就可以理解为什么使用mysqldump导出来的创建触发器的语句会带有definer，并且也能理解为什么导入到新的schema下面就不能导入的原因了。

理解了为什么，我们就可以针对性的进行解决这个问题了。目前的解决方案有以下几种：

方案一

因为AWS RDS版本的MySQL用户没有超级权限，不能指定definer为其他用户，所以我们可以把这个definer定义给删除掉。这样在导入的时候，就会默认使用当前执行导入的用户来作为该触发器的definer。

如果你导出来的SQL文件比较小，可以直接双击打开编辑或者使用vi命令来编辑，找到对应的definer定义，将其删除。

如果你导出来的SQL文件比较大，双击打开或者使用vi命令编辑基本不可能，那么就使用如下的sed语句可以将函数、存储过程、视图、事件、触发器中的definer属性给删除掉。在使用sed命令之前，请先备份你的原来的SQL文件，避免删除失败后，不能恢复。

cp your_mysqldump_file.sql your_mysqldump_file.sql.bak

sed -i -e 's/DEFINER=`app_user`@`10.10.\%`//g' your_mysqldump_file.sql

方案二

由于AWS RDS版本的MySQL提供的用户，没有超级权限，所以它不能创建入触发器的时候，指定其他用户作为definer，那么我们可以将dump出来的SQL文件中的definer改为当前RDS提供的用户。同样需要备份源SQL文件，给自己留个后悔药吃。

cp your_mysqldump_file.sql your_mysqldump_file.sql.bak

sed -i -e 's/DEFINER=`app_user`@`10.10.\%`/DEFINER=`your_rds_admin_user`@`\%`/g' your_mysqldump_file.sql

这样修改后的SQL文件，就可以使用RDS提供的用户导入到新的schema下面了。

这里在替换为RDS admin的用户的时候，需要注意，用户名称后面是需要跟上%还是跟上具体的某一个网段，则需要根据你的mysql.user表中定义的RDS admin用户对应的host列中的值是什么。一般情况下面，RDS提供的admin用户他们的网段都是%，而不是具体的某一个网段。

错误提示二的方案

目前我的RDS版本的MySQL是开启了binlog，并且binlog的格式是row格式，但是我没有配置主从同步的链路。所以，我们要修复前面开始遇到的问题，只需要把参数
log_bin_trust_function_creators由原先默认的off改为on，来开启信任函数的创建者就可以避免创建触发器、存储过程等数据库对象的验证了。

修改参数的操作如下：

mysql> set global log_bin_trust_function_creators = on;
Query OK, 0 rows affected (0.00 sec)

修改完成上面的参数后，再重新导入SQL文件，即便是里面有触发器、函数的创建，也可以创建成功了。不会对这些将要创建的触发器、函数、存储过程进行安全性的检查了。

但是如果需要这个参数长时间生效，需要在MySQL的参数配置文件中增加这个参数的配置。自己安装部署的MySQL服务，可以修改my.cnf配置文件，如果是RDS版本的MySQL，则需要修改参数组中的这个参数，找到对应的参数，修改后，保存既可以，如果是多个RDS实例，使用同一个参数组，则需要复制出来一份新的参数组来给当前需要修改参数的RDS来使用。为了让新的参数组生效，则需要重启MySQL的RDS服务。

总结

这里简单总结一下这篇文章的内容。

这里，我们主要分析了MySQL中创建函数、存储过程、触发器中时候，定义者definer和调用者invoker的使用规则，它们用来控制MySQL数据库中函数、存储过程、触发器等数据库对象的被调用的时候，哪些用户可以调用它们。

同时，还分析了参数
log_bin_trust_function_creators的作用，它用来控制在MySQL中创建函数、存储过程、触发器等数据库对象的时候，是否会对这些待创建的数据库对象进行数据安全性的检查。