查看所有存储过程
select routine_schema as database_name,
routine_name,
routine_type as type,
data_type as return_type,
routine_deFinition as deFinition
from information_schema.routines
where routine_schema not in ('sys', 'information_schema', 'performance_schema')
-- and r.routine_schema = 'database_name'
order by routine_schema,routine_name;、
查看所有视图
select table_schema as database_name,
table_name as vIEw_name
from information_schema.tables
where table_schema not in ('sys', 'performance_schema')
and table_type = 'VIEW'
-- and table_schema = 'database_name'
order by table_schema, table_name;
show create table view_name;
在创建视图或者是存储过程的时候,是需要定义安全验证方式的(也就是安全性SQL SECURITY),其值可以为definer或invoker,表示在执行过程中,使用谁的权限来执行。
definer和invoker的区别
definer: 由definer(定义者)指定的用户的权限来执行
当定义为DEFINER时,必须数据库中存在DEFINER指定的用户,并且该用户拥有对应的操作权限,才能成功执行,与当前用户是否有权限无关。
invoker: 由调用这个视图(存储过程)的用户的权限来执行
当定义为INVOKER时,只要执行者有执行权限,就可以成功执行。
现在在MySQL涉及的definer有view、trigger、function、procedure、event。
如果方便修改mysql中所有已经定义到的definer?
由于前期在测试库上开发的缘故,我们经常定义到的definer为`root`@`%`,后来搬移到生产库上又得改回来,存在着大量的更新,上百个的视图,函数等一个个改不免太麻烦并且也可能遗漏。如下为总结出的方便修改所有definer的方法,可以直到查漏补缺的作用。
现在在mysql涉及的definer有view、trigger、function、procedure、event。我们一个个作介绍。
1.修改function、procedure的definer
SELECT db,NAME,TYPE,DEFINER FROM mysql.proc WHERE DEFINER<>'mysql.sys@localhost'; -- 函数、存储过程
update mysql.proc set definer='user@localhost' where ; -- 如果有限定库或其它可以加上where条件
2.修改event的definer
SELECT db,NAME,body,DEFINER FROM mysql.event WHERE DEFINER<>'mysql.sys@localhost'; -- 定时事件
update mysql.EVENT set definer=' user@localhost ' where ;
3.修改view的definer
相比function的修改麻烦点:
SELECT TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION,SECURITY_TYPE,DEFINER FROM information_schema.views WHERE DEFINER<>'mysql.sys@localhost';
SELECT CONCAT("alter DEFINER=`user`@`localhost` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") FROM information_schema.VIEWS WHERE DEFINER<>'mysql.sys@localhost';
查询出来的语句再执行一遍就好了。
4.修改trigger的definer
目前还没有具体方便的方法,可以借助工具端如HeidiSQL、sqlyog等来一个个修改。注意改前有必要锁表,因为如果改的过程中有其它表改变而触发,会造成数据不一致。
Flush tables with readlock
Unlock table