视图及存储过程用户权限的处理和检查

存储过程相关权限
create_routine
alter_routine
execute

视图相关权限
视图及存储过程用户权限的处理和检查

sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/ ' abc.sql > abc.new.sql
egrep -in "definer|set @@" abc.new.sql

导入存储过程时，应检查并修改为正确数据库用户

[root@localhost tmp]# egrep -in "definer|set @@" abc.new.sql
9253:CREATE DEFINER=`root`@`%` PROCEDURE `nextseq_proc`( )
9277:CREATE DEFINER=`root`@`%` PROCEDURE `setBidTimeForward`(IN p_bid LONG, IN p_months INT)
[root@localhost tmp]#

授予用户全局super权限

grant super on *.* to p2puser@localhost;

撤销用户全局super权限

update mysql.user set super_priv='N' where user='p2puser';

查询具有全局super权限的用户

SELECT user,host FROM mysql.user WHERE super_priv='Y';

查看全局用户权限设置

select * from mysql.user where user='p2puser';

select * from mysql.user\G;

查看数据库用户权限设置

select * from mysql.db where user='p2puser';

select * from mysql.db\G;

授权grant all权限明细

 Select_priv: Y
 Insert_priv: Y
 Update_priv: Y
 Delete_priv: Y
 Create_priv: Y
 Drop_priv: Y
 Grant_priv: N
 References_priv: Y
 Index_priv: Y
 Alter_priv: Y
 Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Create_view_priv: Y
 Show_view_priv: Y
 Create_routine_priv: Y
 Alter_routine_priv: Y
 Execute_priv: Y
 Event_priv: Y
 Trigger_priv: Y