MySQL 用户执行存储过程的权限

╰+哭是因爲堅強的太久メ 2022-08-26 03:56 418阅读 0赞

MySQL中以用户执行存储过程的权限为EXECUTE  
  
比如我们在名为configdb的数据库下创建了如下存储过程,存储过程的定义者为user\_admin  
use configdb;  
drop procedure if exists sp\_dev\_test\_user\_add;  
  
delimiter $$  
CREATE DEFINER=\`user\_admin\`@\`%\` PROCEDURE \`sp\_dev\_test\_user\_add\`(  
in var\_user varchar(30),  
in var\_ip varchar(15),  
in var\_username varchar(30),  
in var\_email varchar(30),  
in var\_orginfo varchar(30)  
)  
BEGIN  
create temporary table errors (error varchar(500));  
if exists ( select user from mysql.user where user=var\_user) then  
    insert into errors values (concat('用户名 "',var\_user,'" 已存在!'));  
end if;  
if exists (select \* from errors) then  
    select error from errors;  
else        
    set @user=concat(var\_user,'@\\'',var\_ip,'\\'');  
    set @s=concat('create user ',@user,' identified by ''12345'';');  
    prepare cmd from @s;  
    execute cmd;  
    set @s=concat('GRANT SELECT ON \`mysql\`.\`func\` TO ',@user,';');  
    prepare cmd from @s;  
    execute cmd;    
    set @s=concat('GRANT SELECT ON \`mysql\`.\`proc\` TO ',@user,';');  
    prepare cmd from @s;  
    execute cmd;   
    replace into dev\_test\_userinfo values (var\_user,var\_username,var\_email,var\_orginfo);  
end if;  
drop temporary table errors;  
END  
$$  
delimiter ;  
  
试着创建一个普通用户user\_test1  
mysql>create user user\_test1 identified by '12345';  
  
查看其权限  
mysql>show grants for user\_test1;  
\+-----------------------------------------------------------------------------------------------------------+  
| Grants for user\_test1@%                                                                                   |  
\+-----------------------------------------------------------------------------------------------------------+  
| GRANT USAGE ON \*.\* TO 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' |  
\+-----------------------------------------------------------------------------------------------------------+  
  
赋予其configdb上的select\\insert\\delete\\update权限  
mysql>grant select,insert,delete,update on configdb.\* to 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9'  
mysql> show grants for user\_test1;  
\+-----------------------------------------------------------------------------------------------------------+  
| Grants for user\_test1@%                                                                                   |  
\+-----------------------------------------------------------------------------------------------------------+  
| GRANT USAGE ON \*.\* TO 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' |  
| GRANT SELECT, INSERT, UPDATE, DELETE ON \`configdb\`.\* TO 'user\_test1'@'%'                                  |  
\+-----------------------------------------------------------------------------------------------------------+  
  
使用此用户登录MySQL执行刚才定义的存储过程  
mysql>use configdb;  
mysql>call sp\_dev\_test\_user\_add('uapp\_yzz','172.16.%','yzz','yzz@email','MySQL DBA');  
ERROR 1370 (42000): execute command denied to user 'user\_test1'@'%' for routine 'configdb.sp\_dev\_test\_user\_add'  
  
看来是权限不足，继续赋予其configdb上的execute权限  
mysql> grant execute on configdb.\* to 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9';  
mysql> show grants for user\_test1;  
\+-----------------------------------------------------------------------------------------------------------+  
| Grants for user\_test1@%                                                                                   |  
\+-----------------------------------------------------------------------------------------------------------+  
| GRANT USAGE ON \*.\* TO 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' |  
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON \`configdb\`.\* TO 'user\_test1'@'%'                         |  
\+-----------------------------------------------------------------------------------------------------------+  
  
重新使用此用户登录MySQL执行刚才定义的存储过程  
mysql>use configdb;  
mysql>call sp\_dev\_test\_user\_add('uapp\_yzz','172.16.%','yzz','yzz@email','MySQL DBA');  
ERROR 1449 (HY000): The user specified as a definer ('user\_admin'@'%') does not exist  
  
这次可以调用该存储过程了，但是提示存储过程定义中的definer不存在，原来仅仅是连接到MySQL服务器的用户具有执行存储过程的权限是远远不够的，最终要通过存储过程定义中指定的definer来执行存储过程。  
  
创建user\_admin'@'%'这个用户，并赋予configdb上相应的权限  
mysql>create user user\_admin identified by '12345';  
mysql> grant select,insert,delete,update on configdb.\* to 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9'；  
mysql> show grants for user\_admin;  
\+-----------------------------------------------------------------------------------------------------------+  
| Grants for user\_admin@%                                                                                   |  
\+-----------------------------------------------------------------------------------------------------------+  
| GRANT USAGE ON \*.\* TO 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' |  
| GRANT SELECT, INSERT, UPDATE, DELETE ON \`configdb\`.\* TO 'user\_admin'@'%'                                  |  
\+-----------------------------------------------------------------------------------------------------------+  
  
重新使用'user\_test1'@'%'用户登录MySQL执行刚才定义的存储过程  
mysql>use configdb;  
mysql> call sp\_dev\_test\_user\_add('uapp\_yzz','172.16.%','yzz','yzz@email','MySQL DBA');  
ERROR 1370 (42000): execute command denied to user 'user\_admin'@'%' for routine 'configdb.sp\_dev\_test\_user\_add'  
  
看来不仅仅是连接到MySQL服务器的用户需要具有存储过程上的执行权限，存储过程定义者同样需要该权限。  
mysql> grant execute on configdb.\* to 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9';  
mysql> show grants for user\_admin;  
\+-----------------------------------------------------------------------------------------------------------+  
| Grants for user\_admin@%                                                                                   |  
\+-----------------------------------------------------------------------------------------------------------+  
| GRANT USAGE ON \*.\* TO 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' |  
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON \`configdb\`.\* TO 'user\_admin'@'%'                         |  
\+-----------------------------------------------------------------------------------------------------------+  
  
重新使用'user\_test1'@'%'用户登录MySQL执行刚才定义的存储过程  
mysql>use configdb;  
mysql> call sp\_dev\_test\_user\_add('uapp\_yzz','172.16.%','yzz','yzz@email','MySQL DBA');  
ERROR 1044 (42000): Access denied for user 'user\_admin'@'%' to database 'configdb'  
可以执行存储过程了，但是提示权限不足，仔细查看存储过程的定义可以看到，存储过程中包含创建用户和赋予权限的语句，而我们赋给'user\_test1'@'%'用户和'user\_admin'@'%'都不具有这样的权限。  
  
赋予'user\_test1'@'%'创建用户的权限和赋权的权限，以及创建临时表的权限  
mysql> grant create user on \*.\* to 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' with grant option;  
mysql> grant create temporary tables on configdb.\* to 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9';  
mysql> show grants for 'user\_test1'@'%';   
\+-----------------------------------------------------------------------------------------------------------------------------------+  
| Grants for user\_test1@%                                                                                                           |  
\+-----------------------------------------------------------------------------------------------------------------------------------+  
| GRANT CREATE USER ON \*.\* TO 'user\_test1'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |  
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON \`configdb\`.\* TO 'user\_test1'@'%'                                         |  
\+-----------------------------------------------------------------------------------------------------------------------------------+  
  
重新使用'user\_test1'@'%'用户登录MySQL执行刚才定义的存储过程  
mysql>use configdb;  
mysql> call sp\_dev\_test\_user\_add('uapp\_yzz','172.16.%','yzz','yzz@email','MySQL DBA');  
ERROR 1044 (42000): Access denied for user 'user\_admin'@'%' to database 'configdb'  
对了，不管你是以什么账户登录的MySQL，最后是使用存储过程的definer执行存储过程的，所以应当把创建用户和赋权的权限付给definer，这里为user\_admin'@'%'这个账户。  
  
赋予'user\_admin'@'%'创建用户的权限和赋权的权限  
mysql> grant create user on \*.\* to 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' with grant option;  
mysql> grant  create temporary tables on configdb.\* to 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9';  
mysql> show grants for 'user\_admin'@'%';   
\+-----------------------------------------------------------------------------------------------------------------------------------+  
| Grants for user\_admin@%                                                                                                           |  
\+-----------------------------------------------------------------------------------------------------------------------------------+  
| GRANT CREATE USER ON \*.\* TO 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |  
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON \`configdb\`.\* TO 'user\_admin'@'%'                                         |  
\+-----------------------------------------------------------------------------------------------------------------------------------+  
  
重新使用'user\_test1'@'%'用户登录MySQL执行刚才定义的存储过程  
mysql>use configdb;  
mysql> call sp\_dev\_test\_user\_add('uapp\_yzz','172.16.%','yzz','yzz@email','MySQL DBA');  
ERROR 1142 (42000): SELECT command denied to user 'user\_admin'@'%' for table 'user'  
  
哦，除了configdb库外还得有mysql库上user表的权限，给加上，看来权限问题还真是棘手，呵呵~  
mysql> grant select,insert,delete,update  on mysql.\* to 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9';  
mysql> show grants for 'user\_admin'@'%';   
\+-----------------------------------------------------------------------------------------------------------------------------------+  
| Grants for user\_admin@%                                                                                                           |  
\+-----------------------------------------------------------------------------------------------------------------------------------+  
| GRANT CREATE USER ON \*.\* TO 'user\_admin'@'%' IDENTIFIED BY PASSWORD '\*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |  
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, EXECUTE ON \`configdb\`.\* TO 'user\_admin'@'%'                |  
| GRANT SELECT, INSERT, UPDATE, DELETE ON \`mysql\`.\* TO 'user\_admin'@'%'                                                             |  
\+-----------------------------------------------------------------------------------------------------------------------------------+  
  
重新使用'user\_test1'@'%'用户登录MySQL执行刚才定义的存储过程  
mysql>use configdb;  
mysql> call sp\_dev\_test\_user\_add('uapp\_yzz','172.16.%','yzz','yzz@email','MySQL DBA');  
Query OK, 0 rows affected (0.05 sec)  
  
终于OK了，相信通过这一系列过程，大家应该能够很清楚的了解MySQL存储过程相关的执行权限了。另外，定义该存储过程还需要有CREATE ROUTINE的权限、更该存储过程需要有ALTER ROUTINE的权限（这里是用超级用户在configdb创建的存储过程，上述权限都是具备的），调用存储过程的用户需要有EXECUTE权限，最终执行存储过程的用户也即存储过程定义者要具备存储过程定义语句中相关的各种权限。  
  
MySQL的权限分的比较细，大致可分为表权限、列权限、过程权限具体可参考MySQL官方手册。