因为某某离职，公司要求将他所有负责的业务系统的mysql账号都进行修改（直接删除，然后创建新账号），不过删除后却发现很多视图和存储过程访问不了。
因为该问题是mysql运维中比较典型的问题所以今天抽空记录一下：
情况是这么个情况：
之前使用的用户：dmpapadmin@'124.205.200.130' 然后领导要求drop 。
现在新建用户：dmpapadmin@'218.247.217.66' 然后授权grant all db_name.* to dmpapadmin@218.247.217.66'
然后使用新用户调用存储过程，报错如下：
[Err] 1449 - The user specified as a definer ('dmpapadmin'@'124.205.200.130') does not exist
很明显了报错说 ，嘿：对不起找不到账号'dmpapadmin'@'124.205.200.130'
很自然想到proc 里出了问题，好查看：
select * from proc limit 1 \G
*************************** 1. row ***************************
db: dsmcenter
name: bak_aaaaaa
type: PROCEDURE
specific_name: bak_aaaaaa
language: SQL
sql_data_access: CONTAINS_SQL
is_deterministic: NO
security_type: DEFINER
param_list:
returns:
省略存储过程body信息
definer: dmpapadmin@124.205.200.130
created: 2014-03-03 17:59:22
modified: 2014-02-13 17:31:21
sql_mode: STRICT_TRANS_TABLES
comment:
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: utf8_bin
看到了吧，存储过程里definer 依然是原来的旧账号，执行存储过程的时候存mysql会去找dmpapadmin@124.205.200.130
如果找不到自然就报错了。
这里需要了解一点基础知识，即存储程序（或说视图，函数，存储过程）他们都有一个SQL SECURITY 属性该属性有连个选择{ DEFINER | INVOKER }
默认情况是definer 即定义者默认情况下deiner的值就是创建存储程序的当前用户，当调其他用户调用存储过程的时候
存储过程回去检查definer里的用户是否存在并且调用用户是否有权限执行（如果用户有super权限那直接可以调用存储程序
如果具有存储过程所在数据库的all on db_name.*权限亦可调用存储过程）。
那么definer，invoke是如何影响存储过程的调用呢？ 请看如下实验：
1，创建安全类型为 invoker的存储过程，definer 为 root@localhost
drop procedure if exists t_p; \d $$ create DEFINER='root'@'localhost' procedure t_p() SQLSECURITY INVOKER begin select 1; end;$$ \d ;
*********** 3. row ***************************
Db: itop1011
Name: t_p
Type: PROCEDURE
Definer: root@localhost
Modified: 2014-03-04 15:01:39
Created: 2014-03-04 15:01:39
Security_type: INVOKER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_bin
然后创建一个新的用户：
grant all on itop1011.* to test@'localhost';
然后使用test用户调用call t_p();
call t_p();
+---+
| 1 |
+---+
| 1 |
+---+
2，创建安全类型为definer的存储过程 definer 为 root@localhost
drop procedure if exists t_p_d; \d $$ create DEFINER='root'@'localhost' procedure t_p_d()SQL SECURITY definer begin select 1; end;$$ \d ;
************ 5. row ***************************
Db: itop1011
Name: t_p_d
Type: PROCEDURE
Definer: root@localhost
Modified: 2014-03-04 15:05:38
Created: 2014-03-04 15:05:38
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_bin
同样test用户登录调用存储过程t_p_d()
call t_p_d();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
上述结果说明：只要拥有执行存储过程的权限（all包括了Execute权限）definer即使是具有super权限的root用户也 并不影响任何合法调用者
3，创建存储安全类型为invoker类型的存储过程，definer 为 test@localhost
drop procedure if exists t_p; \d $$ create DEFINER='test'@'localhost'procedure t_p() SQLSECURITY INVOKER begin select 1; end;$$ \d ;
*********** 8. row ***************************
Db: itop1011
Name: t_p
Type: PROCEDURE
Definer: test@localhost
Modified: 2014-03-04 15:16:59
Created: 2014-03-04 15:16:59
Security_type: INVOKER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_bin
创建另一个普通用户test1
grant all on itop1011.* to test1@'localhost';
然后调用call t_p();
+---+
| 1 |
+---+
| 1 |
+---+
4，创建安全类型为definer的存储过程，definer 为test@localhost
drop procedure if exists t_p_d; \d $$ create DEFINER='test'@'localhost' procedure t_p_d()SQL SECURITY definer begin select 1; end;$$ \d ;
*************************** 9. row ***************************
Db: itop1011
Name: t_p_d
Type: PROCEDURE
Definer: test@localhost
Modified: 2014-03-04 15:22:16
Created: 2014-03-04 15:22:16
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_bin
然后使用普通用户test1调用call t_p_d();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
上述结果说明：只要存储过程调用者test1具有存储过程的调用权限，无论存储过程的definer是谁，SQL SECURITY是definer还是invoker都无所谓。
5，删除 SQL SECURITY是definer的存储过程t_p_d的用户test@localhost
然后使用test1调用存储过程
call t_p_d();
ERROR 1449 (HY000): The user specified as a definer ('test'@'localhost') does not exist
报错信息：调用存储过程的时候回去检查definer用户是否存在，不存在则报错。
6，删除 SQL SECURITY是invoker
的存储过程t_p_d的用户test@localhost 然后使用test1调用存储过程
call t_p();
+---+
| 1 |
+---+
| 1 |
+---+
正常调用，一切都那么的ok。
7，在（5）的基础上创建用户grant all on itop1011.* to test@'%'; 然后使用test1调用
call t_p_d();
+---+
| 1 |
+---+
| 1 |
+---+
8，在（5）的基础上创建用户grant all on itop1011.* to test@'192.156.11.23'; 然后使用test1调用
call t_p_d();
ERROR 1449 (HY000): The user specified as a definer ('test'@'localhost') does not exist
（5），（6），（7），（8） 说明 当security_type为definer的时候 当存储过程被调用的时候mysql会去检查 definer用户如果不存在就报错，上面定义了一个test@'%'的用户后又可以调用是因为%包括了localhost（这里需要提醒一下，如果你通过普通用户定义了很多存储过程或试图或函数，那么当你打算修改该用户的时候你就要注意了，需要同时修改他们的definer为新的用户，否则调用的时候则会报错）而当security_type
为调用者的时候则没影响。因此这里建议创建存储程序的时候SQL SECURITY定义为invoker 。
结论：
如果你的数据库里打算大量使用存储程序（func,proc)或试图，那么你最好让他们的SQL SECURITY都定义为invoke，这样可以避免未来修改账号而导致
存储程序和试图不可访问。同时在mysqldump的时候会报错如：mysqldump: Got error: 1449: The user specified as a definer ('xxx'@'122.215.201.110') does not exist when using LOCK TABLES
ps: 另外要提示的一个问题是我们可以通过修改mysql.proc (proc里包含了function,这里注意func里并不会存储function的信息) 来修改存储
过程和函数的SQL SECURITY的属性。不过如果是视图那么对不起你只能先导出然后修改definer后再导入了具体：
select concat('create OR REPLACE SQLSECURITY INVOKER VIEW
',TABLE_NAME,' as ',VIEW_DEFINITION,';') AS def from information_schema.views \G 然后拷贝出来重建即可。
参考：http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html