MySQL视图已经授权，但是无法访问

开发发来问题说，开发环境的几个视图已经授权，但是指定用户登录后却无法访问。报错信息如下：

[SQL]select * from ipost;
[Err] 1045 - Access denied for user 'iqm'@'%' (using password: YES)

正式环境可以正常访问。

 

一开始以为是权限没有给予正确。

查看正式的授权情况：

mysql> show grants for tiq\G
*************************** 1. row ***************************
Grants for tiq@%: GRANT USAGE ON *.* TO 'tiq'@'%'
*************************** 2. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`iperson` TO 'tiq'@'%'
*************************** 3. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`idept` TO 'tiq'@'%'
*************************** 4. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`icomp` TO 'tiq'@'%'
*************************** 5. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`ipost` TO 'tiq'@'%'
5 rows in set (0.00 sec)

mysql> 

查看测试环境授权情况：

mysql> show grants for tiq\G
*************************** 1. row ***************************
Grants for tiq@%: GRANT USAGE ON *.* TO 'tiq'@'%'
*************************** 2. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`idept` TO 'tiq'@'%'
*************************** 3. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`icomp` TO 'tiq'@'%'
*************************** 4. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`iperson` TO 'tiq'@'%'
*************************** 5. row ***************************
Grants for tiq@%: GRANT SELECT ON `tuc`.`ipost` TO 'tiq'@'%'
5 rows in set (0.00 sec)

mysql>

对比发现授权没有问题。

 

然后查看其中一个视图在正式、测试环境中的定义。

正式环境查看视图的定义：

mysql> show create view ipost\G
*************************** 1. 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
1 row in set (0.00 sec)

mysql>

 

测试环境查看视图的定义：

mysql> show create view ipost\G
*************************** 1. 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
1 row in set, 1 warning (0.00 sec)

mysql>

从这里可以发现，结果显示有个一个warning存在。

查看该warning的具体内容：

mysql> show warnings;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message                                                      |
+-------+------+--------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('uadmin'@'%') does not exist |
+-------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

从这里可以看出，视图创建的时候，将正式环境的用户带了过来，但是测试环境并不存在该用户。其实原因很简单，就开发从ide中直接从正式环境把定义语句导出后在测试环境执行了。