MYSQL的DEFINER和SQL SECURITY参数

研发侧在调用mysql函数报了如下的错误
org.springframework.jdbc.UncategorizedSQLException: ### Error querying database. Cause: java.sql.SQLException: The user specified as a definer ('ceshi'@'%') does not exist 。

数据库里查看这几个函数的信息如下：
(root:]> select ROUTINE_SCHEMA,SPECIFIC_NAME FROM  information_schema.Routines where ROUTINE_NAME in ('testcurrval','testnextval','testsetval');
+----------------+---------------+
| ROUTINE_SCHEMA | SPECIFIC_NAME |
+----------------+---------------+
| test       |    testcurrval       |
| test       |    testnextval       |
| test       |    testsetval        |
+----------------+---------------+
3 rows in set (0.01 sec)

函数在test用户下，初步看见这个报错信息时，觉得可能是程序配置文件配置的用户信息错了

(root:)[(none)]> show grants for 'ceshi'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'ceshi' on host '%'
(root:)[(none)]> show grants for 'test'@'%';
+-------------------------------------------------------------------------+
| Grants for test@%                                                   |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%'                                    |
| GRANT SELECT, INSERT, UPDATE, EXECUTE ON `test`.* TO 'test'@'%' |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

但是研发反馈程序端没有使用ceshi这个用户，所以从数据库层再次排查。

查看函数的创建语句，是不是定义中使用了ceshi@%,查看命令如下：
SELECT * FROM  information_schema.Routines where  ROUTINE_NAME in ('testcurrval','testnextval','testsetval');

通过上述的查询看见字段DEFINER中包含ceshi@%，

(root:=)[(none)]> SELECT DEFINER  FROM  information_schema.Routines where  ROUTINE_NAME in ('testcurrval','testnextval','testsetval');
+---------+
| DEFINER |
+---------+
| ceshi@%  |
| ceshi@%  |
| ceshi@%  |
+---------+

猜测可能是这个问题导致的，解决方法如下：
select db,name,definer from  mysql.proc where definer='ceshi@%';

update mysql.proc set definer='test@%' where definer='ceshi@%';

更新之后，程序侧反应问题解决。

问题解决之后，想究其根本原因，所以做了如下的实验进行测试。

DEFINER参数和SQL SECURITY的实验测试

mysql常见的存储程序(存储过程，函数，触发器，事件，视图) 会在创建时定义用户，在其被调用时，它的执行权限由DEFINER属性和SQL SECURITY 属性控制。

DEFINER测试

1、在测试环境创建用户'funct'@'%'，并授于创建过程，执行过程的权限

GRANT  EXECUTE, CREATE ROUTINE ON `test`.* TO 'funct'@'%' identified by  "funct123";

2、使用用户funct登录，在test数据库下创建函数p2

use test;   DELIMITER $ CREATE PROCEDURE p2() SQL SECURITY DEFINER BEGIN   select * from t0_1; END $ DELIMITER ;

 3、查看该函数的definer信息

select ROUTINE_SCHEMA,SPECIFIC_NAME,DEFINER FROM  information_schema.Routines where ROUTINE_NAME='p2';

可以看到用户自己创建的过程，DEFINER就是他自己。

4、创建另外一个用户funct1并授权，授予调用test数据库存储过程的权限，和授予select的权限

 

GRANT select, execute ON test.* TO 'funct1'@'%' identified by "funct123";

5、使用funct1登录，调用test数据库下的过程p2

6、授予funct用户select的权

GRANT SELECT ON test.* TO 'funct'@'%';

7、funct1用户下重新调用p2

结论

1. 所有存储程序都包含一个DEFINER命名的mysql账号，如果没有指定，默认是创建者。比如funct@’%’用户下创建的过程p2,definer就是funct@’%’。
2. 任意用户A访问该PROCEDURE时，能否成功取决于A是否有调用该PROCEDURE的权限，以及definer指定的用户是否有procedure中的对象的权限。

比如：使用用户funct1访问p2，第一次调用时，funct1用户虽然有execute过程的权限，但是definer定义的funct@’%’用户没有select表对象的权限，就执行失败了。之后赋予了definer定义的funct@’%’用户的select权限，funct1用户下再次调用p2过程就成功了。

SQL SECURITY测试

SQL SECURITY 值有两种，第一种SQL SECURITY DEFINER，另外一种SQL SECURITY INVOKER

第一种由上面的实验也能看出来，定义SQL SECURITY DEFINER时，调用阶段根据调用者的权限进行判断是否可以调用，执行阶段根据DEFINER的账户权限判断操作是否可以继续执行。

第二种定义SQL SECURITY INVOKER时，看如下的实验结果：

1、查看funct的权限

 2、撤销funct@'%'的select权限

revoke select ON `test`.* from 'funct'@'%';

3、以funct用户登录，在test数据库下创建过程p3

mysql -u funct -p   use test;   DELIMITER $ CREATE PROCEDURE p3() SQL SECURITY INVOKER BEGIN   select * from t0_1 limit 1; END $ DELIMITER ;

select ROUTINE_SCHEMA,SPECIFIC_NAME,DEFINER FROM  information_schema.Routines where ROUTINE_NAME='p3';

4、查看用户funct1的权限信息

show grants for funct1@'%';

5、直接以funct1用户登录，调用过程p3

 6、撤销funct1@'%'用户的select的权限

revoke select ON `test`.* from 'funct1'@'%';

 7、重新以funct1登录，再次执行p3

 结论

SQL SECURITY有以下规则：

a)当SQL SECURITY定义为DEFINER时，调用阶段根据调用者的权限进行判断是否可以调用，执行阶段根据DEFINER的账户权限判断操作是否可以继续执行。

例如：具有EXECUTE 的用户，可以调用DEFINER为root的存储执行root账户允许的任意操作。（这是不够安全的）

b)当SQL SECURITY定义为INVOKER时，调用阶段和执行阶段都根据调用者的权限进行判断操作是否可以继续。  （推荐使用）

注意如果同时指定了DEFINER也指定了SQL SECURITY类型则SQL SECURITY优先级高。