玖叶教程网

mysql 查看存储过程 分配存储过程权限

查看存储过程

方法一：

select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

方法二：

show procedure status;

查看存储过程或函数的创建代码

show create procedure proc_name;

show create function func_name;

修改存储过程的定义者

mysql>update mysql.proc set DEFINER='usename' WHERE NAME='proc_name' AND db='mydb';

`hkuser`@`10.0.0.%`

update mysql.proc set DEFINER="[email protected].%";

update mysql.proc set DEFINER="hk@%";

update mysql.proc set DEFINER="[email protected].%" WHERE NAME='HospitalService_ProcessAppointmentOrder_V1_0' AND db='Hospital';

update mysql.proc set DEFINER="[email protected].%" WHERE NAME='PushRecord_QueryUnPushedRecord' AND db='User';

分配查看存储过程及函数的权限：（前提是有select数据库权限）

grant select on mysql.proc to hkdb@"10.0.0.%";

grant select on mysql.func to hkdb@"10.0.0.%";

grant alter routine on *.* to hkdb@"10.0.0.%";

grant execute on *.* to hkdb@"10.0.0.%";

收回权限

revoke select on mysql.* to hkdb@"10.0.0.%";

revoke alter routine on *.* from hkdb@"10.0.0.%";

revoke execute on *.* from hkdb@"10.0.0.%";

revoke create routine on *.* from hkdb@"10.0.0.%";

grant all PRIVILEGES on TD_OA.uservoat to hkkf@"192.168.0.%" IDENTIFIED by "hkkf@123";

grant all PRIVILEGES on TD_OA.oauserlogin to hkkf@"192.168.0.%" IDENTIFIED by "hkkf@123";

grant select on mysql.func to hkkf@"192.168.0.%";

grant select on mysql.proc to hkkf@"192.168.0.%";

grant alter routine on *.* to hkkf@"192.168.0.%";

grant execute on *.* to hkkf@"192.168.0.%";

grant CREATE on *.* to hkkf@"192.168.0.%";

flush PRIVILEGES;