MySQL授权用户对存储过程查询和执行权限【转】

1、授权MySQL存储过程执行权限

在项目中往往会用到存储过程来统计一些比较复杂的报表，如果数据库是mysql，则需要在执行存储过程前需要为用户添加权限，才可以，否则会提示：

mysql User does not have access to metadata required to determine stored procedure parameter types错误。

方法如下（必须俩个权限都执行，缺一不可）:

1.GRANT SELECT ON mysql.proc TO 'testuser'@'%';

2.GRANT EXECUTE ON testdb.* TO 'testuser'@'%' ；

如果执行execute报错

>GRANT EXECUTE ON kams.CAL_RETURN_POINTS TO 'testuser'@'%';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

# 改成
>GRANT EXECUTE ON PROCEDURE kams.CAL_RETURN_POINTS TO 'testuser'@'%';
Query OK, 0 rows affected (0.00 sec)

在sqlyog中使用testuser用户登录调用存储过程进行测试

CALL pro_get_order(1,'鲁J',258,'20170501','20170701');

测试正常了

2、授权MySQL存储过程只读查询权限

在其他RDBMS中，可以将查看某个存储过程（PROCEDURE）定义的权限给某个用户，例如在SQL Server中，可以单独将查看ProcedureName定义的权限授予UserA

GRANT VIEW DEFINITION ON ProcedureName TO UserA; --用具体的存储过程名和账号替换

那么在MySQL中能否实现这个功能呢？ 找了很多资料，没有看到有这方面的功能，官方文档没有涉及这样的权限，网上有个方法：可以通过授予用户查询mysql.proc这样的权限来间接实现这个功能

grant select on mysql.proc to usrname@'xxx.xxx.xxx.xxx';

个人简单测试了一下，这样授权后，发现还是有一些其他问题。

 

mysql> show create procedure prc_insert;  --没有授权前报这个错误。
ERROR 1305 (42000): PROCEDURE prc_insert does not exist
mysql> show create procedure prc_insert\G; --授权后
*************************** 1. row ***************************
           Procedure: prc_insert
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_insert`(in  cnt int)
begin
declare i int;
set i=1;
while i < cnt do
    insert into test(id, name) select i,  CONCAT('name',i) from dual;
 
    set i = i+1;
 
end while;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
 
ERROR: 
No query specified

问题1：这样授权后，你能看到所有数据库存储过程的定义（不仅仅是某个某个存储过程，或某个数据库的存储过程的定义）， 这里就涉及一个权限放大的问题。例如，我本来打算只授予用户A查看存储过程PRC_A的定义权限，但是那样授权后，A能查看很多存储过程的定义，甚至还能查看一些没有访问权限数据库的存储过程的权限。严格意义上来说，这个授权是不合理，而且是有问题的。

问题2：MySQL 8.0开始抛弃了mysql.proc，而且从MySQL 8.0开始，如果你要用SHOW CREATE PROCEDURE或SHOW CREATE FUNCION的话，需要有什么权限呢？。

MySQL 8.0开始，存储过程存储在mysql.routines和mysql.parameters系统表中，但是这些表无法直接访问，只能访问INFORMATION_SCHEMA.ROUTINES。像MySQL 8.0之前那样授权行不通了，

Where are stored procedures stored?

Stored procedures are stored in the mysql.routines and mysql.parameters tables, which are part of the data dictionary. You cannot access these tables directly. Instead, query the INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.29, “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.19, “The INFORMATION_SCHEMA PARAMETERS Table”.

You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.7.9, “SHOW CREATE PROCEDURE Statement”.

个人测试发现，授予alter routine后，就能查看存储过程的定义，但是这个授权也带来一个问题，授予权限的用户不仅可以查看存储过程定义，而且可以删除这个存储过程（这个也是一个问题）。这个当然，不清楚是否还有其它授权来实现。

mysql> grant alter routine on procedure MyDB.prc_2 TO test@'192.168%';
Query OK, 0 rows affected (0.08 sec)

总结：

在MySQL5.7或之前版本，可以通过授予用户查询mysql.proc来间接实现查看存储过程定义的权限，在MySQL 8.0 可以通过授予用ALTER ROUTINE的权限来间接实现查看存储过程定义的权限，两者都有一个问题，那就是会放大权限，这个属于MySQL功能性的缺陷，短时间估计一直存在

————————————————

转自

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/dreamfly88/article/details/72885897

MySQL能否授予查看存储过程定义权限给用户-腾讯云开发者社区-腾讯云
https://cloud.tencent.com/developer/article/2076502