查看存储过程:
方法一:
select `name` from mysql.proc where db = 'your_db_name' and `type`
= 'PROCEDURE'
方法二:
show procedure status;
1.修改mysql
存储过程的definer
修改mysql.proc表 的definer字段
Sql代码
update mysql.proc set definer='author@%'
update mysql.proc set security_type='INVOKER' where db='MC';
UPDATE `mysql`.`proc` SET `definer`='author%' WHERE `db`='test' AND
`name`='proc_name' AND `type`='PROCEDURE';
UPDATE `mysql`.`proc` SET `definer`='xuzhijing@%' WHERE
`db`='myDB'
AND `type`='PROCEDURE';
update mysql.proc set definer='root@%' where
db='myDb';
UPDATE `mysql`.`proc` SET `definer`='root00@%' WHERE
`db`='test' AND `name`='jjjj' AND `type`='PROCEDURE';
UPDATE `mysql`.`proc` SET `definer`='wtc_678869@%'
WHERE `db`='myDB' AND `type`='PROCEDURE';
2.修改security_type
Sql代码
update mysql.proc set security_type='INVOKER' where db='myDB';
(1)MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户;
(2)如果SQL
SECURITY子句指定为DEFINER,存储过程将使用存储过程的DEFINER执行存储过程,验证调用存储过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象的权限;
(3)如果SQL
SECURITY子句指定为INVOKER,那么MySQL将使用当前调用存储过程的用户执行此过程,并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限;
(4)如果不显示的指定SQL SECURITY子句,MySQL默认将以DEFINER执行存储过程。
3.执行存储过程授权
Sql代码
GRANT EXECUTE ON test.* TO 'xuzhijing'@'%';
GRANT CREATE ROUTINE,ALTER ROUTINE, SELECT,CREATE, INSERT,
UPDATE, DELETE, EXECUTE ON test.* TO 'xuzhijing'@'%' IDENTIFIED BY
'111111'
GRANT EXECUTE ON test.* TO 'xuzhijing'@'%';
GRANT CREATE ROUTINE,ALTER ROUTINE, SELECT,CREATE, INSERT,
UPDATE, DELETE, EXECUTE ON test.* TO 'xuzhijing'@'%' IDENTIFIED BY
'111111'
CREATE ROUTINE : 创建存储过程的权限
ALTER ROUTINE : 修改存储过程的权限
4.删除用户
REVOKE all ON test.* FROM xuzhijing@'%'
DELETE FROM user WHERE User='user_name' and Host='host_name';
FLUSH PRIVILEGES;