MySQL迁移后提示查询view权限不足的处理

一.问题描述：

开发人员反应xx物流系统mysql查询视图提示权限不足,请DBA介入处理.

MariaDB [abc]> select * from c_time;

ERROR 1045 (28000): Access denied for user 'lsa'@'%' (using password: YES)

版本：mariadb 10.1.18 for centos 6.6

二.问题分析：

查看lsa'@'%帐号权限如下：

MariaDB [(none)]> show grants for lsa @'%';

+--------------------------------------------------------------------------------------------------------+

| Grants for lsa@% |

+--------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'lsa'@'%' IDENTIFIED BY PASSWORD 'xxxxxxxx' |

| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW ON `abc`.* TO 'lsa'@'%' |

+--------------------------------------------------------------------------------------------------------+

以上说明已存在show view查询视图的权限，为啥还报这个错，是否为开发人员操作不当引起，如是dba亲自验证用lsa帐号登入查询视图试试，确认问题存在，猜想难道是show view权限 

不生效引起，如是，重新刷新权限：

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

经过权限刷新操作后，问题还是依然存在，如下：

MariaDB [abc]> select * from c_time;

ERROR 1045 (28000): Access denied for user 'lsa'@'%' (using password: YES)

用lsa帐号查询其它表都正常，用mysql的root帐号登入查询视图，此时报错又不一样：

MariaDB [(none)]> use abc

Database changed

MariaDB [abc]> select count(*) from c_time;

ERROR 1449 (HY000): The user specified as a definer ('abcadmin'@'%') does not exist

不过以上报错提示比较明显，'abcadmin'@'%'帐号不存在这个视图，如是我们查看视图的定义结构：

MariaDB [abc]> show create view c_time\G

*************************** 1. row ***************************

View: c_time

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`abcadmin`@`%` SQL SECURITY DEFINER VIEW `c_time` AS 

............

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set, 1 warning (0.00 sec)

上面可看出视图的DEFINER为`abcadmin`@`%` ,abcadmin帐号是用于给开发执行ddl操作的，由于自助平台完全代替了手工执行ddl脚本，所以这次迁移到南海就没有再建立abcadmin。

三.问题解决:

上面找到原因后，解决就比较方便。

方法一：建立abcadmin帐号

建立abcadmin帐号后，可以正常查询：

 MariaDB [abc]> select count(*) from c_time;

+----------+

| count(*) |

+----------+

| 440069 |

+----------+

1 row in set (0.96 sec)

方法二：修改DEFINER

将DEFINER=`abcadmin`@`%`修改DEFINER=`lsa`@`%` 

alter ALGORITHM=UNDEFINED DEFINER=`lsa`@`%` SQL SECURITY DEFINER VIEW `c_time` AS 

............

修改definer后，也可正常查询：

MariaDB [abc]> select count(*) from c_time;

+----------+

| count(*) |

+----------+

| 440069 |

+----------+

1 row in set (0.96 sec)

最后权衡分析，我们采用方法二解决问题。