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

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

[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中直接从正式环境把定义语句导出后在测试环境执行了。

上一篇:java实现满天星swing&awt


下一篇:深入理解为什么Java中方法内定义的内部类可以访问方法中的局部变量