开发发来问题说，开发环境的几个视图已经授权，但是指定用户登录后却无法访问。报错信息如下：
[SQL]select * from ipost;
[Err] - Access denied for user 'iqm'@'%' (using password: YES)
正式环境可以正常访问。
一开始以为是权限没有给予正确。
查看正式的授权情况：
mysql> show grants for tiq\G
*************************** . row ***************************
Grants for tiq@%: GRANT USAGE ON *.* TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`iperson` TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`idept` TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`icomp` TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`ipost` TO 'tiq'@'%'
rows in set (0.00 sec) mysql>
查看测试环境授权情况：
mysql> show grants for tiq\G
*************************** . row ***************************
Grants for tiq@%: GRANT USAGE ON *.* TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`idept` TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`icomp` TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`iperson` TO 'tiq'@'%'
*************************** . row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`ipost` TO 'tiq'@'%'
rows in set (0.00 sec) mysql>
对比发现授权没有问题。
然后查看其中一个视图在正式、测试环境中的定义。
正式环境查看视图的定义：
mysql> show create view ipost\G
*************************** . row ***************************
View: ipost
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`uadmin`@`%` SQL SECURITY DEFINER VIEW `ipost` AS select `t_post`.`id` AS `id`,`t_post`.`postcode` AS `postcode`,`t_post`.`postname` AS `postname`,`t_post`.`pk_job` AS `pk_job`,`t_post`.`job_code` AS `job_code`,`t_post`.`job_name` AS `job_name`,`t_post`.`org_code` AS `org_code`,`t_post`.`org_name` AS `org_name`,`t_post`.`pk_org` AS `pk_org`,`t_post`.`pk_post` AS `pk_post`,`t_post`.`status` AS `STATUS`,`t_post`.`enablestate` AS `enablestate`,`t_post`.`writebackoperate` AS `writebackoperate`,`t_post`.`writebackts` AS `writebackts`,`t_post`.`syncts` AS `syncts`,`t_post`.`operate` AS `operate`,`t_post`.`dirty` AS `dirty`,`t_post`.`ts` AS `ts`,`t_post`.`del_flag` AS `del_flag` from `t_post`
character_set_client: utf8
collation_connection: utf8_general_ci
row in set (0.00 sec) mysql>
测试环境查看视图的定义：
mysql> show create view ipost\G
*************************** . row ***************************
View: ipost
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`uadmin`@`%` SQL SECURITY DEFINER VIEW `ipost` AS select `t_post`.`id` AS `id`,`t_post`.`postcode` AS `postcode`,`t_post`.`postname` AS `postname`,`t_post`.`pk_job` AS `pk_job`,`t_post`.`job_code` AS `job_code`,`t_post`.`job_name` AS `job_name`,`t_post`.`org_code` AS `org_code`,`t_post`.`org_name` AS `org_name`,`t_post`.`pk_org` AS `pk_org`,`t_post`.`pk_post` AS `pk_post`,`t_post`.`status` AS `STATUS`,`t_post`.`enablestate` AS `enablestate`,`t_post`.`writebackoperate` AS `writebackoperate`,`t_post`.`writebackts` AS `writebackts`,`t_post`.`syncts` AS `syncts`,`t_post`.`operate` AS `operate`,`t_post`.`dirty` AS `dirty`,`t_post`.`ts` AS `ts`,`t_post`.`del_flag` AS `del_flag` from `t_post`
character_set_client: utf8
collation_connection: utf8_general_ci
row in set, warning (0.00 sec) mysql>
从这里可以发现，结果显示有个一个warning存在。
查看该warning的具体内容：
mysql> show warnings;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------+
| Note | | The user specified as a definer ('uadmin'@'%') does not exist |
+-------+------+--------------------------------------------------------------+
row in set (0.00 sec)
从这里可以看出，视图创建的时候，将正式环境的用户带了过来，但是测试环境并不存在该用户。其实原因很简单，就开发从ide中直接从正式环境把定义语句导出后在测试环境执行了。