华丹快速开发平台使用Mysql作为系统库或连接Mysql业务库时,访问视图有时会报:java.sql.SQLException: Access denied for user 'hdsj'@'%' (using password: YES) 。

产生这种错误基本上确定是:用root创建的视图,而连接数据库时则采用其它用户。如果用同一用户创建的视图不会有权限问题。

这种情况下,首先要保证通过grant授权,形如grant all privileges on hddb.* to hdsj@% identified by 'xxx'.

通过grant授权,数据表等对象是可以访问,但视图有时还不行。

产生这个问题的原因是因为Mysql视图的安全性规则引起的。我们导出视图SQL如下:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `sv_role`AS select `st_role`.`SUBSYS` AS `SUBSYS`,`st_role`.`ROLEID` AS `ROLEID`,concat(`st_role`.`SUBSYS`,'.',`st_role`.`ROLEID`) AS `SUBSYSROLEID`,`st_role`.`ROLEDESC` AS `ROLEDESC`,`st_role`.`ISOUTACCESS` AS `ISOUTACCESS`,`st_role`.`REMARK` AS `REMARK` from `st_role` ;

可以看出通用视图SQL创建后,Mysql添加了ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER这段安全性设置。

安全性为DEFINER时,只有对应定义者拥有对应的权限,才能执行,与当前用户是否有权限无关。如下图:

而当安全性为INVOKER时,只要执行者有执行权限,就可以成功执行。

于是解决方案如下:

1、设置安全性为INVOKER

用root用户登录,在视图设计窗口的“高级”面板中,将视图安全性设置为INVOKER,单击保存。这里必须用root用户或创建者登录修改,否则会报错:1277:Access denied; you need (at least one of) the SUPER privilege(s) for this operation。如下图

2、将定义者设置为对应用户,定义者中初始写的是'root@%',将'root@%'改成了'hdsj@%',问题解决。如下图

3、用业务用户重新创建视图,但需要注意是视图SQL中不要带DEFINER=`root`@`%` SQL SECURITY DEFINER,形如:

CREATE VIEW `sv_role` AS select `st_role`.`SUBSYS` AS `SUBSYS`,`st_role`.`ROLEID` AS `ROLEID`,concat(`st_role`.`SUBSYS`,'.',`st_role`.`ROLEID`) AS `SUBSYSROLEID`,`st_role`.`ROLEDESC` AS `ROLEDESC`,`st_role`.`ISOUTACCESS` AS `ISOUTACCESS`,`st_role`.`REMARK` AS `REMARK` from `st_role` ;

其实第2、3两种方法原理是一样的,但前提是必须保证其它用户不会访问这个视图,否则其它用户也会有无法访问的问题。